TL;DR;
UDF を独自実装する前に、bqutil.fn
を眺めておくと車輪の再発明が回避できるかも
背景
SQL は、特定の処理を行う際にデータの型が同一でないとエラーが発生しますが、もとのスキーマを紹介するよりももっとお手軽にカラムの型を確認したいときがありませんか?
例えば、出力結果を見ただけでは、12345
が STRING なのか INT64 なのか判別不可能ですよね。(もし判別可能な方法知っている人いたら教えて下さい…)
GCP による OSS UDF の bqutil.fn
なのでお手軽に BigQuery の結果の型を確認したい時になにか良い方法がないかなと調べていたら、OSS でbqutil.fn
という UDF が GCP から提供されていた。
例えば型の確認の場合、以下の ユーザー定義関数(UDF) はどの GCP プロジェクトから実行しても実行可能
|
|
この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.
実際に以下のクエリを Google BigQuery で実行すると
|
|
|
|
の結果が出力され、各カラムのデータの型を確認できる。
便利!
これで置き換え可能な UDF は置き換えればメンテンスしないといけない UDF が削減されて嬉しいですね
bqutil.fn.typeof()
の UDF の実態としては
bigquery-utils/udfs/community/typeof.sqlxが参照されて実行されている。
他にも
- URL の key を抽出できるbqutil.fn.url_keys()
- ランダムな値を出力できるbqutil.fn.url_keys()
など、自前で正規表現で頑張って書いているけど実際は OSS の UDF として公開されているケースも多々ありそうなかゆいところに手が届く UDF が多数公開されていた。
変わり種としては、 StatsLib: Statistical UDFsという統計的な処理を行う UDF も公開されていた。
線形回帰や p 値の計算ができる UDF も公開されており、面白い
References
See Also
- 遅すぎる `pandas.read_gbq` を使わずに、Google BigQueryから高速にデータを読み込む
- Standard SQLのCOALESCEで、時間経過によってカラム名が変化したデータを柔軟に抽出する
- Google Cloud Pub/Sub に公開された結果はDataflow template を使えばめちゃくちゃ簡単に確認できる
- CloudComposer のDAGをCircleCIで更新する
- GCPのCloud Composer のDAGを素早く・簡単にデバッグする