ACE-211：BigQuery 資料倉儲實戰——PB 級分析完全指南

前言

BigQuery 大概是 GCP 最有代表性的服務之一。它是一個無伺服器資料倉儲，你用 SQL 就能在幾秒內分析 PB 級資料，完全不用自己管基礎設施。

這篇是 ACE 進階系列第 6 課，我們會從 BigQuery 架構談起，把分區、叢集、定價最佳化一路講清楚，順便整理 ACE 考試裡選型題會考的重點。

BigQuery 架構

資源階層

GCP Project
└── Dataset（資料集，相當於「資料庫」）
    ├── Table（資料表）
    ├── View（視圖）
    ├── Materialized View（物化視圖）
    └── External Table（外部表格，資料在 GCS/Bigtable/Drive）

儲存與運算分離

BigQuery 的核心設計是儲存（Storage）與運算（Compute）分離：

┌─────────────────────────────┐    ┌─────────────────────────────┐
│        Colossus             │    │           Dremel            │
│    （分散式儲存層）           │    │      （查詢引擎，Slots）       │
│  - 欄位式儲存（Capacitor）   │◄───│  - MPP（大規模平行處理）      │
│  - 自動壓縮與加密            │    │  - 按 Slot 分配運算資源       │
│  - 跨區域自動備援            │    │  - On-demand / Flat-rate    │
└─────────────────────────────┘    └─────────────────────────────┘

欄位式儲存（Columnar Storage）的優勢：

分析查詢通常只讀取少數幾欄
欄位連續存放 → 壓縮率更高（同型態資料）
SELECT revenue, date FROM sales → 只讀取 revenue 和 date 兩欄，不讀其他欄位

定價模式

免費層（每月，每個專案）

項目	免費額度
儲存	10 GB
查詢	1 TB

按需定價（On-Demand）

項目	費用
查詢費用	$6.25 / TB（每次查詢掃描量計費）
主動儲存	$0.02 / GB / 月
長期儲存（90 天未修改）	$0.01 / GB / 月（便宜 50%）

BigQuery Editions（2023 起）

BigQuery 從 2023 年起推出三個版本，以 Slot 為計費單位，適合穩定大量查詢的場景：

版本	Slot 費用	特色
Standard	較低	基本功能，適合一般分析
Enterprise	中	BI Engine、CMEK、99.99% SLA
Enterprise Plus	最高	跨區域備援、進階安全功能

選型建議：

隨機查詢、用量不穩定 → On-demand
固定分析工作負載、成本可預期 → Editions（Slot-based）

資料集與資料表

建立資料集與資料表

# 建立資料集（指定區域）
bq mk --dataset \
  --location=asia-east1 \
  --description="銷售分析資料集" \
  my-project:sales_analytics

# 使用 JSON Schema 建立資料表
bq mk --table \
  my-project:sales_analytics.orders \
  order_id:STRING,user_id:STRING,amount:FLOAT64,created_at:TIMESTAMP

# 查看資料表 Schema
bq show my-project:sales_analytics.orders

# 列出資料集內所有資料表
bq ls my-project:sales_analytics

資料表類型

類型	說明	適合場景
Native Table	資料儲存在 BigQuery（Capacitor）	主要分析資料
External Table	資料在 GCS、Bigtable、Drive	避免資料搬移，直接查詢來源
View	儲存的 SQL 查詢（每次執行）	資料抽象、存取控制
Materialized View	預先計算並快取結果	高頻、昂貴的聚合查詢

分區（Partitioning）與叢集（Clustering）

這是 BigQuery 效能和成本最佳化的核心，也是 ACE 考試最常考的主題。

分區（Partitioning）—— 減少掃描量

分區讓 BigQuery 只讀取符合條件的資料分區，直接減少費用：

-- 建立按日期分區的資料表
CREATE TABLE sales_analytics.orders_partitioned
PARTITION BY DATE(created_at)
AS SELECT * FROM sales_analytics.orders;

三種分區方式：

