FirebaseAnalytics/BigQueryを使う上で覚えておきたい関数

# はじめに

この記事は
[Diverse Advent Calendar 2019](https://qiita.com/advent-calendar/2019/diverse)の23日目の記事になります！

前日の記事は[@tacksman]さんの[FlutterとFirestoreを使い始めて一ヶ月がたった](https://qiita.com/tacksman/items/90c3236d68e68a8632ac)というタイトルの記事でした！

僕自身はiOS(Swift)とUnity(C#)でFirestoreを使った経験はあるのですが、
話題のFlutterでFirestoreを使ったことがなく
使い始めて系の記事はこの組み合わせでは未経験の僕には入りやすかったです！

日本語系の記事が少ないようなのでDiverseからどんどん日本語での記事が増えていくといいですね:ok_hand:

前日の記事についてはここまでにさせていただき
今回はFirebase Analytics関連のお話でBigQueryについてになります。

MySQLなどのDB操作の経験はあったのですが、
見慣れないテーブル構造に戸惑った結果FirebaseAnalyticsと連携するBigQueryのテーブル構造を分解してデータを抽出していくにはある関数を知る必要があるよって話になります。

Firebase Analyticsについては以下記事で触れてるのでよかったらぜひ！
[Event, UserPropertiesを送っておくと戦略的に幅が広がる話](https://qiita.com/giiiita/items/9cb73850a3f12b0595d0#event%E3%81%AE%E5%88%B6%E9%99%90)

# BigQuery

[BigQuery](https://cloud.google.com/bigquery/?hl=ja)は、
Google が提供するサーバーレスでスケーラビリティに優れた企業向けデータウェアハウスです。

主に行動ログを貯めるために使用している企業が多いのではないでしょうか？

データを貯めてBIツールで欲しいデータを整形しダッシュボードに表示をして
施策の良し悪しの判断や次にやる施策の方針決めに使ってる企業もあると思います。

ちなみにDiverseでは[Looker](https://ja.looker.com/demo?utm_campaign=7012R000001JFsI&utm_ppccampaign=ja_brand&gclid=Cj0KCQiAxfzvBRCZARIsAGA7YMx2RIcchrdt-2-Y2g_UKnCJE4Fx_I8-BdrGZ40KGlji0SJ2hECs4KsaAmZREALw_wcB)というBIツールを使用しており今年の弊社アドカレでも触れられているのでこちらもよかったらご覧になってください！
[BIツールが定着していない会社でLookerを導入して1年後の世界](https://qiita.com/m_kumamomi/items/5c5e27469eccde8d6fde)

次にこのBigQueryがFirebaseとどのように絡んでくるのかみてみましょう

# Firebase * BigQuery

FirebaseプロジェクトとBigQueryを連携すると
以下6つのFirebaseが提供するサービスのデータをBigQueryに流すことができます。

**Google Analytics**
**Crashlytics**
**Predictions**
**Cloud Messaging**
**Performance Monitoring**

**Firestore**
今年のFirebaseSummit2019で発表された[Extensions](https://firebase.google.com/products/extensions)([githubはこちら](https://github.com/firebase/extensions))を使用することで
FirestoreのCollection単位でBigQueryにデータを流すことが可能です。

Firebaseが提供するサービスの全てがBigQueryと連携しているわけではないですが、
連携すると良いサービスについてはプロダクトチームの判断で連携することが可能だよって感じですかね！

# Analyticsのレコードを覗いてみる

MySQLを使用している人には馴染みの表でデータが表現されています。
1レコードはアプリから送信した一つのEventを表現しており
複数のカラムで1つのEventがログとして保存されてます。

<img width="721" alt="スクリーンショット 2019-12-22 23.04.03.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/75593/83c2f00b-1edb-d962-350a-31bfbca94ef8.png">

1レコードに含まれる情報量多いですよね

詳細が気になる方は[こちら](https://support.google.com/firebase/answer/7029846?hl=ja)をみてみると良いかもしれません。

小ネタになるのですが、
以下スクショのようにEventごとにUserPropertiesでフィルターをかけることが可能です！

これが実現できるのもEventのレコードにUserPropertiesの情報が含まれているからなんですかね！
<img width="672" alt="スクリーンショット 2019-12-23 1.09.56.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/75593/1ab05de2-be2d-a754-b3a4-e56a05f092b2.png">


## データ構造

前提としてマッチングのメッセージの例です。
チャットルームにてメッセージを送信したというEventを送る際に
EventのParametersとしてroomIDと送信者のIDを送るというケースです。

senderIDがxxxのUserをBigQueryから抽出することを本記事では目標にしてみましょう

```swift
Analytics.logEvent(
    "sent_message",
    parameters: [
        "roomID": "1",
        "senderID: "xxx"
    ]
)
```

BigQuery上での構造は以下の内容です。
正直初めてこの構造をみた時、senderIDがxxxの人を抽出するQueryを書くのに悩みました:cry:
MySQLは多少触れたことがあったのですがこのような構造に遭遇してこなかったため未知との遭遇

<img width="844" alt="スクリーンショット 2019-12-22 23.42.20.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/75593/7e3d6bbd-a363-3fad-f608-055cf2f0de37.png">

未知との遭遇のためそいつの正体を知る必要があります:spy_tone3:
BigQuery上のデータ構造を少し分解した図が以下になります。


<img width="936" alt="スクリーンショット 2019-12-22 23.39.43.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/75593/bb51779b-9b5b-666a-adf3-91dba71e4e30.png">

どうやら1カラムにMapで値が入っているように見えますね！
ではどうやってネストされた構造からsenderIDがxxxの人を抽出することができるのでしょうか？

# UNNEST関数

FirebaseAnalyticsのBigQueryのデータ構造を分解していく上で重要なのがこの**UNNEST関数**です
[UNNEST関数](https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja#flattening-arrays)の役割としてはネストされた構造を一組の行に変換することです。

Queryの良し悪しは出ちゃいますが今回はこんな感じで抽出してみました。

UNNESTで欲しい箇所のネストを行に変換したQueryをSubQueryとして今回やってみました。
(Query力低いのでこれもできるとかあればぜひ:sweat_smile:)

```sql
WITH
senderInfo AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'roomID') as roomID,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'senderID') as senderID
  FROM
    `{your_project}.analytics_216055106.events_*`
  WHERE
    event_name = "sent_message"
  GROUP BY roomID, senderID
)
SELECT * FROM senderInfo WHERE senderInfo.senderID = "xxx"
```

Queryを実行する前にどれくらいの容量がかかるのかを確認できるのでConsoleから直で実行する際には確認しておくと良さそうです。
仕組みとしてはDry Runでおおよその容量を目安として表示しているのかなと思います。

<img width="1136" alt="スクリーンショット 2019-12-23 13.51.44.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/75593/80ab72f6-0e18-4e72-397c-52dd3cc703b1.png">
実際の金額いくらなの？っていうのを知りたい場合は、
[Google Cloud 料金計算ツール](https://cloud.google.com/products/calculator/?hl=ja)で確認してみるといいでしょう。





#  さいごに

まだ使い始めて一年も立ってないのでペーペーですが
、今後知見が溜まり次第その知りたい情報はBigQueryを使うべきか？FirebaseConsoleで完結するかの線引き基準などそういう少し掘り下げた記事を書いてみようと思います:v: