Google PDE: BigQuery 分析與倉儲工程 — 學習指南

By Laurynas Brazys · 21 August 2026 · #google #PDE #certification #exam-prep #study-guide

屬於 Google Professional Data Engineer — 學習指南. 使用經過驗證的解答練習： Google 考試中心, 或參加限時模擬考試： ExamRoll.io.

總覽

BigQuery 是一個無伺服器、以欄為基礎 (columnar) 的大規模平行處理 (MPP) 分析倉儲，它將儲存與運算分離，提供近乎無限的擴展性、ANSI SQL 及整合式治理。在 BigQuery 上進行倉儲工程，需要在結構設計（分區、分群、反正規化與正規化、巢狀記錄）、擷取模式（批次載入、串流、Storage Write API）以及工作負載管理（隨選與容量型版本及預留）之間取得平衡。強大的安全性（授權檢視、資料列/資料欄層級政策、政策標籤）與成本控制及效能工具並存，以最小化掃描的位元組數並降低延遲。本節涵蓋了您在生產環境中必須預期的核心設計、操作與故障模式。

儲存與語意：資料集、資料表、檢視與資料湖存取

• 資料集、資料表、檢視：

  • 資料集界定 IAM 與治理的範圍。為每個租戶保留專屬的資料集，以達到隔離與帳務清晰的目的。
  • 標準資料表以原生方式儲存資料；分區與分群決定了資料的佈局與掃描篩減 (pruning)。
  • 檢視封裝了 SQL 邏輯，但本身不儲存資料。授權檢視允許檢視的擁有者向其他專案或租戶揭露受限的資料子集，同時隱藏底層的資料表。
  • 具體化檢視 (Materialized views, MVs) 會持久化預先計算的結果並自動重新整理。當查詢相容時，查詢重寫 (Query rewrite) 會透明地使用 MVs；不相容的述詞或函式則會繞過它們。
  • 外部資料表參照儲存在 Cloud Storage、Google Drive 或 Google Sheets 中的資料。它們避免了資料擷取，但犧牲了吞吐量與函式支援以換取便利性。對於重複性的分析，應將資料擷取至原生資料表。

• 分區、分群與巢狀記錄：

  • 依擷取時間、DATE/TIMESTAMP/DATETIME 或整數範圍進行分區，以篩減掃描範圍。在分區資料欄上使用 WHERE 篩選條件，或使用像 _PARTITIONDATE 這樣的修飾符來啟用篩減。
  • 針對高基數、經常被篩選或串接的資料欄（最多 8 個）進行分群。BigQuery 會自動重新分群；重複的小型 DML 操作可能會暫時降低分群品質。
  • 巢狀與重複記錄 (STRUCT, ARRAY) 可模擬一對多關係，而無需串接 (join) 的額外負擔。請審慎使用 UNNEST；在大型陣列上重複使用 UNNEST 可能會導致資料量急遽擴散。

• 反正規化與正規化：

  • 將維度屬性反正規化至事實資料表中，以最小化串接並利用欄位式掃描的優勢；這對於讀取密集型的分析是理想的選擇。
  • 當寫入放大、更新熱點或自我串接 (self-joins) 造成競爭或複雜性時，應進行正規化（例如，將主要病患資料表與就診資料表分開，以避免自我串接造成的資料爆炸）。可考慮採用混合模式：正規化的核心實體，搭配寬的、反正規化的事實資料表或巢狀子記錄。

• 具體化檢視：設計考量

  • 最適合用於對分區的基礎資料表進行穩定、可增量彙總的場景。MV 的重新整理是非同步的；下游使用者應能容忍資料過時的空窗期，或將查詢基礎資料表作為備援方案。
  • 依分區資料欄進行篩選與分組，以實現增量重新整理。非確定性函式、不支援的串接類型或 UDFs 可能會使 MVs 不符合查詢重寫的資格。

• 聯合查詢、BigLake 與下推 (pushdown)：

  • 聯合查詢能直接用 SQL 讀取外部系統（例如 Cloud SQL）的資料。它們對於輕量級的串接或一次性的探索很方便，但延遲較高且配額更嚴格；若要進行繁重的分析，應將資料提取至 BigQuery。
  • BigLake 資料表透過對 Cloud Storage 或開放資料表格式（如 Parquet）中的資料進行資料欄與資料列層級的控管，統一了資料湖與資料倉儲的治理。述詞與投影下推 (Predicate and projection pushdown) 可減少下載的位元組數；但大規模掃描仍偏好將資料擷取至原生資料表以獲得最佳效能。

