case-kの備忘録

日々の備忘録です。データ分析とか基盤系に興味あります。

BigQuery

BigQueryに書き込まれたSQL Serverの変更ログを用いて、変更のあったPKの最新データと変更前のデータを取得する方法

ZOZO Advent Calendar 2022 カレンダー25日目の記事です qiita.comBigQueryに書き込まれたSQL Serverの変更追跡ログを使って、変更のあったPKの変更ログと変更前のログを取得する方法をご紹介します。ZOZOではSQL Serverの変更追跡機能を使い、変更のあったP…

BigQuery Flex SlotsをPython版に置き換えた

bashでやっていたがエラーハンドリングが色々きつかったのでPythonに書き換えました。ドキュメントは不十分だったのでGitHub見ながら作る感じになります。 一通り機能はあるので同じようなことしようとしてる方の参考になれば幸いです。techblog.zozo.com コ…

embulk-filter-hashやRuryのハッシュ値と同じ値をBigQueryでがんばる

データ基盤旧環境で古い環境のハッシュ変換値をBigQueyの関数で実行した際の備忘録です。MD5の計算まではどの言語を使っても同じですが変換後の処理はそれぞれのライブラリや関数によって違うのでドキュメントや直接コードの確認が必要です。BigQueryのMD5関…

Cloud LoggingでBigQueryにシンクしたカンマ区切りのデータをスキーマ単位で分割する

アプリケーションのログCloud Loggingに出力してますがBigQuerで扱いたかったので整形しました。1カラム(jsonPayload.message)に次のような形でデータが入っています。 col_1: value_1, col_2: value_2, col_3: value_3, col_4: value_4 配列にして要素を取…

BigQueryの個人情報にポリシータグによるカラムレベルのアクセス制御を適用したまま、利用者にマスクしたカラムの閲覧権限を与える方法

本記事はZOZOテクノロジーズ #1 Advent Calendar 2020 - Qiita 25日目の記事です。本記事ではBigQueryにある個人情報に対してカラムレベルのアクセス制御を行うポリシータグを付与したまま、利用者にマスク処理したカラムの閲覧権限を与える方法についてご紹…

Data CatalogにオンプレDBのスキーマを同期させて、バッチ・ストリーミングデータ基盤(BigQuery)のスキーマ反映を自動化する

本記事はZOZOテクノロジーズ #1 Advent Calendar 2020 - Qiita 24日目の記事です。バッチ方式の日次データ基盤とストリーミング方式のリアルタイムデータ基盤のスキーマ反映でData Catalogがどのように役立つのか概要も踏まえてご紹介できればと思います。後…

データ基盤におけるBigQuery Flex Slots導入メリットとオンデマンド自動切り替えの必要性

本記事はZOZOテクノロジーズ #1 Advent Calendar 2020 - Qiita 22日目の記事です。 Flex Slotsの概要や導入のメリット、データ基盤における活用用途をご紹介できればと思います。 また、Flex Slotsの購入が失敗した際にオンデマンドに自動で切り替える必要性…

BigQueryのINFORMATION_SCHEMAを使い、対象テーブルが日付サフィックステーブルかに判定してみる

備忘録です。データ連携の転送ファイルを自動生成するために、対象テーブルのカラムの取得に加えて、テーブルが日付サフィックステーブルか判定する必要がありました。直接テーブルに対してクエリを発行して調べるとお金が高くかかってしまうので、INFORMATI…

BigQueryのFlex Slotsで柔軟にスロットを購入してジョブを制御する

BigQueryのFlex Slotsを試して見ました。バッチで重たいタスクの前にFlex Slotsを購入し、タスク完了後Flex Slotsを元に戻します。 BigQuery Flex Slotsについて やりたいこと 実行コマンド Commitment Reservation Removement 環境情報 権限 BQコマンドの認…

React-ReduxとExpressでBigQueryからデータを取得するAPIを作る

React-ReduxとExpressでBigQueryからデータを取得するAPIを作りました。株価のアプリを作ろうと思い、その過程を記事にしている備忘録となります。 セットアップ 必要なモジュール 前提 サービスアカウントキー クライアントライブラリでAPIを作る アクショ…

BigQueryのウィンドウ処理

