3

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
user4157124
  • 2,809
  • 13
  • 27
  • 42
HegdeS
  • 69
  • 9

1 Answers1

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?

Sergey Geron
  • 9,098
  • 2
  • 22
  • 29
  • Yes. I came to know about the Stream Buffer later and waited for around 90mins. Then I was able to delete the dummy data. – HegdeS Oct 20 '20 at 09:27