BigQuery 查詢與成本：分區、叢集怎麼用？

前言

資料放進 BigQuery 後，SQL 很容易開始寫；帳單和查詢時間卻可能在資料變多後一起增加。BigQuery 代管底層運算，但資料怎麼分區、查詢掃到多少資料，仍要由你設計。

這篇從 BigQuery 的儲存與計費方式開始，接著看分區、叢集、資料匯入和權限設定，最後整理幾個常見的選型情境。

BigQuery 架構

資源階層

flowchart TB
  PROJECT["GCP Project<br/>計費、IAM 與 API 邊界"] --> DATASET["Dataset<br/>區域與資料存取邊界"]
  DATASET --> TABLE["Native Table<br/>資料存於 BigQuery"]
  DATASET --> VIEW["View<br/>儲存 SQL 定義"]
  DATASET --> MV["Materialized View<br/>預先計算結果"]
  DATASET --> EXT["External Table<br/>資料留在 GCS／Bigtable／Drive"]

儲存與運算分離

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

flowchart LR
  LOAD["批次或串流寫入"] --> STORE["Colossus + Capacitor<br/>欄位式、壓縮、加密儲存"]
  SQL["SQL 查詢"] --> PLAN["Dremel 建立平行執行計畫"]
  PLAN --> SLOTS["Slots<br/>大規模平行運算"]
  SLOTS -->|"只讀需要的欄位與區塊"| STORE
  STORE -->|"掃描資料"| SLOTS
  SLOTS --> RESULT["彙整結果"]
  SCALE1["Storage 可獨立成長"] -.-> STORE
  SCALE2["On-demand 或 Editions<br/>可獨立配置 Compute"] -.-> SLOTS

這裡先把 Slot 講清楚，後面 Editions 會一直用到：Slot 是 BigQuery 的運算單位，你可以把它想成一份平行運算資源。用 On-demand 時你完全不用管 slot，BigQuery 自動配給你；切到 Editions 則是反過來，你直接買一批 slot 來跑查詢。

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

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

定價模式

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

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

按需定價（On-Demand）

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

BigQuery Editions（2023 起）

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

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

順帶澄清一個常被誤會的點：99.99% 月度 SLA 適用於 On-demand 與 Standard 以外的 Edition；BigQuery Standard edition 是 99.9%。所以它不是「只有 Enterprise 才有」的特色，但也不是「不論哪個 Edition 都一樣」——Standard 就是低一檔。真正要 Enterprise 起跳的是 BI Engine 與 CMEK。

選型其實很直覺：查詢量飄忽、偶爾才跑一次的，就用 On-demand 按掃描量付費；每天都有穩定大批查詢、想要帳單可預期的，再切到 Editions 用 slot 包月。

資料集與資料表

建立資料集與資料表

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

# 使用 inline 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

資料表類型

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

分區（Partitioning）與叢集（Clustering）

這是 BigQuery 效能和成本最佳化的核心，備考時也要熟悉。

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

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

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

三種分區方式：

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

查詢時使用分區過濾：

-- 好：使用分區欄位過濾，只掃描 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 叢集比較：

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

flowchart TB
  Q["查詢條件與 SELECT 欄位"] --> COL["欄位裁剪<br/>不讀未選取欄位"]
  COL --> PART{"有使用分區欄位過濾？"}
  PART -->|"否"| FULL["掃描所有分區<br/>成本較高"]
  PART -->|"是"| PRUNE["Partition Pruning<br/>只保留目標日期／範圍"]
  PRUNE --> CLUSTER{"條件命中前置叢集欄位？"}
  CLUSTER -->|"否"| PB["掃描該分區多數資料區塊"]
  CLUSTER -->|"是"| CB["Cluster Block Pruning<br/>再跳過不相關區塊"]
  FULL --> SLOT["Slots 平行運算"]
  PB --> SLOT
  CB --> SLOT
  SLOT --> COST["產出結果與實際 bytes processed"]

「查詢可見性」這列初學者容易卡住，補充一下：分區帶來的省錢效果，--dry_run 事前就能估出來（它會直接告訴你這次查詢預計掃多少資料）；但叢集的省錢效果無法靠 dry-run 預估，因為要實際跑完、BigQuery 跳過了哪些區塊之後，你才知道真正掃了多少。

📝 考場提點

比較分區與叢集時，先看查詢條件：若能用分區欄位排除整批資料，就先考慮分區；需要在分區內進一步縮小掃描範圍，再看叢集欄位是否合適。叢集最多 4 欄，欄位順序也會影響效果。

兩個常被埋在選項裡的陷阱：(1) 對分區表下的 WHERE 如果沒帶到分區欄位，照樣會全表掃描、照樣收費——分區不是建了就自動省。(2) 叢集的省錢效果無法用 --dry_run 預估，要實際跑完才知道掃了多少，別被「dry-run 看不到省多少所以叢集沒用」這種選項騙了。

關鍵字：按掃描量計費、partition pruning、cluster column 順序。

資料攝取（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）

有兩種串流方式，新專案建議用 Storage Write API (gRPC)（2021 年 10 月 GA）：

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

