Sync BigQuery to Firestore With Nested data Schema

So I'm new to BigQuery, and I'm trying to achieve mirroring data in Firestore collection in BigQuery. Installed Export Collections to BigQuery extensions in Firebase console and it successfully created a MyTable_raw_changelog table and MyTable_raw_latest view and with SQL statement, data was queried correspondingly.

Issue is that the whole document data came as string JSON in BQ. below is my sample data in Firestore document and the data I see in BQ is string JSON

{
    "User": {
        "id": "sss",
        "email": "ee@ee.net"
    },
    "Arrival": "2020-11-12T23:47:03.341Z",
    "Started At": "2020-11-13T11:33:39.000Z",
    "Billing Log": [
        "Fully Billed"
    ],
    "Autonumber": 23,
    "Latitude": 99.9999,
    "Longitude": -99.9999,
    "POD": [
        {
            "url": "eee",
            "time": 23
        },
        {
            "url": "sss",
            "time": 23

        }
    ]
}

First, I used gen schema view extension https://github.com/firebase/extensions/blob/next/firestore-bigquery-export/guides/GENERATE_SCHEMA_VIEWS.md and it created a 'view' with a schema that I defined in JSON. Even though it generated richly-typed BigQuery view, once view was setup in BQ, there was no way for me to modify the schema (changing type of field from string to array, for example). so they are not flexible since there are more than 60 fields in my JSON and new fields can be added in the future.

Another option I tried was I created a table by uploading newline delimited json which automatically detected fields and created schema which saved me much time, but I could not figure out the way of connecting this table back to the collection in Firestore.

What am I missing here? To sync Firestore with BQ with rich data and scalable schema, are there any better options?



Read more here: https://stackoverflow.com/questions/64887630/sync-bigquery-to-firestore-with-nested-data-schema

Content Attribution

This content was originally published by M.K. Kim at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: