BigQuery 9 — Partitioning (phân vùng)

Vì sao cần phân vùng: chia để trị một bảng khổng lồ

Hãy hình dung bảng transactions của NCB chứa 3 năm giao dịch — hàng tỷ dòng, vài TB dữ liệu, nằm trên một mặt phẳng liền mạch. Mỗi câu query hỏi "giao dịch hôm qua" về nguyên tắc phải soi qua toàn bộ khối dữ liệu đó để tìm ra vài triệu dòng khớp. Đó là lãng phí: bạn trả tiền (và thời gian) để đọc 3 năm dữ liệu chỉ để lấy 1 ngày.

Phân vùng (partitioning) là câu trả lời: chia bảng thành nhiều phân đoạn vật lý độc lập — mỗi partition là một "ngăn kéo" riêng, thường ứng với một ngày. Khi query có điều kiện lọc đúng trên cột phân vùng, BigQuery biết chính xác cần mở những ngăn kéo nào và bỏ qua toàn bộ phần còn lại trước khi đọc một byte nào. Đây gọi là partition pruning — cơ chế cắt giảm chi phí mạnh nhất mà một schema có thể mang lại.

Về mặt tài chính, phân vùng nối thẳng vào cách BigQuery tính bytes billed: ở mô hình on-demand bạn trả theo bytes quét, nên cắt được 99% partition nghĩa là cắt gần 99% hóa đơn của query đó. Bài này xây mô hình tinh thần về partition, ba loại partition có thật, và — quan trọng nhất — vì sao chỉ filter đúng trên cột partition mới kích hoạt pruning.

Mô hình tinh thần: mỗi partition là một "bảng con" tự quản

Điểm cốt lõi cần nắm: partition không phải chỉ là một index logic phủ lên bảng. BigQuery lưu mỗi partition như một khối storage riêng biệt trên Colossus, với metadata riêng (số dòng, khoảng giá trị, kích thước). Nhờ vậy BigQuery có thể quyết định "đọc partition này, bỏ partition kia" từ metadata, trước khi chạm vào dữ liệu thật.

Sơ đồ trên là trọng tâm của cả bài: bảng có 1000+ partition ngày, nhưng câu query chỉ có WHERE txn_date trùng 2 ngày → BigQuery chỉ chạm đúng 2 partition (tô xanh), tổng cộng ~8.3 GB thay vì quét vài TB toàn bảng. Cùng một kết quả nghiệp vụ, bytes billed chênh nhau hàng trăm lần.

Ba loại partition

BigQuery hỗ trợ đúng ba kiểu phân vùng. Chọn đúng kiểu tùy vào cột dữ liệu và cách bạn nạp dữ liệu.

1. Time-unit column partitioning (phổ biến nhất)

Phân vùng theo giá trị của một cột thời gian có sẵn trong dữ liệu — kiểu DATE, TIMESTAMP hoặc DATETIME. Đây là lựa chọn tự nhiên nhất cho dữ liệu ngân hàng: phân vùng bảng giao dịch theo txn_date, bảng khoản vay theo disbursement_date.

• Granularity (độ mịn): DAY (mặc định), hoặc HOUR, MONTH, YEAR. Chọn DAY cho hầu hết use case; chọn HOUR chỉ khi dữ liệu cực lớn trong ngày và query thường lọc theo giờ (cẩn thận giới hạn số partition, xem bên dưới).
• Với cột TIMESTAMP/DATETIME phân vùng theo DAY, mỗi ngày lịch là một partition. NULL rơi vào partition đặc biệt __NULL__; giá trị ngoài khoảng cho phép rơi vào __UNPARTITIONED__.

2. Ingestion-time partitioning

Không dựa vào cột nào trong dữ liệu, mà dựa vào thời điểm mỗi dòng được BigQuery nạp vào bảng. BigQuery tự gắn mỗi dòng vào partition ngày theo thời gian ingest, và cho bạn hai cột giả (pseudo-column) để filter:

