FirebaseExtensionの一つExport Collections to BigQueryについて

#はじめに
[Firebase Advent Calendar 2020 7日目](https://qiita.com/advent-calendar/2020/firebase)の記事になります

今回は[Firebase Extension](https://firebase.google.com/products/extensions?hl=ja)の一つである
[Export Collections to BigQuery](https://firebase.google.com/products/extensions/firestore-bigquery-export?hl=ja)をベースにテーブル構造などの基本的な情報から少し発展してAnalyticsのテーブルとどう関連付けるか？について書きます！


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

![スクリーンショット 2020-12-07 13.14.16（2）.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/75593/46a4ad98-bba4-353e-f9db-3db0edabae9a.png)

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

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

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

```json

[
   {
      "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の違い
最新のデータのみ含まれるかどうかだと認識してます:grin:

例えば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**を使用するのが良さそうです:muscle:

# レコードの特徴

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

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

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

```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 を設定する](https://firebase.google.com/docs/analytics/userid?hl=ja)を確認していただけると:clap:

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

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

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


## posts_raw_changelog or posts_raw_latestのuser_idの持ち方

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

### document_idがuser_idになっている場合

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

```sql
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として何番目か？を調べた上で抜き取る方法

```sql
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として使いたい場合

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

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

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

```sql

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

# さいごに

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

[Event, UserPropertiesを送っておくと戦略的に幅が広がる話](https://qiita.com/giiiita/items/9cb73850a3f12b0595d0)
[FirebaseAnalytics/BigQueryを使う上で覚えておきたい関数](https://qiita.com/giiiita/items/0c44515f248a575e25ee)
[FirebaseAnalytics運用のやらかし技師が教えるやらかし傾向と対策](https://qiita.com/giiiita/items/e0ab22b8a1f337aa54fd)

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