Google BigQuery DML Update query on rows still in the Streaming buffer

Question

I've been recently trying to come up with a retry mechanism for Google's Big Query streaming api for running DML queries with UPDATE statement over Rows that could sometimes still be in the Streaming Buffer. As these rows have not yet been exported to the table, BI's api forbids UPDATE or DELETE statements to be ran on them. As I understand there is no way to manually flush the Streaming Buffer yourself.

My question is, is there a way or a good practice for a call with some sort of retry mechanism that will do this for the announced possible 90 minute of wait time (that the rows can be in the buffer)?

Answer 1

I would recommend copy data from table where streaming occurs to another table on which DML could be run without any limitations. This another permanent table could be created with jobs.insert API You need to treat this permanent table as source of true, on original table you could enable table's expiration time or partition expiration depending on your needs and coping frequency. Now when you have permanent table you could run some other processing on that data or generate report etc. Drawback of above is that you could have some late data anyway you should fetch/copy and deduplicate reasonable window of data to permanent table to guarantee the most recent data

I assume that streaming to your table could be run again and again, so you could theoretically be never able to run you DML as streaming buffer could be never empty.

Anyway if you still need run some retry mechanism try using something like https://github.com/awaitility/awaitility