BigQuery standard sql not deleted?

Question

I can not delete the range defined by where.

My query:

delete from `dataset.events1` as t where t.group='error';

Result:

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

Answer 1

According to the BQ docs:

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.

This looks like the error you're facing.

You can check if your table has a streaming buffer attached through the BigQuery API.

Answer 2

This error message is considered as an expected behavior when querying rows that were recently streamed into the table in order to maintain the data consistency. Based on this, it is required to wait until the buffer is flushed, which can take up to 90 minutes to become available for copy/export and other operations, otherwise you would get the same error.

To validate if the table has an active streaming buffer process, you can check the tables.get response and verify if it contains a section named streamingBuffer.