BigQuery 5 — Truy vấn với GoogleSQL

Mô hình tinh thần: chạy SQL trên BigQuery khác gì?

Ở một cơ sở dữ liệu quan hệ truyền thống (Oracle, PostgreSQL), bạn mở một connection, giữ session, gõ SQL và nhận kết quả trực tiếp từ tiến trình server đang chạy. BigQuery khác về bản chất: không có server để kết nối, không có session cầm tay. Mỗi câu truy vấn bạn gửi đi trở thành một JOB — một đơn vị công việc bất đồng bộ được BigQuery lập lịch, phân rã, phân phối cho hàng trăm worker rồi gom kết quả về.

Hệ quả thực tế của mô hình này:

• Bạn không "mở kết nối rồi chạy nhiều lệnh". Bạn nộp một job, chờ nó chạy xong, đọc kết quả.
• Job có vòng đời rõ ràng (PENDING → RUNNING → DONE) và một job_id để tra cứu về sau.
• Chi phí gắn với số byte quét, không phải thời gian giữ kết nối. Vì thế biết trước một câu query sẽ quét bao nhiêu byte (dry-run) là kỹ năng sống còn — xem chi tiết ở BigQuery 8 — Định giá & bytes.

Trước khi vào cú pháp, hãy nắm cách BigQuery định danh dữ liệu.

Định danh 3 tầng: project.dataset.table

BigQuery tổ chức dữ liệu theo phân cấp ba tầng. Một bảng luôn được tham chiếu đầy đủ dưới dạng project.dataset.table (trong SQL bọc bằng dấu backtick).

Cách viết đầy đủ khi truy vấn:

-- Định danh đầy đủ project.dataset.table
SELECT * FROM `ncb-datalake-prod.core_banking.transactions`;

-- Nếu đang ở đúng project mặc định, có thể bỏ project:
SELECT * FROM `core_banking.transactions`;

