BigQueryのARRAY<STRUCT>のハマリどころ

BigQuery ではパフォーマンス向上のために STRUCT(RECORD)や ARRAY による非正規化が推奨されています。

https://cloud.google.com/bigquery/docs/best-practices-performance-input?hl=ja#denormalizing_data

実際に一般公開データセットのほか、Google Analytics や Firebase を BigQuery に export するときのスキーマでは ARRAY や STRUCT が度々使用されています。

https://cloud.google.com/bigquery/public-data?hl=ja
https://support.google.com/analytics/answer/3437719?hl=ja
https://support.google.com/firebase/answer/7029846?hl=ja

しかし、ARRAY と STRUCT の仕様については公式ドキュメントにまとまっているものの、ARRAY<STRUCT>にはドキュメントに載ってない挙動があってやや注意が必要だということに気がついたのでまとめてみました。

STRUCT の 3 つの構文

初めに STRUCT の構文をおさらいしておきます。
前提として BigQuery の STRUCT ではデータ型は必須で、フィールド名はオプション扱いになります。
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja#struct_type

タプル

SELECT [('foo', 'bar'),
        ('baz', 'qux')] AS column1

STRUCT constructor を明記せずにタプルで表現する記法です。

最低でも 2 つの expression が必須で、フィールド名を指定できないため強制的に匿名フィールドになります。
上記のクエリを実行すると以下の結果になります。

フィールド名を指定しようとすると以下のようなシンタックスエラーになります。

SELECT [('foo' AS aaa, 'bar'),
        ('baz', 'qux')] AS column1

Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [1:10]

この記法は複雑なクエリでは丸括弧が STRUCT のものかそうではないかの区別がつきにくい上に匿名フィールドになってしまうため、積極的な利用はオススメしません。
ドキュメントにあるように、WHERE 句などでの限定的な使用に留めておくのが得策かと思います。

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja#tuple_syntax

型なし STRUCT

SELECT [STRUCT('foo' AS aaa, 'bar' AS bbb),
        STRUCT('baz' AS aaa, 'qux' AS bbb)] AS column1

STRUCT constructor は明記しつつも型を指定しない記法です。

型あり STRUCT

SELECT [STRUCT<aaa STRING, bbb STRING>('foo', 'bar'),
        STRUCT<aaa STRING, ccc STRING>('baz', 'qux')] AS column1

STRUCT constructor を明記し型を指定する記法です。

STRUCT の構文をおさらいしたところで、ARRAY<STRUCT>の具体的な挙動について見ていきましょう。

フィールド名が上書きされるパターン

次の SQL を実行すると結果はどうなるでしょうか。

SELECT [STRUCT('foo' AS aaa, 'bar' AS bbb),
        STRUCT('baz' AS aaa, 'qux' AS ccc)] AS column1

結果はこうなります。

予想通りでしたか？
2 つ目の STRUCT の 2 行目のフィールド名"ccc"が"bbb"に変わっています。この場合はエラーになりません。
そんなこと知ってるよ、という人は問題ありませんが、意外と知らない人も多いのではないでしょうか。かくいう私も最近まで知りませんでした。

1 つ目の STRUCT でフィールド名を指定しなかった場合は匿名フィールドになり、やはり 2 つ目の STRUCT のフィールド名が上書きされます。

SELECT [STRUCT('foo' AS aaa, 'bar'),
        STRUCT('baz' AS aaa, 'qux' AS ccc)] AS column1

型あり STRUCT 構文も最初のクエリと同様にフィールド名が上書きされます。

SELECT [STRUCT<aaa STRING, bbb STRING>('foo', 'bar'),
        STRUCT<aaa STRING, ccc STRING>('baz', 'qux')] AS column1

エラーになるパターン

逆にエラーになるのはデータ型が異なるパターンです。

SELECT [STRUCT('foo' AS aaa, 'bar' AS bbb),
        STRUCT('baz' AS aaa, 1     AS bbb)] AS column1

Array elements of types {STRUCT<aaa STRING, bbb INT64>, STRUCT<aaa STRING, bbb STRING>} do not have a common supertype at [1:8]

また、要素数が異なる場合もシンタックスエラーになります。NULL を表現する場合は後述のように明示的に NULL を指定する必要があります。

SELECT [STRUCT('foo' AS aaa),
        STRUCT('baz' AS aaa, 1 AS bbb)] AS column1

Array elements of types {STRUCT<aaa STRING, bbb INT64>, STRUCT<aaa STRING>} do not have a common supertype at [1:8]

NULL のエラーになるパターン/ならないパターン

型なし STRUCT 構文で NULL を扱う場合はもう少し変わって複雑になります。
2 つ目以降の STRUCT での NULL はエラーになりませんが、1 つ目の STRUCT の場合はエラーになる可能性があります。

