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

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

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

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

如需详细了解如何计算 DML 语句处理的字节数，请参阅按需查询大小计算。

限制

• 每个 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 操作的目标。

DML 语句

以下部分介绍了不同类型的 DML 语句以及如何使用这些语句。

INSERT 语句

使用 INSERT 语句可向现有表中添加新行。以下示例使用明确指定的值将新行插入表 dataset.Inventory 中。

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

如需详细了解 INSERT 语句，请参阅 INSERT 语句。

DELETE 语句

使用 DELETE 语句删除表中的行。以下示例会删除表 dataset.Inventory 中 quantity 值为 0 的所有行。

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

如需删除表中的所有行，请改用 TRUNCATE TABLE 语句。如需详细了解 DELETE 语句，请参阅 DELETE 语句。

TRUNCATE 语句

使用 TRUNCATE 语句可从表中移除所有行，但会保留表元数据，包括表架构、说明和标签。以下示例从表 dataset.Inventory 中移除所有行。

TRUNCATE dataset.Inventory

删除表中的特定行。请改用 DELETE 语句。如需详细了解 TRUNCATE 语句，请参阅 TRUNCATE 语句。

UPDATE 语句

使用 UPDATE 语句更新表中的现有行。UPDATE 语句还必须包含 WHERE 关键字，以指定条件。以下示例将包含字符串 milk 的产品的行的 quantity 值减 10。

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

UPDATE 语句还可以包含 FROM 子句，以包含联接的表。如需详细了解 UPDATE 语句，请参阅 UPDATE 语句。

MERGE 语句

MERGE 语句将 INSERT、UPDATE 和 DELETE 操作合并为一个语句，并以原子方式执行这些操作，以将数据从一个表合并到另一个表。如需详细了解 MERGE 语句并查看相关示例，请参阅 MERGE 语句。

并发作业数

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

注意：DML 语句受到速率限制，例如表写入速率上限。如果您一次针对某个表提交大量作业，则可能会达到速率限制。这些速率不会限制可以运行的 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 作业。当上一个运行的作业完成时，下一个待处理作业将移出队列并运行。加入队列的变更型 DML 语句所共享的每个表的最大队列长度为 20。其他超出每个表的最大队列长度的语句会失败，并显示错误消息：Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

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

DML statement has been queued for too long

DML 语句冲突

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

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

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

精细 DML

预览版

此功能受服务专用条款的“通用服务条款”部分中的“非正式版产品条款”的约束。 您可以按照云数据处理附录中的说明针对此功能处理个人数据，但需遵守您访问 Google Cloud 时所依据的协议中的相关义务和限制。 非正式版功能“按原样”提供，可能只能获得有限的支持。 如需了解详情，请参阅发布阶段说明。

注意： 如需就此功能提供反馈或请求支持，请发送电子邮件至 [e-mail]。

细粒度 DML 是一种旨在优化 UPDATE、DELETE 和 MERGE 语句（也称为变更型 DML 语句）执行的性能增强功能。如果不启用细粒度 DML，系统会在文件组级别执行突变，这可能会导致数据重写效率低下。细粒度 DML 引入了一种更精细的方法，旨在减少需要重写的数据量，并减少总体 slot 消耗量。

启用精细 DML

如需启用精细 DML，请在运行 CREATE TABLE 或 ALTER TABLE DDL 语句时将enable_fine_grained_mutations 表选项设置为 TRUE。

如需使用精细 DML 创建新表，请使用 CREATE TABLE 语句：

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

如需使用精细 DML 更改现有表，请使用 ALTER TABLE 语句：

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

如需使用精细 DML 更改数据集中的所有现有表，请使用 ALTER TABLE 语句：

FOR record IN
 (SELECT CONCAT(table_schema, '.', table_name) AS table_path
 FROM mydataset.INFORMATION_SCHEMA.TABLES)
DO
 EXECUTE IMMEDIATE
   "ALTER TABLE " || record.table_path || " SET OPTIONS(enable_fine_grained_mutations = TRUE)";
END FOR;

将 enable_fine_grained_mutations 选项设置为 TRUE 后，系统会运行变更型 DML 语句，同时启用精细 DML 功能并使用现有的 DML 语句语法。

如需在表上停用精细 DML，请使用 ALTER TABLE DDL 语句将 enable_fine_grained_mutations 设置为 FALSE。

价格

为表启用精细 DML 可能会产生额外的 BigQuery 存储费用，用于存储与精细 DML 操作关联的额外变更元数据。实际费用取决于修改的数据量，但对于大多数情况而言，与表本身的大小相比，预计费用可以忽略不计。

精细 DML 操作采用混合方法处理已删除的数据，将重写成本分摊到多次表变更中。每项 DML 操作都会处理一部分已删除的数据，并且系统还会将剩余的处理任务分流到后台。这些已删除的数据处理作业会产生额外的 BigQuery 计算费用。

您可以使用 BigQuery 预留为这些离线删除数据处理作业分配专用的 BigQuery 计算资源。借助预留，您可以设置这些操作的费用上限。此方法对于非常大的表特别有用，这些表经常执行细粒度变异 DML 操作，否则在执行每个离线细粒度删除数据处理作业时，由于要处理大量字节，因此按需费用较高。

离线精细删除数据处理作业会被视为后台作业，并使用 BACKGROUND 分配类型，而不是 QUERY 分配类型。在没有 BACKGROUND 分配的情况下执行精细 DML 操作的项目会使用按需价格处理已删除的数据。

对于配置为使用按需计算价格的项目，细粒度 DML 语句不会减少扫描的字节数。

如需查找离线精细 DML 删除数据处理作业，请执行以下操作：

SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.JOBS
WHERE
  job_id LIKE "%fine_grained_mutation_garbage_collection%"

如果表接收到大量 DML 作业，则删除的数据处理可以完全由 DML 处理，从而无需进行后台处理。

已删除的数据注意事项

如果项目使用按需付费执行精细 DML 操作，则系统会使用内部 BigQuery 资源定期处理已删除的数据。

如果项目执行精细 DML 操作并分配了 BACKGROUND，则会使用槽来处理已删除的数据，并且受限于已配置的预留的资源可用性。如果已配置的预留中没有足够的可用资源，则处理已删除的数据的时间可能会超出预期。

限制

启用了精细 DML 的表会受到下列限制的约束：

• 您无法使用 tabledata.list 方法从启用了细粒度 DML 的表中读取内容。而是使用 SELECT 语句查询表以读取表记录。
• 无法使用 BigQuery 控制台预览启用了精细 DML 的表。
• 执行 UPDATE、DELETE 或 MERGE 语句后，您无法复制已启用精细 DML 的表。
• 在执行 UPDATE、DELETE 或 MERGE 语句后，您无法创建已启用细粒度 DML 的表的表快照或表克隆。
• 您无法在复制的数据集中的表上启用精细 DML，也无法复制包含已启用精细 DML 的表的数据集。
• 在多语句事务中执行的 DML 语句不会通过细粒度 DML 进行优化。
• 您无法对使用 CREATE TEMP TABLE 语句创建的临时表启用精细的 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 语句，请参阅计划查询。