Loading data from Firestore exports  |  BigQuery  |  Google Cloud (2023)

BigQuery supports loading data from Firestoreexports created using the Firestoremanaged import and export service.The managed import and export service exports Firestore documentsinto a Cloud Storage bucket. You can then load the exported data into aBigQuery table.


When you load data into BigQuery from a Firestoreexport, note the following restrictions:

  • Your dataset must be in the same location as the Cloud Storage bucketcontaining your export files.
  • You can specify only one Cloud Storage URI, and you cannot use a URIwildcard.
  • For a Firestore export to load correctly, documents in theexport data must share a consistent schema with fewer than 10,000 unique fieldnames.
  • You can create a new table to store the data, or you can overwrite an existingtable. You cannot append Firestore export data to an existing table.
  • Your export commandmust specify a collection-ids filter. Data exported without specifying acollection ID filter cannot be loaded into BigQuery.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.

Required permissions

To load data into BigQuery, you need IAM permissions to run a load job and load data into BigQuery tables and partitions. If you are loading data from Cloud Storage, you also need IAM permissions to access the bucket that contains your data.

Permissions to load data into BigQuery

To load data into a new BigQuery table or partition or to append or overwrite an existing table or partition, you need the following IAM permissions:

  • bigquery.tables.create
  • bigquery.tables.updateData
  • bigquery.tables.update
  • bigquery.jobs.create

Each of the following predefined IAM roles includes the permissions that you need in order to load data into a BigQuery table or partition:

(Video) Exporting Data From Cloud Firestore

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (includes the bigquery.jobs.create permission)
  • bigquery.user (includes the bigquery.jobs.create permission)
  • bigquery.jobUser (includes the bigquery.jobs.create permission)

Additionally, if you have the bigquery.datasets.create permission, you can create andupdate tables using a load job in the datasets that you create.

For more information on IAM roles and permissions inBigQuery, see Predefined roles and permissions.

Permissions to load data from Cloud Storage

To load data from a Cloud Storage bucket, you need the following IAM permissions:

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list (required if you are using a URI wildcard)

Loading Firestore export service data

You can load data from a Firestore export metadata file by usingthe Google Cloud console, bq command-line tool, orAPI.

Sometimes Datastore terminology is used in the Google Cloud consoleand the bq command-line tool, but the following procedures are compatible withFirestore export files. Firestore and Datastore sharean export format.


  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project, and then select a dataset.
  3. In the Dataset info section, click add_box Create table.
  4. In the Create table panel, specify the following details:
    1. In the Source section, select Google Cloud Storage in the Create table from list. Then, do the following:
      1. Select a file from the Cloud Storage bucket, or enter the Cloud Storage URI. You cannot include multiple URIs in the Google Cloud console, but wildcards are supported. The Cloud Storage bucket must be in the same location as the dataset that contains the table you want to create, append, or overwrite.
        The URI for your Firestore export file must end with KIND_COLLECTION_ID.export_metadata. For example, in default_namespace_kind_Book.export_metadata, Book is the collection ID, and default_namespace_kind_Book is the file name generated by Firestore. If the URI doesn't end with KIND_COLLECTION_ID.export_metadata, you receive the following error message: does not contain valid backup metadata. (error code: invalid). Loading data from Firestore exports | BigQuery | Google Cloud (1)
      2. For File format, select Cloud Datastore Backup. Firestore and Datastore share the export format.
    2. In the Destination section, specify the following details:
      1. For Dataset, select the dataset in which you want to create the table.
      2. In the Table field, enter the name of the table that you want to create.
      3. Verify that the Table type field is set to Native table.
    3. In the Schema section, no action is necessary. The schema is inferred for a Firestore export.
    4. Optional: Specify Partition and cluster settings. For more information, see Creating partitioned tables and Creating and using clustered tables.
    5. Click Advanced options and do the following:
      • For Write preference, leave Write if empty selected. This option creates a new table and loads your data into it.
      • If you want to ignore values in a row that are not present in the table's schema, then select Unknown values.
      • For Encryption, click Customer-managed key to use a Cloud Key Management Service key. If you leave the Google-managed key setting, BigQuery encrypts the data at rest.
    6. Click Create table.


Use the bq loadcommand with source_format set to DATASTORE_BACKUP.Supply the --location flag and set the value to yourlocation. If you are overwitingan existing table, add the --replace flag.

To load only specific fields, use the --projection_fields flag.

bq --location=LOCATION load \--source_format=FORMAT \DATASET.TABLE \PATH_TO_SOURCE

Replace the following:

  • LOCATION: your location. The --location flagis optional.
  • FORMAT: DATASTORE_BACKUP.Datastore Backup is the correct option for Firestore.Firestore and Datastore share an export format.
  • DATASET: the dataset that contains the tableinto which you're loading data.
  • TABLE: the table into which you're loading data.If the table doesn't exist, it is created.
  • PATH_TO_SOURCE: theCloud Storage URI.

For example, the following command loads thegs://mybucket/20180228T1256/default_namespace/kind_Book/default_namespace_kind_Book.export_metadataFirestore export file into a table named book_data.mybucket and mydataset were created in the US multi-region location.

