【新機能】BigQuery で GROUP BY と SELECT DISTINCT が ARRAY と STRUCT にも使えるようになりました
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
Google Cloud データエンジニアのはんざわです。
2024年8月29日のアップデートにより、ARRAY 型と STRUCT 型にも GROUP BY と SELECT DISTINCT が使えるようになりました。
You can now use the
GROUP BYclause and theSELECT DISTINCTclause with theARRAYandSTRUCTdata types. This feature is in Preview.
本ブログでは、さっそくその使い方を紹介したいと思います。
このアップデートの何が嬉しいの？
GROUP BY と SELECT DISTINCT のそれぞれのケースで、従来のクエリとどのように変わるのかを見てみましょう。
GROUP BY
これまでは、ARRAY 型に集約関数を使用したい場合、一度 ARRAY 型を文字列に変換してから集約関数を実行する必要がありました。
今後はそのような前処理をする必要がなくなり、よりシンプルなクエリで集約関数を使用することが可能になりました。
WITH sample AS (
SELECT ['A', 'B', 'C'] AS arr, 3 AS cnt
UNION ALL
SELECT ['D', 'E', 'F'] AS arr, 2 AS cnt
UNION ALL
SELECT ['A', 'B', 'C'] AS arr, 5 AS cnt
)
SELECT
ARRAY_TO_STRING(arr, ','),
SUM(cnt) AS sum_cnt
FROM
sample
GROUP BY arr
WITH sample AS (
SELECT ['A', 'B', 'C'] AS arr, 3 AS cnt
UNION ALL
SELECT ['D', 'E', 'F'] AS arr, 2 AS cnt
UNION ALL
SELECT ['A', 'B', 'C'] AS arr, 5 AS cnt
)
SELECT
arr,
SUM(cnt) AS sum_cnt
FROM
sample
GROUP BY arr
また、 STRUCT 型も同様に簡潔なクエリで集約関数を実行することができるようになりました。
従来は、STRUCT 型のフィールドから一意になるようなキーの組み合わせを抽出して集約関数を実行する必要がありましたが、今後は不要になると思われます。
WITH sample AS (
SELECT STRUCT(1 AS id, 'apple' AS name) AS f_struct, 3 AS cnt
UNION ALL
SELECT STRUCT(2 AS id, 'banana' AS name) AS f_struct, 2 AS cnt
UNION ALL
SELECT STRUCT(1 AS id, 'apple' AS name) AS f_struct, 5 AS cnt
)
SELECT
f_struct.id,
SUM(cnt) AS sum_cnt
FROM
sample
GROUP BY f_struct.id
WITH sample AS (
SELECT STRUCT(1 AS id, 'apple' AS name) AS f_struct, 3 AS cnt
UNION ALL
SELECT STRUCT(2 AS id, 'banana' AS name) AS f_struct, 2 AS cnt
UNION ALL
SELECT STRUCT(1 AS id, 'apple' AS name) AS f_struct, 5 AS cnt
)
SELECT
f_struct,
SUM(cnt) AS sum_cnt
FROM
sample
GROUP BY f_struct
SELECT DISTINCT
これまでもテーブルの一意判定を行う際に SELECT DISTINCT を使用していましたが、ARRAY 型や STRUCT 型のデータが含まれていると使用することができませんでした。
その対処法として、主キーと QUALIFY でユニーク化するなど、別のアプローチを取る必要がありました。
しかし、DISTINCT が ARRAY 型と STRUCT 型にも対応したことで、今までよりもシンプルなクエリでテーブルをユニーク化することが可能になりました。
WITH sample AS (
SELECT 1 AS id, [1, 2, 3] AS arr
UNION ALL
SELECT 2 AS id, [4, 5, 6] AS arr
UNION ALL
SELECT 1 AS id, [1, 2, 3] AS arr
)
SELECT
*
FROM
sample
QUALIFY ROW_NUMBER() OVER(PARTITION BY id) = 1
WITH sample AS (
SELECT 1 AS id, [1, 2, 3] AS arr
UNION ALL
SELECT 2 AS id, [4, 5, 6] AS arr
UNION ALL
SELECT 1 AS id, [1, 2, 3] AS arr
)
SELECT
DISTINCT *
FROM
sample
関連の機能も検証してみる
今回のアップデートに関連して、いくつか気になる点があったため、検証してみました。
検証項目は以下の通りです。
EXCEPT DISTINCTは使えるのかJSON型でも使えるのか
1. EXCEPT DISTINCT は使えるのか
結論として、EXCEPT DISTINCT はまだ使えないようです。。。
SELECT [1, 2, 3] AS arr, 1 AS cnt
EXCEPT DISTINCT
SELECT [1, 2, 3] AS arr, 1 AS cnt
> Type ARRAY is not supported in INTERSECT DISTINCT or EXCEPT DISTINCT operation
以下のブログでも紹介されているように、EXCEPT DISTINCT を使用することで複数のテーブルの一致判定を行うことができますが、この方法には問題があります。
しかし、この方法には問題があり、ARRAY や STRUCT 型には対応していないため、別のアプローチを取る必要がありました。
EXCEPT DISTINCTを 2 つのテーブルで双方向に行い、対称差を求めます。対称差が存在しなければ、一致していると見なすことができます。
しかし、
Groupableでない型ARRAY,STRUCT,GEOGRAPHYには対応できない問題があります
ARRAY や STRUCT 型には対応していないため、別のアプローチを取る必要がありましたが、今後のアップデートで EXCEPT DISTINCT がこれらの型にも対応することを期待しています。
2. JSON でも使えるのか
残念ながら、こちらも使えないようです。。。
WITH sample AS (
SELECT JSON '{"key": "apple"}' AS json_o
UNION ALL
SELECT JSON '{"key": "apple"}' AS json_o
)
SELECT
DISTINCT json_o
FROM
sample
> Column json_o of type JSON cannot be used in SELECT DISTINCT
筆者は、STRUCT 型があまり好きではないため、代わりに JSON 型を採用しています。
JSON 型のテーブルも、STRUCT 型と同様に主キーと QUALIFY を使ってユニーク化することが多いので、JSON 型でも SELECT DISTINCT が実行できるようになると嬉しいなーと思いました。
（とはいえ、JSON 型はキーの順序が一定でないため、難しいかもしれませんが...）
まとめ
本ブログでは、新たにプレビュー機能となった ARRAY 型と STRUCT 型に対する GROUP BY と SELECT DISTINCT の使用方法を試してみました。
最近プレビューに追加された JSON_KEYS の機能もそうですが、地味ながらも便利なアップデートが続いている印象です。
このような痒い所に手が届く機能がどんどん追加されることは、筆者として非常に嬉しい限りです。
今後のアップデートにも期待が高まります。
