BigQueryの少し凝った使い方メモ（ビュー・マテリアライズドビュー・テーブル関数）

普段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