使用 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"}
    ]

插入数据

您可以使用 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"

更新数据

您可以使用 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

使用 MERGE 语句

您可以使用 DML MERGE 语句将某个分区表的 INSERTUPDATEDELETE 操作合并为一个语句,并以原子方式执行这些操作。

在使用 MERGE 语句时删减分区

当您对分区表运行 MERGE 语句时,可以使用 _PARTITIONTIME 伪列(对于提取时间分区表)或使用日期/时间戳列(对于分区表)来限制语句中涉及的分区。删减分区可以降低费用,同时提高查询性能。

您可以在以下位置使用分区删减条件:在子查询过滤器中、在 search_condition 过滤器中或在 merge_condition 过滤器中。

下面的每个示例都使用 _PARTITIONTIME 伪列来查询提取时间分区表。

使用子查询过滤源数据

您可以在子查询中使用过滤器来删减分区。例如,以下 MERGE 语句仅扫描源表中 '2018-01-01' 分区内的行。

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

when_clausesearch_condition 中使用过滤器

查询优化器会尝试在 search_condition 中使用过滤器来删减分区。例如,在以下 MERGE 语句中,仅扫描目标表中 '2018-01-01''2018-01-02''2018-01-03' 分区中的行。

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

在以下示例中,WHEN NOT MATCHED BY SOURCE 子句需要目标表中的所有数据。因此,系统会扫描所有分区,您需要为所有分区中读取的字节付费。

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

通常情况下,如果您结合使用 WHEN NOT MATCHEDWHEN NOT MATCHED BY SOURCE 子句,BigQuery 会假设源表与目标表之间存在 FULL OUTER JOIN。通常,不能在 FULL OUTER JOIN 中删减分区。但如果使用了常量 false 谓词,则可以使用过滤条件来实现分区删减。以下查询使用分区删减,以便仅扫描目标表和源表中的 '2018-01-01' 分区。

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

merge_condition 中使用过滤器

查询优化器会尝试在 merge_condition 中使用过滤器来删减分区。例如,以下查询将仅扫描目标表和源表中的 '2018-01-01' 分区。

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

在此示例中,merge_condition 用作联接源表和目标表的谓词。无法确定查询优化器是否能使用谓词下推(具体取决于联接的类型)。

在以下示例中,MERGE 语句不允许分区删减,因为分区筛选器是连接条件中的谓词,无法直接应用于表。

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

限制

如需了解 DML 限制,请参阅数据操纵语言页面中的限制

配额

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

价格

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

后续步骤