突然ですが、皆さんは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行の中に複数の値が入っていた状態から、それぞれが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とうまく付き合って行けそうです。