Export database(having nested documents) from cloud firestore to Bigquery using command

Question

I am trying to export cloud firestore data into bigquery to do sql operations.

Exported cloud firestore to cloud storage [using] (https://cloud.google.com/firestore/docs/manage-data/export-import) gcloud beta firestore export gs://htna-3695c-storage --collection-ids='users','feeds'
Followed https://cloud.google.com/bigquery/docs/loading-data-cloud-firestoreto import from bigquery.

We have 2 collections: Users & Feeds in the cloud firestore. I have successfully exported feeds collection but am not able to export users collection. I am getting an error while importing data from storage to bigquery

Error: unexpected property name 'Contacts'. we have contacts field in the collection users. this contacts field is of type 'Map'.

I also tried the command line. Below is the command to export bigquery.

**bq --location=US load --source_format=DATASTORE_BACKUP myproject_Dataset.users gs://myproject-storage/2019-04-19T13:29:28_75338/all_namespaces/kind_users/all_namespaces_kind_users.export_metadata**

here also I got the same error:

'unexpected property name 'Contacts'.

I thought to add projection fields to export only specified fields some thing like below **bq --location=US load --projection_fields=[Coins,Referral,Profile] --source_format=DATASTORE_BACKUP myproject_Dataset.users gs://myproject-storage/2019-04-19T13:29:28_75338/all_namespaces/kind_users/all_namespaces_kind_users.export_metadata**

here also I got the error:

Waiting on >bqjob_r73b7ddbc9398b737_0000016a4909dd27_1 ... (0s) Current status: DONE
BigQuery error in load operation: Error processing job 'myproject:bqjob_r73b7ddbc9398b737_0000016a4909dd27_1': An internal error occurred and the request could not be completed. Error: 4550751

Can anyone please let me know how to fix these issues?

Thank you in advance.Image of firestore Db

I am also seeing a similar error when using --projection-fields but only on some of my collections and it only occurs when trying to import a field that is a map type in firestore.. — Kebabman

Tlaquetzal Tlaquetzal · Accepted Answer · 2019-08-01T17:10:17

It seems to be an issue with some of your documents. According to the limitations of the BigQuery import of Firestore data, you must have a schema with less than 10,000 unique field names. In your Contacts schema, you are using the contacts' names as keys, that design is likely to produce a big amount of field names. You would need to check if this is happening to your other documents.

As a workaround, you could change the design (at some stage of the loading process) from:

"Contacts" : {
    "contact1" : "XXXXX",
    "contact2" : "YYYYY",
}

to:

"Contacts" : [
    {
        "name" : "contact1",
        "number" : "XXXXX"
     },
     {
        "name" : "contact2",
        "number" : "YYYYY"
     },
]

This schema would reduce drastically the number of field_names which will make it easier to manipulate from BigQuery.

Export database(having nested documents) from cloud firestore to Bigquery using command

1 Answers