BigQuery入門者がはじめに躓くだろうネスト型との付き合い方

# はじめに
業務でセッション データやUIDデータをGoogleAnalytycsから BigQuery にエクスポートし、BQをデータ基盤として活用しはじめた頃の話です。
その当時、GAからエクスポートされたデータが**特徴的**で最初に出したいデータが出せずハマったのでアウトプットしていきます。
※個人ブログから技術的アウトプットはQiitaへ引っ越ししたので、こちらは過去に書いたブログとなります。


# ハマったポイント

**下記のようなクエリで、とりあえずhoge_viewsのカウントをとりたかった。**

```sql
select
   event_params.value.string_value
   , count(*) as hoge_views
from 'テーブル名'
where event_name = "play_count"
group by 1
order by hoge_views desc
```

すると

```sql
Cannot access field key on a value with type ARRAY<STRUCT<key STRING,
value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64
```

と、エラーになった。

上記のエラーは「**配列内の要素にはアクセスできません**」ということ。

:::note warn
 「配列？？」「テーブルに配列なんてあんの？？」
:::


となった。

# BigQueryでよくみるデータ形式  - ネスト形式 -

実は、**BigQueryのカラムには配列も入れることが可能**で、
Google AnalyticsやFirebaseのデータには多くの配列が含まれています。

Fireabaseの送信する「イベント情報」は、1つのイベントに対して`params`という配列データが渡ってきます。

下記の部分で1行となり、

`event_params.key や event_params.value.string_value`

が配列となっています。

