サンフランシスコのパブリック データなど）を使ったことがある方なら、おなじみの SQL 表が大きくなったようなものであることはおわかりでしょう。次のようなイメージです。
実際の BigQuery は、もう少し複雑な構造になっています。BigQuery 表の各行は、単純なキーと値のペアである必要はありません。各行は単純なデータ（文字列、整数、浮動小数点数など）を含む JSON オブジェクトのようなものですが、これには配列、構造体、構造体の配列などのさらに複雑なデータも含めることができます。次のようなイメージの方が近いでしょう。
これは「2017 年最高のエラー メッセージ」賞を獲得できそうなメッセージではありませんが2、よく考えればエラーの原因はわかります。文字列値を「配列に埋め込まれている構造体の要素」と比較しようとしているからです。もちろん、要素は最終的に文字列ではあるものの、ここではまったく別のオブジェクトです。
この点を修正するために、UNNEST 関数を使用します。UNNEST 関数は、配列を受け取ってそれを個々の要素に分解します。まずは、簡単な例を見てみましょう。
次の SQL を実行します。
#standardSQL
WITH data AS (
SELECT "primes under 15" AS description,
[1,2,3,5,7,11,13] AS primes_array)
SELECT *
FROM data
このように、文字列型の 1 つの列とデータの配列が含まれた結果が返されます。
さらに、次の SQL を実行してみましょう。
#standardSQL
WITH data AS (
SELECT "primes under 15" AS description,
[1,2,3,5,7,11,13] AS primes_array)
SELECT description, prime
FROM data CROSS JOIN UNNEST (primes_array) as prime
ここで行っているのは、「primes_array を個々の要素に分解し、各メンバーを元の列のクローンと結合するように BigQuery にリクエストする」という操作です。これによって、次のようなデータ構造が得られます。
結果は先ほどのものに似ていますが、各素数が 1 つの行として返されています。
データ構造には、元の primes_array も含まれています。（後述のように）これが役に立つ場合もありますが、今回のケースでは少しわかりづらくなりますので、SELECT
*. ではなく、description と prime という個々のフィールドのみを指定しています3
CROSS
JOIN 構文はカンマで置き換えるのが一般的なので、クエリは次のようになります。
#standardSQL
WITH data AS (
SELECT "primes under 15" AS description,
[1,2,3,5,7,11,13] AS primes_array)
SELECT description, prime
FROM data, UNNEST (primes_array) as prime
これは少し読みやすくなっていますが、先ほどの例とまったく同じクエリです。さらに、先ほどお話ししたこのデータ フォーマットでは JOIN を使う必要はないということの証明にもなります。
ここで便利なのは、1 つの行に 1 つの「prime」データがあることです。そのため、次のような比較を行うことができます。
#standardSQL
WITH data AS (
SELECT "primes under 15" AS description,
[1,2,3,5,7,11,13] AS primes_array)
SELECT description, prime
FROM data, UNNEST (primes_array) as prime
WHERE prime > 8
こうすると、8 以上 15 以下の素数を得ることができます。
では、Firebase Analytics データの例に戻り、 UNNEST 関数を使って特定の名前のイベントを探してみます。
#standardSQL
SELECT event.name, event.timestamp_micros
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST(event_dim) as event
WHERE event.name = "round_completed"
イベントにはそれぞれ params 配列があり、ここにすべてのイベント パラメータが含まれています。この配列を UNNEST すれば、特定のイベント パラメータ値を持つイベントを返すクエリを作ることができます。
#standardSQL
SELECT event, event.name, event.timestamp_micros
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST(event_dim) as event,
UNNEST(event.params) as event_param
WHERE event.name = "round_completed"
AND event_param.key = "score"
AND event_param.value.int_value > 10000
この例では、クエリのフィールドの 1 つとして「event」を選択しています。これによって、すべてのイベント パラメータを含む元の配列がきれいにグループ化された表が返されます。
ユーザー プロパティに対するクエリも同じように行うことができます。ユーザーがどの言語でアプリを使っているのかを知りたい場合、アプリが「language」ユーザー プロパティでトラッキングしている値を使います。最初に、UNNEST クエリを使って各ユーザーと選択された言語のリストを取得します。
#standardSQL
SELECT
user_dim.app_info.app_instance_id as unique_id,
MAX(user_prop.key) as keyname,
MAX(user_prop.value.value.string_value) as keyvalue
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST(user_dim.user_properties) AS user_prop
WHERE user_prop.key = "language"
GROUP BY unique_id
次に、各グループのユーザー数の合計を計算するために、副問い合わせとして先ほどの SQL を使用します4。 #standardSQL
SELECT keyvalue, count(*) as count
FROM (
SELECT
user_dim.app_info.app_instance_id as unique_id,
MAX(user_prop.key) as keyname,
MAX(user_prop.value.value.string_value) as keyvalue
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST(user_dim.user_properties) AS user_prop
WHERE user_prop.key = "language"
GROUP BY unique_id
)
GROUP BY keyvalue
ORDER BY count DESC
1 つの大きなビッグクエリ（だじゃれではありません）を作りたいのであれば、イベント パラメータとユーザー プロパティの両方を UNNEST することもできます。そうすると、特定の条件に一致するイベント パラメータを持つ特定の名前のイベントを、特定の条件に一致するユーザーで絞り込むことができます。
#standardSQL
SELECT user_dim, event, event.name, event.timestamp_micros
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST(event_dim) as event,
UNNEST(event.params) as event_param,
UNNEST(user_dim.user_properties) as user_prop
WHERE event.name = "round_completed"
AND event_param.key = "squares_daubed"
AND event_param.value.int_value > 20
AND user_prop.key = "elite_powers"
AND (CAST(user_prop.value.value.string_value as int64)) > 1
UNNEST 関数を使いこなせるようになると、そのすばらしさに気づくはずです。そして、Firebase Analytics のデータ操作がずっと楽しくなるでしょう。さらに詳しい情報は、BigQuery の標準 SQL ドキュメントの配列の操作セクションをご覧ください。
BigQuery では、毎月 1 テラバイトのデータを無料で使えますので、気軽に試していただくことができます。ぜひ、配列の展開をお楽しみください！
1 ↩ BigQuery チームによると、BigQuery に格納されたデータに対するクエリで好まれているのは標準 SQL です。しかし、彼らはあらゆるすばらしいパーティに招かれたいのでそう言っているだけでしょう。
2 ↩ Messies はまたもや受賞を逃しました。
3 ↩ 「SELECT * EXCEPT (primes_array)」と書くこともできます。この方が便利なこともあるでしょう。
4 ↩ 厳密には、すべての「アプリのインスタンス」という意味です。複数の端末からアプリを使っているユーザーは二重にカウントされます。
Posted by Toru Kaneko - Google Cloud Team