• _PARTITIONTIME: TIMESTAMP đầu partition theo UTC (ví dụ 2026-06-14 00:00:00 UTC).
• _PARTITIONDATE: DATE tương ứng (ví dụ 2026-06-14).

Kiểu này hợp khi dữ liệu đến theo luồng nạp (streaming/batch daily load) và bản thân dòng không có cột ngày đáng tin. Lưu ý: _PARTITIONTIME theo UTC, nên nếu nghiệp vụ tính theo giờ Việt Nam (UTC+7) phải cẩn thận lệch ngày ở ranh giới nửa đêm.

3. Integer-range partitioning

Phân vùng theo một cột số nguyên, chia thành các bucket đều nhau bằng RANGE_BUCKET. Bạn khai báo start, end, interval. Hợp cho các cột ID có phân bố đều mà bạn hay lọc theo khoảng — ví dụ customer_id, branch_id, hoặc mã sản phẩm.

Ví dụ chia theo branch_id từ 0 đến 1000, mỗi bucket 10 chi nhánh: RANGE_BUCKET(branch_id, GENERATE_ARRAY(0, 1000, 10)).

Vì sao PHẢI filter trên cột partition mới cắt được dữ liệu

Đây là hiểu lầm tốn tiền phổ biến nhất. Partition pruning chỉ kích hoạt khi bộ optimizer, ở thời điểm lập kế hoạch (trước khi đọc dữ liệu), có thể suy ra tập partition cần đọc từ điều kiện WHERE. Điều đó đòi hỏi filter phải đặt trực tiếp trên cột phân vùng với biểu thức đủ tường minh.

Lý do sâu xa: BigQuery quyết định partition nào cần đọc từ metadata của cột partition, chứ không phải từ dữ liệu bên trong. Nếu điều kiện lọc bọc cột partition trong một hàm, hoặc so cột partition với một cột khác, optimizer không thể ánh xạ điều kiện đó về danh sách partition trước khi đọc → nó đành đọc tất cả partition rồi mới lọc. Bạn vẫn ra đúng kết quả, nhưng bytes billed như thể bảng chưa hề phân vùng.

Quy tắc vàng: để cột partition "trần" một bên dấu so sánh, phía kia là hằng số hoặc biểu thức hằng. Muốn lọc theo giờ trên cột TIMESTAMP đã phân vùng theo DAY, hãy so trực tiếp txn_ts >= '2026-06-14 08:00:00' AND txn_ts < '2026-06-14 18:00:00' thay vì DATE(txn_ts) = ... hay EXTRACT(HOUR FROM txn_ts) = ....

SQL thật: tạo bảng phân vùng và query cắt partition

-- Tạo bảng giao dịch phân vùng theo NGÀY trên cột txn_date (DATE)
CREATE TABLE `ncb-dwh.core.transactions`
(
  txn_id       STRING,
  customer_id  STRING,
  branch_id    INT64,
  txn_ts       TIMESTAMP,
  txn_date     DATE,
  amount       NUMERIC,
  channel      STRING,
  risk_flag    BOOL
)
PARTITION BY txn_date
OPTIONS (
  -- Cầu dao: query KHÔNG có filter trên txn_date sẽ bị từ chối
  require_partition_filter = TRUE,
  -- Tự xóa partition cũ hơn 730 ngày (2 năm)
  partition_expiration_days = 730,
  description = "Giao dịch lõi, phân vùng theo ngày giao dịch"
);

Câu query khai thác pruning — chỉ chạm các partition trong 7 ngày gần nhất:

-- Chỉ đọc partition khớp WHERE trên cột partition txn_date
SELECT
  txn_date,
  channel,
  COUNT(*)        AS so_giao_dich,
  SUM(amount)     AS tong_tien
FROM `ncb-dwh.core.transactions`
WHERE txn_date >= CURRENT_DATE() - 7   -- ← filter TRÊN cột partition => pruning
  AND channel = 'MOBILE'
GROUP BY txn_date, channel
ORDER BY txn_date;

