BigQuery - 删除分区表中的行

Question

我是一名有用的助手，可以为您翻译文本。

我在BigQuery上拥有一个按天分区的表。当我尝试使用类似于以下查询删除表中的某些行时：

DELETE FROM `MY_DATASET.partitioned_table` WHERE id = 2374180

我收到以下错误信息：

错误：暂不支持在分区表上执行DML语句。

快速的谷歌搜索引导我跳转至https://cloud.google.com/bigquery/docs/loading-data-sql-dml，其中也指出："尚未支持修改分区表的DML语句"。

因此，目前是否有解决方法可以用于从分区表中删除行？

- Kwame

Answer 1

在此阶段，DML存在一些已知的问题/限制。

比如：

• 无法使用DML语句修改具有其架构中REQUIRED字段的表。
• 每个DML语句都会发起一个隐式事务，这意味着语句所做出的更改将在每个成功的DML语句结束时自动提交。不支持多语句事务。
• 允许在表上同时运行以下组合的DML语句： UPDATE和INSERT
  DELETE和INSERT
  INSERT和INSERT
  否则其中一个DML语句将被终止。例如，如果两个UPDATE语句同时针对该表执行，则只有其中一个将成功。
• 最近通过BigQuery Streaming（tabledata.insertall）写入的表不能使用UPDATE或DELETE语句进行修改。要检查表是否具有流缓冲区，请检查tables.get响应中是否存在名为streamingBuffer的部分。如果不存在，则可以使用UPDATE或DELETE语句修改该表。
• 尚不支持修改分区表的DML语句。

请注意配额限制。

• 每个表每天的最大UPDATE/DELETE语句数：48
• 每个项目每天的最大UPDATE/DELETE语句数：500
• 每个表每天的最大INSERT语句数：1,000
• 每个项目每天的最大INSERT语句数：10,000

您可以将整个分区复制到非分区表中，并在那里执行DML语句。然后将临时表写回分区。此外，如果您遇到每个表每天的DML更新限制语句，请创建表的副本并在新表上运行DML以避免限制。

- Pentium10

---

似乎现在允许必填字段。 - Mingwei Samuel

最重要的部分是：“尚不支持修改分区表的DML语句。”基本上，删除在分区表上无法正常工作... - Matt

Answer 2

您可以使用命令行bq rm来删除分区表中的分区，示例如下：

bq rm 'mydataset.mytable$20160301'

- Andre Leite

Answer 3

我已经完成了它，没有使用临时表，步骤如下：

1）准备查询语句，选择应该保留的特定分区中的所有行：

SELECT * FROM `your_data_set.tablename` WHERE 
_PARTITIONTIME = timestamp('2017-12-07') 
AND condition_to_keep_rows_which_shouldn't_be_deleted = 'condition' 

如果需要，可以针对其他分区运行此命令。

2) 选择查询结果的目标表，在其中指向特定分区，您需要提供表名，如下所示：

tablename$20171207

3）勾选“覆盖表”选项 -> 它将仅覆盖特定分区

4）运行查询，从指定的分区中删除冗余行！

//请记住，您可能需要对其他分区运行此操作，其中要删除的行分布在多个分区中

- rtbf

---

同样地，如果您想删除除一个分区之外的所有分区，则可以执行此操作并将目标表设置为不包括$<date>。这是我的用例，这确实有所帮助！ - gilmatic

Answer 4

看起来在我写这篇文章时，这不再是BigQuery的限制了！

在标准SQL中，像上面那样对分区表进行操作，将会成功，假设被删除的行不是最近（在过去30分钟内）通过流式插入插入的。

DML的当前文档：https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language

在BQ UI中为我工作的示例查询：

DELETE 
FROM dataset_name.partitioned_table_on_timestamp_column
WHERE 
timestamp >= '2020-02-01' AND timestamp < '2020-06-01'

仓鼠完成旋转后，我们得到BQ响应：

This statement removed 101 rows from partitioned_table_on_timestamp_column

- JJC