LoginSignup
48
32

More than 3 years have passed since last update.

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

Posted at

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

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

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

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

image.png

僕自身のよく出会うケースとしては、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の値がSplashActivityga_session_idの値をとってきたいとします。ここで途端に頭が混乱します。SQL何もわからない状態です。

解決策

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

image.png

これに対する解決策はシンプルです。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とうまく付き合って行けそうです。

48
32
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
48
32