Firebase #2 Advent Calendar 2019の10日目の記事です。
半分ぐらいCGPの記事ですが、~~GCPカレンダーが埋まってしまったので~~ Firestore利用者のお役に立てればと思いこちらで書かせていただきます。

はじめに

現行プロジェクトでFirestoreからBigQueryにデータを同期して、分析等に使うことになりました。
調べたところBigQuery側でほぼ完結し、コードを書かずに済む方法があったので記事にしました。（ただしSQLは書いてます！）

Firestore→BigQueryの自動連携設定

Export Collections to BigQueryを使います。
従来はFIrestoreの更新に合わせて手動でBigQueryにデータを入れるか、FirestoreのバックアップをCloudStorageにエクスポートしてBigQueryにインポートする手段が取られていました。

Extensionは2019年9月にリリースされたもので、Firestoreの更新のたびにBigQueryに更新情報を渡すようになり常に最新版を得ることができます。実際にはCloudFunctionsにFirestoreの更新で起動するトリガーが登録されています。

注意点

BigQueryのロケーションを指定したい場合は、先にデータセットを作る必要があります
- 指定しないとデフォルトでUSになってしまうためです
この拡張機能を使用するには、Blazeプランを利用する必要があります
Collection1つにつき、1つの拡張機能が必要です

登録方法

install to consoleからプロジェクトを選び、次に進むを選び続けます（下記画像参照）

ロケーションはfirestoreと同じに
CollectionPathはfirestoreのコレクション名
Table IDはBigQuery側で新しく作るテーブル名（CollectionPathと同じで問題なし）

インストールを押して、しばらく待てば完了です。
あとはFirestore側で値が更新されると、BigQuery側でテーブルとビューを作成し、更新ログを登録してくれます。

BigQueryのデータをjson→RDB仕様に変換したビューの作成

ここまででデータセットに2つの情報が出来上がります。

xxx_raw_changelog（テーブル）
xxx_raw_latest（ビュー）

このうちビュー側のクエリを実行すると、結果カラムは5つあり、その中のdataカラムにjson形式でFirestoreの最新データが反映されてます。ビューをクエリ→SELECT data FROM ...に書き換えればそれが確認できます。

このままではdataカラムはjson形式なので、何かと使いにくいです。そこでjson形式→RDB形式に変換してビューで保存します。
実は当初テーブルを作る予定でしたが、ビューのほうが良いことに気づきました。もしテーブルを作るなら一度DELETEでテーブルを削除してからINSERT句で登録するクエリを作り、クエリのスケジュール登録する・・・くらいしか思いつきませんが。これだと最新版を反映するには手動でクエリを叩く必要もある一方、ビューにすれば常に最新版を反映することができます。

しかしどうやってjsonデータを加工するんだ？最悪SQLゴリゴリ書かないとなのか？と困ってたのですが、公式ドキュメントにありました。
標準 SQL の JSON 関数

以下例をもとに作っていきます。

例

もとのjsonデータ

[
  {
    "objectID": "001",
    "name": { "firstName": "太郎", "lastName": "田中" },
    "age": 30
  },
  {
    "objectID": "002",
    "name": { "firstName": "花子", "lastName": "山田" },
    "age": 20
  },
  {
    "objectID": "003",
    "name": { "firstName": "次郎", "lastName": "鈴木" },
    "age": 18
  }
]

生成したいビュー

objectID	lastName	firstName	age
001	田中	太郎	30
002	山田	花子	20
003	鈴木	次郎	18

クエリの作成

json形式から取り出すにはJSON_EXTRACTを使います。引数にjson形式のデータとプロパティ名を指定すると、文字列として取得できます。ネストしたオブジェクト形式のものを取り出す場合は関数もネストさせればOKです。整数データを取り出す場合はCASTで一度文字列に変換した後数値型に戻してあげればOKです。他にもJSON_EXTRACT_SCALARがありますが、これはjson形式で数値→文字列に変換する場合に使います。

SELECT 
  JSON_EXTRACT(data, '$.objectID') as objectID, /* 文字型 */
  JSON_EXTRACT(JSON_EXTRACT(data, '$.name'), '$.lastName') as lastName, /* ネストしたオブジェクト */
  JSON_EXTRACT(JSON_EXTRACT(data, '$.name'), '$.firstName') as firstName, 
  CAST(JSON_EXTRACT(data, '$.age') as INT64) as age /*数値型*/
FROM `dataのあるテーブル名`

あとはこれをビューで保存するだけでOKです。

実行結果

例と異なりますが、こんな感じになります。
これで今日からあなたもSQLライフを楽しめますね！

まとめ

私はGCP自体あまり触ったこと無いし、BigQueryはつい最近使い始めたばかりでしたが、色々調べながらうまく連携することができました。料金のコスパは全く考えてないのでそれが一番の心配ですが。。。実験的に使っていこうと思います。

ExtensionsでFirestore→BigQueryのエクスポート手順