• 萬用字元資料表與舊式分片：

  • 萬用字元查詢是針對依日期分片 (date-sharded) 的資料表的一種舊有模式。應優先選用原生分區，但若有需要時可使用：SELECT … FROM bigquery-public-data.noaa_gsod.gsod* WHERE _TABLE_SUFFIX >= ‘2010’。

查詢最佳化與工作負載管理

• 分割區修剪與叢集：

  • 務必在分割區欄位上進行篩選，以避免掃描冷分割區。使用 BETWEEN 搭配窄範圍的視窗。
  • 依選擇性排序叢集鍵；前面的鍵應對應到頻繁的篩選和 join。避免在基數非常低的欄位上進行叢集。

• 查詢計畫最佳化：

  • 使用 EXPLAIN 和執行詳細資訊來找出傾斜的 join、大規模的 shuffle 或未修剪的掃描。
  • 使用 SELECT 清單和子查詢提早減少欄位；BigQuery 是欄式資料庫，能有效率地捨棄未使用的欄位。
  • 對於大型資料，優先使用近似彙總（例如 APPROX_QUANTILES）以在速度和成本之間取得平衡。

  • 當擷取來源可能重複事件時，使用視窗函式進行重複資料刪除：

undefined

• Join 與反正規化：

  • 將 join 鍵與叢集鍵共置以減少 shuffle。對於極端傾斜的情況，布隆過濾器或預先彙總會有所幫助。
  • 將小型的、緩慢變化的維度反正規化至事實表中，以避免熱點 join。對於經常更新的超寬維度，則進行正規化並依賴叢集鍵和具體化 join。

• 工作負載管理、slot、版本與自動擴展：

  • 隨選：BigQuery 彈性地為每個查詢擴展運算資源；您按掃描的 TB 數付費。透過設定計費的最大位元組數和分割區修剪來控制成本。
  • 以容量為基礎，搭配 BigQuery 版本（Standard、Enterprise、Enterprise Plus）使用 slot 預留。購買基準承諾、建立預留，並指派專案或資料夾。自動擴展可以在高峰期增加 slot，並在需求下降時釋放；為 ETL 和 BI 使用不同的預留，以防止干擾。
  • 工作優先級：互動式（預設）用於低延遲；批次式用於回填和排程查詢。批次工作會排入佇列，直到預留或服務中有閒置容量可用時，再以正常成本執行。

• 並行與配額：

  • 使用預留和指派來隔離關鍵工作負載。對於混合租戶，將它們放在具有客製化並行限制的不同預留或專案中。
  • 為工作加上標籤以利歸因；監控 INFORMATION_SCHEMA.JOBS 和 Cloud Monitoring 指標，以了解 slot 使用率和佇列延遲。

• BI 快取與資料新鮮度：

  • 查詢結果快取能改善相同查詢的延遲/成本；對於需要小時內新鮮度的用戶端，請停用此功能。有些 BI 工具會獨立快取資料——停用報表快取以顯示最新結果。

擷取、聯合查詢與復原

• 載入工作：

  • 從 Cloud Storage 進行批次載入（偏好 Avro/Parquet）是可靠且具成本效益的方式。明確設定 schema 和編碼；CSV 編碼不符是造成逐位元組差異的常見原因。
  • 使用分割區裝飾器或載入到分割資料表以避免合併。對於大型載入，按分割區進行平行處理。

• 串流擷取與 Storage Write API：

  • 舊版的串流插入雖然簡單，但有更嚴格的配額，且可能在幾秒鐘內呈現最終一致性；對非常近期資料的時間旅行可能會延遲。
  • Storage Write API 是建議用於高吞吐量、低延遲寫入且具備更佳重複資料刪除控制的途徑。使用冪等性（串流偏移量）來防止重複資料。
  • 應用程式設計應能容忍傳輸中的事件：將互動式查詢延遲預期的可用時間（例如，觀測到的延遲的 2 倍），或在擷取時間分割區上使用浮水印。

• Dataflow 與無效信件設計：

  • 對於合作夥伴提供且包含格式錯誤資料列的 CSV，使用 Dataflow 進行解析和驗證，將有效的記錄透過 Storage Write API 寫入 BigQuery，並將錯誤路由到無效信件資料表以供檢查。
  • 當大規模讀取 BigQuery 時，偏好使用基於查詢的讀取（fromQuery），以僅選擇必要的欄位並減少 shuffle。

• 排程查詢與轉換：

  • 使用排程查詢進行 ELT 轉換、增量匯總和資料表維護。偏好寫入分割且叢集化的目標。排程查詢預設為批次優先級，並與預留整合。

• 通知與可觀測性：

  • 使用日誌接收器將 BigQuery 稽核日誌匯出到 Pub/Sub，以觸發特定資料表插入工作的警示：
    • 篩選器範例：

undefined

