使用變更資料擷取功能串流資料表更新

BigQuery 變更資料擷取 (CDC) 會處理並將串流變更套用至現有資料，藉此更新 BigQuery 資料表。這項同步作業是透過 BigQuery Storage Write API 即時串流的 upsert 和刪除資料列作業完成，您應先熟悉這項 API，再繼續操作。

事前準備

授予 Identity and Access Management (IAM) 角色，為使用者提供執行本文件中各項工作的必要權限，並確保工作流程符合各項必要條件。

所需權限

如要取得使用 Storage Write API 所需的權限，請向管理員要求授予您 BigQuery 資料編輯者 (roles/bigquery.dataEditor) 的 IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

這個預先定義的角色包含 bigquery.tables.updateData 權限，這是使用 Storage Write API 所需的權限。

您或許還可透過自訂角色或其他預先定義的角色取得此權限。

如要進一步瞭解 BigQuery 中的 IAM 角色和權限，請參閱「IAM 簡介」。

事前準備

如要使用 BigQuery CDC，工作流程必須符合下列條件：

  • 您必須在預設串流中使用 Storage Write API。
  • 您必須使用 protobuf 格式做為擷取格式。不支援 Apache Arrow 格式。
  • 您必須為 BigQuery 中的目的地資料表宣告主鍵。系統支援最多包含 16 個欄的複合式主鍵。
  • 您必須有足夠的 BigQuery 運算資源，才能執行 CDC 資料列作業。請注意，如果 CDC 資料列修改作業失敗，您可能會不小心保留原本要刪除的資料。詳情請參閱已刪除資料的注意事項。

指定現有記錄的變更

在 BigQuery CDC 中，虛擬欄 _CHANGE_TYPE 會指出要針對每個資料列處理的變更類型。如要使用 CDC，請在使用 Storage Write API 串流傳送資料列修改內容時，設定 _CHANGE_TYPE。虛擬欄 _CHANGE_TYPE 只接受 UPSERT 和 DELETE 的值。當 Storage Write API 以這種方式將資料表的資料列修改內容串流傳輸時，資料表就會視為「已啟用 CDC」。

使用 UPSERT 和 DELETE 值的範例

請參考 BigQuery 中的下表：

ID 名稱 薪資
100 Charlie 2000
101 Tal 3000
102 Lee 5000

下列資料列修改項目會透過 Storage Write API 串流傳輸：

ID 名稱 薪資 _CHANGE_TYPE
100 刪除
101 Tal 8000 UPSERT
105 Izumi 6000 UPSERT

更新後的資料表如下所示：

ID 名稱 薪資
101 Tal 8000
102 Lee 5000
105 Izumi 6000

管理資料表的過時狀態

根據預設，每次執行查詢時，BigQuery 都會傳回最新的結果。為了在查詢支援 CDC 的資料表時提供最新結果，BigQuery 必須將每個串流資料列修改項目套用至查詢開始時間，以便查詢資料表的最新版本。在查詢執行期間套用這些資料列修改作業會增加查詢延遲時間和成本。不過，如果您不需要完全最新的查詢結果，可以為資料表設定 max_staleness 選項，藉此降低查詢費用和延遲時間。設定這個選項後，BigQuery 會在 max_staleness 值定義的間隔內至少套用一次資料列修改，讓您執行查詢時不必等待更新套用，但會導致部分資料失效。

這項行為特別適合用於資料新鮮度不重要的資訊主頁和報表。這也有助於控管費用，因為您可以更有效地控制 BigQuery 套用資料列修改作業的頻率。

使用 max_staleness 選項組合查詢資料表

當您查詢設定 max_staleness 選項的資料表時，BigQuery 會根據 max_staleness 的值和上次套用工作發生的時間 (以資料表的 upsert_stream_apply_watermark 時間戳記表示) 傳回結果。

請參考以下範例，其中資料表的 max_staleness 選項設為 10 分鐘，且最近的套用工作發生在 T20：

查詢執行時間會在資料過時的最大時間間隔內發生。

如果您在 T25 查詢資料表，則資料表的目前版本會延遲 5 分鐘，這小於 max_staleness 的 10 分鐘間隔。在這種情況下，BigQuery 會傳回 T20 的資料表版本，也就是說，傳回的資料也已過時 5 分鐘。

在表格上設定 max_staleness 選項後，BigQuery 會在 max_staleness 間隔內至少套用一次待處理的資料列修改作業。不過，在某些情況下，BigQuery 可能無法在指定時間內完成套用這些待處理資料列修改作業的程序。

