使用資料操縱語言 (DML) 轉換資料

BigQuery 資料操縱語言 (DML) 可讓您在 BigQuery 資料表中更新、插入及刪除資料。

執行 DML 陳述式的程序與執行 SELECT 陳述式的相同，但必須符合下列條件：

• 您必須使用 GoogleSQL。如要啟用 GoogleSQL，請參閱「切換 SQL 方言」。
• 您無法為查詢指定目標資料表。

如要進一步瞭解如何計算 DML 陳述式處理的位元組數，請參閱「隨選查詢大小計算」。

限制

• 每個 DML 陳述式都會啟動一個隱含交易，這表示在每個 DML 陳述式成功結束時，系統會自動修訂此陳述式所做的變更。

• 最近使用 tabledata.insertall 串流方法寫入的資料列，無法使用資料操作語言 (DML) 修改，例如 UPDATE、DELETE、MERGE 或 TRUNCATE 陳述式。最近的寫入是指過去 30 分鐘內發生的寫入作業。資料表中的所有其他資料列還是可以透過 UPDATE、DELETE、MERGE 或 TRUNCATE 陳述式加以修改。串流資料最多可能需要 90 分鐘的處理時間，才能複製。

  此外，最近使用 Storage Write API 寫入的資料列，可以使用 UPDATE、DELETE 或 MERGE 陳述式加以修改。詳情請參閱使用資料操縱語言 (DML) 處理最近串流的資料。

• MERGE 陳述式不支援在 when_clause、search_condition、merge_update_clause 或 merge_insert_clause 中使用相互關聯的子查詢。

• 包含 DML 陳述式的查詢，無法使用萬用字元資料表做為查詢目標。例如，可在 UPDATE 查詢的 FROM 子句中使用萬用字元資料表，但無法將萬用字元資料表做為 UPDATE 運算的目標。

DML 陳述式

以下各節說明不同類型的 DML 陳述式，以及如何使用這些陳述式。

INSERT 陳述式

如要在現有資料表中加入新列，請使用 INSERT 陳述式。以下範例會將新資料列插入 dataset.Inventory 資料表，並明確指定值。

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

如要進一步瞭解 INSERT 陳述式，請參閱 INSERT 陳述式。

DELETE 陳述式

如要刪除資料表中的列，請使用 DELETE 陳述式。以下範例會刪除資料表 dataset.Inventory 中具有 quantity 值 0 的所有資料列。

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

如要刪除資料表中的所有資料列，請改用 TRUNCATE TABLE 陳述式。如要進一步瞭解 DELETE 陳述式，請參閱DELETE 陳述式。

TRUNCATE 陳述式

使用 TRUNCATE 陳述式從資料表移除所有資料列，但保留資料表的中繼資料，包括資料表結構定義、說明和標籤。下列範例會移除 dataset.Inventory 資料表中的所有資料列。

TRUNCATE dataset.Inventory

刪除資料表中的特定資料列。請改用 DELETE 陳述式。如要進一步瞭解 TRUNCATE 陳述式，請參閱 TRUNCATE 陳述式。

UPDATE 陳述式

如要更新資料表中現有的列，請使用 UPDATE 陳述式。UPDATE 陳述式也必須包含 WHERE 關鍵字，以指定條件。以下範例會針對包含 milk 字串的產品，將資料列的 quantity 值減 10。

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

UPDATE 陳述式也可以包含 FROM 子句，以納入已聯結的資料表。 如要進一步瞭解 UPDATE 陳述式，請參閱 UPDATE 陳述式。

MERGE 陳述式

MERGE 陳述式會將 INSERT、UPDATE 和 DELETE 作業合併成單一陳述式，並以不可分割的形式執行這些作業，將資料從一個資料表合併到另一個資料表。如要進一步瞭解 MERGE 陳述式和相關範例，請參閱 MERGE 陳述式。

並行工作

BigQuery 會管理 DML 陳述式的並行作業，這些陳述式會在資料表中新增、修改或刪除資料列。

注意： DML 陳述式受頻率限制，例如資料表寫入頻率上限。 如果一次對資料表提交大量工作，可能會達到速率限制。這些費率不會限制可執行的 DML 陳述式總數。 如果收到錯誤訊息，指出您超出速率限制，請在每次重試之間使用指數輪詢重試作業。

INSERT DML 並行