Kiểm chứng bằng dry-run (xem ước lượng bytes trước khi chạy): con số "This query will process X" trên bản có WHERE txn_date >= CURRENT_DATE() - 7 sẽ nhỏ hơn bản không có filter partition hàng trăm lần — đó chính là pruning hiện thành tiền.

Với ingestion-time partitioning, cú pháp và filter khác một chút:

-- Bảng phân vùng theo thời điểm nạp (ingestion-time)
CREATE TABLE `ncb-dwh.staging.events_raw`
(
  event_id  STRING,
  payload   JSON
)
PARTITION BY DATE(_PARTITIONTIME)
OPTIONS (require_partition_filter = TRUE);

-- Filter trên cột giả _PARTITIONTIME để pruning
SELECT event_id
FROM `ncb-dwh.staging.events_raw`
WHERE _PARTITIONTIME >= TIMESTAMP('2026-06-14')
  AND _PARTITIONTIME <  TIMESTAMP('2026-06-15');

require_partition_filter — cầu dao bắt buộc lọc partition

Tùy chọn require_partition_filter = TRUE biến "nên lọc partition" thành "bắt buộc lọc partition". Bất kỳ query nào chạm bảng mà không có điều kiện lọc trên cột partition sẽ bị từ chối ngay lập tức với lỗi, thay vì âm thầm quét toàn bảng và đội hóa đơn.

Đây là hàng rào cực kỳ đáng giá cho môi trường ngân hàng nhiều analyst: nó ngăn SELECT * FROM transactions vô ý quét 3 năm dữ liệu. Kết hợp với maximum_bytes_billed làm cầu dao thứ hai theo bytes, bạn có hai lớp bảo vệ độc lập. Có thể bật khi tạo bảng (OPTIONS) hoặc sửa sau bằng ALTER TABLE ... SET OPTIONS (require_partition_filter = TRUE).

Giới hạn số partition và partition expiration

Giới hạn số partition: một bảng phân vùng có trần tối đa 4.000 partition. Con số này định hình lựa chọn granularity của bạn:

• Phân vùng theo DAY: 4.000 ngày ≈ ~11 năm dữ liệu — thoải mái cho hầu hết bảng.
• Phân vùng theo HOUR: 4.000 giờ ≈ ~166 ngày — hết rất nhanh, chỉ dùng khi thực sự cần độ mịn giờ và có kế hoạch dọn dữ liệu.
• Ngoài ra còn giới hạn số partition bị sửa đổi trong một job (ví dụ một câu load/DML ghi vào quá nhiều partition cùng lúc sẽ bị chặn).

Nếu cần giữ nhiều năm mà không muốn chạm trần, cân nhắc granularity MONTH/YEAR, hoặc kết hợp phân vùng với clustering để có độ mịn cắt dữ liệu bên trong partition mà không tốn thêm partition.

Partition expiration: partition_expiration_days cho BigQuery tự động xóa partition khi nó quá tuổi tính từ mốc thời gian của partition. Đặt partition_expiration_days = 730 nghĩa là dữ liệu quá 2 năm tự biến mất — vừa tuân thủ chính sách lưu trữ (retention) của ngân hàng, vừa giữ số partition dưới trần, vừa giảm chi phí storage mà không cần job dọn dẹp thủ công. Đây là công cụ vòng đời dữ liệu (data lifecycle) gần như "cài đặt một lần rồi quên".

Partition + clustering: hai tầng bổ sung nhau

Phân vùng cắt dữ liệu theo một cột (thường là thời gian) ở mức khối storage. Nhưng bên trong mỗi partition, dữ liệu vẫn có thể lớn và query còn lọc theo cột khác (ví dụ customer_id, channel). Đó là lúc clustering vào cuộc: sắp xếp dữ liệu bên trong partition theo tối đa 4 cột, để BigQuery cắt tiếp các block không khớp. Hai kỹ thuật xếp chồng lên nhau, và cùng với các thủ thuật khác tạo thành bộ kỹ thuật tối ưu query. Mô hình thường dùng cho bảng giao dịch ngân hàng: partition theo txn_date, cluster theo customer_id.

