FirestoreのドキュメントをBigQueryに連携して集計する

# 差分確認クエリ
SELECT * FROM `プロジェクト名.firestore_export.xxx_raw_changelog`

# 最新ビュー確認クエリ
SELECT * FROM `プロジェクト名.firestore_export.xxx_raw_latest`

SELECT * FROM `プロジェクト名.firestore_export.xxx_raw_latest`

SELECT *
FROM `プロジェクト名.firestore_export.xxx_raw_changelog`
WHERE document_name = "bigquery-mirror-test"
ORDER BY TIMESTAMP ASC

# GCPの認証
gcloud auth login

 ❯ npx @firebaseextensions/fs-bq-import-collection
? What is your Firebase project ID?
# FirebaseのプロジェクトID

? What is your BigQuery project ID?
# BigQueryのプロジェクトID

? What is the path of the the Cloud Firestore Collection you would like to import from? (This may, or may not, be the same Collection for which you plan to mirror changes.)
# インポートしたいFirebaseのコレクション名

? Would you like to import documents via a Collection Group query?
# よくわからなかった

? What is the ID of the BigQuery dataset that you would like to use? (A dataset will be created if it doesn't already exist)
#  BigQueryの保存先のデータセット名。先ほどインストールしたFirebase拡張のものと合わせる。インストールする際にデフォルト設定で進めていた場合、firestore_exportとなる。

? What is the identifying prefix of the BigQuery table that you would like to import to? (A table will be created if one doesn't already exist) 
# BIgQueryのデータセット内に用意するテーブル、ビューの名前の接頭辞

? How many documents should the import stream into BigQuery at once?
# インポートの流量。デフォルトの300で良さそうだが、データ量多ければ大きくして良い

? Where would you like the BigQuery dataset to be located? 
# BigQueryデータセットの「データのロケーション」。東京ならasia-northeast1

? Would you like to run the import across multiple threads? 
# yesにしておくと高速に処理されそう

? Would you like to use the new optimized snapshot query script? 
# yesにしておくと高速に処理されそう

? Would you like to use a local firestore emulator? 
# よくわからなかった

WITH latest AS (
  SELECT
    max(timestamp) as latest_timestamp,
    document_name
  FROM
    `Firebaseプロジェクト.firestore_export.mid_term_test_raw_changelog`
  GROUP BY
    document_name
),
extracted_data AS (
  SELECT
    JSON_EXTRACT(data, '$.uid') AS uid,  --- JSONとして取り込まれたデータから、キーを指定して値を取得
    JSON_EXTRACT(data, '$.test_id') AS test_id, 
    JSON_EXTRACT(data, '$.score') AS score,
  FROM
    `Firebaseプロジェクト.firestore_export.mid_term_test_raw_changelog` AS t
    JOIN latest ON (
      t.document_name = latest.document_name
      AND (
        IFNULL(t.timestamp, timestamp("1970-01-01 00:00:00+00"))
      ) = (
        IFNULL(
          latest.latest_timestamp,
          timestamp("1970-01-01 00:00:00+00")
        )
      )
    )
  WHERE
    operation != "DELETE"
)
SELECT
  uid AS user,
  COUNT(*) AS count,
  ARRAY_AGG(
    STRUCT(
      test_id,
      score
    )
  ) AS scores
FROM
  extracted_data
GROUP BY
  uid;