在BigQuery中使用流缓冲区更新或删除表？ [英] Update or Delete tables with streaming buffer in BigQuery?

问题描述

在尝试从通过GCP控制台创建的表中删除记录并使用GCP BigQuery Node.js表插入函数更新时，出现以下错误。

$ b

不支持在表stackdriver-360-150317上使用UPDATE或DELETE DML语句：使用流缓冲区的my_dataset.users

该表格是在没有流式传输功能的情况下创建的。从我正在阅读的文档 最近通过BigQuery Streaming（tabledata.insertall）写入的表格不能使用UPDATE或DELETE语句 进行修改。

这是否意味着一旦用这个函数将一个记录插入到表中，就没有办法删除记录了？在所有？如果是这种情况，是否意味着表格需要从头开始删除和重新创建？如果情况并非如此。你可以请建议一种解决方法来避免这个问题吗？

谢谢！

解决方案

要检查表是否有流缓冲区，请检查名为 streamingBuffer 的节的 tables.get 或者，当流式传输到分区表时，流式缓冲区中的数据对于 _PARTITIONTIME 伪列具有NULL值，因此即使可以检查一个简单的WHERE查询。 p>

流式传输的数据可在第一个数据流插入表格的几秒钟内进行实时分析，但最多可能需要90分钟才能进行复制/导出和其他操作。您可能必须等待最多90分钟，以便所有缓冲区都保留在群集中。您可以使用查询来查看流缓冲区是否为空或不像您提到的那样。

如果使用load job创建表，则不会有流缓冲区，但可能你会给它一些值。

I'm getting this following error when trying to delete records from a table created through GCP Console and updated with GCP BigQuery Node.js table insert function.

UPDATE or DELETE DML statements are not supported over table stackdriver-360-150317:my_dataset.users with streaming buffer

The table was created without streaming features. And from what I'm reading in documentation Tables that have been written to recently via BigQuery Streaming (tabledata.insertall) cannot be modified using UPDATE or DELETE statements.

Does it mean that once a record has been inserted with this function into a table, there's no way to delete records? At all? If that's the case, does it mean that table needs to be deleted and recreated from scratch? If that's not the case. Can you please suggest a workaround to avoid this issue?

Thanks!

解决方案

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.

这篇关于在BigQuery中使用流缓冲区更新或删除表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！