数据操纵语言

借助 BigQuery 数据操纵语言 (DML),您可以在 BigQuery 表中更新、插入和删除数据。

执行 DML 语句的过程与执行 SELECT 语句的过程相同,但必须符合以下条件:

  • 必须使用标准 SQL。要启用标准 SQL,请参阅切换 SQL 方言
  • 不能指定目标表。例如,在 Cloud Console 中,必须将目标表设置为未选择任何表

    显示未选择目标表的 Cloud Console

限制

  • 每个 DML 语句都会启动一个隐式事务,这表示在每个 DML 语句成功结束时,系统会自动提交该语句所做的更改。不支持多语句事务。

  • 最近通过流式插入(tabledata.insertall 方法)写入表的行不能使用 UPDATEDELETEMERGE 语句进行修改。最近的写入通常是指在最近 30 分钟内发生的写入。请注意,表中的所有其他行仍可以使用 UPDATEDELETEMERGE 语句进行修改。

  • MERGE 语句不支持 when_clausesearch_conditionmerge_update_clausemerge_insert_clause 中的关联子查询。

  • 包含 DML 语句的查询不能将通配符表用作查询的目标。例如,通配符表可用于 UPDATE 查询的 FROM 子句,但不能用作 UPDATE 操作的目标。

  • DML 语句受到表元数据更新操作的速率限制的约束。如需了解详情,请参阅配额和限制文档中的数据操纵语言语句

并发作业

BigQuery 管理用于添加、修改或删除表中各行的 DML 语句的并发。

INSERT DML 并发

在任意 24 小时内,INSERT 到表中的前 1000 条语句将并发运行。达到此限制后,写入表的 INSERT 语句的并发限制为 10。超出 10 的任何 INSERT DML 作业都将以 PENDING 状态排入队列。上一个作业完成后,下一个 PENDING 作业将移出队列并运行。目前,在任何给定时间内,最多可在表中将 100 个 INSERT DML 语句排入队列。

UPDATE、DELETE、MERGE DML 并发

我们将 UPDATEDELETEMERGE DML 语句称为变更型 DML 语句。如果您在某个表上提交了一个或多个变更型 DML 语句,而该表上有其他变更型 DML 作业仍在运行(或待处理),BigQuery 会并发运行这些语句(最多可以并发运行某个固定数量的 DML 语句)。如果达到并发限制,BigQuery 会自动将任何其他变更型 DML 作业以待处理状态排入队列。当上一个运行的作业完成时,下一个 PENDING 作业将移出队列并运行。

排队时间超过 6 小时的 INTERACTIVE 优先级 DML 作业将失败。

DML 语句冲突

在表上并发运行多个变更型 DML 语句可能会失败,因为它们所做的更改存在冲突。BigQuery 会重试这些失败的操作。

  • 在表中插入行的 INSERT DML 语句不会与任何其他并发运行的 DML 语句发生冲突。

  • 只包含 INSERT 子句而不包含 UPDATEDELETE 子句的 MERGE DML 语句不会与任何其他并发运行的 DML 语句发生冲突。

  • 只要 MERGE 语句不更新或删除现有行,包含 UPDATEDELETE 子句的 MERGE DML 语句就不会与任何其他并发运行的 DML 语句发生冲突。

价格

如需了解 DML 价格,请参阅“价格”页面上的数据操纵语言价格

最佳做法

为了获得最佳性能,我们建议使用以下模式:

  • 避免提交大量个别行更新或插入操作。而是尽可能将 DML 操作组合在一起。如需了解详情,请参阅更新或插入单行的 DML 语句

  • 如果更新或删除操作通常发生在旧数据或者特定日期范围内,请考虑对您的表进行分区。通过分区可确保仅针对表中的特定分区进行更改。

  • 如果每个分区中的数据量较小,并且每次更新都会修改大部分分区,请避免对分区表进行分区。

  • 如果您经常更新对应的一列或多列位于较小范围值的行,请考虑使用聚簇表。聚簇操作可确保仅对一组特定的块进行更改,从而减少需要读取和写入的数据量。以下是针对一系列列值进行过滤的 UPDATE 语句的示例:

    UPDATE s = "some string" WHERE id BETWEEN 54 AND 75
    

    以下是针对一小部分列值进行过滤的类似示例:

    UPDATE s = "some string" WHERE id IN (54, 57, 60)
    

    在以下情况下,请考虑对 id 列进行聚簇。

  • 如果您需要 OLTP 功能,请考虑使用 Cloud SQL 联合查询,使 BigQuery 能够查询 Cloud SQL 中的数据。

后续步骤

  • 请参阅 DML 语法页面中的 DML 语法和示例。