使用数据操纵语言 (DML) 转换数据

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

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

  • 您必须使用 GoogleSQL。如需启用 GoogleSQL，请参阅切换 SQL 方言。
  • 您无法为查询指定目标表。

如需查看 BigQuery DML 语句列表及其使用示例，请参阅 GoogleSQL 中的数据操纵语言语句。

限制

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

  • 最近使用 tabledata.insertall 流式传输方法写入的行无法使用数据操纵语言 (DML) 修改，例如 UPDATE、DELETE、MERGE 或 TRUNCATE 语句。最近的写入是指最近 30 分钟内发生的写入。表中的所有其他行仍可以使用 UPDATE、DELETE、MERGE 或 TRUNCATE 语句进行修改。流式插入的数据最多可能需要 90 分钟才能用于复制操作。

    或者，可以使用 UPDATE、DELETE 或 MERGE 语句修改最近使用 Storage Write API 写入的行。如需了解详情，请参阅对最近流式插入的数据使用数据操纵语言 (DML)。

  • MERGE 语句不支持 when_clause、search_condition、merge_update_clause 或 merge_insert_clause 中的关联子查询。

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

并发作业

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

INSERT DML 并发

在任意 24 小时内，前 1500 个 INSERT 语句在提交后便立即运行。达到此限制后，写入表的 INSERT 语句的并发限制为 10。其他 INSERT 语句会添加到 PENDING 队列中。在任何给定时间内，最多可在表中将 100 个 INSERT 语句排入队列。INSERT 语句完成后，下一个 INSERT 语句会从队列中移除并运行。

如果您必须更频繁地运行 DML INSERT 语句，请考虑使用 Storage Write API 将数据流式插入到表中。

UPDATE、DELETE、MERGE DML 并发

UPDATE、DELETE 和 MERGE DML 语句称为变更型 DML 语句。如果您在某个表上提交了一个或多个变更型 DML 语句，而该表上有其他变更型 DML 作业仍在运行（或待处理），则 BigQuery 最多并发运行 2 个作业，之后最多可以将 20 个作业加入队列作为 PENDING 作业。当上一个运行的作业完成时，下一个 PENDING 作业将移出队列并运行。加入队列的变更型 DML 语句所共享的每个表的最大队列长度为 20。其他超出每个表的最大队列长度的语句会失败，并显示错误消息：Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:TABLE, limit is 20.

排队时间超过 6 小时的互动优先级 DML 作业会失败，并显示以下错误消息：

DML statement has been queued for too long

DML 语句冲突

在表上并发运行的 DML 语句会在语句尝试更改同一分区时导致 DML 语句冲突。只要不修改同一分区，语句就会成功。BigQuery 最多尝试重新运行失败的语句三次。

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

  • 只要 MERGE DML 语句仅插入行并且不会删除或更新任何现有行，就不会与其他并发运行的 DML 语句发生冲突。这可能包括具有 UPDATE 或 DELETE 子句的 MERGE 语句，前提是查询运行时未调用这些子句。

价格

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

最佳做法

为获得最佳性能，Google 建议您使用以下模式：

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

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

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

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

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id BETWEEN 54 AND 75;

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

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id IN (54, 57, 60);

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

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

如需了解优化查询性能的最佳实践，请参阅优化查询性能简介。

后续步骤

  • 如需查看 DML 语法信息和示例，请参阅 DML 语法。
  • 如需了解如何在计划查询中使用 DML 语句，请参阅计划查询。