在任何 24 小時期間內，系統會在提交前 1500 個 INSERT 陳述式後立即執行。達到此上限後，寫入資料表的 INSERT 陳述式並行數會限制為 10。其他 INSERT陳述式會加入 PENDING 佇列。在任何時間點，最多可針對資料表排定 100 個INSERT陳述式。INSERT 陳述式完成後，下一個 INSERT 陳述式會從佇列中移除並執行。

如果必須更頻繁地執行 DML INSERT 陳述式，建議使用 Storage Write API 將資料串流至資料表。

UPDATE、DELETE、MERGE DML 並行

UPDATE、DELETE 和 MERGE DML 陳述式稱為變動 DML 陳述式。如果您在資料表上提交一或多個變動 DML 陳述式，而該資料表上仍有其他變動 DML 工作正在執行 (或等待中)，BigQuery 最多會並行執行 2 個陳述式，之後最多會將 20 個陳述式排入佇列，狀態為 PENDING。先前執行的工作完成後，系統會將下一個待處理工作從佇列中移除並執行。已加入佇列的變動 DML 陳述式會共用每個資料表的佇列，長度上限為 20。如果每個資料表的佇列長度超過上限，額外陳述式就會失敗，並顯示以下錯誤訊息：Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

如果互動式優先順序 DML 工作排入佇列超過 7 小時，就會失敗並顯示下列錯誤訊息：

DML statement has been queued for too long

DML 陳述式衝突

如果對資料表同時執行變異 DML 陳述式，且陳述式嘗試變異相同分區，就會導致 DML 陳述式衝突。只要陳述式未修改相同分割區，就會成功執行。BigQuery 最多會嘗試重新執行失敗的陳述式三次。

• 將資料列插入資料表的 INSERT DML 陳述式，不會與任何其他並行執行的 DML 陳述式發生衝突。

• 只要 MERGE DML 陳述式只會插入資料列，不會刪除或更新任何現有資料列，就不會與其他並行執行的 DML 陳述式發生衝突。這類陳述式可以包含 UPDATE 陳述式，以及 UPDATE 或 DELETE 子句，只要查詢執行時未叫用這些子句即可。MERGE

精細 DML

預先發布版

這項功能適用《服務專屬條款》中「一般服務條款」一節的《正式發布前產品條款》。 您可以按照《Cloud 資料處理附加條款》規定的方式，處理這項功能會使用的個人資料，但請遵守 Google Cloud 使用協議中的義務和限制。 正式發布前的功能是按照「原樣」提供，支援範圍可能有限。 詳情請參閱推出階段說明。

注意： 如要提供意見回饋或尋求這項功能支援，請傳送電子郵件至 [e-mail]。

細微 DML 是一種效能提升功能，旨在最佳化 UPDATE、DELETE 和 MERGE 陳述式的執行作業 (也稱為變動 DML 陳述式)。如果未啟用精細 DML，系統會在檔案群組層級執行突變，導致資料重寫效率不彰。細微 DML 採用更精細的方法，旨在減少需要重寫的資料量，並降低整體時段消耗量。

啟用精細 DML

如要啟用細部 DML，請在執行 CREATE TABLE 或 ALTER TABLE DDL 陳述式時，將enable_fine_grained_mutations 資料表選項設為 TRUE。

如要使用精細 DML 建立新資料表，請使用 CREATE TABLE 陳述式：

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

如要使用細微 DML 變更現有資料表，請使用 ALTER TABLE 陳述式：

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

如要使用細微的 DML 變更資料集中的所有現有資料表，請使用 ALTER TABLE 陳述式：

FOR record IN
 (SELECT CONCAT(table_schema, '.', table_name) AS table_path
 FROM mydataset.INFORMATION_SCHEMA.TABLES)
DO
 EXECUTE IMMEDIATE
   "ALTER TABLE " || record.table_path || " SET OPTIONS(enable_fine_grained_mutations = TRUE)";
END FOR;

將 enable_fine_grained_mutations 選項設為 TRUE 後，系統會執行變動 DML 陳述式，並啟用精細 DML 功能，且使用現有的 DML 陳述式語法。

如要在資料表上停用細微 DML，請使用 ALTER TABLE DDL 陳述式，將 enable_fine_grained_mutations 設為 FALSE。

定價

