使用資料操縱語言 (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 分鐘的處理時間，才能複製資料。

  或者，您也可以使用 UPDATE、DELETE 或 MERGE 陳述式修改最近使用 Storage Write API 寫入的資料列。詳情請參閱「使用資料操縱語言 (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.

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

DML statement has been queued for too long

DML 陳述式衝突

當 DML 陳述式嘗試變更相同的分區時，在資料表上同時執行 DML 陳述式會導致 DML 陳述式發生衝突。只要陳述式不會修改相同的分割區，就會成功。BigQuery 最多會嘗試重新執行失敗的陳述式三次。

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

• 只要 MERGE DML 陳述式只插入資料列，且不會刪除或更新任何現有資料列，就不會與其他並行執行的 DML 陳述式發生衝突。這可能包括含有 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 可能會產生額外的 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 作業的專案，通常會使用內部 BigQuery 資源處理已刪除的資料。

執行精細 DML 作業的專案會使用 BACKGROUND 指派程序處理已刪除的資料，並受設定的預留作業資源可用性的影響。如果在已設定的預留容量內沒有足夠的資源，處理已刪除的資料可能會比預期時間更久。

限制

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

• 您無法使用 tabledata.list 方法從啟用精細 DML 的資料表讀取內容。請改為使用 SELECT 陳述式查詢資料表，以便讀取資料表記錄。
• 啟用精細 DML 的資料表無法透過 BigQuery 主控台進行預覽。
• 您無法在啟用精細 DML 的情況下複製資料表。
• 您無法為啟用精細 DML 的資料表建立資料表快照或資料表複本。
• 您無法在複本資料集中的資料表上啟用精細的 DML，也無法複製包含已啟用精細 DML 的資料表的資料集。
• 在 多陳述式交易中執行的 DML 陳述式不會透過精細 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 陳述式，請參閱排定查詢。