BigQueryのSQLについて、ドキュメントを読んだり実験したりしながら挙動を解き明かしていこうと思います。第4回はARRAYとSTRUCTについて紹介します。
今回扱う型
- ARRAY
- STRUCT
ARRAY型
ARRAY型は決まった要素型をもつ可変長の配列です。たとえば ARRAY<INT64> は整数の配列を意味します。
これだけで済むなら話は簡単なのですが、ARRAY型の意味論を考えるにあたっては以下の制約が重要になります。
- ARRAYのネスト
- ARRAYとNULLの関係
ARRAYのネスト
ARRAYをARRAYの要素型にすることはできません。したがって、 ARRAY<ARRAY<INT64>> は無効です。
ただし、間にSTRUCTを挟んで ARRAY<STRUCT<ARRAY<INT64>>> とすることはできるため、表現力の観点からはARRAYのネストは可能ともみなせます。
ARRAYとNULLの関係
ARRAYの振舞いは以下の2つの場合により大きく異なります。
- クエリ処理時
- テーブルへの保存時 (ジョブの結果を保存する一時テーブルも含む)
クエリ処理時は以下のように振舞います。
- NULL と 空配列 ([]) は区別されます。
- 要素は NULL になることが可能です。
いっぽう、テーブルへの保存時は以下のように振舞います。
- NULL は空配列 ([]) として扱われます。
- 要素に NULL が含まれていると、エラーになります。
SELECT [NULL] -- Error
SELECT [NULL][OFFSET(0)] -- NULL
SELECT
-- []
CASE WHEN TRUE THEN [] END,
-- []
CASE WHEN FALSE THEN [] END,
-- FALSE
CASE WHEN TRUE THEN [] END IS NULL,
-- TRUE
CASE WHEN FALSE THEN [] END IS NULL
第1回〜第3回まででうすうす察していた方もいると思いますが、ここまでで紹介した BigQuery の型は、どことなく Protocol Buffers っぽい感じがあります。その印象が最も強く残るのが、この ARRAY 型の制約です。私は BigQuery の内部構造について知っているわけではないですが、テーブルが Protocol Buffers の Wire Format で保存されているとしたら色々なところに納得がいきます。
ARRAY の作成
要素を書き下すには [1, 2, 3] のように書くか、型を明示して ARRAY<INT64>[1, 2, 3] のように書きます。
規則的な配列は GENERATE_ARRAY やその亜種で生成できます。
クエリから配列を作るには、 Array subquery を使って ARRAY(SELECT ...) のように書くか、 ARRAY_AGG を使います。
ARRAY のアクセス
添字アクセスは array[1] のように行いますが、添字の種類を明示して array[OFFSET(1)] のように書くこともできます。添字の種類として以下の4種類があります。
- OFFSET(i) 0-originで範囲外はエラーになる (デフォルト)
- SAFE_OFFSET(i) 0-originで範囲外はNULLになる
- ORDINAL(i) 1-originで範囲外はエラーになる
- SAFE_ORDINAL(i) 1-originで範囲外はNULLになる
さらに頻出なのは、 UNNEST() で直接クエリの対象にする方法です。
STRUCT型
STRUCT型は、端的に言うとタプルです。つまり、決まった長さをもち、それぞれの要素が独立に型を持ちます。
STRUCT型では、各要素に名前をつけることもできます。しかし、基本的な振舞いとしては順序対として考えたほうが自然です。名前はあくまでも「おまけ」です。
STRUCT型は STRUCT<INT64, STRING> のように、要素型を順に並べて表記します。要素に名前をつけるときは STRUCT<n INT64, STRING, x FLOAT64> のように、名前を前置します。
STRUCT型の制約は以下の通りです。
- 要素は1つ以上必要。
STRUCT型は、要素の名前を変えても互換性のある型として扱われます。いっぽう、要素の順番を変えると互換性がありません。原則として、手前側の式に由来する STRUCT 型の要素名がそのまま使われます。
-- OK (x, y として扱われる)
SELECT STRUCT(1 AS x, 'foo' AS y)
UNION ALL
SELECT STRUCT(2 AS y, 'bar' AS x)
-- Error
SELECT STRUCT(1 AS x, 'foo' AS y)
UNION ALL
SELECT STRUCT('bar' AS y, 2 AS x)
STRUCT の生成
要素を書き下すには以下のいずれかの記法を用います。
- (1, 'foo') ただし2要素以上で匿名の場合に限る
- STRUCT(1 AS x, 'foo')
- STRUCT<x INT64, STRING>(1, 'foo')
また、 SELECT AS STRUCT を使うことでも STRUCT を生成できます。
STRUCT のアクセス
STRUCT では、 ARRAY と同じ添字記法が使えます。ただし、インデックスは数値リテラルである必要があります。 s[1] はできても s[i] はできません。
STRUCT の要素に名前がついている場合は、ドット記法でも要素にアクセスできます。ただし、同じ名前の要素が2つあるときは、その名前でアクセスすることはできません。
また、 UNNEST() の特別なルールとして、 ARRAY<STRUCT> を対象に UNNEST した場合は、 STRUCT の内容が行として展開されます。このとき、要素に名前があればその名前で列にアクセスできます。
ARRAY, STRUCT 同士の比較
型強制の処理
BigQueryでは、一部の型に型強制関係があります。たとえば以下の例では INT64 → FLOAT64 への型強制が発生しています。
SELECT 1 -- 1.0 に型強制される
UNION ALL
SELECT 1.5
しかし、 ARRAY, STRUCT はこの型強制関係に対して非変 (invariant) に振舞います。 (原理的には共変に振舞えるにもかかわらず)
-- Error
SELECT [1]
UNION ALL
SELECT [1.5]
比較
ARRAY, STRUCT の比較処理のほとんどは禁止されています。
- = を使った比較
- IS DISTINCT FROM を使った比較
- <, <=, >, >=, BETWEEN を使った比較
- ORDER BY を使った比較
- PARTITION BY を使った比較
- aggregation(DISTINCT ...) を使った比較
例外的に、以下のみ許されています。
- SELECT ... GROUP BY を使った比較
- SELECT DISTINCT ... を使った比較
これらの2つのケースでは、長さと各位置の要素が再帰的に比較されます。また、グルーピングの性質上、 NULL 同士は等しいものとみなされます。
まとめ
BigQueryの以下の型について、細部の振舞いに注意しながら紹介しました。
- ARRAY型: いわゆる配列
- STRUCT型: いわゆるタプル型で、補助的に要素名を付与できる。
BigQueryのSQLいろいろ (3) 日付・時刻型 | Wantedly Engineer Blog
/assets/images/7052304/original/49100b17-5866-4cec-b50b-a37ea73565e9?1638782149)
/assets/images/7052304/original/49100b17-5866-4cec-b50b-a37ea73565e9?1638782149)
/assets/images/7053725/original/28d1b135-13b4-4f46-895f-d5f5fb5ae243?1624246858)
