BigQueryクエリパラメータまとめ

BigQuery Advent Calendar 2020の12日目の記事です。

BigQueryのクエリパラメータについて、詳細なドキュメントが見つけにくかったり、ハマったりしたことがあったのでまとめてみました。

クエリパラメータ

クエリパラメータを使う

他のRDBMSでもよく使われるクエリパラメータ（バインド変数）をBigQueryでも利用できます。
アプリケーションから、抽出条件や集計条件をパラメータとして渡したり、SQLインジェクションを防ぎます。

基本的なことは以下のドキュメントに記載されています。
パラメータ化されたクエリの実行  |  BigQuery  |  Google Cloud

名前付きパラメータを利用する場合、以下のように @ の後に識別子を付けます。
呼び出し側では、各パラメータに値をセットします。

SELECT
    timestamp,
    user_id,
FROM
    foo_log
WHERE
    timestamp >= @begin_date and
    timestamp < @end_date

パラメータは、識別子、列名、テーブル名、などには利用できません。
あくまで任意の式を置き換えるものとして利用します。
以下のようなことがしたい時は、動的SQLを利用します。
標準 SQL のスクリプト  |  BigQuery  |  Google Cloud

-- NG
SELECT
    timestamp,
    user_id,
FROM
    foo_log
ORDER BY
    -- クエリパラメータでソート条件のカラム名を切り替えるようなことはできない
    @sort_column

-- NG
SELECT
    timestamp,
    user_id,
FROM
    foo_log
WHERE
    -- IS TRUEやIS FALSEは、その連なり自体が識別子なので、クエリパラメータで識別子の一部を置き換えることはできない
    some_flag IS @some_flag

Web UIでクエリパラメータを使う

各種言語のSDKから利用したりCLIで利用したりできますが、Web UIからクエリパラメータを直接利用することは、現時点では残念ながらできません。
アプリケーションからSDK経由で条件を指定するためにクエリパラメータを使用している場合、実際に利用されるクエリを書き換えずに、 単体でテストをしたいことがよくあります。
クエリパラメータを利用しているクエリをWeb UIでサクッと実行できないのは、少々不便です。
この辺り、みなさんどのように扱っているのでしょうか。

無理やりWeb UIでクエリパラメータを利用するならば、

EXECUTE IMMEDIATE """
SELECT @param
"""
USING
1 AS param

こんな感じで、動的SQLを利用すればできなくはありません。
実際にアプリケーションから利用されるクエリ自体を書き換える必要はありませんが、クエリは単なる文字列になってしまい構文解析されないので見にくくなります。（補完もされません）

CLIでクエリパラメータを使う

bq query コマンドを利用してクエリを実行します。
--parameter オプションでクエリパラメータを指定することができます。

$ bq help query
Python script for interacting with BigQuery.


USAGE: bq.py [--global_flags] <command> [--command_flags] [args]

省略...

--parameter: Either a file containing a JSON list of query parameters, or a query parameter in the form "name:type:value". An empty name produces a positional parameter. The
    type may be omitted to assume STRING: name::value or ::value. The value "NULL" produces a null value.;
    repeat this option to specify a list of values

省略...

--parameter の値として パラメータ名:型:値 のように指定します。

$ bq query --parameter='param:INT64:1' 'select @param'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
|   1 |
+-----+

型を省略するとSTRINGとして扱われます。
文字列の値をクオートする必要はありません。

$ bq query --parameter='param::A' 'select @param'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
| A   |
+-----+

複数のパラメータを渡す時は --parameter オプションを並べます。

$ bq query --parameter='param1::A' --parameter='param2::B' 'select @param1, @param2'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+-----+-----+
| f0_ | f1_ |
+-----+-----+
| A   | B   |
+-----+-----+

NULLを値として指定できます。

$ bq query --parameter='param:STRING:NULL' 'ASSERT @param IS NULL'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
Assertion successful

値として指定するNULLは大文字である必要があります。
小文字だとエラーになります。