分區類型	說明	範例
按攝取時間（Ingestion Time）	自動用 `_PARTITIONTIME` 分區	日誌資料
按日期/時間欄位	指定 timestamp/date 欄位	`PARTITION BY DATE(created_at)`
按整數範圍	指定整數欄位的範圍	`PARTITION BY RANGE_BUCKET(user_id, ...)`

查詢時使用分區過濾：

-- 好：使用分區欄位過濾，只掃描 1 天的資料
SELECT order_id, amount
FROM sales_analytics.orders_partitioned
WHERE DATE(created_at) = '2026-03-11';

-- 壞：沒有分區過濾，掃描全部資料（全表掃描，費用高！）
SELECT order_id, amount
FROM sales_analytics.orders_partitioned
WHERE user_id = 'USER-123';

叢集（Clustering）—— 進一步縮小掃描範圍

叢集在分區內按指定欄位排序存放，讓 BigQuery 能跳過不相關的資料區塊：

-- 建立分區 + 叢集的資料表（最佳實踐）
CREATE TABLE sales_analytics.orders_optimized
PARTITION BY DATE(created_at)
CLUSTER BY user_id, product_category
AS SELECT * FROM sales_analytics.orders;

特性	說明
最多叢集欄位數	4 個
費用影響	叢集不影響儲存費用，但能顯著減少查詢掃描量
叢集欄位順序	第一個欄位效果最好，依次遞減

分區 vs 叢集比較：

特性	分區	叢集
減少費用	✅ 直接減少掃描資料量	✅ 進一步縮小掃描範圍
最適欄位	日期/時間欄位	高基數分類欄位（user_id, category）
查詢可見性	`--dry_run` 可看分區	費用只在執行後才知道
最佳實踐	先分區，再叢集	搭配分區使用

資料攝取（Data Ingestion）

批次載入（Batch Load）

# 從 GCS 載入 CSV
bq load \
  --source_format=CSV \
  --autodetect \
  my-project:sales_analytics.orders \
  gs://my-bucket/orders/*.csv

# 從 GCS 載入 Parquet（推薦格式，自帶 Schema）
bq load \
  --source_format=PARQUET \
  my-project:sales_analytics.orders \
  gs://my-bucket/orders/*.parquet

支援格式：CSV、JSON、Avro、Parquet、ORC

串流寫入（Streaming）

有兩種串流方式，2022 年後推薦使用 Storage Write API：

方式	說明	建議
Storage Write API	新版 API，低延遲、低成本，支援精確一次語義	✅ 推薦
Legacy Streaming Inserts	舊版，延遲較高，費用較高（$0.01/200MB）	舊系統相容用

Storage Write API 範例（Python）：

from google.cloud.bigquery_storage_v1 import BigQueryWriteClient
from google.cloud.bigquery_storage_v1.types import ProtoRows

# Storage Write API 支援三種模式：
# - Committed Stream（立即可查詢，精確一次）
# - Pending Stream（批次 commit）
# - Default Stream（與 legacy streaming 類似）

BigQuery Data Transfer Service

用於排程自動從外部來源載入資料：

# 從 Cloud Storage 建立排程資料傳輸
bq mk \
  --transfer_config \
  --target_dataset=sales_analytics \
  --display_name="Daily Orders Import" \
  --data_source=google_cloud_storage \
  --params='{"data_path_template":"gs://my-bucket/orders/*.csv","destination_table_name_template":"orders_{run_time|\"%Y%m%d\"}"}'

支援來源：Google Analytics、Google Ads、Salesforce、YouTube、Amazon S3 等。

查詢最佳實踐

BigQuery SQL 完整支援

-- 視窗函數（Window Functions）
SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total,
  RANK() OVER (PARTITION BY DATE_TRUNC(order_date, MONTH) ORDER BY amount DESC) AS rank_in_month
FROM sales_analytics.orders;

-- MERGE（UPSERT 語法）
MERGE sales_analytics.customers AS target
USING (SELECT * FROM sales_analytics.new_customers) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
  UPDATE SET target.email = source.email
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, name) VALUES (source.customer_id, source.email, source.name);