Storage Write API (gRPC) 範例（Python）：

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

# Storage Write API (gRPC) 常用的三種模式（另有進階用的 Buffered type）：
# - 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 角色

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

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

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

# Policy Tag 要寫在 schema 裡（CREATE TABLE 的 DDL 不能指定 policy tag）
# schema.json 是欄位陣列，在要保護的欄位上加 policyTags（taxonomy 要和資料表在同一個 location）：
# [{"name": "user_id", "type": "STRING"},
#  {"name": "email", "type": "STRING", "description": "PII",
#   "policyTags": {"names": ["projects/my-project/locations/asia-east1/taxonomies/123/policyTags/456"]}}]
bq mk --table my-project:sales_analytics.users schema.json

沒有被授予 roles/datacatalog.categoryFineGrainedReader 的使用者讀不到被保護的欄位：SELECT * 會直接報錯，要用 SELECT * EXCEPT (欄位名) 排除該欄才查得到其他欄位。

資料列層級安全（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 會自動改讀預算好的結果，不用每次重掃整張原表——像 daily 收入彙整這種高頻又昂貴的聚合查詢，最有感，費用直接砍下來。

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）

# 每 24 小時（從建立排程起算）執行一次彙整查詢
bq query \
  --use_legacy_sql=false \
  --display_name='Daily summary' \
  --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 資料庫選型

表格超出欄寬時，可左右捲動；鍵盤可聚焦表格後用左右方向鍵查看。

選型決策樹：

flowchart TB
  SQL{"需要 SQL 嗎？"}
  SQL -->|"是"| WORK{"主要是分析還是交易？"}
  WORK -->|"PB 級 OLAP、彙整、BI"| BQ["BigQuery"]
  WORK -->|"高頻單筆 OLTP、ACID"| GLOBAL{"需要全球一致性<br/>與水平擴展？"}
  GLOBAL -->|"是"| SPANNER["Cloud Spanner"]
  GLOBAL -->|"否"| PG{"需要強化 PostgreSQL 效能？"}
  PG -->|"是"| ALLOY["AlloyDB"]
  PG -->|"否"| CSQL["Cloud SQL"]
  SQL -->|"否"| MODEL{"主要資料模型？"}
  MODEL -->|"時序／IoT、極低延遲"| BT["Bigtable"]
  MODEL -->|"文件、行動／Web 即時同步"| FS["Firestore"]

📝 考場提點

選資料庫時，先分清楚主要工作是交易還是分析。高頻單筆 UPDATE／DELETE 和訂單交易，通常要看 Cloud SQL 或 Spanner；大量資料的報表、彙整與商業分析，才是 BigQuery 擅長的場景。不要只因為兩者都支援 SQL，就把它們當成可直接互換的資料庫。

查詢費用也要看實際掃描量。SELECT * 可能讀到不需要的欄位；分區表若沒有合適的過濾條件，也可能掃描比預期更多的資料。

ACE 考試重點整理

必背知識點

1. 免費層：每月 1 TiB 查詢 + 10 GiB 儲存
2. 按需定價：$6.25 / TiB 掃描量（US 多區域價，各區不同）
3. 分區優先，再叢集：減少費用最有效的方法
4. 叢集最多 4 個欄位
5. 長期儲存：90 天未修改 → 費用約降低 50%
6. Storage Write API (gRPC) 優先於 Storage Write API (REST)：精確一次、低成本
7. Materialized View 自動刷新，快取昂貴聚合結果
8. 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

flowchart TB
  RAW["GCS 歷史 Parquet"] -->|"bq load"| ORDERS["orders Table<br/>按 created_at 分區<br/>以 user_id、category 叢集"]
  LIVE["即時訂單事件"] -->|"Storage Write API (gRPC)"| ORDERS
  ORDERS -->|"自動增量維護"| MV["daily_revenue<br/>Materialized View"]
  ANALYST["分析查詢<br/>最近 7 天、指定欄位"] --> MV
  MV --> REPORT["營收報表／BI"]
  ANALYST -. "未命中物化視圖時" .-> ORDERS

# 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,
  APPROX_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 適合大量資料的分析查詢。實作前可以回頭檢查這幾件事：

• 架構：儲存（Colossus/Capacitor）與運算（Dremel/Slots）分離，欄位式儲存
• 定價：按掃描量計費（$6.25/TiB，US 多區域價），免費 1 TiB/月；長期儲存 90 天後約打 5 折
• 最佳化：分區（減少費用）+ 叢集（進一步縮小掃描，最多 4 欄）
• 攝取：批次用 bq load；串流用 Storage Write API (gRPC)（優先於 Storage Write API (REST)）
• 安全：Policy Tags（欄位層級）+ Row Access Policies（資料列層級）
• 選型：先分清分析與線上交易，再依資料模型、查詢方式和一致性需求比較服務

下一課 GCP-111：Cloud Run functions 入門：函式會在什麼時候執行？（原 Cloud Functions），看看怎麼用最少的程式碼來接住並處理 GCP 事件。