$ bq query --parameter='param:STRING:null' 'ASSERT @param IS NULL'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
Error in query string: Error processing job 'youreproject:bqjob_xxxx': Assertion failed

空文字列を指定する場合は値を省略します。

$ bq query --parameter='param::' 'select @param'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
|     |
+-----+

型を ARRAY<TYPE> とすることで配列も渡せます。

$ bq query --parameter='param:array<string>:["A", "B", null]' 'select a from unnest(@param) a'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+------+
|  a   |
+------+
| A    |
| B    |
| NULL |
+------+

型を STRUCT<field TYPE> とすると、構造体を渡すこともできます。

$ bq query --parameter='param:STRUCT<foo INT64, bar STRING>:{"foo": 1, "bar": "A"}' 'select @param'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+-----------------------+
|          f0_          |
+-----------------------+
| {"foo":"1","bar":"A"} |
+-----------------------+

なぜかstructが小文字だとエラーになります。（これはハマった！）

$ bq query --parameter='param:struct<foo INT64, bar STRING>:{"foo": 1, "bar": "A"}' 'select @param'
BigQuery error in query operation: Invalid value for type: STRUCT<FOO INT64, BAR STRING> is not a valid value

構造体のフィールドの値にnullを指定する時は、小文字で記述しないとエラーになります。（これもハマった！）
通常の型の値にNULLを指定する際は、逆に大文字である必要があったのと対照的です、、、

$ bq query --parameter='param:STRUCT<foo INT64, bar STRING>:{"foo": null, "bar": "A"}' 'select @param'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+------------------------+
|          f0_           |
+------------------------+
| {"foo":null,"bar":"A"} |
+------------------------+

構造体の配列を渡すこともできます。
ちょっとしたダミーテーブルを渡すのにも使えます。

$ bq query --parameter='data:ARRAY<STRUCT<timestamp TIMESTAMP, user_id STRING>>:[{"timestamp": "2020-12-01 12:34:56+09:00", "user_id": "aaa"}, {"timestamp": "2020-12-02 23:45:00+09:00", "user_id": "bbb"}]' 'select * from unnest(@data)'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+---------------------+---------+
|      timestamp      | user_id |
+---------------------+---------+
| 2020-12-01 03:34:56 | aaa     |
| 2020-12-02 14:45:00 | bbb     |
+---------------------+---------+

クエリパラメータをJSONファイルで指定する

ここまで複雑になってくると、オプションに直書きで指定するのは大変になってきますね。
そういえば、さきほど bq query コマンドのヘルプを見た時、JSONって表示されていた気がします。

--parameter: Either a file containing a JSON list of query parameters, or a query parameter in the form "name:type:value". An empty name produces a positional parameter. The
    type may be omitted to assume STRING: name::value or ::value. The value "NULL" produces a null value.;
    repeat this option to specify a list of values

どうやらJSONファイルにパラメータをまとめて指定できるようです。
このJSONファイルの形式について、ドキュメントを探したのですがなかなか見つけられず困りました。
下記のAPIリファレンスに記載がありました。
QueryParameter  |  BigQuery  |  Google Cloud

この形式に従ったJSONファイルを作成しパラメータを指定します。

[
    {
        "name": "data",
        "parameterType": {
            "type": "ARRAY",
            "arrayType": {
                "type": "STRUCT",
                "structTypes": [
                    {
                        "name": "timestamp",
                        "type": {
                            "type": "TIMESTAMP"
                        }
                    },
                    {
                        "name": "user_id",
                        "type": {
                            "type": "STRING"
                        }
                    }
                ]
            }
        },
        "parameterValue": {
            "arrayValues": [
                {
                    "structValues": {
                        "timestamp": {
                            "value": "2020-12-01 12:34:56+09:00"
                        },
                        "user_id": {
                            "value": "aaa"
                        }
                    }
                },
                {
                    "structValues": {
                        "timestamp": {
                            "value": "2020-12-02 23:45:00+09:00"
                        },
                        "user_id": {
                            "value": "bbb"
                        }
                    }
                }
            ]
        }
    }
]