為資料表啟用精細 DML 時，系統會儲存與精細 DML 作業相關的額外變動中繼資料，因此可能會產生額外的 BigQuery 儲存費用。實際費用取決於修改的資料量，但與資料表本身的大小相比，預計在大多數情況下可忽略不計。

細微 DML 作業會以混合方式處理已刪除的資料，將重寫成本分散到多個資料表變異。每項 DML 作業都會處理部分已刪除的資料，系統也會將剩餘的處理作業卸載至背景。這些已刪除的資料處理工作會產生額外的 BigQuery 運算費用。

您可以使用 BigQuery 預留項目，為這些離線刪除資料處理工作分配專用的 BigQuery 運算資源。預留項目可讓您設定執行這些作業的費用上限。如果資料表非常龐大，且經常執行細微的變動 DML 作業，這個方法就特別實用。否則，執行每項離線細微刪除資料處理工作時，由於處理的位元組數量龐大，因此會產生高昂的隨選費用。

離線精細刪除資料處理工作視為背景工作，並使用 BACKGROUND 指派類型，而非 QUERY 指派類型。專案執行細微的 DML 作業時，如果沒有BACKGROUND指派程序，系統會使用以量計價刪除資料。

如果專案設定為使用以量計價的運算價格，細微的 DML 陳述式不會減少掃描的位元組數。

如要找出離線細部 DML 刪除資料處理作業：

SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.JOBS
WHERE
  job_id LIKE "%fine_grained_mutation_garbage_collection%"

如果資料表收到大量 DML 工作，則刪除的資料處理作業可完全由 DML 處理，不需要背景處理。

刪除資料的注意事項

使用隨選價格執行精細 DML 作業的專案，通常會使用 BigQuery 內部資源處理已刪除的資料。

執行細微 DML 作業的專案會使用時段處理已刪除的資料，並受限於設定預留資源的可用性。BACKGROUND指派程序如果設定的預留量沒有足夠資源，處理已刪除的資料可能需要比預期更長的時間。

限制

啟用精細 DML 的資料表有下列限制：

• 您無法使用 tabledata.list 方法從啟用細部 DML 的資料表讀取內容。請改為使用 SELECT 陳述式查詢資料表，以讀取資料表記錄。
• 如果資料表已啟用精細 DML，就無法使用 BigQuery 控制台預覽。
• 執行 UPDATE、DELETE 或 MERGE 陳述式後，您無法複製已啟用精細 DML 的資料表。
• 執行 UPDATE、DELETE 或 MERGE 陳述式後，您無法為啟用精細 DML 的資料表建立資料表快照或資料表副本。
• 您無法在複製的資料集中，對資料表啟用精細 DML，也無法複製含有已啟用精細 DML 資料表的資料集。
• 在多重陳述式交易中執行的 DML 陳述式不會透過細微的 DML 進行最佳化。
• 您無法在以 CREATE TEMP TABLE 陳述式建立的臨時資料表上啟用精細 DML。

最佳做法

為獲得最佳成效，Google 建議採用下列模式：

• 避免提交大量個別的資料列更新或插入作業。 請盡可能將 DML 作業分組。詳情請參閱「更新或插入單列的 DML 陳述式」。

• 如果更新或刪除作業通常是針對舊資料或特定日期範圍內的資料，建議分割資料表。分區可確保變更僅限於資料表中的特定分區。

• 如果每個分區的資料量很小，且每次更新都會修改大部分的分區，請避免對資料表進行分區。

• 如果您經常更新一或多個資料欄的值落在狹窄範圍內的資料列，請考慮使用叢集資料表。分群可確保變更僅限於特定區塊集，減少需要讀取和寫入的資料量。以下是 UPDATE 陳述式的範例，可根據資料欄值範圍進行篩選：

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id BETWEEN 54 AND 75;

  以下是類似的範例，但會根據一小部分資料欄值進行篩選：

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id IN (54, 57, 60);

  在這些情況下，請考慮對 id 欄進行分群。

• 如需 OLTP 功能，請考慮使用 Cloud SQL 聯合查詢，讓 BigQuery 查詢 Cloud SQL 中的資料。

如要瞭解最佳化查詢效能的最佳做法，請參閱「最佳化查詢效能簡介」。

後續步驟

• 如需 DML 語法資訊和範例，請參閱 DML 語法。
• 如要瞭解如何在排定時程的查詢中使用 DML 陳述式，請參閱排定查詢時程。