17 Mar 19 · DataFreak · #bigquery ·   Bookmark   ×

Solved: BigQuery Error - UPDATE or DELETE statement over table would affect rows in the streaming buffer, which 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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds