Update or Delete tables with streaming buffer in BigQuery?

To check if the table has a streaming buffer, check the tables.get response for a section named streamingBuffer or, when streaming to a partitioned table, data in the streaming buffer has a NULL value for the _PARTITIONTIME pseudo column, so even with a simple WHERE query can be checked.

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, but probably you streamed some values to it.

---

Note the answer below to work with tables that have ongoing streaming buffers. Just use a WHERE to filter out the latest minutes of data and your queries will work. -- Fh

Make sure to change your filters so they don't include data that could be in the current streaming buffer.

For example, this query fails while I'm streaming to this table:

DELETE FROM `project.dataset.table` 
WHERE id LIKE '%-%'

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

You can fix it by only deleting older records:

DELETE FROM `project.dataset.table` 
WHERE id LIKE '%-%'
AND ts < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 40 MINUTE)

4282 rows affected.