舉例來說，如果您在 T35 查詢資料表，但未完成套用待處理的資料列修改作業，則目前的資料表版本會延遲 15 分鐘，超過 max_staleness 的 10 分鐘間隔。在這種情況下，BigQuery 會在查詢執行期間，為目前的查詢套用 T20 和 T35 之間的所有資料列修改，表示查詢資料會完全更新，但會產生額外的查詢延遲。這會視為執行階段合併工作。

查詢執行時間超出資料失效的最大時間間隔。

表格的 max_staleness 值通常應為下列兩個值中較高的值：

  • 工作流程可容許的資料過時程度上限。
  • 將更新/插入變更套用至資料表所需的時間上限的兩倍，再加上一些額外的緩衝時間。

如要計算將更新/插入變更套用至現有資料表所需的時間，請使用下列 SQL 查詢來判斷背景套用工作 95 百分位數的時間長度，再加上七分鐘的緩衝區，以便進行 BigQuery 寫入最佳化儲存空間 (串流緩衝區) 轉換。

SELECT
  project_id,
  destination_table.dataset_id,
  destination_table.table_id,
  APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)] AS p95_background_apply_duration_in_seconds,
  CEILING(APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)]*2/60)+7 AS recommended_max_staleness_with_buffer_in_minutes
FROM `region-REGION`.INFORMATION_SCHEMA.JOBS AS job
WHERE
  project_id = 'PROJECT_ID'
  AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
  AND job_id LIKE "%cdc_background%"
GROUP BY 1,2,3;

更改下列內容：

  • REGION：專案所在的區域名稱。例如：us。
  • PROJECT_ID：包含 BigQuery 資料表的專案 ID，這些資料表會由 BigQuery CDC 修改。

背景套用工作持續時間會受到多項因素影響，包括在失效間隔內發出的 CDC 作業數量和複雜度、資料表大小，以及 BigQuery 資源的可用性。如要進一步瞭解資源可用性，請參閱「調整及監控背景預留空間」。

使用 max_staleness 選項建立資料表

如要使用 max_staleness 選項建立資料表，請使用 CREATE TABLE 陳述式。以下範例會建立資料表 employees，其 max_staleness 限制為 10 分鐘：

CREATE TABLE employees (
  id INT64 PRIMARY KEY NOT ENFORCED,
  name STRING)
  CLUSTER BY
    id
  OPTIONS (
    max_staleness = INTERVAL 10 MINUTE);

修改現有資料表的 max_staleness 選項

如要在現有資料表中新增或修改 max_staleness 限制，請使用 ALTER TABLE 陳述式。以下範例會將 employees 資料表的 max_staleness 限制變更為 15 分鐘：

ALTER TABLE employees
SET OPTIONS (
  max_staleness = INTERVAL 15 MINUTE);

判斷表格的目前 max_staleness 值

如要判斷資料表目前的 max_staleness 值，請查詢 INFORMATION_SCHEMA.TABLE_OPTIONS 檢視畫面。以下範例會檢查資料表 mytable 的目前 max_staleness 值：

SELECT
  option_name,
  option_value
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
  option_name = 'max_staleness'
  AND table_name = 'TABLE_NAME';

更改下列內容：

  • DATASET_NAME：可用於 CDC 的資料表所在資料集名稱。
  • TABLE_NAME：已啟用 CDC 的資料表名稱。

結果顯示 max_staleness 值為 10 分鐘：

+---------------------+--------------+
| Row |  option_name  | option_value |
+---------------------+--------------+
|  1  | max_staleness | 0-0 0 0:10:0 |
+---------------------+--------------+

監控資料表上傳/插入作業進度

如要監控資料表的狀態，並查看上次套用列修改時間，請查詢 INFORMATION_SCHEMA.TABLES 檢視畫面，取得 upsert_stream_apply_watermark 時間戳記。

以下範例會檢查資料表 mytable 的 upsert_stream_apply_watermark 值：

SELECT upsert_stream_apply_watermark
FROM DATASET_NAME.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'TABLE_NAME';

更改下列內容：

  • DATASET_NAME：可用於 CDC 的資料表所在資料集名稱。
  • TABLE_NAME：已啟用 CDC 的資料表名稱。

結果大致如下：

[{
 "upsert_stream_apply_watermark": "2022-09-15T04:17:19.909Z"
}]

[email protected] 服務帳戶會執行 Upsert 作業，並顯示在含有 CDC 支援表格的專案工作記錄中。