Use case thực tế

Đội Data NCB có bảng transactions 3 năm (~3.6 TB, ~2 tỷ dòng), ban đầu không phân vùng. Một báo cáo đối soát cuối ngày chạy WHERE DATE(txn_ts) = CURRENT_DATE() - 1 — vì cột txn_ts là TIMESTAMP chưa phân vùng, mỗi lần chạy quét trọn ~3.6 TB, và có hàng chục biến thể báo cáo tương tự mỗi ngày.

Can thiệp, không đổi kết quả nghiệp vụ:

1. Thêm cột txn_date DATE và phân vùng theo ngày (PARTITION BY txn_date). Mỗi ngày ~4 GB thành một partition riêng.
2. Đổi filter từ WHERE DATE(txn_ts) = ... (bọc hàm → không pruning) sang WHERE txn_date = CURRENT_DATE() - 1 (cột partition trần → pruning). Bytes quét rớt từ ~3.6 TB xuống ~4 GB/lần — giảm ~99.9%.
3. Bật require_partition_filter = TRUE để không analyst nào lỡ tay quét cả bảng nữa.
4. Đặt partition_expiration_days = 1095 (3 năm) để dữ liệu cũ tự rụng theo chính sách retention, giữ số partition dưới trần và cắt chi phí storage dữ liệu quá hạn.

Kết quả: một họ báo cáo cuối ngày trước đây quét hàng chục TB/ngày nay chỉ còn vài GB/lần, hóa đơn on-demand của nhóm query này giảm hơn 99%, và cầu dao require_partition_filter chặn được cả những query vô ý trong tương lai.

Ghi nhớ

• Phân vùng chia bảng thành các partition độc lập trên storage; filter đúng cột partition kích hoạt partition pruning — chỉ đọc partition khớp, cắt gần như toàn bộ bytes còn lại trước khi đọc dữ liệu.
• Ba loại: time-unit column (cột DATE/TIMESTAMP/DATETIME, granularity DAY/HOUR/MONTH/YEAR), ingestion-time (cột giả _PARTITIONTIME/_PARTITIONDATE, theo UTC), integer-range (RANGE_BUCKET trên cột số nguyên).
• Pruning chỉ xảy ra khi filter đặt trực tiếp trên cột partition với hằng số/biểu thức hằng. Bọc cột trong DATE(), CAST() hay so với cột khác → mất pruning, quét toàn bảng.
• require_partition_filter = TRUE từ chối mọi query không lọc partition — cầu dao chống quét nhầm cả bảng.
• Giới hạn ~4.000 partition/bảng: DAY ≈ 11 năm, HOUR ≈ 166 ngày; chọn granularity theo tầm dữ liệu.
• partition_expiration_days tự xóa partition quá tuổi — dọn dữ liệu cũ, giữ số partition dưới trần, giảm chi phí storage tự động.
• Phân vùng và clustering bổ sung nhau: partition cắt theo thời gian, cluster cắt tiếp bên trong partition theo cột khác.

Nguồn tham khảo

• Partitioned tables: https://cloud.google.com/bigquery/docs/partitioned-tables
• Creating and using partitioned tables: https://cloud.google.com/bigquery/docs/creating-partitioned-tables
• Querying partitioned tables: https://cloud.google.com/bigquery/docs/querying-partitioned-tables
• Managing partitioned tables (expiration, require filter): https://cloud.google.com/bigquery/docs/managing-partitioned-tables
• Best practices — performance overview: https://cloud.google.com/bigquery/docs/best-practices-performance-overview
• Clustered tables: https://cloud.google.com/bigquery/docs/clustered-tables
• BigQuery pricing: https://cloud.google.com/bigquery/pricing
• "Google BigQuery: The Definitive Guide" — Lakshmanan & Tigani, O'Reilly