本頁提供分區資料表的資料操縱語言 (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 陳述式