Delete data from BigQuery using console which was inserted using Streaming Insert

Question

I have inserted some data into my table using Streaming Insert (insertAll) to test how the code works. Now I want that data to be deleted from the table.

I used the following query:

DELETE FROM MY_DATASET.my_Table WHERE someColumn like 'XYZ%'

And got the following error:

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

Answer 1

In my case waiting a couple of hours after last insert helped.

Another quicker solution was to filter by time column (if you have one in your table), so that BigQuery knows that your deletes won't affect streaming data:

time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 MINUTE)

Take a look at Update or Delete tables with streaming buffer in BigQuery?