使用 DML 更新分区表数据

本页面简要介绍数据操纵语言 (DML) 对分区表的支持。

如需详细了解 DML，请参阅以下内容：

示例中使用的表

下面的 JSON 架构定义表示在本页示例中使用的表。

mytable：注入时间分区表

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
    ]

mytable2：标准（非分区）表

    [
      {"name": "id", "type": "INTEGER"},
      {"name": "ts", "type": "TIMESTAMP"}
    ]

mycolumntable：使用 ts TIMESTAMP 列进行分区的分区表

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
      {"name": "field3", "type": "BOOLEAN"}
      {"name": "ts", "type": "TIMESTAMP"}
    ]

在显示 COLUMN_ID 的示例中，将其替换为您要进行操作的列的名称。

插入数据

您可以使用 DML INSERT 语句向分区表添加行。

将数据插入注入时间分区表

使用 DML 语句向注入时间分区表添加行时，您可以指定应将行添加到哪个分区。您可以使用 _PARTITIONTIME 伪列来引用分区。

例如，以下 INSERT 语句会向 mytable 的 2017 年 5 月 1 日分区 (“2017-05-01”) 添加一行。

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01"),
  1,
  "one"

您只能使用与精确日期边界对应的时间戳。例如，以下 DML 语句会返回错误：

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01 21:30:00"),
  1,
  "one"

向分区表插入数据

使用 DML 将数据插入到分区表的方式与将数据插入到非分区表的方式相同。

例如，以下 INSERT 语句通过从 mytable2（非分区表）中选择数据来向分区表 mycolumntable 添加行。

INSERT INTO
  project_id.dataset.mycolumntable (ts,
    field1)
SELECT
  ts,
  id
FROM
  project_id.dataset.mytable2

删除数据

您可以使用 DML DELETE 语句从分区表中删除行。

删除注入时间分区表中的数据

以下 DELETE 语句会删除 mytable 的 2017 年 6 月 1 日分区 ("2017-06-01") 中符合以下条件的所有行：field1 等于 21。您可以使用 _PARTITIONTIME 伪列来引用分区。

DELETE
  project_id.dataset.mytable
WHERE
  field1 = 21
  AND _PARTITIONTIME = "2017-06-01"

删除分区表中的数据

使用 DML 从分区表中删除数据的方式与从非分区表中删除数据的方式相同。

例如，以下 DELETE 语句会删除 mycolumntable 的 2017 年 6 月 1 日分区 ("2017-06-01") 中符合以下条件的所有行：field1 等于 21。

DELETE
  project_id.dataset.mycolumntable
WHERE
  field1 = 21
  AND DATE(ts) = "2017-06-01"

使用 DML DELETE 删除分区

如果符合条件的 DELETE 语句涵盖分区中的所有行，则 BigQuery 将移除整个分区。此移除操作不需要扫描字节或使用槽。以下 DELETE 语句示例涵盖 _PARTITIONDATE 伪列上的过滤器的整个分区：

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

常见的不符合条件的情况

具有以下特征的查询可能无法从优化中获益：

覆盖部分分区
引用非分区列
通过 BigQuery Storage Write API 或旧版流式插入 API 最近注入的数据
过滤条件包含子查询或不受支持的谓词

优化资格因分区类型、底层存储元数据和过滤条件谓词而异。最佳做法是执行试运行，以验证查询处理的结果为 0 字节。

多语句事务

此优化适用于多语句事务。以下查询示例在单个事务中将分区替换为另一个表中的数据，而不扫描分区中的 DELETE 语句。

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

更新数据

您可以使用 UPDATE 语句更新分区表中的行。

更新注入时间分区表中的数据

以下 UPDATE 语句会将行从一个分区移动到另一个分区。在 mytable 的 2017 年 5 月 1 日分区 (“2017-05-01”) 中，field1 等于 21 的所有行都会移动到 2017 年 6 月 1 日分区 (“2017-06-01”)。

UPDATE
  project_id.dataset.mytable
SET
  _PARTITIONTIME = "2017-06-01"
WHERE
  _PARTITIONTIME = "2017-05-01"
  AND field1 = 21

更新分区表中的数据

