Export Firebase Firestore data to BigQuery

This article is about "how to export Firestore data to BigQuery".

Use Firebase Extension if your service is before published

I haven't used yet but I think you can use this.

Firebase Extensions | Export Collections to BigQuery

This extension can sync Firestore data with BigQuery in realtime.

But if your service is already published, you have to move existing Firestore data to BigQuery by other method.

Use gcloud if your service is already published

You can use "gcloud" command (and "bq" command installed at the same time) to export it.

Create service account

Set roles refering these documents.

• Export and import data  |  Firebase
• Loading data from Firestore exports  |  BigQuery  |  Google Cloud

Command Example

When this commands are executed, BigQuery table is replaced with latest firestore data.

# Set service account key file
gcloud auth activate-service-account --key-file key.json

# Set project
gcloud config set project my-firebase-project

# Export Firestore data to Storage by collection group
gcloud firestore export gs://firestore-export-for-bq/2020-06-29T10:17:23.011+09:00 --collection-ids=user,shop,product

# Import data to BigQuery by collection group
bq load --source_format=DATASTORE_BACKUP --replace=true --projection_fields=name firestore.user gs://firestore-export-for-bq/2020-06-29T10:17:23.011+09:00/all_namespaces/kind_user/all_namespaces_kind_user.export_metadata
bq load --source_format=DATASTORE_BACKUP --replace=true --projection_fields=name,owners firestore.shop gs://firestore-export-for-bq/2020-06-29T10:17:23.011+09:00/all_namespaces/kind_shop/all_namespaces_kind_shop.export_metadata
bq load --source_format=DATASTORE_BACKUP --replace=true --projection_fields=name,price firestore.product gs://firestore-export-for-bq/2020-06-29T10:17:23.011+09:00/all_namespaces/kind_product/all_namespaces_kind_product.export_metadata

You can filter like privacy data by using projection_fields.

Schedule commands using Google official image in Docker Hub

Google publishes official Docker image to use gcloud commands.

google/cloud-sdk - Docker Hub

I think we can create scheduled BigQuery exporting easily by using this image with Cloud Run.