使用数据操纵语言 (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 语句的并发。
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
细粒度 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 中的数据。
如需了解优化查询性能的最佳实践,请参阅优化查询性能简介。