CODEWITHSUNDEEP
sqlfirebasegoogle-cloud-firestoregoogle-bigqueryfirebase-extensions
wihee
wihee

Reputation: 85

BigQuery make a table/view from json data column

I have installed the firebase extension for streaming data to bigquery. After that i have backfilled the table with historical data. One thing I struggle with is that the table is build on changelogs, so that the data of each streamed event is contained as an nested json object in one column called 'data'.

I'm wondering if there is an easy function which creates a view/table out of this json object. So that it takes the keys seperatly as columns and then fills it with the values for each row.

One difficulty is that different event types contain different json objects which are nested differently.

That's why I want to create an table/view for each event. enter image description here

Upvotes: 0

Views: 915

Answers (1)

Renaud Tarnec
Renaud Tarnec

Reputation: 83068

I'm wondering if there is an easy function which creates a view/table out of this json object. So that it takes the keys seperatly as columns and then fills it with the values for each row.

Yes you can use the fs-bq-schema-views script as explained in the Extension documentation.

The script generates a BigQuery view of your raw changelog based on a schema configuration that you write in JSON format. It's very easy, just follow the instructions.

One difficulty is that different event types contain different json objects which are nested differently.

You could create two different views based on two differents schemas, one for each type of event and filter the records when querying the views.

Upvotes: 1

Related Questions