Loading data from Cloud Firestore exports

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

Note: Some of the features described above are not supported when loading Cloud Firestore exports. You cannot use URI wildcards or append data to an existing table when loading data from a Cloud Firestore export.

Limitations

When you load data into BigQuery from a Cloud Firestore export, note the following restrictions:

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

Required permissions

When you load data into BigQuery, you need permissions to run a load job and project or dataset-level permissions that allow you to load data into new or existing BigQuery tables and partitions. If you are loading data from Cloud Storage, you also need permissions to access to the bucket that contains your data.

BigQuery permissions

If you are loading data into a new table, you must have WRITER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.create permissions.

If you are using a load job to overwrite an existing table or to append data to an existing table, you must have WRITER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.updateData permissions.

The following predefined, project-level IAM roles include both bigquery.tables.create and bigquery.tables.updateData permissions:

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can create and update tables in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables in it.

You also need bigquery.jobs.create permissions to run the load job. The following predefined, project-level IAM roles include bigquery.jobs.create permissions:

• bigquery.user
• bigquery.jobUser
• bigquery.admin

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Cloud Storage permissions

In order to load data from a Cloud Storage bucket, you must be granted storage.objects.get permissions at the project level or on that individual bucket. If you are using a URI wildcard, you must also have storage.objects.list permissions.

The predefined IAM role storage.objectViewer can be granted to provide both storage.objects.get and storage.objects.list permissions.

Loading Cloud Firestore export service data

You can load data from a Cloud Firestore export metadata file by using the BigQuery web UI, bq command line tool, or API.

Sometimes Cloud Datastore terminology is used in the UI or or in the commands, but the following procedures are compatible with Cloud Firestore export files. Cloud Firestore and Cloud Datastore share an export format.

Note: You can load specific fields by using the --projection_fields flag in the CLI or by setting the projectionFields property in the load job configuration. This feature is not available in the classic BigQuery web UI.

Console

1. Open the BigQuery web UI in the GCP Console.
   Go to the BigQuery web UI
2. In the navigation panel, in the Resources section, expand your project and select a dataset. Click Create table. The process for loading data is the same as the process for creating an empty table.

3. On the Create table page, in the Source section:

   • For Create table from, select Cloud Storage

   • In the source field, enter the Cloud Storage URI. The Cloud Storage bucket must be in the same location as your dataset. The URI for your Cloud Firestore export file should end with [KIND_COLLECTION_ID].export_metadata. For example: default_namespace_kind_Book.export_metadata. In this example, Book is the collection ID, and default_namespace_kind_Book is the file name generated by Cloud Firestore.

     Note: Do not use the file ending in overall_export_metadata. This file is not usable by BigQuery.

     Verify [KIND_COLLECTION_ID] is specified in your Cloud Storage URI. If you specify the URI without [KIND_COLLECTION_ID], you receive the following error: does not contain valid backup metadata. (error code: invalid).

   • For File format, select Cloud Datastore Backup. Cloud Datastore Backup is the correct option for Cloud Firestore. Cloud Firestore and Cloud Datastore share an export format.

4. On the Create table page, in the Destination section:

   • For Dataset name, choose the appropriate dataset.

     

   • In the Table name field, enter the name of the table you're creating in BigQuery.

   • Verify that Table type is set to Native table.

5. In the Schema section, no action is necessary. The schema is inferred for a Cloud Firestore export.

6. Select applicable items in the Advanced options section. If you are overwriting an existing table, set Write preference to Overwrite table.

   

7. Click Create table.

Classic UI

1. Go to the classic BigQuery web UI.
   Go to the BigQuery web UI
2. In the navigation panel, hover on a dataset, click the down arrow icon , and click Create new table.

3. On the Create Table page, in the Source Data section:

   • Leave Create from source selected.

   • For Location, select Cloud Storage and in the source field, enter the Cloud Storage URI. The Cloud Storage bucket must be in the same location as your dataset. The URI for your Cloud Firestore export file should end with [KIND_COLLECTION_ID].export_metadata. For example: default_namespace_kind_Book.export_metadata. In this example, Book is the collection ID, and default_namespace_kind_Book is the file name generated by Cloud Firestore.

     Note: Do not use the file ending in overall_export_metadata. This file is not usable by BigQuery.

     Verify [KIND_COLLECTION_ID] is specified in your Cloud Storage URI. If you specify the URI without [KIND_COLLECTION_ID], you receive the following error: does not contain valid backup metadata. (error code: invalid).

   • For File format, select Cloud Datastore Backup. Cloud Datastore Backup is the correct option for Cloud Firestore. Cloud Firestore and Cloud Datastore share an export format.

4. On the Create Table page, in the Destination Table section:

   • For Table name, choose the appropriate dataset, and in the table name field, enter the name of the table you're creating in BigQuery.
   • Verify that Table type is set to Native table.

5. In the Schema section, no action is necessary. The schema is inferred for a Cloud Firestore export.

6. Select applicable items in the Options section. If you are overwriting an existing table, set Write preference to Overwrite table.

7. Click Create Table.

CLI

Use the bq load command with source_format set to DATASTORE_BACKUP. Supply the --location flag and set the value to your location. If you are overwiting an 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]

where:

• [LOCATION] is your location. The --location flag is optional.
• [FORMAT] is DATASTORE_BACKUP. Cloud Datastore Backup is the correct option for Cloud Firestore. Cloud Firestore and Cloud Datastore share an export format.
• [DATASET] is the dataset that contains the table into which you're loading data.
• [TABLE] is the table into which you're loading data. If the table does not exist, it is created.
• [PATH_TO_SOURCE] is the Cloud Storage URI.

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

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

API

Set the following properties to load Cloud Firestore export data using the API.

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

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

3. The sourceUris must be fully-qualified, in the format gs://[BUCKET]/[OBJECT] in the load job configuration. The file (object) name must end in [KIND_NAME].export_metadata. Only one URI is allowed for Cloud Firestore exports, and you cannot use a wildcard.

4. Specify the data format by setting the sourceFormat property to DATASTORE_BACKUP in the load job configuration. Cloud Datastore Backup is the correct option for Cloud Firestore. Cloud Firestore and Cloud Datastore share an export format.

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

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

Note: If you prefer to skip the loading process, you can query the export directly by setting it up as an external data source. For more information, see External data sources.

Cloud Firestore options

To change how BigQuery parses Cloud Firestore export data, specify the following options:

CSV option	Classic UI option	CLI flag	BigQuery API property	Description
Projection fields	None	--projection_fields	projectionFields	(Optional) A comma-separated list that indicates which document fields to load from a Cloud 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.
Number of bad records allowed	Number of errors allowed	--max_bad_records	maxBadRecords	(Optional) The maximum number of bad records that BigQuery can ignore when running the job. If the number of bad records exceeds this value, an invalid error is returned in the job result. The default value is 0, which requires that all records are valid.

Data type conversion

BigQuery converts data from each document in Cloud Firestore export files to BigQuery's data types. The following table describes the conversion between data types.

Cloud Firestore data type	BigQuery data type
Array	RECORD
Boolean	BOOLEAN
Reference	RECORD
Date and time	TIMESTAMP
Map	RECORD
Floating-point number	FLOAT
Geographical point	RECORD [{"lat","FLOAT"}, {"long","FLOAT"}]
Integer	INTEGER
String	STRING (truncated to 64 KB)

Firestore key properties

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

Key property	Description	BigQuery data type
__key__.app	The Cloud 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	Cloud 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