管理自訂排序

將更新/插入串流傳送至 BigQuery 時，系統會根據記錄在 BigQuery 中擷取的 BigQuery 系統時間，決定以相同主鍵排序記錄的預設行為。換句話說，最近以最新時間戳記攝入的記錄，優先於先前以較舊時間戳記攝入的記錄。對於某些用途 (例如在極短的時間內，同一個主鍵可能會經常發生更新/插入作業，或是無法保證更新/插入作業的順序)，這可能就不足以應付。在這些情況下，可能需要使用者提供的排序鍵。

如要設定使用者提供的排序鍵，請使用虛擬欄 _CHANGE_SEQUENCE_NUMBER 來表示 BigQuery 應套用的記錄順序，依據的是具有相同主要鍵的兩個相符記錄之間較大的 _CHANGE_SEQUENCE_NUMBER。虛擬欄 _CHANGE_SEQUENCE_NUMBER 是選填欄，只接受固定格式 STRING 的值。

_CHANGE_SEQUENCE_NUMBER 格式

虛擬欄 _CHANGE_SEQUENCE_NUMBER 只接受以固定格式編寫的 STRING 值。這個固定格式使用以十六進制編寫的 STRING 值，並以正斜線 / 分隔各個部分。每個部分最多可使用 16 個十六進位字元表示，每個 _CHANGE_SEQUENCE_NUMBER 最多可包含四個部分。_CHANGE_SEQUENCE_NUMBER 的允許範圍支援 0/0/0/0 和 FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF 之間的值。_CHANGE_SEQUENCE_NUMBER 值支援大小寫字元。

您可以使用單一區段來表示基本排序鍵。舉例來說，如果您想只根據應用程式伺服器中的記錄處理時間戳記排序鍵，可以使用一個區段：'2024-04-30 11:19:44 UTC'，將時間戳記轉換為 Epoch 的毫秒數 (在本例中為 '18F2EBB6480')，以十六進位表示。用戶端使用 Storage Write API 向 BigQuery 發出寫入作業時，必須負責將資料轉換為十六進位。

支援多個部分可讓您將多個處理邏輯值組合成一個鍵，以便處理更複雜的用途。舉例來說，如果您想根據應用程式伺服器的記錄處理時間戳記、記錄序號和記錄狀態排序鍵，可以使用三個部分：'2024-04-30 11:19:44 UTC' / '123' / 'complete'，每個部分都以十六進制表示。在決定處理邏輯的優先順序時，請務必考量各個部分的順序。BigQuery 會比較 _CHANGE_SEQUENCE_NUMBER 值，方法是比較第一個區段，然後只在前一個區段相等的情況下比較下一個區段。

BigQuery 會使用 _CHANGE_SEQUENCE_NUMBER 比較兩個或多個 _CHANGE_SEQUENCE_NUMBER 欄位 (以無號數值) 來執行排序作業。

請參考下列 _CHANGE_SEQUENCE_NUMBER 比較範例，以及其優先順序結果：

  • 範例 1：

    • 記錄 #1：_CHANGE_SEQUENCE_NUMBER = '77'
    • 記錄 #2：_CHANGE_SEQUENCE_NUMBER = '7B'

    結果：系統會將記錄 2 視為最新記錄，因為 '7B' > '77' (即 '123' > '119')

  • 範例 2：

    • 記錄 #1：_CHANGE_SEQUENCE_NUMBER = 'FFF/B'
    • 記錄 #2：_CHANGE_SEQUENCE_NUMBER = 'FFF/ABC'

    結果：系統會將記錄 2 視為最新記錄，因為「FFF/ABC」>「FFF/B」(即「4095/2748」>「4095/11」)

  • 範例 3：

    • 記錄 #1：_CHANGE_SEQUENCE_NUMBER = 'BA/FFFFFFFF'
    • 記錄 2：_CHANGE_SEQUENCE_NUMBER = 'ABC'

    結果：系統會將記錄 2 視為最新記錄，因為「ABC」>「BA/FFFFFFFF」(即「2748」>「186/4294967295」)

  • 示例 4：

    • 記錄 #1：_CHANGE_SEQUENCE_NUMBER = 'FFF/ABC'
    • 記錄 2：_CHANGE_SEQUENCE_NUMBER = 'ABC'

    結果：系統會將記錄 1 視為最新記錄，因為「FFF/ABC」>「ABC」(即「4095/2748」>「2748」)

如果兩個 _CHANGE_SEQUENCE_NUMBER 值相同，則具有最新 BigQuery 系統攝入時間的記錄優先於先前攝入的記錄。