Lưu ý: dấu backtick ` bắt buộc khi tên có dấu gạch nối (như ncb-datalake-prod) hoặc ký tự đặc biệt.

GoogleSQL — Standard SQL đổi tên

Từ 2022, Google đổi tên Standard SQL thành GoogleSQL. Đây chỉ là đổi nhãn — cùng một phương ngữ tuân thủ ANSI SQL:2011, hỗ trợ đầy đủ subquery tương quan, CTE (WITH), window function, và các kiểu lồng STRUCT/ARRAY. Phương ngữ cũ Legacy SQL (cú pháp [project:dataset.table], các hàm TABLE_DATE_RANGE...) đã lỗi thời; bài viết mới nên luôn dùng GoogleSQL.

Vài đặc điểm GoogleSQL cần nhớ ngay:

• Bảng trong backtick, chuỗi trong nháy đơn: FROM `ds.t` WHERE status = 'ACTIVE'.
• Hỗ trợ kiểu lồng ARRAY và STRUCT gốc — điểm mạnh riêng của BigQuery, xử lý dữ liệu phi chuẩn hóa mà không cần join. Chi tiết ở BigQuery 6 — Nested & Repeated.
• SAFE_CAST, SAFE_DIVIDE để tránh lỗi tràn/chia 0.
• EXCEPT, REPLACE trong SELECT * EXCEPT(col) để loại cột nhanh.

Vòng đời một query job

Khi bạn nhấn "Run", điều xảy ra bên trong không phải "chạy SQL" mà là một chuỗi pha có kiểm soát. Hiểu vòng đời này giúp bạn đọc đúng các thông số về sau (slot, bytes, stage).

Diễn giải các pha:

1. Submit — Client gọi jobs.insert. BigQuery cấp một job_id, đưa job vào trạng thái PENDING.
2. Plan — Bộ lập kế hoạch phân tích SQL, xác định các bảng/cột cần đọc, và sinh query plan: một cây các stage phụ thuộc nhau. Ở đây BigQuery cũng tính trước số byte sẽ quét (đây chính là con số dry-run trả về).
3. Execute — Job chuyển RUNNING. Dremel phân phối mỗi stage cho nhiều worker song song; dữ liệu trung gian trao đổi giữa các stage qua shuffle.
4. Result — Kết quả ghi vào một bảng tạm ẩn (giữ ~24 giờ). Job chuyển DONE. Client đọc kết quả và các thống kê: totalBytesProcessed, totalBytesBilled, totalSlotMs, cacheHit.

Mọi job (kể cả query lỗi) đều để lại bản ghi trong INFORMATION_SCHEMA.JOBS, tra cứu được bằng job_id — nền tảng cho việc giám sát chi phí và chẩn đoán hiệu năng ở các bài sau.

Ba cách chạy một query

1. BigQuery Console (giao diện web)

Nhanh nhất để khám phá. Gõ SQL vào editor, giao diện hiển thị ngay ước lượng byte sẽ quét ở góc trên phải trước khi bạn chạy — đây là dry-run tự động. Sau khi chạy, tab "Execution details" cho xem query plan.

2. bq CLI

Công cụ dòng lệnh, tiện để script hóa và đưa vào pipeline/Airflow.

# Chạy một query GoogleSQL (--nouse_legacy_sql để chắc chắn dùng GoogleSQL)
bq query --nouse_legacy_sql \
  'SELECT status, COUNT(*) AS n
   FROM `ncb-datalake-prod.core_banking.transactions`
   WHERE txn_date = "2026-07-14"
   GROUP BY status'

# Dry-run: KHÔNG chạy, chỉ ước lượng byte
bq query --dry_run --nouse_legacy_sql \
  'SELECT * FROM `ncb-datalake-prod.core_banking.transactions`'

3. API / Client library

Ứng dụng gọi trực tiếp jobs.insert qua REST hoặc client library (Python, Java...). Đây là cách các dịch vụ backend truy vấn BigQuery.

from google.cloud import bigquery

client = bigquery.Client(project="ncb-datalake-prod")

# Bật dry-run để ước lượng bytes, không chạy thật
cfg = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
job = client.query(
    "SELECT * FROM `core_banking.transactions` WHERE amount > 1000000",
    job_config=cfg,
)
print(f"Sẽ quét: {job.total_bytes_processed / 1e9:.2f} GB")

Dry-run: ước lượng bytes trước khi trả tiền

Vì BigQuery on-demand tính tiền theo byte quét, chạy nhầm một câu SELECT * trên bảng nhiều TB là mất tiền thật. Dry-run giải quyết điều này: BigQuery lập kế hoạch query, trả về totalBytesProcessed ước tính nhưng không thực thi và không tính phí.

Quy tắc vàng: luôn dry-run một câu query lạ trên bảng lớn trước khi chạy thật. Con số byte quyết định bạn có nên thêm WHERE lọc partition, chọn ít cột hơn, hay dừng lại suy nghĩ. Chi tiết cách con số này thành tiền và các bẫy (ví dụ SELECT * không giảm byte dù có LIMIT) nằm ở BigQuery 8 — Định giá & bytes.

Preview: xem dữ liệu miễn phí

Muốn "liếc" vài dòng đầu của bảng mà không tốn byte nào? Đừng dùng SELECT * ... LIMIT 10 (vẫn quét cả cột!). Dùng preview:

• Console: tab Preview của bảng.
• CLI: bq head -n 10 core_banking.transactions.
• API: tabledata.list.

Preview đọc trực tiếp dữ liệu đã lưu, không chạy query engine, không tính phí. Rất hợp để kiểm tra nhanh schema và giá trị mẫu.

Query cache: kết quả lặp lại là miễn phí

BigQuery tự động lưu kết quả mỗi query vào một cache theo user, giữ ~24 giờ. Nếu bạn (cùng project) chạy lại đúng chuỗi SQL byte-for-byte và bảng nguồn chưa thay đổi, BigQuery trả kết quả từ cache: cacheHit = true, 0 byte tính phí, trả về gần như tức thì.

Cache bị vô hiệu khi:

• SQL khác dù chỉ một khoảng trắng hoặc comment.
• Bảng nguồn có ghi mới (dữ liệu đổi).
• Query dùng hàm phi tất định như CURRENT_TIMESTAMP(), RAND(), hoặc nguồn động (external table, wildcard đổi).
• Bạn tắt bằng use_query_cache = false (thường dùng khi benchmark hiệu năng thật).

Cache là cách tối ưu chi phí "miễn phí" nhất: dashboard chạy lại cùng câu query trong ngày sẽ không phát sinh byte mới.

Các mệnh đề SQL cơ bản qua ví dụ ngân hàng

GoogleSQL theo đúng thứ tự logic quen thuộc. Dưới đây là câu truy vấn tổng hợp giao dịch theo chi nhánh — chạy được thật trên BigQuery:

-- Tổng hợp giao dịch tháng 6/2026 theo chi nhánh, chỉ giao dịch thành công
SELECT
    t.branch_id,
    COUNT(*)                              AS so_giao_dich,
    COUNT(DISTINCT t.customer_id)         AS so_khach,
    ROUND(SUM(t.amount), 0)               AS tong_tien,
    ROUND(AVG(t.amount), 0)               AS trung_binh
FROM `ncb-datalake-prod.core_banking.transactions` AS t
WHERE t.txn_date BETWEEN '2026-06-01' AND '2026-06-30'
  AND t.status = 'SUCCESS'
GROUP BY t.branch_id
HAVING SUM(t.amount) > 1000000000          -- chi nhánh có doanh số > 1 tỷ
ORDER BY tong_tien DESC
LIMIT 20;

Trình tự thực thi logic (khác thứ tự viết): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Một ví dụ dùng CTE và JOIN (chi tiết join/window ở bài 7):

-- Khách VIP: tổng chi tiêu tháng 6 và tên khách
WITH chi_tieu AS (
    SELECT customer_id, SUM(amount) AS tong
    FROM `ncb-datalake-prod.core_banking.transactions`
    WHERE txn_date BETWEEN '2026-06-01' AND '2026-06-30'
      AND status = 'SUCCESS'
    GROUP BY customer_id
)
SELECT c.full_name, c.segment, ct.tong
FROM chi_tieu AS ct
JOIN `ncb-datalake-prod.core_banking.customers` AS c
  ON c.customer_id = ct.customer_id
WHERE ct.tong > 500000000
ORDER BY ct.tong DESC;

Use case thực tế

Đội Data Analytics của NCB xây một dashboard theo dõi giao dịch chi nhánh, refresh 4 lần/ngày. Bản đầu tiên dùng SELECT * trên bảng transactions 2,4 TB không lọc partition: mỗi lần refresh quét ~2,4 TB, 4 lần/ngày ≈ 9,6 TB/ngày ≈ 288 TB/tháng — với giá on-demand ~6,25 USD/TB là khoảng 1.800 USD/tháng chỉ cho một dashboard.

Sau khi rà lại theo đúng các nguyên tắc bài này:

• Chỉ chọn 6 cột cần thay vì SELECT * → giảm ~70% byte (nhờ lưu cột).
• Thêm WHERE txn_date >= ... lọc đúng partition ngày → chỉ quét dữ liệu 30 ngày gần nhất.
• Ba refresh sau trong ngày trúng query cache (dữ liệu batch chỉ nạp 1 lần/ngày) → 3/4 lần chạy tốn 0 byte.

Kết quả: byte quét thực tế giảm còn khoảng 1,8 TB/tháng, chi phí về ~11 USD/tháng — giảm hơn 99%, chỉ nhờ hiểu byte, cache và partition mà không đổi một dòng dữ liệu nào.

Ghi nhớ

• BigQuery không có "kết nối/session": mỗi query là một JOB bất đồng bộ có job_id và vòng đời submit → plan → execute → result.
• Định danh 3 tầng project.dataset.table; dataset gắn location, chỉ join được dữ liệu cùng location.
• GoogleSQL = tên mới của Standard SQL (ANSI). Tránh Legacy SQL.
• Dry-run ước lượng byte mà không tính phí — luôn dry-run câu lạ trên bảng lớn.
• Preview (bq head, tab Preview) xem dữ liệu miễn phí; LIMIT thì không giảm byte quét.
• Query cache 24h: cùng SQL + dữ liệu chưa đổi ⇒ cacheHit=true, 0 byte. Hàm phi tất định làm hỏng cache.
• Ba cách chạy: Console (khám phá), bq CLI (script), API/client (ứng dụng).
• Chọn cột cụ thể + lọc partition trong WHERE là hai đòn bẩy giảm byte lớn nhất.

Nguồn tham khảo

• BigQuery documentation — https://cloud.google.com/bigquery/docs
• Running queries (Console/CLI/API, dry-run, cache) — https://cloud.google.com/bigquery/docs/running-queries
• Query plan and timeline / execution details — https://cloud.google.com/bigquery/docs/query-plan-explanation
• INFORMATION_SCHEMA JOBS view — https://cloud.google.com/bigquery/docs/information-schema-jobs
• Best practices for performance and cost — https://cloud.google.com/bigquery/docs/best-practices-performance-overview
• BigQuery pricing (on-demand & bytes) — https://cloud.google.com/bigquery/pricing
• "Dremel: Interactive Analysis of Web-Scale Datasets", Melnik et al., VLDB 2010
• "Google BigQuery: The Definitive Guide", Lakshmanan & Tigani, O'Reilly