SELECT [STRUCT('foo' AS aaa, 'bar' AS bbb),
        STRUCT('baz' AS aaa, NULL  AS bbb)] AS column1

下のクエリでは NULL のあとにそれぞれ STRING と BOOL を指定しています。
どちらもエラーメッセージでは 1 つ目の STRUCT がSTRUCT<aaa STRING, bbb INT64>になっており、 NULL が INT64 扱いになっていることがわかります。

SELECT [STRUCT('foo' AS aaa, NULL  AS bbb),
        STRUCT('baz' AS aaa, 'qux' AS bbb)] AS column1

ARRAY elements of types {STRUCT<aaa STRING, bbb INT64>, STRUCT<aaa STRING, bbb STRING>} do not have a common supertype at [1:8]

SELECT [STRUCT('foo' AS aaa, NULL AS bbb),
        STRUCT('baz' AS aaa, TRUE AS bbb)] AS column1

ARRAY elements of types {STRUCT<aaa STRING, bbb INT64>, STRUCT<aaa STRING, bbb BOOL>} do not have a common supertype at [1:8]

これは NULL の定数リテラルにも型があり、そのデフォルトが INT64 であることが原因のようです。
SELECT NULL AS col1といったクエリの結果を試しにテーブル出力してみると、col1が INTEGER 型になることがわかります。

ところが、2 行目が INT64 や STRUCT の場合はシンタックスエラーになりません。

SELECT [STRUCT('foo' AS aaa, NULL AS bbb),
        STRUCT('baz' AS aaa, 1    AS bbb)] AS column1

SELECT [STRUCT('foo' AS aaa, NULL                 AS bbb),
        STRUCT('baz' AS aaa, STRUCT('baz' AS ccc) AS bbb)] AS column1

また、すべての行が NULL の場合もシンタックスエラーになりません。

SELECT [STRUCT('foo' AS aaa, NULL AS bbb),
        STRUCT('baz' AS aaa, NULL AS bbb)] AS column1

どうやらデータ型によってシンタックスエラーになる場合とそうではない場合があるようなので、すべてのデータ型で試してみました。
結論としては STRING と BOOL のみがシンタックスエラーになるようです。エラーになる場合とそうではない場合の区別は不明です。

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja

日本語名	データ型	シンタックスエラー
配列型	ARRAY	エラーにならない
ブール型	BOOL	エラーになる
バイト型	BYTES	エラーにならない
日付型	DATE	エラーにならない
日時型	DATETIME	エラーにならない
地理型	GEOGRAPHY	エラーにならない
整数型	INT64	エラーにならない
少数型	NUMERIC,BIGNUMERIC	エラーにならない
浮動小数点型	FLOAT64	エラーにならない
文字列型	STRING	エラーになる
構造体型	STRUCT	エラーにならない
時刻型	TIME	エラーにならない
タイムスタンプ型	TIMESTAMP	エラーにならない

STRING と BOOL のみ発生するシンタックスエラーの回避方法は 3 種類あります。

• 型あり STRUCT 構文

SELECT [STRUCT<aaa STRING, bbb STRING>('foo', NULL),
        STRUCT<aaa STRING, bbb STRING>('baz', 'qux')] AS column1

• CAST による型変換

SELECT [STRUCT('foo' AS aaa, CAST(NULL AS STRING) AS bbb),
        STRUCT('baz' AS aaa, 'qux' AS bbb)] AS column1

• 型あり ARRAY

SELECT ARRAY<STRUCT<aaa STRING, bbb STRING>>[('foo', NULL), ('baz', 'qux')] AS column1

ということで、型あり STRUCT 構文であれば ARRAY の先頭の STRUCT に NULL が含まれていても問題ないのですが、実際にクエリで STRUCT を書く場合は型なし構文のほうが記述量が少なく書きやすいために悩ましいところです。

型なし STRUCT と型あり STRUCT の混在

おまけとして、実際にこのようなクエリを書く機会がどれだけあるか不明ですが、一応型なしと型ありの STRUCT を混在させることも可能です。

SELECT [STRUCT<aaa STRING, bbb STRING>('foo', 'bar'),
        STRUCT('baz', 'qux')] AS column1

まとめ

今回取り上げたようなシンプルなクエリであればまだ気が付きやすいですが、何重にもネストした STRUCT を含んだ複雑なクエリでは、フィールド名の上書きや先頭の NULL 由来のシンタックスエラーをうっかり見落としてしまいかねません。
実際のユースケースでは事前にテーブルを作ってから insert することが多いのでこのような問題に遭遇することは少ないかもしれませんが、STRUCT を含む SQL を書いたり自動生成するような機会があったときには念頭においておきたいところです。