I am migrating data from a sql server database systme to bigquery at the moment, and I have encountered a problem when trying to delete records from a bigquery table with active streaming buffer, can you confirm how long does a streaming buffer persist before it is removed so the delete operation can run against it? I find this has caused unnecessary inconvenience during development.
Many thanks for your help and I look forward to hearing from you.
Best regards,
According to official documentation
Data can take up to 90 minutes to become available for copy and export operations. Also, when streaming to a partitioned table, data in the streaming buffer has a NULL value for the _PARTITIONTIME pseudo column. To see whether data is available for copy and export, check the tables.get response for a section named streamingBuffer. If that section is absent, your data should be available for copy or export, and should have a non-null value for the _PARTITIONTIME pseudo column. Additionally, the streamingBuffer.oldestEntryTime field can be leveraged to identify the age of records in the streaming buffer.
Streaming into partitioned tables
When the data is streamed, data between 7 days in the past and 3 days in the future is placed in the streaming buffer, and then it is extracted to the corresponding partitions. Data outside of this window (but inside the 1 year, 6 month range) is placed in streaming buffer, and then it is extracted to the UNPARTITIONED partition. When there's enough unpartitioned data, it is loaded to the corresponding partitions.
We overcomed your situation by delaying the delete requests, or do once in 24 hours. You could script now the query for delete to use the streamingBuffer.oldestEntryTime as a param, and attempt to delete anything older than that.
https://cloud.google.com/bigquery/streaming-data-into-bigquery
