0

As far as I understand, UPDATEs and DELETEs are working on partitioned tables with streaming buffer, if query is not touching any records in streaming buffer. Otherwise, the following error is reported:

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

Issue is similar to discussed this question, however it's about column partitioned tables, not about ingestion-time partitioned tables.

Problem is, that while ingestion-time partitioned have means to ignore data in streaming buffer via conditions on _PARTITIONTIME, it's not available for column-partitioned tables. Are there any other approaches that would allow to ignore streaming buffer data in DML statements?

chemikadze
  • 815
  • 4
  • 12

1 Answers1

0

At the moment you can only use Legacy SQL to get information about the streaming buffer.

Get all data from the streaming buffer like this:

#legacySQL
select MIN(partitioned_tstamp) AS min_tstamp
       , MAX(partitioned_tstamp) AS max_tstamp
       , COUNT(1) AS lines
FROM [my_dataset_id.mystreaming_data_table$__UNPARTITIONED__]

And get a summary of all partitions in the table like this:

#legacySQL
SELECT * 
FROM [my_dataset_id.mystreaming_data_table$__PARTITIONS_SUMMARY__]

I have no idea why this isn't supported yet in standard SQL or when it will be.

Scipio Hollandicus
  • 1
  • Problem is to delete data from such table - it does not seem possible to ignore records in streaming buffer. – chemikadze Jan 09 '19 at 09:49