Stream-insert and afterwards periodically merge into BigQuery within Dataflow pipeline

user_1273518 0
Viewed 188

Is it a valid approach when building a dataflow pipeline which aims to store the newest data per key in BigQuery to

  1. stream-insert the events in a partitioned staging table
  2. periodically merge (update/insert) into target table, (so that only the newest data to a key is stored in this table). It's a requirement that the merge happens every 2-5 minutes and respects all rows in the staging table.

The idea of this approach is taken from the Google project https://github.com/GoogleCloudPlatform/DataflowTemplates, com.google.cloud.teleport.v2.templates.DataStreamToBigQuery

So far it works okay in our tests, the question here arises from the fact, that Google states in its documentation:

"Rows that were written to a table recently by using streaming (the tabledata.insertall method or the Storage Write API) cannot be modified with UPDATE, DELETE, or MERGE statements." https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language#limitations

Has someone gone this road in a production dataflow pipeline with stable positive results?

apache-beam google-cloud-dataflow google-bigquery
2 Answers

After a few hours and some thinking, I think I can answer my own question: Since I only stream to the staging table and merge into the target table, the approach is perfectly fine.

user_12735180

I did this yesterday and the time lag is around 15-45 minutes. If you have an ingestion time column/field you can use that to restrict which rows you are UPDATEing.

user_159219410
Related