https://www.kwbtblog.com/entry/2023/02/28/091849

普段Google BigQueryでは簡単な集計SQLしか使っていないのですが、集計SQL以外にも色々便利な機能があります。

ただ、使用頻度が高くないので、いざ使おうとするとマニュアルを探すところから始まるので、自分用に簡単な使い方をまとめておこうと思います。

ここではプログラミング関連をまとめました。

「BigQueryの少し凝った使い方メモ」記事一覧

関数

ユーザー定義関数を作れる
値を返す
- テーブルは返せない。テーブルを返すのはテーブル関数で別にある
戻り値生成に複雑な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での出力はクエリ実行完了後にしか表示されないので難しい。

代案としてログをテーブル出力する方法が考えられるが手軽ではない。

BigQueryの少し凝った使い方メモ（プログラミング）