0

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

Renaud Tarnec
  • 79,263
  • 10
  • 95
  • 121
wihee
  • 55
  • 7

1 Answers1

1

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.

Renaud Tarnec
  • 79,263
  • 10
  • 95
  • 121
  • 1
    Thank you! Btw your youtube video was awesome as well (explaining the extension) You have helped me a lot this week! – wihee Sep 23 '22 at 08:33