Cloud Firestore in Dataki, through BigQuery

02·Set it up

4 steps, about 15 minutes

1. 01

   Install the extension

   In the Firebase console, open Extensions, find Stream Firestore to BigQuery and choose Install. Or from a terminal:

   firebase ext:install firebase/firestore-bigquery-export --project=your-project
   firebase deploy --only extensions --project=your-project

   • Collection path: the collection to export, e.g. orders. A subcollection takes a wildcard, e.g. users/{uid}/orders.
   • Dataset ID defaults to firestore_export. Table ID is the prefix for everything it creates, e.g. orders.
   • Dataset location cannot be changed after installing. Pick the region the rest of your BigQuery data is in.
   • Table partitioning cannot be added later either. Set it now for a large collection.
2. 02

   Import the documents that already exist

   The extension only sees changes made after it is running. Once it is, import the rest with Google's script, which reads Firestore with your own credentials and asks for the project, the collection, the dataset and the table prefix. Give it the same values as the extension.

   gcloud auth application-default login
   npx @firebaseextensions/fs-bq-import-collection

   • Run it once. A second run imports every document again.
   • Imported rows are marked IMPORT in the operation column, and any later change to a document supersedes them.
3. 03

   Connect the dataset in Dataki

   Open app.dataki.ai/connect and, under Linked Google Cloud Projects, choose Link New Google Project. Sign in with a Google account that can manage IAM in the project holding the data, and pick that project. Dataki creates a service account named Dataki in it with two roles, BigQuery Data Viewer and BigQuery Job User: it can read tables and run queries.

   The project's datasets then appear among your sources. Pick firestore_export. Each dataset is one source in Dataki, and the free plan includes one.

   For each collection, Dataki sees orders_raw_changelog, one row per change, and orders_raw_latest, one row per document as it is now. The document is a JSON string in the data column, and Dataki reads its fields with JSON_VALUE.

4. 04

   Optional: give the fields real columns

   For documents with many fields, Google's schema-views tool builds typed views from a short file naming each field and its type. Dataki then sees real columns, with numbers and timestamps typed, instead of one JSON string.

   {
     "fields": [
       { "name": "status", "type": "string" },
       { "name": "total", "type": "number" },
       { "name": "createdAt", "type": "timestamp" }
     ]
   }

   npx @firebaseextensions/fs-bq-schema-views \
     --non-interactive \
     --project=your-project \
     --big-query-project=your-project \
     --dataset=firestore_export \
     --table-name-prefix=orders \
     --schema-files=./orders.json

   • It creates orders_schema_orders_latest and orders_schema_orders_changelog next to the raw tables.
   • An array field is unnested into _member and _index columns, one row per element, so count documents with COUNT(DISTINCT document_id).