Problem
A BigQuery DML job fails to update or delete data in a table with records in the insertAll API streaming buffer. The error message thrown shows as follows:
UPDATE or DELETE statement over table 'table_name' would affect rows in the streaming buffer, which is not supported.
Environment
- BigQuery Jobs on live-streaming data
Solution
Consider migrating from the legacy insertAll API to the Storage Write API which supports DML operations against recently streamed data.
Alternatively, change the query in such a way that it does not update/delete data of the last 90 mins. To perform the following, the query can simply be modified to include a timestamp for filtering, for example:
Alternatively, change the query in such a way that it does not update/delete data of the last 90 mins. To perform the following, the query can simply be modified to include a timestamp for filtering, for example:
WHERE timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 MINUTE)
Cause
This issue is caused by the legacy insertAll API's streaming buffer, which cannot be modified by mutating DML operations.