普段Google BigQueryでは簡単な集計SQLしか使っていないのですが、集計SQL以外にも色々便利な機能があります。
ただ、使用頻度が高くないので、いざ使おうとするとマニュアルを探すところから始まるので、自分用に簡単な使い方をまとめておこうと思います。
ここではプログラミング関連をまとめました。
「BigQueryの少し凝った使い方メモ」記事一覧
- BigQueryの少し凝った使い方メモ（ビュー・マテリアライズドビュー・テーブル関数）
- BigQueryの少し凝った使い方メモ（テーブル作成・データ更新）
- BigQueryの少し凝った使い方メモ（スケジュールされたクエリ）
- BigQueryの少し凝った使い方メモ（プログラミング）
- BigQueryの少し凝った使い方メモ（etc）
関数
- ユーザー定義関数を作れる
- 値を返す
- テーブルは返せない。テーブルを返すのはテーブル関数で別にある
- 戻り値生成に複雑なSQL文が書ける
AS以降は(value)- 返す値を
()で囲む - 値をSQLで生成する場合、値は
(SQL文)となる - よって、
AS以降はAS ((SQL文))となる
- 返す値を
- 一時関数
- ステートメント実行期間中のみ有効
- 複数ステートメントでの利用が前提
一時関数
/* temp function */ CREATE TEMP FUNCTION test_function( param_01 INT64, param_02 STRING ) AS ( ( SELECT param_01 ) ); /* use temp function */ SELECT test_function(123, "ABC") ;
外部関数
単一ステートメントで使用する場合は外部関数にする
/* function */ CREATE OR REPLACE FUNCTION `project_name.dataset_name`.test_function( param_01 INT64, param_02 STRING ) AS ( ( SELECT param_01 ) ); /* check function */ SELECT `project_name.dataset_name`.test_function(123, "ABC") ;
複数の値を返す
複数の値を返したい場合はSTRUCTを返す
/* temp function */ CREATE TEMP FUNCTION test_function( param_01 INT64, param_02 STRING ) AS ( ( SELECT STRUCT<val_01 INT64, val_02 STRING>(param_01, param_02) ) ); /* use temp function */ SELECT test_function(123, "ABC").val_01, test_function(123, "ABC").val_02 ;
変数・BEGIN..END
DECLAREは文の頭で書かなければいけないDEFAULTで宣言と代入を同時に行えるDEFAULTは省略可能DEFAULTがあると型省略可- 変数の値をSELECT文の結果でセットできる
- 変数の値を関数の戻り値でセットできる
- テーブルは変数にできない
- テーブルを変数にするには一時テーブルを使う
BEGIN..ENDは変数のローカルスコープを作るBEGIN直下でDECLAREを書ける
例
/* var */ DECLARE x INT64 DEFAULT 0; DECLARE y STRING; DECLARE z DEFAULT 123; SET y=CONCAT("A", "B", "C"); SET z=(SELECT 123); /* use var */ SELECT x, y, z; /* declare in begin-end */ BEGIN DECLARE a DEFAULT 123; SELECT a; END;
プロシージャ
- 複数ステートメント文を呼び出すことができる
- パラメータを渡せる
CALLで呼び出す- テーブルを返すことはできない
例
/* procedure */ CREATE OR REPLACE PROCEDURE `project_name.dataset_name`.test_procedure( param_01 INT64, param_02 STRING ) BEGIN DECLARE x INT64; DECLARE y STRING; SET x=param_01; SET y=param_02; END ; /* call */ CALL `project_name.dataset_name`.test_procedure(123, "ABC");
一時テーブル
- テーブルを変数にしたようなもの
- ステートメント実行期間中のみ有効
- 別のステートメントにテーブルを渡すのに使う
例
/* temp table */ CREATE TEMP TABLE temp_table AS ( SELECT 123 AS val_01, "ABC" AS val_02 ) ; /* use temp table */ SELECT * FROM temp_table ;
テーブルのループ処理
- テーブルの1行毎に処理を実行することができる
- テーブルの行は
STRUCTに入ってくる - 処理順序未定
- 処理順序を明確にする場合は
ORDER BYで並びを指定する
例
/* temp table */ CREATE TEMP TABLE temp_table AS ( SELECT val_01, 1 AS val_02, FROM UNNEST(GENERATE_DATE_ARRAY("2022-01-01", "2022-01-3", INTERVAL 1 DAY)) AS val_01 ) ; /* for loop */ FOR line IN(SELECT * FROM temp_table ORDER BY val_01) DO BEGIN DECLARE x DEFAULT line.val_01; DECLARE y DEFAULT line.val_02; SELECT x, y; END; END FOR ;
テーブル・カラムを変数で指定するのは難しい
- 変数でテーブル名を指定して、そのテーブルの値を取得
- 変数でカラム名を指定して、そのカラムでフィルタリングした値を取得
上記のようなクエリを書きたい時があるが、そういった、動的なクエリ生成を、BigQueryの機能だけで実現するのは難しい。
EXECUTE IMMEDIATEで、任意のSQL文字列をSQL文として実行することは可能だが、戻り値は変数に限られ、テーブルを返すことはできない。
デバッグ表示は難しい
長時間実行するクエリで、途中経過をコンソール出力して確認したい時があるが、SELECTでの出力はクエリ実行完了後にしか表示されないので難しい。
代案としてログをテーブル出力する方法が考えられるが手軽ではない。
