使用 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 陳述式會從 2017 年 6 月 1 日分區 (“2017-06-01”) 或 mytable 中刪除所有列，其中 field1 等於 21。您可以使用 _PARTITIONTIME 虛擬資料欄來參照分區。

DELETE PROJECT_ID.DATASET.mytable WHERE field1 = 21 AND _PARTITIONTIME = “2017-05-01”

刪除分區資料表中的資料

使用 DML 從分區資料表中刪除資料的處理程序與從非分區資料表中刪除資料的處理程序相同。

例如，以下 DELETE 陳述式會從 2017 年 6 月 1 日分區 (“2017-06-01”) 或 mycolumntable 中刪除所有列，其中 field1 等於 21。

DELETE PROJECT_ID.DATASET.mycolumntable WHERE field1 = 21 AND DATE(ts) = “2017-05-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 定價。

後續步驟

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

傳送您對下列選項的寶貴意見...

這個網頁
文件意見
BigQuery
產品意見