-- ARRAY 和 STRUCT
SELECT
  order_id,
  ARRAY_AGG(STRUCT(product_id, quantity)) AS items
FROM order_items
GROUP BY order_id;

費用節省技巧

-- ❌ 壞：SELECT * 掃描所有欄位
SELECT * FROM sales_analytics.orders_partitioned;

-- ✅ 好：只選需要的欄位
SELECT order_id, amount, user_id
FROM sales_analytics.orders_partitioned
WHERE DATE(created_at) = '2026-03-11';

-- 查詢前估算費用（Dry Run）

# Dry run 估算掃描量（不實際執行，不收費）
bq query \
  --use_legacy_sql=false \
  --dry_run \
  'SELECT order_id FROM sales.orders WHERE DATE(created_at) = "2026-03-11"'

存取控制

IAM 角色

角色	權限
`roles/bigquery.admin`	完整管理權限
`roles/bigquery.dataOwner`	建立/修改/刪除資料集與資料表
`roles/bigquery.dataEditor`	讀取 + 寫入資料
`roles/bigquery.dataViewer`	只讀資料
`roles/bigquery.jobUser`	只能執行查詢（無法看資料）
`roles/bigquery.user`	dataViewer + jobUser（常用組合）

欄位層級安全（Column-Level Security）

使用 Policy Tags 保護敏感欄位（如 PII）：

-- 在建立資料表時指定 Policy Tag
CREATE TABLE sales_analytics.users (
  user_id STRING,
  name STRING,
  email STRING OPTIONS(description="PII",
    policy_tags=["projects/my-project/locations/us/taxonomies/123/policyTags/456"]),
  phone STRING OPTIONS(policy_tags=["projects/my-project/..."])
);

沒有被授予 roles/datacatalog.categoryFineGrainedReader 的使用者，查詢結果中對應欄位會被隱藏。

資料列層級安全（Row-Level Security）

-- 建立資料列存取政策（只讓 APAC 團隊看亞洲訂單）
CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON sales_analytics.orders
GRANT TO ("group:apac-team@company.com")
FILTER USING (region = 'APAC');

重要功能速覽

物化視圖（Materialized View）

-- 自動維護的預計算結果
CREATE MATERIALIZED VIEW sales_analytics.daily_revenue
AS
SELECT
  DATE(created_at) AS date,
  SUM(amount) AS total_revenue,
  COUNT(*) AS order_count
FROM sales_analytics.orders
GROUP BY DATE(created_at);

物化視圖會在來源資料更新時自動刷新，查詢時直接讀快取結果，大幅降低昂貴聚合查詢的費用。

BigQuery Omni

BigQuery Omni 讓你直接用 BigQuery SQL 查詢存在 AWS S3 和 Azure Blob Storage 的資料，不需要搬移資料：

-- 查詢 AWS S3 的資料（透過 BigQuery Omni）
SELECT * FROM `my-project.aws-dataset.s3_table`
WHERE order_date >= '2026-01-01';

排程查詢（Scheduled Queries）

# 每天凌晨 2 點執行彙整查詢
bq query \
  --use_legacy_sql=false \
  --schedule='every 24 hours' \
  --destination_table=sales_analytics.daily_summary \
  'SELECT DATE(created_at) as date, SUM(amount) FROM sales_analytics.orders GROUP BY 1'

BigQuery vs 其他 GCP 資料庫選型

需求場景	選擇	理由
批次分析、商業智慧報表	BigQuery	PB 級、無伺服器、SQL
傳統 OLTP（訂單、用戶）	Cloud SQL	關聯式、ACID、< 100 GB
全球一致性 OLTP	Cloud Spanner	全球分散式、水平擴展
時序資料、IoT（低延遲）	Bigtable	毫秒延遲、百萬 QPS
行動/Web 即時資料	Firestore	即時同步、彈性 Schema
高效能 PostgreSQL	AlloyDB	PostgreSQL 相容，4x 快

選型決策樹：

