使用 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 陳述式，將分區資料表的 INSERT、UPDATE 和 DELETE 作業合併成單一陳述式，並以不可分割的形式予以執行。

在使用 `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_clause` 的 `search_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 MATCHED 和 WHEN NOT MATCHED BY SOURCE 子句時，BigQuery 會假設來源和目標資料表間有 FULL OUTER JOIN。一般來說，您無法縮減 FULL OUTER JOIN 中的分區，但如果您使用常數假述詞，就可以使用篩選條件來縮減分區。下列查詢使用分區縮減作業，只掃描目標和來源資料表中的 '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 定價。

後續步驟

瞭解如何建立與使用擷取時間分區資料表
瞭解如何建立與使用分區資料表
瞭解如何查詢分區資料表
取得 DML 簡介
瞭解如何使用 DML 語法撰寫 DML 陳述式

本頁內容對您是否有任何幫助？請提供意見：

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

上次更新：九月 11, 2019。