使用 data manipulation language (DML) 轉換資料

BigQuery data manipulation language (DML) 可讓您在 BigQuery 資料表中更新、插入及刪除資料。

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

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

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

限制

• 每個 DML 陳述式都會啟動隱含交易，也就是說，陳述式所做的變更會在每個成功的 DML 陳述式結束時自動修訂。

• 最近使用 BigQuery Storage Write API (REST) 寫入的資料列，無法透過資料操作語言 (DML) 修改，例如 UPDATE、DELETE、MERGE 或 TRUNCATE 陳述式。最近寫入的資料是指過去 30 分鐘內寫入的資料。資料表中的所有其他資料列仍可使用 UPDATE、DELETE、MERGE 或 TRUNCATE 陳述式修改。串流資料最多可能需要 90 分鐘，才能用於複製作業。

  或者，您也可以使用 UPDATE、DELETE 或 MERGE 陳述式，修改最近使用 Storage Write API (gRPC) 寫入的資料列。詳情請參閱「對最近串流的資料使用 data manipulation language (DML)」。

• MERGE 陳述式不支援 when_clause、search_condition、merge_update_clause 或 merge_insert_clause 內的關聯子查詢。

• 含有 DML 陳述式的查詢無法使用Wildcard 資料表做為查詢目標。舉例來說，Wildcard 資料表可用於 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 小時內，前 1, 500 個 INSERT 陳述式會在提交後立即執行。達到此上限後，寫入資料表的 INSERT 陳述式並行數量上限為 10。其他 INSERT 陳述式會新增至 PENDING 佇列。在任何時間點，一個資料表最多可排入 100 個 INSERT 陳述式。當 INSERT 陳述式完成時，下一個 INSERT 陳述式會從佇列中移除並執行。

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

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 或 DELETE 子句的 MERGE 陳述式，前提是執行查詢時不會叫用這些子句。

• 自動 DML 佇列功能僅適用於在多陳述式交易外執行的獨立 DML 陳述式。在多陳述式交易中，變更 DML 陳述式會取得資料表層級鎖定，而非鎖定個別分區。修改相同資料表的並行多重陳述式交易不會排入佇列，而是會立即失敗，即使這些交易指定不同的分區也一樣。如果預存程序或多陳述式查詢只執行單一變動 DML 陳述式 (例如 MERGE 陳述式)，請勿將陳述式包裝在 BEGIN TRANSACTION 和 COMMIT TRANSACTION 陳述式中。詳情請參閱「交易並行」。

精細 DML

預覽

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

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

精細 DML 是一項效能強化功能，旨在最佳化 UPDATE、DELETE 和 MERGE 陳述式 (也稱為「異動」DML 陳述式) 的執行。

效能注意事項

如果未啟用精細 DML，DML 異動會在檔案群組層級執行，可能導致資料重寫效率不彰，尤其是稀疏異動。這可能會導致額外的運算單元消耗和更長的執行時間。

精細 DML 是一項效能強化功能，旨在透過更精細的方法，減少檔案群組層級需要重寫的資料量，進而最佳化這些變動 DML 陳述式。這種做法可大幅減少變動 DML 工作耗用的處理、I/O 和運算單元時間。

使用精細 DML 時，請留意下列成效考量：

• 精細 DML 作業會以混合方式處理已刪除的資料，將重寫成本分散到多個資料表異動。每項 DML 作業可能會處理一部分的已刪除資料，然後將剩餘的已刪除資料處理作業卸載至背景垃圾收集程序。詳情請參閱「刪除資料的注意事項」。
• 如果資料表經常執行變動 DML 作業，後續 SELECT 查詢和 DML 工作的延遲時間可能會增加。如要評估啟用這項功能的影響，請對實際 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，請查詢 INFORMATION_SCHEMA.TABLES 檢視表。以下範例會檢查資料集內有哪些資料表已啟用這項功能：

SELECT
  table_schema AS datasetId,
  table_name AS tableId,
  is_fine_grained_mutations_enabled
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLES;

將 DATASET_NAME 替換為資料集名稱，檢查是否有任何資料表啟用精細 DML。

停用精細 DML

如要停用現有資料表的精細 DML，請使用 ALTER TABLE 陳述式。

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

停用精細 DML 時，可能需要一些時間才能完全處理所有刪除的資料，請參閱「刪除資料的注意事項」。因此，在完成這項作業之前，精細 DML 限制可能仍會存在。

定價

為資料表啟用精細 DML 可能會產生額外費用。這些費用包括：

• BigQuery 儲存空間費用：用於儲存與精細 DML 作業相關聯的額外異動中繼資料。實際儲存費用取決於修改的資料量，但與資料表本身的大小相比，在大多數情況下，這筆費用應該微不足道。
• BigQuery 運算費用：使用卸載的垃圾收集工作處理已刪除的資料，以及後續的 SELECT 查詢處理尚未收集垃圾的其他刪除中繼資料。

您可以使用 BigQuery 預留項目，分配專用的 BigQuery 運算資源，處理卸載的刪除資料工作。預留項目可讓您設定執行這些作業的費用上限。如果資料表非常大，且經常執行精細的變動 DML 作業，這種做法特別實用，通常也建議採用。否則，每次執行卸載的刪除資料處理工作時，都會處理大量位元組，導致隨選費用高昂。

