问题描述
我在BigQuery上有一个按天划分的表.当我尝试使用以下查询从表中删除一些行时:
I have a Day-Partitioned Table on BigQuery. When I try to delete some rows from the table using a query like:
DELETE FROM `MY_DATASET.partitioned_table` WHERE id = 2374180
我收到以下错误:
通过Google的快速搜索，我可以找到: https://cloud. google.com/bigquery/docs/loading-data-sql-dml 在其中还显示:尚不支持修改分区表的DML语句."
A quick Google search leads me to: https://cloud.google.com/bigquery/docs/loading-data-sql-dml where it also says: "DML statements that modify partitioned tables are not yet supported."
那么，现在有什么解决方法可用于从分区表中删除行吗?
So for now, is there a workaround that we can use in deleting rows from a partitioned table?
推荐答案
DML具有一些此阶段的已知问题/限制.
DML has some known issues/limitation in this phase.
例如:
- DML语句不能用于修改其模式中具有REQUIRED字段的表.
- 每个DML语句都会启动一个隐式事务，这意味着该语句所做的更改将在每个成功的DML语句结束时自动提交.不支持多语句交易.
- 以下DML语句组合允许在表上同时运行:UPDATE和INSERT
删除并插入
INSERT和INSERT
否则，其中一个DML语句将被中止.例如，如果针对该表同时执行两个UPDATE语句，则其中只有一个会成功. - 最近通过BigQuery Streaming写入的表(tabledata.insertall)不能使用UPDATE或DELETE语句进行修改.要检查表是否具有流缓冲区，请检查tables.get响应以获取名为streamingBuffer的部分.如果不存在，则可以使用UPDATE或DELETE语句修改表.
- 尚不支持修改分区表的DML语句.
- DML statements cannot be used to modify tables with REQUIRED fields in their schema.
- Each DML statement initiates an implicit transaction, which means that changes made by the statement are automatically committed at the end of each successful DML statement. There is no support for multi-statement transactions.
- The following combinations of DML statements are allowed to run concurrently on a table:UPDATE and INSERT
DELETE and INSERT
INSERT and INSERT
Otherwise one of the DML statements will be aborted. For example, if two UPDATE statements execute simultaneously against the table then only one of them will succeed. - Tables that have been written to recently via BigQuery Streaming (tabledata.insertall) cannot be modified using UPDATE or DELETE statements. To check if the table has a streaming buffer, check the tables.get response for a section named streamingBuffer. If it is absent, the table can be modified using UPDATE or DELETE statements.
- DML statements that modify partitioned tables are not yet supported.
还要注意配额限制
- 每张表每天最多可有UPDATE/DELETE条语句:48
- 每个项目每天最多UPDATE/DELETE条语句:500
- 每张表每天最多INSERT语句:1,000
- 每个项目每天最多INSERT语句:10,000
您可以做的是将整个分区复制到一个未分区的表中，然后在其中执行DML语句.比将临时表写回到分区.另外，如果您每天每张表都遇到DML更新限制声明，则需要创建表的副本并在新表上运行DML，以避免出现限制.
What you can do is copy the entire partition to a non-partitioned table and execute the DML statement there. Than write back the temp table to the partition. Also if you ran into DML update limit statements per day per table, you need to create a copy of the table and run the DML on the new table to avoid the limit.
这篇关于BigQuery-从分区表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！