使用資料操縱語言 (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 陳述式。這種做法可大幅減少變更 DML 工作所耗用的處理、I/O 和時段時間。

使用細部 DML 時，請注意以下成效考量事項：

• 細微 DML 作業會以混合方式處理已刪除的資料，將重寫成本分散到多個資料表變異中。每個 DML 作業可能會處理部分已刪除的資料，然後將剩餘的已刪除資料處理作業卸載至背景垃圾收集程序。詳情請參閱刪除資料的注意事項。
• 如果資料表經常發生變動 DML 作業，後續的 SELECT 查詢和 DML 工作可能會出現延遲。如要評估啟用這項功能帶來的影響，請針對一連串實際的 DML 作業和後續讀取作業，設定效能基準。
• 對於經常變動且超過 2 TB 的大型資料表，不建議使用細微 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 類型配額。

刪除資料的注意事項

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

使用隨選價格或沒有BACKGROUND指派項目的專案，會使用隨選價格處理已刪除的資料，並定期使用 BigQuery 內部資源處理已刪除的資料。

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

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

限制

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

• 您無法使用 tabledata.list 方法從啟用細部 DML 的資料表讀取內容。請改為使用 SELECT 陳述式查詢資料表，以讀取資料表記錄。
• 您無法使用 BigQuery 控制台預覽啟用精細 DML 的資料表。
• 執行 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 中的資料。

• 如要解決配額錯誤並避免再次發生，請參閱 BigQuery 疑難排解頁面上的這項錯誤的相關指南。Too many DML statements outstanding against table,

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

後續步驟

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