Problem
Sometimes, when issuing a DELETE command against the Google Bigquery table using ZappySys BigQuery Driver, the Driver throws the below error.
UPDATE or DELETE statement over table TestDataset.DataTypeTest would affect rows in the streaming buffer, which is not supported.
Possible Cause
You are trying to delete records from a BigQuery table created in less than 90 minutes
Here is what Google Official Site describes
Rows that were recently written using the tabledata.insertall streaming method can’t be modified with data manipulation language (DML), such as UPDATE, DELETE, MERGE, or TRUNCATE statements. The recent writes are those that occurred within the last 30 minutes. All other rows in the table remain modifiable by using UPDATE, DELETE, MERGE, or TRUNCATE statements. The streamed data can take up to 90 minutes to become available for copy operations.
Solution
To fix this error, you can try the following steps
Method-1# Attach streaming buffer so you can delete in real-time
-OR-
Method-2# Delete only those records which are older than 30 minutes (to be safe side use 90 minutes window) by adding WHERE clause like below. It’s only possible if you have a column that keeps track of the record created date time. Modify your ETL Process to add such field if you do not have one.
DELETE FROM
project.dataset.table
WHERE id LIKE ‘%-%’
AND SomeTimeStampColumn < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 40 MINUTE)
Contact Us
If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.