bigquery

TL;DR; UDF を独自実装する前に、bqutil.fnを眺めておくと車輪の再発明が回避できるかも 背景 SQL は、特定の処理を行う際にデータの型が同一でないとエラーが発生しますが、もとのスキーマを紹介するよりももっとお手軽にカラムの型を確認したいときがありませんか? 例えば、出力結果を見ただけでは、12345 が STRING なのか INT64 なのか判別不可能ですよね。(もし判別可能な方法知っている人いたら教えて下さい…) GCP による OSS UDF の bqutil.fn なのでお手軽に BigQuery の結果の型を確認したい時になにか良い方法がないかなと調べていたら、OSS でbqutil.fnという UDF が GCP から提供されていた。 例えば型の確認の場合、以下の ユーザー定義関数（UDF) はどの GCP プロジェクトから実行しても実行可能 1 bqutil.fn.typeof() このbqutil.fn はbigquery-utils/udfs/community/のディレクトリに格納されている UDF がbqutil という GCP プロジェクトのfn データセットに同期されているので、どの GCP プロジェクトの Google BigQuery から実行しても bqutil.fn.typeof()を実行可能にしているらしい。 頭良い This directory contains community contributed user-defined functions to extend BigQuery for more specialized usage patterns. Each UDF within this directory will be automatically synchronized to the bqutil project within the fn dataset for reference in queries....

データの蓄積帰還が長くなってくると、例えば JSON 形式でログを取っているが、同じデータでもマイグレーションやロギングロジックの更新などでkey の名前が変化したりする場合がある。 その場合取り扱いに困るのが、古い key と新しい key をどのように併合するかだ。 例えば特定の日次できれいにデータが入れ替わっているのなら、色々やりようがあるが、クライアントなどのログの場合データの変化も均一ではないので、徐々に変化していることが大半なので、日次で別々の抽出をして結合するというアプローチも難しい。 その際に役立つのが Standard SQL 条件付き構文の COALESCE だ。 COALECSCE は、引数の最初の非 NULL の値を返す関数で、 1 COALESCE(NULL, 'B', 'C') だと Bが返される。この関数を使うことで、複数カラムを一つに併合することができる。 具体例を交えつつ実践してみる 例えば、以下のように昔のカラム名が title で、全く同じデータが新しいカラムの title_v2 に入ってきているとする。 NOTE: json を例題に key の抽出にしたほうが実際の状況に沿いますが、カラムのみで表現したほうが説明が簡単なので今回はそちらを採用。 用意したデータ 1 2 3 4 5 6 7 8 WITH menues AS (SELECT "うどん" as title, NULL as title_v2, "2021/10/06" as created UNION ALL SELECT "ラーメン", NULL, "2021/10/07" UNION ALL SELECT NULL, "そば", "2021/10/08" UNION ALL SELECT "カツ丼", NULL, "2021/10/09" UNION ALL SELECT "カツ丼", "カツ丼", "2021/10/10" UNION ALL SELECT NULL, "カレー", "2021/10/11") SELECT * FROM menues title title_v2 created うどん 2021/10/06 ラーメン 2021/10/07 そば 2021/10/08 カツ丼 2021/10/09 カツ丼 カツ丼 2021/10/10 カレー 2021/10/11 2021/10/10 のデータなどは旧カラムと新カラムにダブルライトされています。...

pandas.read_gbq 便利ですよね。 クレデンシャルファイルを認証画面からコピペすれば Jupyter Notebook 上で簡単に認証され、Google BigQuery が実行されてその結果がそのままデータフレームとして扱えます。 Jupyter Notebook と Google BigQuery を連携させたいときは愛用していました(過去形)。 問題点 そこそこ大きなデータを持ってこようとすると、めちゃくちゃ遅くてストレスが凄い 解決方法として、Google BigQuery で巨大なデータをダウンロードする方法について書きます。 実は Google の公式ドキュメントでも推奨されています。 https://cloud.google.com/bigquery/docs/pandas-gbq-migration https://cloud.google.com/bigquery/docs/bigquery-storage-python-pandas 方法は以下の２つ。 google-cloud-bigquery をインストールして、マジックコマンドで Google BQ を実行 BQ 実行 →BigQuery table として保存 →GCS へ保存 → gsutil でマシンへコピー 1 番目は、Jupyter 上でマジックコマンドで Google BQ が実行できて、速度も pandas.rad_gbq よりも高速です 2 番目はそもそも実行結果が巨大な場合で、目安としては1GB以上なら 2 番目の方法を使えば楽です。 1, google-cloud-bigquery をインストールして、Jupyter Notebook のマジックコマンドで Google BQ を実行 1 pip install --upgrade google-cloud-bigquery[bqstorage,pandas] magic command を実行...