以下のように --parameter オプションの値にそのJSONファイルへのファイルパスを指定すると、利用することができます。

$ bq query --parameter='parameter.json' 'select * from unnest(@data)'
Waiting on bqjob_xxxx ... (0s) Current status: DONE
+---------------------+---------+
|      timestamp      | user_id |
+---------------------+---------+
| 2020-12-01 03:34:56 | aaa     |
| 2020-12-02 14:45:00 | bbb     |
+---------------------+---------+

クエリパラメータとキャッシュ

クエリパラメータを利用しても、内部の値が変われば同一クエリとして扱われず、キャッシュは効かないようです。
Oracleのように、バインド変数を使うことでハードパースによる実行計画をキャッシュするような効果はありません。

Web UIのようにキャッシュが使われたかどうかが表示されればいいのですが、CLIでは表示させる方法を見つけられませんでした。
下記のようにして、キャッシュが使われたかどうかを確認しました。

# --require_cacheオプションを使うと、キャッシュを利用できないクエリの場合エラーになる
$ bq query --parameter='param::A' --require_cache 'select @param'
BigQuery error in query operation: Error processing job 'yourproject:bqjob_xxxx_xxxx_1': Not found: Table youreproject:_xxxxx_.xxxx was not found in location asia-northeast1

# 一度クエリを実行してキャッシュさせる
$ bq query --parameter='param::A' 'select @param'
Waiting on bqjob_xxxx_xxxxx_1 ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
| A   |
+-----+

# --require_cacheオプションを使っても、エラーにならない -> キャッシュされていたのでキャッシュを利用できた
$ bq query --parameter='param::A' --require_cache 'select @param'
Waiting on bqjob_xxxx_xxxx_1 ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
| A   |
+-----+

# クエリパラメータの値を変更して、--require_cacheオプションを使うと、エラーになる -> キャッシュされていないのでキャッシュを利用できなかった
$ bq query --parameter='param::B' --require_cache 'select @param'
BigQuery error in query operation: Error processing job 'yourproject:bqjob_xxxx_xxxx_1': Not found: Table yourproject:_xxxxx_.xxxx was not found in location asia-northeast1

その他

クエリパラメータとは直接関係ありませんが、クエリ内で値を共通的に利用する方法が他にもいくつかあるので紹介します。
以下の方法（UDF以外）とクエリパラメータを併せて利用することもできます。

定数用のサブクエリを使う

パラメータ用のサブクエリを用意し、クエリ内で使いまわしたい値をまとめておきます。
このサブクエリを CROSS JOIN でJOINして使いまわします。
変更が1箇所で済み、見通しが良くなります。

WITH
    params AS (
        SELECT
            -- ここだけ変更すればいい
            TIMESTAMP('2020-11-01', 'Asia/Tokyo') AS begin_date,
            TIMESTAMP('2020-12-01', 'Asia/Tokyo') AS end_date,
    ),
    log1 AS (
        SELECT
            log.*
        FROM
            foo_log log,
            params
            log.timestamp >= params.begin_date AND
            log.timestamp < params.end_date
    ),
    log2 AS (
        SELECT
            log.*
        FROM
            bar_log log,
            params
        WHERE
            -- log2の抽出対象期間もlog1と同じ期間にしたい
            log.timestamp >= params.begin_date AND
            log.timestamp < params.end_date
    )
SELECT
    log1.*,
    log2.*,
FROM
    log1 LEFT JOIN log1 ON (
        log1.id = log2.id
    )

スクリプトで変数を使う

スクリプトで変数を宣言し、値を代入してからクエリで使います。
クエリが長くなると、変数名なのかカラム名なのか見分けがつきにくくなるかもしれません。

DECLARE param1 INT64;
SET param1 = 1;
SELECT param1;

UDFを使う

必要な値を返すUDFを作成して利用します。

CREATE TEMP FUNCTION answer()
RETURNS INT64
LANGUAGE js
AS
"return 42";

SELECT answer();