(Video) How to Export & Import Database from FireStore || Firebase Cloud Database || In Just 2 Steps

bq --location=US load \--source_format=DATASTORE_BACKUP \mydataset.book_data \gs://mybucket/20180228T1256/default_namespace/kind_Book/default_namespace_kind_Book.export_metadata


Set the following properties to load Firestore export datausing the API.

  1. Create a load job configuration that points to the source data inCloud Storage.

  2. Specify your location in the locationproperty in the jobReference section of the job resource.

  3. The sourceUris must be fully qualified, in the formatgs://BUCKET/OBJECT in theload job configuration. The file (object) namemust end in KIND_NAME.export_metadata. Only one URIis allowed for Firestore exports, and you cannot use a wildcard.

  4. Specify the data format by setting the sourceFormat property toDATASTORE_BACKUP in the load job configuration. Datastore Backupis the correct option for Firestore. Firestore andDatastore share an export format.

  5. To load only specific fields, set the projectionFields property.

    (Video) Import/Export Firebase Firestore Data of a project | Generate .json file of Firebase Firestore

  6. If you are overwriting an existing table, specify the write dispositionby setting the writeDisposition property to WRITE_TRUNCATE.


Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

View on GitHub Feedback

# TODO(developer): Set table_id to the ID of the table to create.table_id = "your-project.your_dataset.your_table_name"# TODO(developer): Set uri to the path of the kind export metadatauri = ( "gs://cloud-samples-data/bigquery/us-states" "/2021-07-02T16:04:48_70344/all_namespaces/kind_us-states" "/all_namespaces_kind_us-states.export_metadata")# TODO(developer): Set projection_fields to a list of document properties# to import. Leave unset or set to `None` for all fields.projection_fields = ["name", "post_abbr"]from google.cloud import bigquery# Construct a BigQuery client object.client = bigquery.Client()job_config = bigquery.LoadJobConfig( source_format=bigquery.SourceFormat.DATASTORE_BACKUP, projection_fields=projection_fields,)load_job = client.load_table_from_uri( uri, table_id, job_config=job_config) # Make an API request.load_job.result() # Waits for the job to complete.destination_table = client.get_table(table_id)print("Loaded {} rows.".format(destination_table.num_rows))
(Video) Import CSV Data to Firestore using GCP
(Video) Export Firestore collections to BigQuery and use Data Studio to analyze and aggregate data

Firestore options

To change how BigQuery parses Firestore exportdata, specify the following option:

Google Cloud console option `bq` flag BigQuery API property Description
Not available --projection_fields projectionFields (Java, Python) (Optional) A comma-separated list that indicates which document fields to load from a Firestore export. By default, BigQuery loads all fields. Field names are case-sensitive and must be present in the export. You cannot specify field paths within a map field such as map.foo.

Data type conversion

BigQuery converts data from each document inFirestore export files to BigQuerydata types.The following table describes the conversion between supported data types.

Firestore data type BigQuery data type
Reference RECORD
Date and time TIMESTAMP
Floating-point number FLOAT
Geographical point


[{"lat","FLOAT"}, {"long","FLOAT"}] 
String STRING (truncated to 64 KB)

Firestore key properties

Each document in Firestore has a unique key that containsinformation such as the document ID and the document path.BigQuery creates a RECORD data type (also known as aSTRUCT)for the key, with nested fields for each piece of information, as described inthe following table.

Key property Description BigQuery data type
__key__.app The Firestore app name. STRING
__key__.id The document's ID, or null if __key__.name is set. INTEGER
__key__.kind The document's collection ID. STRING
__key__.name The document's name, or null if __key__.id is set. STRING
__key__.namespace Firestore does not support custom namespaces. The default namespace is represented by an empty string. STRING
__key__.path The path of the document: the sequence of the document and the collection pairs from the root collection. For example: "Country", "USA", "PostalCode", 10011, "Route", 1234. STRING


1. FireKit for Firebase - Data Import/Export Tool for Firebase Cloud Firestore
(RetroPortal Studio)
2. Bulk Upload JSON Data to Cloud Firestore Collections | Quick Firebase Tutorial
(RetroPortal Studio)
3. Firebase Cloud Firestore Import Export JSON CSV Data with Ease | Refi App GUI tool for Firestore
(Abdul Aziz Ahwan)
4. Importing Data to a Firestore Database || #qwiklabs || #GSP642
(Quick Lab)
5. Importing Data to a Firestore Database [ GSP 642 ] Google Cloud Lab Solution
6. Cloud Firestore Data Modeling (Google I/O'19)
Top Articles
Latest Posts
Article information

Author: Manual Maggio

Last Updated: 02/07/2023

Views: 6668

Rating: 4.9 / 5 (69 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Manual Maggio

Birthday: 1998-01-20

Address: 359 Kelvin Stream, Lake Eldonview, MT 33517-1242

Phone: +577037762465

Job: Product Hospitality Supervisor

Hobby: Gardening, Web surfing, Video gaming, Amateur radio, Flag Football, Reading, Table tennis

Introduction: My name is Manual Maggio, I am a thankful, tender, adventurous, delightful, fantastic, proud, graceful person who loves writing and wants to share my knowledge and understanding with you.