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

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

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

> [ネストされた繰り返し列の指定（公式ドキュメント）](https://cloud.google.com/bigquery/docs/nested-repeated?hl=ja)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221497/207dfc2f-e58d-03a8-b2db-1820cc75d7f6.png)

僕自身のよく出会うケースとしては、[**Firebase の BigQuery Export**](https://support.google.com/analytics/answer/7030014?hl=ja)です。
この機能はFirebase Analytics（アプリ版Google Analyticsのようなもの）のデータをサンプリングなしでBigQueryへエクスポートし、自由にクエリを書いて分析できるようにするというすごく便利なものです。

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

## どこで苦しむのか

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

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

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

```sql
SELECT
  user_id,
  event_params.key,
FROM
  sample_table
```

しかし、このクエリは実行することができません。直接繰り返し列の中の列をとってくることはできないのです。
ここでは、`UNNEST`という関数を使って、列のフラット化を行う必要があります。
UNNESTに関する詳しい説明は[**ここ**](https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja#flattening-arrays)をご覧ください。

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

```sql
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もとってくるように追記します。（型の違いは無視しています）

```sql
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は条件をかける時、行単位に着目するので、複数の行に渡る条件には弱い側面があります。これは言い換えると、横持ちのデータには強いが縦持ちのデータにはあまり強くないと言えます。今の状態は縦持ちの状態に当たります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221497/aeac4ff7-80b0-bd64-bcc5-32ab155fb695.png)

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