Export Collections to BigQuery - Table structure in BigQuery.

[mpro-madhu]

Hello Team,

When I export Firestore collection to bigquery, I see the table in bigquery is having entire json document in single field. I need the ison document to be converted into column / row format on the table. How to do this ?

Also, how can I stream data from more than one collection from Firestore ?

Thanks, Madhu

[Goule]

You can use this script to create your own schema: https://github.com/firebase/extensions/blob/9f8d7fd6048bcaa7b5bc505cebe2e90494359e33/firestore-bigquery-export/guides/GENERATE_SCHEMA_VIEWS.md This can't be done automatically.

[mpro-madhu]

Thanks @Goule

I could proceed by using scripts for generating schema views. However I notice, when there is array of objects in Firestore document , it is not UNNESTing the object in array. Object appears as JSON values in view columns. How to UNNEST this ?

[MorenoMdz]

You can use this script to create your own schema: https://github.com/firebase/extensions/blob/9f8d7fd6048bcaa7b5bc505cebe2e90494359e33/firestore-bigquery-export/guides/GENERATE_SCHEMA_VIEWS.md This can't be done automatically.

Hey @Goule, having this as a View instead of a Table is so it can keep the data in sync? When thinking about the query performance having it as a table would be ideal right?

[cabljac]

related - https://github.com/firebase/extensions/issues/1051, https://github.com/firebase/extensions/issues/573

[cabljac]

typed arrays in schemas have been added in https://github.com/firebase/extensions/pull/1366, closing as resolved for now