精細 DML 卸載的刪除資料處理工作會視為背景工作，因此必須使用 BACKGROUND 預留項目指派類型，而非 QUERY 預留項目指派類型。如果專案執行精細 DML 作業，但未指派 BACKGROUND，則會採用隨選定價，處理卸載的刪除資料工作。

作業	以量計價	依據容量計費
變異 DML 陳述式	使用標準 DML 大小，決定隨選掃描位元組的計算方式。 啟用精細 DML 不會減少 DML 陳述式本身的掃描位元組數。	在陳述式執行階段，使用指派 QUERY 類型的運算單元。
已卸載刪除的資料處理工作	使用標準 DML 大小，決定執行刪除資料處理工作時，掃描的隨選位元組計算方式。	執行已刪除的資料處理工作時，使用指派為 BACKGROUND 類型的運算單元。

已刪除資料的注意事項

精細 DML 作業會採用混合式方法管理已刪除的資料，結合內嵌處理和卸載垃圾收集，分配重寫成本，並針對資料表發出的多個變動 DML 陳述式，最佳化效能。

執行變動 DML 陳述式時，BigQuery 會嘗試從先前的 DML 陳述式內嵌執行部分相關垃圾收集。任何未在內嵌處理的已刪除資料，都會卸載至背景程序，以便日後清理。

使用 BACKGROUND 指派程序執行精細 DML 作業的專案，會使用運算單元卸載垃圾收集工作。處理已刪除的資料時，須視設定的預留項目資源可用性而定。如果設定的預留項目中沒有足夠的可用資源，處理卸載的垃圾收集作業可能需要比預期更長的時間。

如果專案使用隨選計價模式執行精細 DML 作業，或未指派 BACKGROUND，則會使用內部 BigQuery 資源處理卸載的垃圾回收工作，並按照隨選計價費率收費。詳情請參閱「定價」。

如果使用 BACKGROUND 指派，卸載垃圾收集工作的時間點取決於資料表 DML 活動的頻率和資源可用性：

• 如果資料表有持續變動的 DML 作業，每個 DML 都會處理一部分的垃圾收集工作負載，確保讀取和寫入效能一致，因此垃圾收集作業會隨著後續 DML 的執行而定期處理。
• 如果資料表沒有後續的 DML 活動，系統會在刪除的資料達到 5 天時，自動觸發卸載垃圾收集。
• 在極少數情況下，可能需要較長時間才能完全處理刪除的資料。

如要找出已卸載的精細 DML 刪除資料處理工作，請查詢 INFORMATION_SCHEMA.JOBS 檢視表：

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

限制

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

• 如果大型資料表經常變動的分區超過 2 TB，則不建議使用精細 DML。這些資料表在後續查詢時可能會面臨額外的記憶體壓力，導致讀取延遲時間增加或查詢錯誤。
• 在啟用精細 DML 的資料表上，一次只能執行一個變動 DML 陳述式。後續工作會排入 PENDING 佇列。如要進一步瞭解 DML 變動並行行為，請參閱「UPDATE、DELETE、MERGE DML 並行」。
• 啟用精細 DML 的資料表無法個別刪除或覆寫分區。如要刪除或取代分區中的資料，您必須使用變動 DML 陳述式，例如 DELETE、UPDATE、MERGE 或 TRUNCATE。
• 您無法使用 tabledata.list 方法，從已啟用精細 DML 的資料表讀取內容。請改用 SELECT 陳述式查詢資料表，讀取資料表記錄。
• 啟用精細 DML 的資料表無法使用 BigQuery 控制台預覽。
• 執行 UPDATE、DELETE 或 MERGE 陳述式後，您無法複製已啟用精細 DML 的資料表。
• 執行 UPDATE、DELETE 或 MERGE 陳述式後，您無法為啟用精細 DML 的資料表建立資料表快照或資料表複製。
• 執行 UPDATE、DELETE 或 MERGE 陳述式後，您無法重新命名已啟用精細 DML 的資料表。
• 如果資料表已啟用精細 DML，則不支援跨區域複製。雖然系統會禁止建立已包含這類資料表的複製資料集，但不會禁止在現有複製資料集內的資料表上啟用精細 DML。不過，如果啟用精細 DML 的資料表，次要執行個體可能會產生不正確的查詢結果。
• 在多陳述式交易中執行的 DML 陳述式，不會透過精細 DML 進行最佳化。
• 您無法在使用 CREATE TEMP TABLE 陳述式建立的臨時資料表上啟用精細 DML。
• 在背景垃圾收集工作完成前，INFORMATION_SCHEMA.TABLE_STORAGE 檢視表和 INFORMATION_SCHEMA.PARTITIONS 檢視表中的中繼資料，可能會暫時包含最近使用精細 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 中的資料。

• 如要解決並避免配額錯誤 Too many DML statements outstanding against table,，請按照 BigQuery 疑難排解頁面上的錯誤指南操作。

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

後續步驟

• 如需 DML 語法資訊和範例，請參閱「DML 語法」。
• 進一步瞭解如何使用 DML 更新分區資料表資料。
• 如要瞭解如何在排定的查詢中使用 DML 陳述式，請參閱「排定查詢」。