BigQuery UPDATE or DELETE statement over table with streaming buffer is not supported

When trying to Update or Delete rows from a table with streaming buffer attached, Bigquery throws the below error message:

UPDATE or DELETE statement over table dataset.events1 would affect rows in 
the streaming buffer, which is not supported.

Solution:

Rows that were written to a table recently via streaming (using the tabledata.insertall method) cannot be modified using UPDATE, DELETE, or MERGE statements. Recent writes are typically those that occur within the last 30 minutes. Note that all other rows in the table remain modifiable by using UPDATE, DELETE, or MERGE statements.

To fix this error:

• Verify if your table has a streaming buffer attached using the BigQuery API
• Add a filter clause to your UPDATE / DELETE / MERGE statement that will only update records older than 30 minutes. eg. where RowCreateTimestamp < TIMESTAMP_SUB(RowCreateTimestamp, INTERVAL 30 MINUTE)

DataFreak

posted on 17 Mar 19