普段Google BigQueryでは簡単な集計SQLしか使っていないのですが、集計SQL以外にも色々便利な機能があります。
ただ、使用頻度が高くないので、いざ使おうとするとマニュアルを探すところから始まるので、自分用に簡単な使い方をまとめておこうと思います。
ここではビュー・マテリアライズドビュー・テーブル関数をまとめました。
ビュー
- いわゆるビュー
- SELECTクエリをビューとして保存できる
- ビューを他のクエリから呼び出すことができる
- 複数ステートメントクエリをビューとすることはできない
- パーティショニングできない
マテリアライズドビュー
- キャッシュを持ったビュー
- 1度ビューを呼び出すと、結果がキャッシュされ、次回以降はキャッシュを参照して元データ呼出や演算が行われないので高速になる
- 元データが更新されると更新された箇所に関するキャッシュの再計算が行われる
- キャッシュに対してストレージ料金が発生する
- クエリ制約
- クエリでは一部の集計関数しか使えない
- クエリで使える集計関数でも、その関数の結果で更に演算することはできない
- クエリでOUTER JOINが使えない
- パーティショニングできるが、取り込み時間パーティショニングには使えない
- プロジェクトをまたいだテーブルの呼出はできない
- パーティショニングを使う時は、ソースのパーティショニング列をそのままビューのカラムに出力する
制約が多いので、分割テーブル(取り込み時間パーティショニングでない)の単純な変換ビューに特化してして使うのが無難。
例
/* materialized view */ CREATE OR REPLACE MATERIALIZED VIEW `project_name.dataset_name`.test_materialized_view PARTITION BY DATE(created_at) AS ( WITH tbl AS ( SELECT created_at, val_01 FROM `project_name.dataset_name`.table_name ) SELECT created_at, val_01*10 AS val_10 FROM tbl ) ; /* check */ SELECT * FROM `project_name.dataset_name`.test_materialized_view WHERE DATE(created_at)="2022-01-01" LIMIT 100
テーブル関数
- テーブルを返すユーザー定義関数
- 変数を渡せるビューのようなもの
- ビューより使い勝手がいい
パーティショニングできるビューが欲しいと思うことがあるが、それに相当するものがテーブル関数で作れる。
例
/* table function */ CREATE OR REPLACE TABLE FUNCTION `project_name.dataset_name`.test_table_function( param_dt DATE, param_int INT64, param_any ANY TYPE ) AS ( SELECT param_dt AS val_dt, param_int AS val_int, param_any AS val_any ); /* check */ SELECT * FROM `project_name.dataset_name`.test_table_function( DATE("2022-01-01"), 123, "ABC" ) LIMIT 100