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

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

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

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

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

限制

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

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

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

  • MERGE 语句不支持 when_clausesearch_conditionmerge_update_clausemerge_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 并发

UPDATEDELETEMERGE 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 语句发生冲突。这可能包括具有 UPDATEDELETE 子句的 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 语句,请参阅计划查询