• 使用 Cloud Logging 稽核日誌和 INFORMATION_SCHEMA 檢視來發現使用模式並強制執行治理。

• 時間旅行、快照與複本：

  • 時間旅行讓您可以在過去的某個時間點（預設 7 天）查詢資料表。使用 FOR SYSTEM_TIME AS OF 來讀取過去的狀態。
  • 資料表快照透過寫入時複製（copy-on-write）來擷取某個時間點的檢視；用於一致性的回填或復原。資料表複本提供近乎即時的中繼資料副本，用於開發或假設分析，在分歧前僅需最少儲存空間。
  • 復原選項：
    • 小錯誤：使用時間旅行查詢並透過 INSERT...SELECT 來還原。
    • 大規模還原：從快照或複本建立，然後交換。
  • 設定資料表和分割區過期時間以強制執行保留政策；驗證保留政策是否與時間旅行的需求一致。

• 聯合查詢來源：

  • 對於輕量級的 join，使用 Cloud SQL 聯合查詢；對於持續性的分析或大規模掃描，排程擷取-載入至原生資料表。
  • 建立在 Cloud Storage 中 Parquet/ORC 上的 BigLake 資料表可以強制執行政策標籤並下推篩選器與欄位投影；但延遲仍預期會比原生儲存高。

安全性、治理與成本控管

• IAM 與最低權限原則：

  • 僅將資料集層級的角色（如 BigQuery Data Viewer、Data Editor）以最低權限原則授予核准的使用者；將 API 存取權限限制於服務帳號和受管理的群組。
  • 透過資料集和專案來隔離客戶和環境。按專案或資料夾分配 reservations，以防止「吵雜的鄰居」（noisy neighbors）問題。

• 授權檢視與資料列層級安全性：

  • 授權檢視僅對外部專案公開選定的欄/列，而檢視所在的專案則保留對資料表的存取權。將檢視與來源資料表放在同一個資料集中，或使用資料集層級的授權給目標專案。
  • 資料列層級安全性透過資料列存取政策，在查詢時根據使用者或群組過濾資料列；可與授權檢視結合以實現分層控制。

• 資料欄層級安全性與政策標籤：

  • 使用 Data Catalog 政策標籤來保護敏感資料欄並啟用資料遮罩。將存取權限分配給標籤（而非資料表），以符合資料分類。對於合作夥伴的存取，可透過標籤遮罩或拒絕 PII（個人可識別資訊）資料欄。

• BigQuery ML 與倉儲內分析：

  • 直接在 BigQuery 中訓練和提供模型（例如，線性/邏輯迴歸、XGBoost、K-means、時間序列），使用 CREATE MODEL 和 ML.PREDICT。將特徵儲存在分割資料表中，並使用排程進行重新訓練。
  • 遠端模型讓您能從 SQL 中呼叫 Vertex AI 或外部端點，在聯合治理的框架內進行評分。將輸出快取到資料表中，以分攤重複查詢的延遲。

• 成本控制與效能疑難排解：

  • 減少掃描的位元組數：
    • 進行分割與叢集化；務必根據這些鍵值進行篩選。
    • 只 SELECT 需要的資料欄；避免使用 SELECT *。
    • 在適用情況下使用具體化檢視和查詢結果快取。
    • 設定 maximum_bytes_billed 來限制成本上限。
  • 效能疑難排解：
    • 檢查工作執行詳細資訊，找出資料傾斜、未被修剪的分割區或 shuffle 熱點。重新設計 join 的鍵值或進行預先彙總以減少 shuffle。
    • 驗證 MV 的重寫；確保述詞（predicate）相容且函式具確定性。
    • 對於缺少最新串流資料的儀表板，需考慮到一致性延遲或停用用戶端快取。
  • 治理可見性：使用 Cloud Logging、INFORMATION_SCHEMA 和細緻的工作標籤進行稽核，以便進行成本分攤和識別異常值。

實務問題情境

NovaCare Health 經營一個區域性的遠距醫療平台。最初的單一資料表 patient_and_visit 設計支援了試點計畫，但在規模擴大 100 倍後，報告開始逾時，串流 upsert 操作出現重複資料，且合作夥伴要求嚴格的資料隔離。

解決方法：

1. 重新設計結構描述與佈局

   • 建立正規化的核心資料表：patients(patient_id, demographics, updated_at) 和 visits(visit_id, patient_id, visit_ts, metrics, updated_at)。
   • 將 visits 設為以 DATE(visit_ts) 分割的資料表，並以 patient_id 和 visit_id 進行叢集化。將小型的參考維度反正規化到 visits 表中，以加快儀表板速度。
   • 理由：正規化可避免在單一熱點資料表上進行昂貴的自我 join 和大量的資料列更新。分割可修剪歷史資料的掃描；叢集化則將 patient_id 上的 join 和篩選操作集中存放，減少 shuffle。

