こんにちは、サーバーサイドやっております金城(@o0h_)です。
最近通読した漫画はフットボールネーションです面白いですね・・・もちろん喧嘩稼業（９）も買いました！！！！
よろしくおねがいします。
あどべんと！
この記事は、Connehito Advent Calendarのday-2です！
やっぱりPHPer的には7.2がホットなネタ！？などとも思ったのですが、
@kiyoeshiの温かいUX改善を読んで、私もfumufumuとなりましたので
データ分析系のテーマで1本ぶっこんでみます。
ここのところ社内でデータ部分析基盤を整えていこうという動きがり、その流れで話題にした内容になります。
「Firebaseで集めた記録を、BigQueryで簡単に「生きたデータ」化しようね」という話です。
今更始める Firebase + BigQueryを使ったサクサクデータ分析
先日、ディレクターからこんな相談を受けました。
"ママリアプリ内で記事を読んでいる人が、事前にどんなコンテンツを見ていたのかを知りたい"。
Firebaseで「閲覧イベント」を記録していて、その属性として「閲覧コンテンツ」や「リファラとなるコンテンツ」を埋め込んいるので、無事にニーズに答える事ができました。
この記事では、その際に活用した「BigQueryの使い方」を紹介します。
💡 オススメできる、秀逸な元ネタがあります
最初にお断りしておくと、Google Developersに非常によくまとまっている記事があります。
developers-jp.googleblog.com
なので、まずはサッとこちらの記事に目を通していただくのが良いと思います。それで「違和感がなかった、理解ができた」のであれば、いま私が書いているこの記事を読むのに貴重な時間を費やしていないで貴方の分析の世界に戻りましょう！
実際に私もこちらの記事を参考に知識を得ましたし、書いてある通りに動かせた時は相当感動した(!)事を覚えています。
ただし「BigQueryに慣れている人向け」に書かれているなという印象も受けたのです。なので、もう少し「手前のイメージ」に焦点を当てて解説をしてみたいなーと思って記事にしてみました！*1
こんな人向け
- Firebase AnalyticsのBigQuery Export*2を知っている・設定しているが使いこなせている感が薄い・・・ 😩
- SQLは書けるけど、BigQueryはそんなに触っていないかもしれない😼
- BigQueryの「レコード型」について馴染みがない、もしくは苦手意識がある😨
- もっと呼吸をするかのように分析の世界にダイブしたい！😼
これらに当てはまる人は、最後まで気合でお付き合いいただけると幸いです・・・！
まずは、アウトプットイメージを共有
データベースや分析の話をしようとすると、どうしても抽象的な事柄や概念が多く出てきます。
そしてこの記事の目的も「イメージを掴んでもらうこと」に終止します。そのため、この先「BigQueryの不思議な機能」*3をたくさん紡いでいくつもりです。
まず最初に「我々の行き着く先」を示すことで、「結局なにをしたいんだっけ」を回避したり「具体と抽象を行き来すること」をサポートしたいと思います。 なお、この記事ではStandard SQLの利用を前提とします。*4
扱う題材
Googleの用意しているサンプルデータを使わせていただきます！*5 https://bigquery.cloud.google.com/dataset/firebase-analytics-sample-data:ios_dataset?pli=1
これは iOS と Android のクロスプラットフォーム ゲームアプリのサンプル とのことです。
今回は、iOSの1テーブルのみを用いて*6話を進めしょう。
ゲームということで、データを眺めていたら「アイテム購入」というイベントが記録されていました。
分析の定番である「売れ筋商品を調べる」を、今回のケースとして取り扱ってみたいと思います！
「アイテム購入」というイベント1回分が以下のような形式で記録されています。
{ "name": "item_purchased", "params": [ { "key": "firebase_event_origin", "value": { "string_value": "app", "int_value": null, "float_value": null, "double_value": null } }, { "key": "product_amount", "value": { "string_value": null, "int_value": "3", "float_value": null, "double_value": null } }, { "key": "product_category", "value": { "string_value": "powerups", "int_value": null, "float_value": null, "double_value": null } }, { "key": "coin_cost", "value": { "string_value": null, "int_value": "270", "float_value": null, "double_value": null } } ], "timestamp_micros": "1465272865000066", "previous_timestamp_micros": "1465343504200066", "date": "20160607" }
この中で、今回着目したい属性はこちらです。
- product_category
- product_amount
- coin_cost
amoutに関しては「○個パック」的な分類になっているっぽいので、「カテゴリ・バリエーションごとの」「売上回数・売上額」を調べてみたいと思います。
最終的に、以下のようなアウトプットとなります。
| Row | category | amount | total_cost | total_amount | cnt | |
|---|---|---|---|---|---|---|
| 1 | keys | 10 | 4000 | 40 | 4 | |
| 2 | coins | 150 | null | 1350 | 9 | |
| 3 | coins | 300 | null | 300 | 1 | |
| 4 | powerups | 25 | 13500 | 150 | 6 | |
| 5 | powerups | 3 | 4320 | 48 | 16 |
抑えておきたいポイント
こらから書き上げようとしているのは、まるで「初見殺し」のような印象さえ受けかねないSQL文となります。
件のGoogle Developersのエントリーでは、わざわざ言及されていないような概念も多分に出現していました。
独断と偏見で「ここだけ理解されていれば応用が効く！」と感じる点を、5つに分けて説明します。
- ポイント①: 「レコード型」は階層化構造をとることを理解する
- ポイント②:
UNNEST()を利用して配列をフラット化 - ポイント③: CROSS JOINを用いた絞り込み
- ポイント④: SELECT句の中にサブクエリを利用できる
- ポイント⑤: FIrebaseのスキーマについて
ポイント①: 「レコード型」は階層化構造をとることを理解する
通常のSQLであれば、「テーブル」として列×行のデータとして格納されます。
それに対して、「レコード型」は「ネストされた構造」を取ることとなりますので、ちょっと発想を変える必要があります。
例えば、これはBigQuery Consoleのpreviewをキャプチャしたものなのですが、視覚的には何段にもなっているこのデータが「1行」の内容になります。
あるいは、「レコード型データを展開するように」という指示でSELECTを掛けた様子が以下です。
ポイント②: UNNEST() を利用して配列をフラット化
Fireabaseの送信する「イベント情報」は、1つのイベントに対して paramsという配列データが渡ってきます。
先程jsonで示したのを見ると直感的に理解しやすいと思います。
「複数の値やフィールドを1つのデータとして扱うときには便利」な反面、「単一値をもつカラムとはちょっと使い勝手が変わってくる」というのが、SQLの世界観に配列がいることの便利さと難しさであると思います。
例えば、「アイテム購入のデータだけを取ってこよう」として以下のクエリを投げるとエラーとなります。
そこで登場するのが、 UNNEST() という関数です。
これはその名の通り、「入れ子構造を解く」という働きをしてくれます。
実際のデータに対して利用するにはCROSS JOINを一緒に用いることが多いので、ごくシンプルなコンテンツを用いて例を示します。
「配列型であるカラムに対して UNNEST を用いた」時の様子が下図です。
ポイント③: CROSS JOINを用いた絞り込み
Standard SQLでは FROM 句の中に , で対象をつなげることによって、CROSS JOINを利用することができます。
CROSS JOINやその記法自体はBigQuery特有の話題ではないのですが、本格的にUNNEST と 配列型 の話を取り上げる前に小さいサンプルを示させてください。*7
例えば、以下のように「最高の夏の予定」というデータがあるとします*8
ここから、「Takeshiと一緒に行く予定」のみを抽出するにはどうすれば良いでしょう？
そのときに必要なのが、UNNESTなのです。
絞り込みたい列である party を「フラット化」すると以下のようになります。
あるいは「予定と同行者」のみに絞って全データを俯瞰すると、「交差結合されているということ」がより鮮明でしょう。
つまり、「オリジナルのデータ」の横に「配列化されていたデータの、それぞれの要素が1つずつ全組み合わせ」くっついた状態になるのです。
「絞り込みたい列」が単一のデータになってしまえば、あとは通常通りにWHEREに利用することができます。
これが、「(配列型と)UNNESTの威力」になります。
ポイント④: SELECT句の中にサブクエリを利用できる
例えば、このようなテーブルがあったとします。
「各生徒の得点と、平均点の差」を出したいときに、このような書き方も可能になります。
ポイントは、1データだけ返すようにSELECTを組む
ということです。
単体で説明しても「どのようにありがたいのか」というイメージが湧きづらく、「もっとシンプルに書けるのでは」という印象を受けそうな機能です。
具体的な活用方法を示そうとすると、どうしても他の知識が必要な高度なクエリになってしまうと思います・・・
そのため、ここでは敢えて魅力を語ろうとはしないことにします。
構文として「SELECTにSELECTが書ける・・・」ということだけ抑えておいてください！
ポイント④: WITH句の利用が可能に
Standard SQLの機能として、WITH句が利用可能になっています。
これはBigQuery特有の事項ではないので、あまり馴染みがないようでしたらSQL一般の書籍やサイトの情報も参考できるかと思います。(ので、サクッと次に行きます！)
実際に、先の「テスト結果」のデータはこれを用いて生成されたものでした。
必ずしも必要になる使い方ではありませんが、どうしてもJOINやUNNESTの多用が増えてクエリが複雑になりがちなので、Viewを利用して見た目を整えていくのがおすすめです。
ポイント⑤: FIrebaseのスキーマについて
ここまでBigQuery自体の話をしてきましたが、いよいよ実データ = Firabaseの話になります。 Firebase Analytics からエクスポートされるデータは、以下のようになります。 support.google.com
肝となるのは、
ユーザー(user_dim)単位で1レコードとなり、従属するイベント(event_dim)が配列としてぶら下がっている- eventは
nameとparamsからなり、paramsはkeyとvalueからなる構造体である - 更に
valueはそれ自体が構造体で、string/int/float/doubleという各データ型を持ち、対応するデータ型のフィールドにのみ値をもつ
といった点でしょうか。 詳しく言うと、
- 「アイテム購入」というイベントに対して
- 「購入アイテム名」は「文字列」なので
event_dim[i].params[name=item_name].value.string_valueに格納される - 「購入額」は「文字列」なので
event_dim[i].params[name=cost].value.int_valueに格納される
と言った様子です。
実際に試してみる！
随分と長くなってしまいましたが、武器は全て揃いました！
冒頭で示した命題「アイテム購読における、カテゴリ・バリエーションごとの売上回数・売上額」のデータを抽出してみたいと思います。
早速になりますが完成形を示しますので、これまでのポイントを踏まえて解釈してみてください。
WITH purchases AS ( SELECT (SELECT value.string_value FROM UNNEST(events.params) WHERE key = 'product_category') AS category, (SELECT value.int_value FROM UNNEST(events.params) WHERE key = 'coin_cost') AS coin_cost, (SELECT value.int_value FROM UNNEST(events.params) WHERE key = 'product_amount') AS amount FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`, UNNEST(event_dim) AS events WHERE events.name = 'item_purchased' ) SELECT category, amount, SUM(coin_cost) AS total_cost, SUM(amount) AS total_amount, COUNT(*) AS cnt FROM purchases GROUP BY category, amount
purchases viewができてしまえば、見慣れた通りのSQL文になるのではないでしょうか。
やっていることとしては、
event_dimをまずUNNESTでフラット化して- そのデータを元データの
eventsテーブルへCROSS JOIN - 結合されたデータは
event_dimの各要素となるSTRUCTUREの属性で絞りこめるので、イベント名がitem_purchasedのデータで写像を組み上げる - 更に各レコード(=イベント)は配列型で
paramsを持つので、 これもUNNESTして「絞り込み可能」に - すると「購入情報」が取得可能になるので、
categorycostamountのそれぞれの属性について、 - 各々のデータ型に対応するフィールドから、実際の値を取得する
という処理です。
慣れるまでは複雑ではありますが、抑えるべき概念を抑えて「配列型」として「集合の中に、プチ集合がある」というようなイメージを描けると、少し見通しが良くなるのではないでしょうか。*9
Google Developersには、こんな一文があります。
UNNEST 関数を使いこなせるようになると、そのすばらしさに気づくはずです。 そして、Firebase Analytics のデータ操作がずっと楽しくなるでしょう。
最後に
改めて、「自分が苦しんだ点」を中心に掘りつつアウトプットとしてみました。
書いてて随分と長くなったので、自分でかなりびっくりしています・・・
お付き合いいただき、ありがとうございました 😩
この記事が、私のような「ドキュメントはGet Startedですら読むのを面倒臭がるタイプ」のエンジニアやプランナーのお役に立てることを祈ります・・！
次回予告
アドベントカレンダー3日目は、弊社の開発とサービスの基盤を支える @shnagaiによるエントリーです 🙆 qiita.com
*1:そもそも、「知らない人」にとっては、この話はググるためのイディオムが浮かばないですよね。。どうにかStackoverflowにたどり着いて→見慣れない関数を見つけて→UNNESTをキーワードにして検索したらたどり着いた、という経緯でした
*2:こちらのGoogleのドキュメントを参照してください
*3:まさにwonder!!なサービスですよね、と思います。Google様すごい
*4:Standard SQLの利用方法やメリットについては、VASILYさんの記事が簡潔にまとまっていてわかりやすいので、ご参照ください
*5:内容に言及している記事: Google Developers Japan: BigQuery と Firebase Analytics でモバイルアプリを理解する
*6:Standard SQLで複数テーブルを用いる場合、ワイルドカード テーブルを使用した複数テーブルの照会やWITH句を用いたviewの利用などの手段があります
*7:私自身、業務でCROSS JOINを使うことなど無いので、パット見でピンとこなかった・・というのが正直なところでして。。そのため、本記事に取り上げました。
*8:遊ぶ予定をペタバイト級のデータウェアハウスで管理って何事なのでしょう・・
*9:「テスト結果」の例で示したように、まさに「WITH句でテーブル様のデータセットを作ってみる」という事が可能なわけです。ここに触れると、少し「配列」「フラット化」への抵抗が薄れるかもしれません。