BigQueryウィンドウ処理の備忘録です。 Dataset RANK() LAG/LEAD ROW_NUMBER() Dataset console.cloud.google.com RANK() ベンダーごとの旅客数が多いレコード順にランクを付与します。 SELECT vendor_id, passenger_count, RANK() OVER (PARTITION BY vendo…

Python Client GBQ & GCS

Code library Read From GBQ To Local Upload From Local To GCS Read From GCS Load From GCS To GBQ Code # python_client_gbq_gcs.py github.com library !gcloud auth application-default login !pip install --upgrade google-cloud-bigquery !pip ins…

Dataflow Google BigQuery I/O connector:Python

Dataflowを使ってBigQueryからBigQueryに書き込む処理とCloud StorageからBigQueryに書き込む処理をします。 Code options GBQ to GBQ GCS to GBQ beam.apache.org Code github.com options # -*- coding: utf-8 -*- import apache_beam as beam from apache…

BigQueryマスタ更新のベストプラクティスと見解

こちらの記事でベストプラクティスは以下と紹介されており、実運用を考慮した際、現実的には「Data Loadingを利用して追加」する方法が良いようです。 この方法は1テーブルにレコードを追加し、最新のTIME STAMPを参照するVIEWテーブルを作り参照させる方法…

Cloud Functionsで検知したファイルをBigQueryに書き込む

Cloud FunctionsでバケットにアップしたファイルをBigQueryに書き込む処理をします。 code Cloud Functionsとは 制限事項 Cloud Functionsを試す 事前準備 動作確認 code github.com Cloud Functionsとは イベントを検知し処理を実行するトリガーの役割があ…

Jupyter NotebookでBigQueryを使う方法

JupyterからBigQueryを使うことがよくあるので備忘録として残しておきます。 PythonクライアントライブラリをインストールすることでJupyterから実行できます。 # auth gcloud auth application-default login # install python client pip install --upgrad…

BigQueryのStandard SQLで四分位計算

GBQのStandard SQLでの四分位計算方法を備忘録として残しておきます。 これまで中央値などの計算はGBQのレガシーSQLを使っていたのですが、制限等何かと不便でしたので調べてみました。 SELECT day , min, percentiles[offset(25)] AS per_25, percentiles[o…

BQコマンド備忘録

備忘録です。BigQueryの集計結果をサーバで扱いたい場合、以下の方法が考えられます。gsutilだと一度GCSにデータを保存する必要があるので手っ取り早く手元にデータを落としたい場合、BQコマンドは便利です。データサイズが大きい場合や取得に時間がかかるの…

分析基盤をPostgreSQLからBigQueryに移行させた時の知見

PostgreSQLベースのBIツールの参照DBをBigQueryに移行したことで、 パフォーマンスが大きく改善(30分以上 -> 10秒)したので、その時の知見をメモとして残しておきたいと思います。 経緯 BigQueryの選定理由 1. 現行がRDBベース 2. 集計結果のデータサイズが…

BigQueryでカンマ区切りのデータの分割方法

BIgQueryでカンマ区切りのデータを分割する方法をご紹介します。データはここに置いておきます。 github.com サンプルの中身はカンマ区切りのデータとなっています。 SQLを実行して、以下のようにカンマ区切りのデータ項目を分割することが本記事の目的です…

BigQueryでサイズの大きいデータをGCSに出力する方法

データ分析する場合BigQueryで集計し、集計結果をGCSに出力、Jupyterで分析することが多いかと思います。 集計結果が大きいとそのままCloud Storageに出力することはできません。集計結果をBigQueryのテーブルに出力し、Cloud Storageへエクスポートを選ぶと…

BigQueryで標準時(UTC・EST)をJSTに変換してみた

時系列データでタイムスタンプがJSTでないことはまあまああるかと思います。BigQueryでJSTに変換する方法を記事にしました。 UTC - > JSTとEST -> JST変換をします。データは以下の形式ならそのままクエリ実行できると思います。 「2018-05-12 21:02:20」 Bi…

BigQueryにタブ区切りのTSVファイル[テキスト]を全件ロードする方法

ただの備忘録です。業務でタブ区切りの8万のTSVファイル(テキスト)をBigQueryに全件ロードする方法を探してた際のtipsです。ログデータを扱う場合、タブ区切りになっていることがあるかと思います。bqコマンド使ったロード方法やサポートされているデータ形…

【GCP入門】Google BigQuery データセット作成からデータ抽出までハンズオン形式で行います

今回はGCPが提供するGoogle BIgQueryに関する記事を書きます。データ読み込み・出力方法やクエリ検索まで備忘録も兼ねて記事を書きます。 この記事の目的 BigQueryとは BigQueryのメリット・デメリット メリット デメリット クエリ備忘録 集計関数 条件付き…