使用 DML 更新分区表中数据的方式与更新非分区表中数据的方式相同。例如，以下 UPDATE 语句会将行从一个分区移动到另一个分区。在 mytable 的 2017 年 5 月 1 日分区 (“2017-05-01”) 中，field1 等于 21 的所有行都会移动到 2017 年 6 月 1 日分区 (“2017-06-01”)。

UPDATE
  project_id.dataset.mycolumntable
SET
  ts = "2017-06-01"
WHERE
  DATE(ts) = "2017-05-01"
  AND field1 = 21

每小时、每月和每年分区表中的 DML

您可以使用 DML 语句修改每小时、每月或每年分区表。提供相关日期/时间戳/日期时间的小时、月份或年度范围，如以下每月分区表示例所示：

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'

或者，如包含 DATETIME 列的另一个分区表示例所示：

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    dt_column BETWEEN DATETIME("2020-01-01")
    AND DATETIME("2020-05-01");'

使用 `MERGE` 语句

您可以使用 DML MERGE 语句将某个分区表的 INSERT、UPDATE 和 DELETE 操作合并为一个语句，并以原子方式执行这些操作。

在使用 `MERGE` 语句时删减分区

当您对分区表运行 MERGE 语句时，可以在子查询过滤条件、search_condition 过滤条件或 merge_condition 过滤条件中添加分区列来限制扫描的分区。在扫描源表和/或目标表时可能会进行删减。

下面的每个示例都使用 _PARTITIONTIME 伪列作为过滤条件来查询注入时间分区表。

使用子查询过滤源数据

在以下 MERGE 语句中，USING 子句中的子查询会过滤源表中的 _PARTITIONTIME 伪列。

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED THEN
  DELETE

查看查询执行计划，子查询首先运行。只会扫描源表中 '2018-01-01' 分区内的行。以下是查询计划的相关阶段：

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

在 `when_clause` 的 `search_condition` 中使用过滤器

如果 search_condition 包含过滤器，则查询优化器会尝试删减分区。例如，在以下 MERGE 语句中，每个 WHEN MATCHED 和 WHEN NOT MATCHED 子句都包含一个针对 _PARTITIONTIME 伪列的过滤条件。

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

在联接阶段，只会扫描目标表中的以下分区：'2018-01-01'、'2018-01-02' 和 '2018-01-03'（即所有 search_condition 的并集）过滤条件。

在查询执行计划中：

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

但在下面的示例中，WHEN NOT MATCHED BY SOURCE 子句没有过滤表达式：

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = COLUMN_ID + 1

此查询必须扫描整个目标表以计算 WHEN NOT MATCHED BY SOURCE 子句。因此，系统不会删减分区。

在 `merge_condition` 中使用常量假谓词

如果同时使用 WHEN NOT MATCHED 和 WHEN NOT MATCHED BY SOURCE 子句，则 BigQuery 通常会执行完整的外部联接，无法删减。但是，如果合并条件使用 false 常量，则 BigQuery 可以使用过滤条件进行删减。如需详细了解如何使用常量假谓词，请参阅 MERGE 语句文档中的 merge_condition 子句的说明。

以下示例仅扫描目标表和源表中的 '2018-01-01' 分区。

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
  INSERT(COLUMN_ID) VALUES(COLUMN_ID)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

在 `merge_condition` 中使用过滤器

查询优化器会尝试在 merge_condition 中使用过滤器来删减分区。根据联接类型，查询优化器不一定会将谓词下推到表扫描阶段。

在以下示例中，merge_condition 用作联接源表和目标表的谓词。查询优化器可以在扫描这两个表时将此谓词下推。因此，查询仅扫描目标表和源表中的 '2018-01-01' 分区。

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

在下一个示例中，merge_condition 不包含源表的谓词，因此不能对源表执行分区删减。该语句包含目标表的谓词，但该语句使用 WHEN NOT MATCHED BY SOURCE 子句，而不是 WHEN MATCHED 子句。这意味着查询必须扫描整个目标表以查找不匹配的行。

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

限制

如需了解 DML 限制，请参阅 DML 参考页面中的限制。

配额

如需了解 DML 配额信息，请参阅配额和限制页面中的 DML 语句。

价格

如需了解 DML 价格，请参阅分区表的 DML 价格。

表安全性

如需控制对 BigQuery 中表的访问权限，请参阅表访问权限控制简介。

后续步骤

了解如何创建分区表
了解如何查询分区表
查看 DML 简介
了解如何使用 DML 语法编写 DML 语句