Bigquery how do I extract JSON into different columns only when the row is updated on Bigquery

Question

I have a table with a column which stores data in JSON format. How do I extract JSON values into columns only when there's a new entry or an existing row is updated instead of extracting all the rows everytime.

Trying to reduce the cost of bigquery usage

Answer 1

I can see two options here:-

1. For an automated way, one option is using eventrc, https://cloud.google.com/eventarc/docs/event-providers-targets#triggers where in you can configure the event type mentioned here https://cloud.google.com/eventarc/docs/event-types#methodname_15 . Based on your use case, if its an calling an updateTable method or InsertTable method, you will have select the trigger. If its none, you can select insertJob method and then route the event destination to Cloud functions or Workflows to perform follow-up task

2. If its manual, you can build the table to be materialized and then query the materialized Vs base table to find changes on the columns or new values.

if you can let me know sample data you have, i can try and provide you with example of above options.

hope this helps.