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

Author: Omid Vahdaty 26.3.2020‚Äč

Streaming insert is a services that allow ingestion of events in real time to BigQuery.
In case you get this error, you are most likely trying to delete data which falls in range of streaming insert window of time being used.

sometimes it take up to 90 mins to ingest the data. thus…. try to delete data from further back in time.
for example:

DELETE FROM `MyProject.MyDataset.MyTable` 
WHERE id LIKE '%BigDataDemystified%'
AND time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 MINUTE)

——————————————————————————————————————————
I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way.
If you have any comments, thoughts, questions, or you need someone to consult with,

feel free to contact me via LinkedIn:

Leave a Reply