はじめに

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

ハマったポイント

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

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

すると

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

と、エラーになった。

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

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

となった。

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

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

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

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

event_params.key や event_params.value.string_value

が配列となっています。

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

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

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

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

ネストされたデータ形式

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

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

通常のSQLであれば、**「列×行のデータ」**として格納されます。
それに対して、「RECORD型（レガシー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が空白になっていたところが、埋め尽くされていて、
行番号も各行に振られているのがわかります。

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

UNNEST前

UNNESTした結果

つまり

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

また、

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

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

これらをうまく使って

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

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

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

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

ex/ 配列内のデータを失いたくない時
理想

このような場合、

selectの中で入れ子でselectする

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

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

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入門者がはじめに躓くだろうネスト型との付き合い方