Streaming changes from BigQuery?

[tang8330]

Does BigQuery have a change-data-capture equivalent such that we can stream changes that are happening to tables within BigQuery?

I'm thinking about something similar to Snowflake Streams

Scouring around, I found BigQuery change history - however it does not support update or deletions. 

https://cloud.google.com/bigquery/docs/change-history?hl=en#limitations

I was wondering if anyone was able to find a workaround to deal with this.

Thanks!

[kolban]

I'm tempted to say that the answer to your question is "no".  BigQuery is an optimized data warehouse as opposed to a transactional OLTP database.  As such, deletions and updates to the data managed by BigQuery should very much be the exceptions.  Can you describe your use case that you want to achieve?  Maybe if we hear this we can provide some alternative guidance?

[aaronscottb]

It depends on your use case as mentioned by kolban.

If you just wanna see changes in update/delete. You might wanna use BigQuery audit logs in this link. 

It also has stream logs options and you may use protoPayload.methodName as filter of your desired methodname.

[anjshar]

@tang8330  Did you find a way to get the inserts. updates and deletes information yet in google big query table?

@aaronscottb @kolban Is it still the case since on the official documentation big query do mention CDC enabled tables or that is just from the source end but not when we read from big query tables to different destinations.

[kolban]

The CDC capabilities in BigQuery **today** are specifically for BigQuery processing INCOMING CDC messages.  So, for example, there is an external database that is streaming CDC records **into** BigQuery, then BigQuery can now process those.  What you are asking for is for BigQuery to externalize CDC records OUT from BigQuery.

Have a look at this video at this time mark:

https://youtu.be/NxMuLljlCko?t=1145

This is *possibly* what you are contemplating ...

[anjshar]

@kolban Thanks a lot for your response. Yes I am asking to pull CDC data from BigQuery, Is there a better way to get the change history today from any of the system tables for each row in a table to get the inserts, updates and deletes?

[kolban]

As I understand it, there are bits and pieces available with a LOT more to come.  Sadly, on a public forum, we can't chat about what's coming but if you were to reach out to your Google Cloud account reps, they can likely work with you to get a private briefing.   For example, the following:

https://cloud.google.com/bigquery/docs/change-history

Shows how you can get the "APPENDS" (inserts) into a table ... but there is not story (yet) for DELETES or UPDATES.   In the previous post, there was mention of "continuous queries" and that holds promise to your question as well.

[Rubenb1]

stacksync.cloud provided a nice wrapper for complete CDC in BigQuery. It works pretty well. You can CDC data changes out of BigQuery easily and it works for all operations (including creates, updates and deletes). Under the hood it uses Bigquery of events and Event Arc.

Hope it helps!