在表格中使用自訂排序時，請務必提供 _CHANGE_SEQUENCE_NUMBER 值。任何未指定 _CHANGE_SEQUENCE_NUMBER 值的寫入要求，都會導致資料列混合，其中包含有 _CHANGE_SEQUENCE_NUMBER 值和沒有 _CHANGE_SEQUENCE_NUMBER 值的資料列，導致排序無法預測。

設定 BigQuery 預留項目，以便與 CDC 搭配使用

您可以使用 BigQuery 預留項目，為 CDC 資料列修改作業分配專用的 BigQuery 運算資源。您可以使用預留項目，設定執行這些作業的費用上限。對於經常針對大型資料表執行 CDC 作業的工作流程而言，這種方法特別實用，因為在執行每項作業時，系統會處理大量位元組，因此會產生高額的隨選費用。

在 max_staleness 間隔內套用待處理的資料列修改作業的 BigQuery CDC 工作，會視為背景工作，並使用 BACKGROUND 指派類型，而非 QUERY 指派類型。相反地，如果查詢不在 max_staleness 間隔範圍內，且需要在查詢執行時套用資料列修改，則會使用 QUERY 指派類型。沒有 max_staleness 設定的資料表，或 max_staleness 設為 0 的資料表，也會使用 QUERY 指派類型。未指派 BACKGROUND 的 BigQuery CDC 背景工作會以以量計價為準。在設計 BigQuery CDC 的工作負載管理策略時，請務必考量這項因素。

如要設定 BigQuery 保留項目以供 CDC 使用，請先在 BigQuery 資料表所在的區域設定保留項目。如需保留空間大小的相關指南，請參閱「調整及監控 BACKGROUND 保留空間」一文。建立保留項目後，請指派 BigQuery 專案給保留項目，並執行下列 CREATE ASSIGNMENT 陳述式，將 job_type 選項設為 BACKGROUND：

CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-REGION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

更改下列內容：

  • ADMIN_PROJECT_ID：擁有預留容量的管理專案 ID。
  • REGION：專案所在的區域名稱。例如：us。
  • RESERVATION_NAME：保留項目的名稱。
  • ASSIGNMENT_ID：作業的 ID。ID 必須是專案和位置的專屬 ID，開頭和結尾必須是小寫英文字母或數字，且只能包含小寫英文字母、數字和破折號。
  • PROJECT_ID：包含 BigQuery 表格 (由 BigQuery CDC 修改) 的專案 ID。這項專案會指派給預留項目。

評估及監控 BACKGROUND 預留項目

預留項目會決定可用於執行 BigQuery 運算作業的運算資源數量。如果預留空間不足，CDC 資料列修改作業的處理時間可能會增加。如要準確設定預留空間大小，請查詢 INFORMATION_SCHEMA.JOBS_TIMELINE 檢視畫面，監控執行 CDC 作業的專案歷來版位用量：

SELECT
  period_start,
  SUM(period_slot_ms) / (1000 * 60) AS slots_used
