[Google Cloud BigQuery] ARRAY型のあれこれ

データアナリティクス事業本部のはんざわです。
今まであまりARRAY型のデータにガッツリ触れる機会がなかったのですが、先日の案件でそのような機会があったのでその時に知ったことをまとめておきます。

そもそもARRAY型とは

ARRAY型ではないゼロ以上の要素の順序付きリストをARRAY型と言います。

また、ARRAY型とNULLには3つのルールが存在し、それぞれ以下になっています。

• ARRAY には NULL を指定できます。
• クエリ内では NULL と空の ARRAY は 2つの別個の値ですが、BigQuery はクエリ結果で NULL ARRAY を空の ARRAY に変換します。
• NULL 要素を含む ARRAY はクエリ内で使用できますが、そのような ARRAY がクエリ結果にあると、BigQuery でエラーが発生します。

※ 公式ドキュメントより引用

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_type

まずはARRAY型のテーブルを作ってみる

まずは普通のARRAY型カラムを含んだテーブルを作成してみます。
以下のようなJSONL形式のファイルを用意しました。colorsカラムがARRAY型になっています。
このファイルをGUIからアップロードしてテーブルを作成します。

{"id": 1, "type": "cat", "colors": ["black", "white"]}
{"id": 2, "type": "dog", "colors": ["brown", "white"]}
{"id": 3, "type": "bird", "colors": ["blue"]}

以下のようなテーブルが作成されました。
colorsの要素が2つ以上ある場合は1つのレコードに複数の行を保持していることがわかります。

[ルール１] ARRAYにNULL

1.1. ARRAYがNULLのデータ

ARRAY には NULL を指定できます。　

※ 公式ドキュメントより引用

今度はレコードのcolorsカラムがNULLになっているデータをアップロードしてみます。

{"id": 1, "type": "cat", "colors": ["black", "white"]}
{"id": 2, "type": "dog", "colors": null}
{"id": 3, "type": "bird", "colors": ["blue"]}

上手く読み込ませることができました。
テーブルを見ると2行目のcolorsカラムにnullになっていることがわかります。

ARRAY型がNULLでもBigQueryに読み込ませることができます

1.2. ARRAYカラムが存在しないデータ

次に似たようなケースでレコードのcolorsカラムが存在しないデータをアップロードしてみます。

{"id": 1, "type": "cat", "colors": ["black", "white"]}
{"id": 2, "type": "dog"}
{"id": 3, "type": "bird", "colors": ["blue"]}

こちらもNULL同様に読み込ませることができました。

[ルール2] NULLと空のARRAY

クエリ内では NULL と空の ARRAY は 2 つの別個の値ですが、BigQuery はクエリ結果で NULL ARRAY を空の ARRAY に変換します。

※ 公式ドキュメントより引用

2.1. 2種類のテーブルを作成

2つのJSONLデータを用意し、テーブルを作成しました。
片方はARRAY型にNULLを指定したデータで、もう片方は空のARRAYを指定したデータです。

ARRAY型にNULLのデータ

{"id": 1, "type": "cat", "colors": ["black", "white"]}
{"id": 2, "type": "dog", "colors": ["brown", "white"]}
{"id": 3, "type": "bird", "colors": null}

空のARRAYデータ

{"id": 1, "type": "cat", "colors": ["black", "white"]}
{"id": 2, "type": "dog", "colors": ["brown", "white"]}
{"id": 3, "type": "bird", "colors": []}

どちらのテーブルも見た目は全く一緒です。一見すると両方ともnullが格納されているように見えますが、実際には空のARRAYが格納されています。
最初のテーブルは公式ドキュメントに記載してある通り、nullは空のARRAYに変換されています。

本当に空のARRAYが格納されているか確認してみます。

2.2. SQLでデータの確認

さっそくですがクエリの結果です。

ARRAY型にNULLのデータ

空のARRAYデータ

結果を見て分かる通り、nullで検索しても該当のデータはヒットしませんでした。
これは前述した通り、見た目はnullでも実際は空のARRAYが格納されているからです。
ARRAY型でにnull検索したい場合は空のARRAYで検索するようにしましょう。ARRAY_LENGTH関数を使うとARRAYの長さで検索することができます。

[ルール3] NULL要素を含むARRAY

NULL 要素を含む ARRAY はクエリ内で使用できますが、そのような ARRAY がクエリ結果にあると、BigQuery でエラーが発生します。

※ 公式ドキュメントより引用

3. ARRAY型配列にNULLを含むデータ

{"id": 1, "type": "cat", "colors": ["red", "blue"]}
{"id": 2, "type": "dog", "colors": ["black", "white"]}
{"id": 3, "type": "bird", "colors": ["red", "yellow", null]}

上記のような配列にNULLが含まれるデータをロードするとエラーを吐きます。
以下エラー文です。

Error while reading data, error message: JSON parsing error in row starting at position 107: Only optional fields can be set to NULL. Field: colors; Value: NULL

BigQueryではARRAY型配列の中にNULL要素を含むテーブルを作成することができません。

あらかじめNULLを削除してからデータをロードしましょう。

これまでのまとめ

1. ARRAYでNULLを指定することも可能、ただし空のARRAYに置き換わる
2. NULL検索したい場合は空のARRAYで検索する
3. 配列のNULLは取り除く

まとめ

配列の挙動についてまとめました。
他にも外部テーブルで読み込んだ時や最近GAになったJSON型で配列を扱う場合の挙動も調査したいと思っています。