2. 使用 Storage Write API 擷取資料並強制執行冪等性

   • 使用 Dataflow pipeline 來解析傳入的事件、進行驗證，並使用具冪等性的偏移量（offset）寫入 Storage Write API 中的具名串流。
   • 將格式錯誤的事件路由到一個 dead-letter BigQuery 資料表以進行分類處理。
   • 理由：相較於傳統的串流方式，Storage Write API 提供更高的吞吐量、更低的延遲和更好的重複資料刪除保證。Dead-lettering 機制保留了對合作夥伴資料品質問題的可見性。

3. 在查詢中設計以確保資料新鮮度與重複資料刪除

   • 對於互動式分析，在查詢最新分割區之前，加入一個短的浮水印（例如，觀察到的可用性時間的 2 倍）；或使用 _PARTITIONDATE 篩選 partition_date <= CURRENT_DATE() 以排除正在傳輸中的資料列。
   • 在需要容忍上游重試的檢視中，使用 ROW_NUMBER() OVER (PARTITION BY visit_id ORDER BY event_ts DESC) = 1。
   • 理由：串流在短時間內是最終一致的。浮水印和基於視窗的重複資料刪除可保護儀表板免受暫時性資料缺口和重複資料的影響。

4. 使用具體化檢視加速常見的彙總操作

   • 在 visits 資料表上建立與分割區對齊的 MV，用於按 DATE(visit_ts) 和病患群組分類的每日 KPI。確保述詞與重寫相容。
   • 理由：MV 減少了重複性報告的延遲和掃描位元組數；BigQuery 會自動重寫查詢以透明地使用 MV。

5. 強制執行租戶隔離與細緻的安全性控制

   • 將每個合作夥伴放置在專屬的資料集中。將最低權限的資料集角色授予合作夥伴群組。
   • 發布授權檢視以進行共享的、跨合作夥伴的基準比較，而不洩露原始資料表。
   • 對 PII 資料欄應用政策標籤，並在 visits 資料表上新增資料列存取政策，以根據 partner_id 限制內部多租戶分析的存取。
   • 理由：每個租戶一個資料集的區隔方式，加上授權檢視和政策標籤，可在啟用受管理的共享同時，強制執行最低權限原則。

6. 使用版本、預留和自動擴展來管理工作負載

   • 購買 BigQuery 版本的容量，並建立兩個 reservations：etl（用於 Dataflow sinks、排程轉換）和 bi（用於 ad hoc/報表）。相應地分配專案，並啟用自動擴展以應對尖峰負載。
   • 將 ELT 查詢排程為具有明確 SLA 的批次作業；為互動式專案設定 maximum_bytes_billed。
   • 理由：分開的 reservations 可防止 ETL 佔用 BI 的資源。自動擴展可在不超額配置的情況下應對突發性負載。

7. 治理成本並觀察使用情況

   • 在共享檢視中要求對 visit_ts 進行篩選；拒絕 SELECT *。使用 INFORMATION_SCHEMA.JOBS 來偵測未被修剪的掃描和傾斜的 join。
   • 將 BigQuery 稽核日誌匯出到 Pub/Sub，並使用日誌接收器篩選 visits 上的 insert jobs，以觸發對意外流量激增的監控警報。
   • 理由：位元組修剪和資料欄投影可控制成本；稽核日誌可近乎即時地呈現存取模式和異常情況。

8. 規劃復原與資料回填

   • 啟用符合合規性和時間旅行需求的預設資料表過期政策。對於大型編輯，可建立快照，執行變更，並在必要時快速回復。使用資料表複本進行開發/測試的假設分析，而無需複製儲存空間。
   • 理由：快照和複本提供了快速、節省空間的安全網；時間旅行則涵蓋了小規模的修正性還原。

9. 整合倉儲內機器學習

   • 將工程化的特徵儲存在分割資料表中，並訓練 BigQuery ML 分類模型以預測再入院風險。對於託管在 Vertex AI 上的外部模型，可建立遠端模型並將預測結果快取在叢集化資料表中，以實現低延遲的 join。
   • 理由：將 ML 靠近資料可減少資料移動和治理的複雜性；快取遠端推論的結果可分攤延遲和成本。

透過此設計，NovaCare 在 100 倍負載下實現了可預測的效能、強大的租戶隔離和受控的成本，同時保留了低延遲分析和可重現的復原能力。

← 資料儲存、資料湖與檔案格式 · 所有領域 · 使用 Dataflow 與 Apache Beam 進行串流處理 →

練習這些題目 → · 在 ExamRoll.io 上限時練習 →

瀏覽 Google →