是否需要 SQL？
├─ 是 → 分析（OLAP）還是交易（OLTP）？
│        ├─ 分析 → BigQuery ✅
│        └─ 交易 → 需要全球 / 大量擴展？
│                   ├─ 是 → Cloud Spanner
│                   └─ 否 → Cloud SQL / AlloyDB
└─ 否 → 時序/IoT → Bigtable；文件 → Firestore

ACE 考試重點整理

必背知識點

免費層：每月 1 TB 查詢 + 10 GB 儲存
按需定價：$6.25 / TB 掃描量
分區優先，再叢集：減少費用最有效的方法
叢集最多 4 個欄位
長期儲存：90 天未修改 → 費用降低 50%
Storage Write API 取代舊 Streaming Inserts（精確一次、低成本）
Materialized View 自動刷新，快取昂貴聚合結果
Policy Tags 實現欄位層級安全

常見陷阱題

Q：BigQuery 適合替代 Cloud SQL 的交易資料庫嗎？ A：❌ 不適合。BigQuery 是 OLAP（分析），Cloud SQL 是 OLTP（交易）。BigQuery DML（UPDATE/DELETE）效能差，不適合高頻單行操作。

Q：如何讓 BigQuery 查詢只掃描最近 7 天的資料？ A：建立按日期分區的資料表，查詢時在 WHERE 子句加上分區欄位過濾條件。

Q：分區和叢集可以同時使用嗎？ A：✅ 可以，而且是最佳實踐。先按日期分區，再按常用過濾欄位叢集。

Q：SELECT * 有什麼問題？ A：BigQuery 按掃描欄位大小計費，SELECT * 會讀取所有欄位，費用最高。應只選需要的欄位。

Q：BigQuery 查詢時如何估算費用不花錢？ A：使用 --dry_run 參數（bq CLI）或在 Console 右上角看估算掃描量，dry run 不實際執行也不收費。

實戰範例：建立電商分析 Pipeline

# 1. 建立資料集
bq mk --dataset \
  --location=asia-east1 \
  my-project:ecommerce_analytics

# 2. 建立分區 + 叢集的訂單表
bq query --use_legacy_sql=false '
CREATE TABLE ecommerce_analytics.orders (
  order_id STRING NOT NULL,
  user_id STRING,
  product_id STRING,
  category STRING,
  amount FLOAT64,
  created_at TIMESTAMP
)
PARTITION BY DATE(created_at)
CLUSTER BY user_id, category'

# 3. 從 GCS 批次載入歷史資料
bq load \
  --source_format=PARQUET \
  ecommerce_analytics.orders \
  gs://data-lake/orders/year=*/month=*/*.parquet

# 4. 建立每日收入物化視圖
bq query --use_legacy_sql=false '
CREATE MATERIALIZED VIEW ecommerce_analytics.daily_revenue AS
SELECT
  DATE(created_at) AS date,
  category,
  SUM(amount) AS revenue,
  COUNT(DISTINCT user_id) AS unique_buyers
FROM ecommerce_analytics.orders
GROUP BY 1, 2'

# 5. 查詢（自動使用物化視圖快取）
bq query --use_legacy_sql=false '
SELECT date, category, revenue
FROM ecommerce_analytics.daily_revenue
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY date DESC, revenue DESC'

總結

BigQuery 是 GCP 資料分析的核心服務，關鍵要點：

架構：儲存（Colossus/Capacitor）與運算（Dremel/Slots）分離，欄位式儲存
定價：按掃描量計費（$6.25/TB），免費 1TB/月；長期儲存 90 天後打 5 折
最佳化：分區（減少費用）+ 叢集（進一步縮小掃描，最多 4 欄）
攝取：批次用 bq load；串流用 Storage Write API（取代舊 Streaming Inserts）
安全：Policy Tags（欄位層級）+ Row Access Policies（資料列層級）
選型：分析 → BigQuery；交易 → Cloud SQL；全球 → Spanner；IoT → Bigtable

下一課 GCP-111：Cloud Functions 入門，看看怎麼用最少的程式碼來接住並處理 GCP 事件。

ACE 服務實戰 — 6/11 完成查看系列全覽 →

前言