BigQueryのクエリ(Google SQL構文)にはParameterized Queryという機能があり、クエリに任意の式を埋め込むことができます。
上記のページより引用すると以下の@genderや@statesのように、@識別子の形式でクエリを書き、実行時にパラメータを渡します。これはNamed Parameter(名前付きパラメータ)と呼ばれます。
SELECT name, SUM(number) AS count FROM `bigquery-public-data.usa_names.usa_1910_2013` WHERE gender = @gender AND state IN UNNEST(@states) GROUP BY name ORDER BY count DESC LIMIT 10;
BigQuery Emulatorもこの機能に対応しており、ほとんどのケースでうまく動作しますが、たまにうまくいかないケースがあります。
うまくいかないケース
以下なぜうまくいかないのか？という話をしますが、BigQuery Emulatorとgo-zetasqlite, go-zetasql, zetasql の関係等々については前提として省略します。
作者であるgoccyさんのスライドがわかりやすいでしょう。
BigQueryエミュレータの作り方 - Speaker Deck
パターン1: zetasqlの解析でエラーになる
このissueで報告されている問題のパターンで、FROMにUNNEST(@param)を書くとエラーになるというものです。
これは確かにエラーになります。
パターン2: zetasqlは解析できるけどgo-zetasqliteでエラーになる
例えばSELECT @paramで@paramが配列などです。
これもエラーになります。
なぜなの?
これはクエリ実行時に渡されたパラメータの型と、クエリを解析した結果のパラメータの型が食い違ってしまうことがあるのが原因です。
食い違ってしまうことがあるのは、bigquery-emulatorとその内部で使っているgo-zetasqliteのパラメータの扱いの違いによります。
bigquery-emulatorでは、一応クエリ実行時に渡された型を使っていますが、go-zetasqliteはパラメータの型を扱いません。*1
じゃあなぜちゃんと動いてるの？
go-zetasqliteは多くの場合ではパラメータの型を使わなくてもうまく動きます。
go-zetasqliteの中で使われていて、BigQueryのSQLパーサであるzetasqlはAllowUndeclaredParametersという設定を用意しています。（go-zetasqliteではこの設定を有効にしています。）
この設定を有効にすると宣言されていない（解析の際にパラメータの型が渡されていない）パラメータは、解析の際にはINT64として扱われてます。
型を無視してINT64として扱っているので上述の「zetasqlの解析でエラーになる」の通り動かないケースもありますが、型のチェックがあまり厳しくないコンテキストや関数では解析時にはINT64として扱うけど、解析結果ではそのコンテキストや関数が期待する型をそのパラメータの型として構文木を作ってくれます。
例えばパラメータがWHERE句で WHERE x = @x という形で使われていた場合、@xはxと同じ型であろうと推測できます。
同様に、LOWER(@x) という形で使われていた場合、 LOWER関数の引数は文字列型なので、文字列であろうと推測できます。
このような場合、
- 解析した結果期待される型とクエリ実行時に渡した型が一致していて
- 厳密には渡す型はリテラルであるか、構造体であるか、配列であるかだけ合ってればいいです
- つまり int64 と string と date は何を渡しても動作は同じ
- 渡した値が期待される型にキャストできる
のであれば問題なく動作します。
echo 'SELECT LOWER(@x)' | bq --project_id test --dataset_id test --api http://localhost:9050 query --parameter 'x:string:"FOO"' +-------+ | $col1 | +-------+ | "foo" | +-------+
なんでうまくいかないの？
パターン1: zetasqlの解析でエラーになる
このパターンはzetasqlの中でそのコンテキストにおいてパラメータが正しい型であるか検証されているが、クエリ実行時に渡した型ではなくINT64として扱われているので期待する型でないケースです。
例として FROM UNNEST(@param) がありましたが、 FROM で UNNEST が使われている場合、zetasqlは引数が配列型であるかどうかをチェックし、配列型でない場合は例外を投げてしまいます。
他にも、ARRAY_CONCAT は引数の型チェックをしますし、その他多くの関数で型がチェックされている場合はエラーになります。
ちなみにチェックの緩いやつはうまくいくうえに、解析した結果ちゃんと型が付きます。例えば DATE_ADD の場合
echo -n 'select DATE_ADD(@x, INTERVAL 1 MONTH)' | bq --project_id test --dataset_id test --api http://localhost:9050 query --parameter 'x:int64:2024-01-01'
これを投げると、zetasqlは↓を返してきて
QueryStmt
+-output_column_list=
| +-$query.$col1#1 AS `$col1` [DATE]
+-query=
+-ProjectScan
+-parse_location=0-37
+-column_list=[$query.$col1#1]
+-expr_list=
| +-$col1#1 :=
| +-FunctionCall(ZetaSQL:date_add(DATE, INT64, ENUM<zetasql.functions.DateTimestampPart>) -> DATE)
| +-parse_location=7-37
| +-Parameter(parse_location=16-18, type=DATE, name="x")
| +-Literal(parse_location=29-30, type=INT64, value=1)
| +-Literal(type=ENUM<zetasql.functions.DateTimestampPart>, value=MONTH)
+-input_scan=
+-SingleRowScan
クエリ結果も期待通りの結果になります。
echo -n 'select DATE_ADD(@x, INTERVAL 1 MONTH)' | bq --project_id test --dataset_id test --api http://localhost:9050 query --parameter 'x:int64:2024-01-01' +------------+ | $col1 | +------------+ | 2024-02-01 | +------------+
パターン2: zetasqlは解析できるけどgo-zetasqliteでエラーになる
これはbigquery-emulatorがgo-zetasqliteに渡す値の型と、zetasqlの解析の結果期待される型が食い違っているのでキャストに失敗するケースです。
例としては SELECT @param を出しましたが、これだけ見ても型がわかりませんね。
ここに配列を渡した場合、zetasqlがクエリを解析する際には(ほんとは違うけど)INT64として素通しします。
しかし、正しく配列型であることを指定してパラメータを渡した場合、bigquery-emulatorはそこが配列型であることを知っているのでgo-zetasqliteに@paramを配列として渡します。
go-zetasqliteはクエリを実行する際に、当然解析したクエリが期待する型に合わせてパラメータをキャストする必要があります。
その際、配列をINT64にキャストしようとしてしまってエラーになるわけです。
一体どうすれば
そもそも SELECT @param とか SELECT * FROM UNNEST(@param) とか、本番で使うことあるんかい、と突っ込まれそうですがしばしばあります。（まぁ大抵回避可能だと思います）
どうしても使う場合、本物のBigQueryで使うときには無駄になっちゃいますが、一旦 CAST すれば問題を回避することができます。
echo 'select * from UNNEST(@x)' | bq --project_id test --dataset_id test --api http://localhost:9050 query --parameter 'x:array<string>:["foo","bar","baz"]' BigQuery error in query operation: Error processing job 'test:bqjob_r6ebd5d509bffc7d9_0000018d6031a91d_1': failed to analyze: INVALID_ARGUMENT: Values referenced in UNNEST must be arrays. UNNEST contains expression of type INT64 [at 1:22]
❯ echo 'select * from UNNEST(CAST(@x as array<string>))' | bq --project_id test --dataset_id test --api http://localhost:9050 query --parameter 'x:array<string>:["foo","bar","baz"]' +----------+ | $unnest1 | +----------+ | foo | | bar | | baz | +----------+