FROM
  region-REGION.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE
  DATE(job_creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND CURRENT_DATE()
  AND job_id LIKE '%cdc_background%'
GROUP BY
  period_start
ORDER BY
  period_start DESC;

將 REGION 替換為專案所在的區域名稱。例如：us。

已刪除資料的注意事項

  • BigQuery CDC 作業會使用 BigQuery 運算資源。如果 CDC 作業已設定為使用隨選計費，系統會定期使用內部 BigQuery 資源執行 CDC 作業。如果 CDC 作業是使用 BACKGROUND 預留作業進行設定，則 CDC 作業會受到所設定預留作業的資源可用性影響。如果在已設定的預留空間中沒有足夠的資源，處理 CDC 作業 (包括刪除作業) 可能會比預期時間長。
  • 只有在 upsert_stream_apply_watermark 時間戳記超過 Storage Write API 串流作業的時間戳記時，系統才會套用 CDC DELETE 作業。如要進一步瞭解 upsert_stream_apply_watermark 時間戳記，請參閱「監控資料表更新/插入作業進度」。
  • 為了套用不按順序到達的 CDC DELETE 作業，BigQuery 會保留兩天的刪除保留期間。在標準Google Cloud 資料刪除程序開始之前，這段期間會儲存資料表 DELETE 作業。在刪除保留期間內執行的 DELETE 作業，會採用標準的 BigQuery 儲存空間定價。

限制

  • BigQuery CDC 不會執行鍵強制執行，因此主鍵必須是唯一的。
  • 主索引鍵不得超過 16 個資料欄。
  • 啟用 CDC 的資料表不得有超過 2,000 個由資料表結構定義的頂層欄。
  • 啟用 CDC 的資料表不支援下列功能：
  • 資料表的 max_staleness 值過低，因此執行資料表合併作業的 CDC 啟用資料表無法支援下列作業：
  • 針對啟用 CDC 的資料表執行 BigQuery 匯出作業時，系統不會匯出最近透過串流修改的資料列，因為背景工作尚未套用這些修改。如要匯出完整的資料表，請使用 EXPORT DATA 陳述式。
  • 如果查詢會在分區資料表上觸發執行階段合併作業，則會掃描整個資料表，無論查詢是否限制在分區的子集內皆是如此。
  • 如果您使用標準版，就無法使用 BACKGROUND 保留功能，因此套用待處理的資料列修改作業時，系統會使用以量計價的定價模式。不過，無論您使用哪個版本，都可以查詢支援 CDC 的資料表。
  • 執行資料表讀取作業時，虛擬資料欄 _CHANGE_TYPE 和 _CHANGE_SEQUENCE_NUMBER 無法進行查詢。
  • 系統不支援在同一個連線中，將 _CHANGE_TYPE 的 UPSERT 或 DELETE 值列與 _CHANGE_TYPE 的 INSERT 或未指定值列混合，這會導致以下驗證錯誤：The given value is not a valid CHANGE_TYPE。

BigQuery CDC 定價

BigQuery CDC 會使用 Storage Write API 擷取資料、使用 BigQuery 儲存空間儲存資料，以及使用 BigQuery 運算作業資料列修改作業，這些都會產生費用。如需價格資訊，請參閱 BigQuery 定價。

估算 BigQuery CDC 費用

除了 一般 BigQuery 成本估算最佳做法，如果工作流程具有大量資料、低 max_staleness 設定或經常變更的資料，則估算 BigQuery CDC 的成本可能很重要。

BigQuery 資料攝入定價和 BigQuery 儲存空間定價，是直接根據您攝入及儲存的資料量 (包括擬造欄) 來計算。不過，由於 BigQuery 運算價格與用於執行 BigQuery CDC 工作的運算資源消耗量相關，因此較難估算。

BigQuery CDC 工作分為三類：

  • 背景套用工作：以資料表 max_staleness 值定義的規則間隔，在背景執行的工作。這些工作會將最近串流的資料列修改內容套用至啟用 CDC 的資料表。
  • 查詢工作：在 max_staleness 視窗中執行的 GoogleSQL 查詢，且只從 CDC 基準資料表讀取資料。
  • 執行階段合併工作：由在 max_staleness 時間窗口外執行的臨時 GoogleSQL 查詢觸發的工作。這些工作必須在查詢執行期間，針對 CDC 基準資料表和最近串流的資料列修改內容，執行即時合併作業。

只有查詢工作可利用 BigQuery 分割作業。背景套用工作和執行階段合併工作無法使用分割作業，因為在套用最近串流的資料列修改時，無法保證最近串流的更新/插入作業會套用至哪個資料表分割作業。換句話說，在背景套用工作和執行階段合併工作期間，系統會讀取完整基準資料表。基於相同原因，只有查詢工作才能從 BigQuery 叢集資料欄的篩選器中受益。瞭解執行 CDC 作業所需讀取的資料量，有助於估算總成本。

如果從資料表基準讀取的資料量偏高，請考慮使用 BigQuery 容量定價模式，因為這不是根據處理的資料量收費。

BigQuery CDC 費用最佳做法

除了一般 BigQuery 費用最佳做法之外，請使用下列技巧，以便最佳化 BigQuery CDC 作業的費用：

  • 除非必要，否則請避免將表格的 max_staleness 選項設為非常低的值。max_staleness 值可增加背景套用工作和執行階段合併工作發生的機率，這類工作比查詢工作費用更高且速度較慢。如需詳細指南，請參閱「建議的表格 max_staleness 值」。
  • 建議您設定 BigQuery 預留空間，以便搭配 CDC 資料表使用。否則，背景套用工作和執行階段合併工作會採用以量計價，這可能會因資料處理量增加而導致費用增加。如需更多詳細資訊，請參閱「BigQuery 預留項目」，並按照這篇文章中的指示，瞭解如何為 BigQuery CDC 使用 BACKGROUND 預留項目的大小和監控。

後續步驟