はじめに

Firebase Advent Calendar 2020 7日目の記事になります

今回はFirebase Extensionの一つである
Export Collections to BigQueryをベースにテーブル構造などの基本的な情報から少し発展してAnalyticsのテーブルとどう関連付けるか？について書きます！

設定について

・location系
firestoreと合わせておくのが無難感
・CollectionPath
BigQueryにDataをExportしたい対象のCollectionPathを入れます。
・Dataset ID
firestoreのデータを流すDataSetの名前(ID)を入れます。
・TableID
対象CollectionPathのデータがExportされるTable名を入れます。

※以下設定を行った程で話を進めます
CollectionPath: posts
DatasetID: firestore_export
TableID: posts

テーブル構造

Export Collections to BigQueryの設定を行うと
BigQuery上にfirestore_exportと命名されたdatasetが作成されその中に以下二つのテーブルが含まれます。
posts_raw_changelog
posts_raw_latest

テーブルのスキーマは両方以下です。

[
   {
      "name": "timestamp",
      "type": "TIMESTAMP"
   },
   {
      "name": "event_id",
      "type": "STRING"
   },
   {
      "name": "document_name",
      "type": "STRING"
   },
   {
      "name": "operation",
      "type": "STRING"
   },
   {
      "name": "data",
      "type": "STRING"
   },
   {
      "name": "document_id",
      "type": "STRING"
   },
]

raw_changelogとposts_raw_latestの違い

最新のデータのみ含まれるかどうかだと認識してます

例えばPostsCollectionの同じDocumentに以下順に操作が行われたとします。
1. Documetを作成
2. Documentを更新
3. Documentを削除

raw_changelogとposts_raw_latestそれぞれのテーブルに含まれるレコードは以下です。

raw_changelog
レコード数は3つ(Documentの作成,更新、削除)
posts_raw_latest
レコード数は1つ(Documentの削除)

データを調べる際に
最新のDocumentを元に調べたい場合はraw_latestを使用するのが良さそうです

レコードの特徴

document_name

Documentの絶対path?が入ります。

projects/{project_name}/databases/(default)/documents/posts/{document_id}

data

json形式でDocumentの内容が入ります。
例えばpostsCollectionのDocumentのスキーマがauthorIdとbodyだった場合dataには以下のようにjsonで入ります。

{
 "authorId": "author_id_1",
 "body": "hello"
}

operation

CRAETE, UPDATE, DELETEのどれかが入る認識です。

document_id

カラム名どおりDocumentIDが入ります。

event_id

レコードごとのユニーク値と思えば良さそうです。

timestamp

作成、更新(UPDATE, DELETE)があったタイミングのtimestampになります。

Analyticsのテーブル結合方法

前提としてanalytics_データセットに含まれる
event_テーブルにuser_idというカラムが存在している程で話ます。

AnalyticsEvent送信時に
user_idをレコードに付与させる方法についてはユーザー ID を設定するを確認していただけると

方法としてはevent_テーブルと用途によって
どちらか(posts_raw_changelog or posts_raw_latest)をuser_idで結合させるという方法です。

結合するためposts_raw_changelog or posts_raw_latestから
user_idを抜き取って一時的なテーブルを作成するイメージです。

今回は最低限結合で使用するuser_idのみ抽出します

posts_raw_changelog or posts_raw_latestのuser_idの持ち方

大きくは3つあると思ってます。
種類があるのはfirestore上でどのようにデータを保持するのか？といった設計な話にも寄るかな？と思ってますが大きくはは3つあると思って良さそうです。
(こういうケースもあるなどございましたらご共有ください

document_idがuser_idになっている場合

特に工夫することがなく一番楽かと思います

WITH
posts AS (
 SELECT 
  document_id as user_id
 FROM `{project_name}.firestore_export.posts_raw_latest`
)

document_nameにuser_idが含まれている場合

projects/{project_name}/databases/(default)/documents/users/{user_id}/posts/{document_id}

↑このようなusersのsubCollectionにpostsが存在している場合
pathは"/"で区切られているのでuser_idが"/"で分割した場合一番最初を0として何番目か？を調べた上で抜き取る方法

WITH
posts AS (
  SELECT 
   SPLIT(document_name, '/')[OFFSET(6)] as user_id
  FROM `{project_name}.firestore_export.posts_raw_latest`
 )

dataにuser_idが含まれている場合

dataの内容が以下でauthorIdをuser_idとして使いたい場合

{
 "authorId": "author_id_1",
 "body": "hello"
}

WITH
posts AS (
  SELECT 
   JSON_EXTRACT(data, '$.authorId') as user_id,
  FROM `{project_name}.firestore_export.posts_raw_latest`
 )

上3つのどれかの方法で一時テーブルを作成した程でeventテーブルと結合させる

SELECT
 *
FROM 
 `{project_id}.analytics_{id}.events_20201207` AS events
LEFT JOIN posts ON events.user_id = posts.user_id

さいごに

Analytics系では以下の記事もありますので興味をお持ちの方はぜひ！

Event, UserPropertiesを送っておくと戦略的に幅が広がる話
FirebaseAnalytics/BigQueryを使う上で覚えておきたい関数
FirebaseAnalytics運用のやらかし技師が教えるやらかし傾向と対策

弊社ではこんな感じで使ってるよーであったりこんな悩みがあるとかもぜひ聞きたいです