BigQueryのSTRUCT型とうまく付き合う

突然ですが、皆さんはBigQueryは好きですか？僕は大好きです！毎日触っていても飽きません。
では、皆さんはBigQueryのSTRUCT型は好きですか？僕は大嫌いです。もう二度と触りたくありません。

正確にはネストされた繰り返し列のことです。

ネストされた繰り返し列の指定（公式ドキュメント）

こんな感じのデータです。

僕自身のよく出会うケースとしては、Firebase の BigQuery Exportです。
この機能はFirebase Analytics（アプリ版Google Analyticsのようなもの）のデータをサンプリングなしでBigQueryへエクスポートし、自由にクエリを書いて分析できるようにするというすごく便利なものです。

しかし、毎度この繰り返し列に苦しめられています。
そこで、もう二度と苦しめられる人を生み出さないように、上手く付き合っていく方法を書いていこうと思います。

どこで苦しむのか

例えば、以下のようなスキーマのテーブルがあるとします。（上のスクショのデータをイメージしています。）

• user_id
• timestamp
• event_name
• event_params（ネストされた繰り返しフィールド）
  • event_params.key
  • event_params.value

ここで例えばevent_params.keyをとってきたいとして、このようなクエリを書いたとします。

SELECT
  user_id,
  event_params.key,
FROM
  sample_table

しかし、このクエリは実行することができません。直接繰り返し列の中の列をとってくることはできないのです。
ここでは、UNNESTという関数を使って、列のフラット化を行う必要があります。
UNNESTに関する詳しい説明はここをご覧ください。

UNNESTを使って実行できる状態のクエリは以下のようなものになります。

SELECT
  user_id,
  event_params.key
FROM
  sample_table,
  UNNEST(event_params) event_params

実行前：

	user_id	event_params.key
1	A	session_engaged
		ga_session_id
		firebase_screen_class
		engaged_session_event

実行後

	user_id	key
1	A	session_engaged
2	A	ga_session_id
3	A	firebase_screen_class
4	A	engaged_session_event

１行の中に複数の値が入っていた状態から、それぞれが1行に分解されたのがわかると思います。
ここまでは大丈夫なのですが、ここからが苦しいんです。

さて、先ほどのクエリにkeyに対するvalueもとってくるように追記します。（型の違いは無視しています）

SELECT
  user_id,
  event_params.key,
  event_params.value
FROM
  sample_table,
  UNNEST(event_params) event_params

	user_id	key	value
1	A	session_engaged	1
2	A	ga_session_id	1234
3	A	firebase_screen_class	SplashActivity
4	A	engaged_session_event	1

意図通りのデータがとってこれました。
ここで、例えばfirebase_screen_classの値がSplashActivityのga_session_idの値をとってきたいとします。ここで途端に頭が混乱します。SQL何もわからない状態です。

解決策

なぜわからなくなるのでしょうか？それは、SQLという言語の特性にあります。
SQLは条件をかける時、行単位に着目するので、複数の行に渡る条件には弱い側面があります。これは言い換えると、横持ちのデータには強いが縦持ちのデータにはあまり強くないと言えます。今の状態は縦持ちの状態に当たります。

これに対する解決策はシンプルです。SQLで扱いづらい縦持ちのデータから、扱いやすい横持ちのデータに変換します。
サンプルのクエリは以下です。

SELECT
  user_id,
  MAX(CASE WHEN event_params.key = 'ga_session_id' THEN event_params.value END) AS ga_session_id,
MAX(CASE WHEN event_params.key = 'session_engaged' THEN event_params.value END) AS session_engaged,
MAX(CASE WHEN event_params.key = 'firebase_screen_class' THEN event_params.value END) AS firebase_screen_class,
MAX(CASE WHEN event_params.key = 'engaged_session_event' THEN event_params.value END) AS engaged_session_event,
FROM
  sample_table,
  UNNEST(event_params) event_params

実行すると、以下のような結果が得られます。

	user_id	ga_session_id	session_engaged	firebase_screen_class	engaged_session_event
1	A	1234	1	SplashActivity	1

これで、通常と同じように条件をかけたり集計したりできるようになりました。
これからもBigQueryとうまく付き合って行けそうです。