![https://cdn-ak.f.st-hatena.com/images/fotolife/h/hiroism0329/20210320/20210320230240.png](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hiroism0329/20210320/20210320230240.png)

一方、`event_dateやevent_timestamp, event_name` は配列ではないため、**空白**になっています。

ですが、データがないわけではなく、下記のリストのようにevent_paramsの中にさらにデータが入っているという感じになるので、
表形式で表現すると空白ができてしまいます。

```sql
- event_date
- event_date
- event_timestamp
- event_name
  - event_params
  - event_params1
  - event_params2
  - event_params3
```

これを**「ネストされたデータ形式」**といいます。

# ネストされたデータ形式

- **各レコードの1個のカラムに複数の値、つまり複数行に相当するデータを入れることができる仕様になっているのが大きな特徴**

実際のデータ構造：**1つのセルにテーブルが入るイメージ**

![実際のデータ構造](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hiroism0329/20210320/20210320204433.png)


- 通常のSQLであれば、**「列×行のデータ」**として格納されます。
- それに対して、「**RECORD型（レガシーSQL）**」「**配列型（標準SQL）」**は「**ネストされた構造**」を取ることとなり、ちょっと発想を変える必要がある。

```sql
type: RECORD型
mode: REPEATED
```

**クエリでは**

- **親エンティティ: `event_params`**
- **子エンティティ: `event_params.key`**

のようにドットをつけた`hoge.fuga`の形式でアクセスできる。

# 配列の中身を取るにはUNNESTとCROSS JOIN

「BigQueryには配列がある」ということを説明しましたが、エラーにもあったように、配列の中身をそのまま参照することができないです。

そこで、**配列の中身を参照するには、いったん配列を「展開」する必要**があります。

それをできるのが**UNNEST関数とCROSS JOIN**。

- UNNEST は**配列を行に展開する**関数
- 「入れ子構造を解く」という働きをしてくれます。
- CROSS JOINは総当り的にJOINする結合方法。
- CROSS JOINとUNNESTを組み合わせて使い、元のテーブル``{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}``に対して
配列のunnestをCROSS JOINのする。（**相関クロス結合**という処理）

# 書式と実行

### 書式

- `テーブル名 「,」 UNNEST as hoge`
- `,` がCROSS JOINの役割

### 実際にUNNESTとCROSS JOINを使ってみる

下記のようなSQLを実行すると、先程はevent_dateや`event_timestamp`, `event_name`が空白になっていたところが、埋め尽くされていて、
行番号も各行に振られているのがわかります。

```sql
select
  event_date
  , event_timestamp
  , event_name
  , e.key
  , e.value.string_value
from 'テーブル名',unnest(event_params) as e
```
### UNNEST前
![UNNEST前](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hiroism0329/20210320/20210320233423.png)

### UNNESTした結果
![UNNESTした結果](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hiroism0329/20210320/20210320233228.png)


つまり

- UNNEST(配列)とすることで、配列が1行ずつに展開できる。

また、

- `テーブル名 「,」 UNNEST as hoge` の`,`は`CROSS JOIN` にも置き換えられる。つまり上記のSQLはいかに置き換えられる。

```sql
select
  event_date
  , event_timestamp
  , event_name
  , e.key
  , e.value.string_value
from 'テーブル名',unnest(event_params) as e
```

![https://cdn-ak.f.st-hatena.com/images/fotolife/o/o0h/20171202/20171202200938.png](https://cdn-ak.f.st-hatena.com/images/fotolife/o/o0h/20171202/20171202200938.png)

これらをうまく使って

```sql
select
  e.value.string_value as hoge_name,
  count(*) as hoge_views
from 'テーブル名',unnest(event_params) as e
where event_name = "hoge_play_count"
  and e.key = "hoge_name"
group by 1
order by 2 desc
```

![https://cdn-ak.f.st-hatena.com/images/fotolife/h/hiroism0329/20210321/20210321000255.png](https://cdn-ak.f.st-hatena.com/images/fotolife/h/hiroism0329/20210321/20210321000255.png)

とネスト構造になっていたhoge_nameを抽出し、view数のランキングとして抽出できた。

# よく使う事例2：unnestを使って配列を横展開

配列を横展開し、1つの行として複数列（横型）に変換したい場合がある。

- ex/ 配列内のデータを失いたくない時
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/563702/5b434a0f-8a2c-088f-4f51-bec9439187d0.png)

    

- 理想
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/563702/01a71466-635a-00fb-d06d-3cd6fc79eee0.png)

    

このような場合、

:::note warn
selectの中で入れ子でselectする
:::

どういうことかというと、

```python
1/ from区の中でunnest関数を使って配列を展開
2/ whereでkey　を指定
3/ selectで必要な列(value)を取得
4/ 横展開が必要なkeyに対して,1~3を繰り返す
```

```sql
select 
  event_date
  , event_name
  , (select value.int_value from unnest(event_params) where key = 'firebase_previpus_id') as prev
  , (select value.int_value from unnest(event_params) where key = 'firebase_screen_id') as current
from 'テーブル名'
```

これで、横型にできる。

# 今日のまとめ

- **BigQueryのカラムには配列も入れることが可能**で、Google AnalyticsやFirebaseのデータには多くの配列が含まれている
- Fireabaseの送信する「イベント情報」は、1つのイベントに対して`params`という配列データが渡ってくる。
- **配列の中身を参照するには、いったん配列を展開する必要**があります。それをできるのが**UNNEST関数とCROSS JOIN**。
- UNNEST は配列をフラットなレコードに直す（展開する）ための関数
- 「入れ子構造を解く」という働きをしてくれます。
- CROSS JOINは総当り的にJOINする結合方法。
- 書式は`テーブル名 「,」 UNNEST as hoge`
- `「,」` は、CROSS JOIN にも置き換えられる。つまり`テーブル名 CROSS JOIN UNNEST as hoge`でもOK

### 参考
- [BigQuery 活用術: UNNEST 関数](https://developers-jp.googleblog.com/2017/04/bigquery-tip-unnest-function.html)
- [cross join unnestを利用して、カンマ区切りのデータを単一の列の複数レコードに分解する。 - Qiita](https://qiita.com/shohei727/items/80aebf3956d48fb509a6)
- [cross join unnestを利用して、カンマ区切りのデータを単一の列の複数レコードに分解する。 - Qiita](https://qiita.com/shohei727/items/80aebf3956d48fb509a6)