BigQuery UPDATE or DELETE DML

Question

Tables that have been written to recently via BigQuery Streaming (tabledata.insertall) cannot be modified using UPDATE or DELETE statements. To check if the table has a streaming buffer, check the tables.get response for a section named streamingBuffer. If it is absent, the table can be modified using UPDATE or DELETE statements.

When I try to modify my table (rows were recently inserted data, table created few days ago)

delete table_dataset.table1 where true

I have following error - Error: UPDATE or DELETE DML statements are not supported over table with streaming buffer However once I deleted all these records somehow maybe after some delay.

What is the streaming buffer ? When exactly I can modify my table ? If I use JOB which create table or export data from another source can I run UPDATE/DELETE DDL?

Answer 1

Streamed data is available for real-time analysis within a few seconds of the first streaming insertion into a table but it can take up to 90 minutes to become available for copy/export and other operations. You probably have to wait up to 90 minutes so all buffer is persisted on the cluster. You can use queries to see if the streaming buffer is empty or not like you mentioned.

If you use load job to create the table, you won't have streaming buffer.