BigQuery 6 — Dữ liệu lồng (STRUCT & ARRAY)

Mô hình tinh thần — một hàng có thể chứa cả cây dữ liệu

Trong warehouse quan hệ truyền thống, một hàng là "phẳng": mỗi cột chứa đúng một giá trị nguyên tử. Muốn biểu diễn quan hệ một-nhiều (một khách hàng có nhiều giao dịch), bạn phải tách ra bảng con và nối lại bằng JOIN.

BigQuery đi theo hướng khác. Vì lưu trữ theo cột (Capacitor) và kế thừa mô hình dữ liệu của Dremel, BigQuery cho phép một cột chứa cả một cấu trúc lồng: một hàng customer có thể mang thẳng bên trong nó một mảng các giao dịch, mỗi giao dịch lại là một struct nhiều trường. Toàn bộ "cây" đó nằm gọn trong một hàng logic — không cần bảng con, không cần join.

Hai viên gạch tạo nên khả năng này:

• STRUCT (trong lược đồ hiển thị là kiểu RECORD): gom nhiều trường có tên thành một cột hợp thành. Ví dụ address là struct gồm street, city, province.
• ARRAY (trong lược đồ là chế độ REPEATED): một cột chứa danh sách giá trị cùng kiểu, thay vì một giá trị duy nhất. Ví dụ transactions là mảng.

Sức mạnh thật sự đến khi lồng hai thứ này vào nhau: một cột ARRAY<STRUCT<...>> — mảng các struct — chính là cách BigQuery biểu diễn một bảng con ngay bên trong một hàng cha.

Sơ đồ trên là một hàng duy nhất: hai cột phẳng, một cột STRUCT (address), và một cột ARRAY-of-STRUCT (transactions) chứa ba giao dịch. Tất cả thuộc cùng một bản ghi customer.

Vì sao BigQuery khuyến khích denormalize thành cột lồng

Trong warehouse cổ điển, chuẩn hoá (normalization) là mặc định tốt: tách dữ liệu ra nhiều bảng để tránh trùng lặp, rồi join khi cần. Với BigQuery, lời khuyên đảo ngược: nên denormalize — gộp quan hệ một-nhiều vào cột lồng ngay trong bảng cha, thay vì tách bảng.

Lý do nằm ở kiến trúc thực thi phân tán:

• JOIN cần shuffle. Để nối hai bảng lớn theo khoá, BigQuery phải phân phối lại (repartition/shuffle) dữ liệu qua mạng Jupiter sao cho các hàng cùng khoá gặp nhau trên cùng một worker. Shuffle là pha tốn kém nhất trong nhiều truy vấn — nó xuất hiện dưới dạng shuffleOutputBytes trong query plan, và khi vượt bộ nhớ sẽ sinh shuffleOutputBytesSpilled (tràn xuống đĩa, chậm hơn nhiều).
• Cột lồng thì đã "join sẵn". Khi giao dịch nằm ngay trong hàng khách hàng, dữ liệu liên quan đã ở cùng một chỗ. Bung mảng bằng UNNEST là thao tác cục bộ trên từng hàng, không cần shuffle qua mạng. Bạn đổi một JOIN toàn cục lấy một phép "mở gói" cục bộ.
• Lưu trữ cột vẫn hiệu quả. Nhờ định dạng Capacitor, cột lồng không đọc bị buộc phải quét — nếu truy vấn chỉ chạm transactions.amount, BigQuery chỉ đọc đúng đường dẫn cột đó, không đọc cả struct.

Điểm cân bằng: denormalize làm tăng dung lượng lưu trữ (dữ liệu cha lặp lại theo mỗi phần tử con khi bung phẳng ở góc nhìn khác) và làm cập nhật khó hơn (sửa một phần tử trong mảng phải viết lại cả hàng). Vì vậy nó hợp nhất với dữ liệu thiên về đọc/phân tích, ít cập nhật lẻ — đúng đặc trưng của warehouse. Xem thêm cách khai thác chiến lược này ở bài tối ưu & denormalize, và so sánh với các dạng JOIN/window ở bài joins & window.

Định nghĩa lược đồ lồng

Cú pháp DDL dùng STRUCT<...> cho record và ARRAY<...> cho repeated. Trường lồng lấy vào nhau tự nhiên:

CREATE TABLE banking.customers_nested (
  customer_id STRING NOT NULL,
  full_name   STRING,
  -- STRUCT: một cột hợp thành nhiều trường con (RECORD, mode NULLABLE)
  address STRUCT<
    street   STRING,
    city     STRING,
    province STRING
  >,
  -- ARRAY of STRUCT: bảng con nhúng trong hàng (RECORD, mode REPEATED)
  transactions ARRAY<STRUCT<
    txn_id  STRING,
    amount  NUMERIC,
    ts      TIMESTAMP,
    channel STRING          -- 'ATM' | 'MOBILE' | 'BRANCH'
  >>
);

Khi xem lược đồ trên console, address hiện là RECORD / NULLABLE, còn transactions là RECORD / REPEATED. Mỗi trường con hiển thị theo đường dẫn chấm: transactions.amount, address.city.

UNNEST — bung mảng thành hàng

Cột ARRAY không thể SELECT trực tiếp như cột phẳng khi bạn muốn thao tác trên từng phần tử. Bạn bung nó bằng UNNEST, biến mảng thành một tập hàng ảo rồi cross join tương quan với hàng cha. BigQuery cho phép viết gọn bằng cách đặt mảng vào mệnh đề FROM cùng dấu phẩy — đây là correlated cross join ngầm với hàng cha:

-- Bung mỗi giao dịch trong mảng thành một hàng riêng,
-- kèm theo thông tin khách hàng cha
SELECT
  c.customer_id,
  c.full_name,
  c.address.city                       AS city,   -- truy cập STRUCT bằng dấu chấm
  t.txn_id,
  t.amount,
  t.channel
FROM banking.customers_nested AS c,
     UNNEST(c.transactions) AS t        -- bung ARRAY<STRUCT> thành hàng, alias t
WHERE t.channel = 'MOBILE'
  AND t.amount >= 5000000
ORDER BY t.amount DESC;

Vài điểm mấu chốt:

• UNNEST(c.transactions) AS t sinh một hàng cho mỗi phần tử của mảng. Sau khi bung, t hành xử như một bảng con: t.amount, t.channel là các cột bình thường.
• Cột cha (c.customer_id, c.address.city) tự động lặp lại theo mỗi phần tử con — chính là kết quả bạn sẽ có nếu join hai bảng chuẩn hoá, nhưng không tốn shuffle.
• Struct lồng thì cứ nối dấu chấm: c.address.city. Không cần UNNEST vì struct là một giá trị hợp thành, không phải mảng.

Truy vấn mảng mà không cần bung

Nhiều câu hỏi trả lời được ngay trên mảng bằng hàm mảng và subquery trên UNNEST, khỏi bung ra ở cấp truy vấn ngoài:

SELECT
  customer_id,
  ARRAY_LENGTH(transactions)                              AS so_giao_dich,
  -- subquery tương quan chạy trên mảng của chính hàng này
  (SELECT SUM(amount)
   FROM UNNEST(transactions)
   WHERE channel = 'ATM')                                 AS tong_rut_atm,
  (SELECT COUNT(*)
   FROM UNNEST(transactions) t
   WHERE t.amount >= 100000000)                           AS so_gd_lon
FROM banking.customers_nested
WHERE EXISTS (SELECT 1 FROM UNNEST(transactions) t
              WHERE t.channel = 'BRANCH');                 -- lọc hàng cha theo điều kiện trên mảng

Ở đây mỗi subquery UNNEST(transactions) chỉ chạy cục bộ trong phạm vi một hàng cha — không có join toàn cục, không shuffle. Đây là điểm khác biệt hiệu năng cốt lõi so với việc tách transactions ra bảng riêng rồi GROUP BY customer_id.

Ảnh hưởng hiệu năng — tránh shuffle từ JOIN

Khi bạn giữ chuẩn hoá và join, query plan điển hình có một stage JOIN với shuffleOutputBytes lớn: dữ liệu hai bảng bị đẩy qua mạng để gom theo khoá. Nếu phân bố khoá lệch (một khách hàng có cực nhiều giao dịch), bạn còn gặp data skew — biểu hiện ở chênh lệch lớn giữa computeMsAvg và computeMsMax giữa các worker, và có thể shuffleOutputBytesSpilled > 0.

Với mô hình lồng, UNNEST thay thế JOIN bằng thao tác nội bộ hàng:

Lưu ý thực tế: nếu cả hai phía join đều nhỏ (ví dụ bảng tra cứu vài nghìn hàng), BigQuery dùng broadcast join — bảng nhỏ được phát tới mọi worker, cũng không shuffle bảng lớn. Denormalize phát huy nhất khi phía "nhiều" thực sự lớn và luôn đi kèm hàng cha. Xem thêm cơ chế đọc shuffleOutputBytes, skew avg-vs-max trong bài tối ưu.

Use case thực tế

Đội phân tích rủi ro NCB cần báo cáo hằng ngày: với ~8 triệu khách hàng, mỗi khách trung bình 30 giao dịch/tháng (~240 triệu dòng giao dịch), tính tổng số tiền rút ATM và số giao dịch trên 100 triệu đồng theo từng khách.

• Cách cũ (chuẩn hoá): join customers (8 triệu hàng) với transactions (240 triệu hàng) theo customer_id, rồi GROUP BY. Stage JOIN đẩy hàng trăm GB qua shuffle; những "khách hàng doanh nghiệp" có hàng chục nghìn giao dịch gây skew, một vài worker chạy lâu gấp nhiều lần trung bình (computeMsMax >> computeMsAvg), thỉnh thoảng spill.
• Cách lồng: dựng bảng customers_nested với transactions ARRAY<STRUCT>. Báo cáo trở thành subquery SELECT SUM(amount) FROM UNNEST(transactions) WHERE channel='ATM' — chạy cục bộ trên mỗi hàng, không stage JOIN, không shuffle cho phần tổng hợp. Truy vấn chỉ đọc đúng cột transactions.amount và transactions.channel nhờ lưu trữ cột, nên totalBytesProcessed (và do đó chi phí on-demand) giảm rõ so với việc quét hai bảng phẳng.

Đánh đổi: bảng lồng lớn hơn về lưu trữ và khó cập nhật giao dịch lẻ; đội chấp nhận vì đây là bảng phân tích chỉ nạp mới theo ngày, gần như không sửa tại chỗ.

Ghi nhớ

• STRUCT = RECORD (gộp trường), ARRAY = REPEATED (nhiều giá trị/hàng); lồng lại thành ARRAY<STRUCT> để nhúng bảng con vào hàng cha.
• BigQuery khuyến khích denormalize thành cột lồng thay vì tách bảng, vì JOIN nhiều bảng phải shuffle qua mạng — pha tốn kém nhất, còn UNNEST là thao tác cục bộ trong hàng.
• Truy cập STRUCT bằng dấu chấm (address.city); bung ARRAY bằng UNNEST trong FROM (correlated cross join ngầm).
• Nhiều tổng hợp làm được ngay bằng subquery trên UNNEST(mảng) hoặc hàm mảng (ARRAY_LENGTH, EXISTS), khỏi bung ở cấp ngoài — giữ tính cục bộ.
• Đọc query plan: JOIN chuẩn hoá có shuffleOutputBytes cao và dễ skew (chênh lệch computeMsAvg vs computeMsMax); mô hình lồng loại bỏ shuffle cho phần nối.
• Đánh đổi: tốn lưu trữ hơn, cập nhật lẻ khó hơn — hợp dữ liệu thiên đọc/phân tích, ít sửa tại chỗ.
• Nếu một phía join nhỏ, BigQuery tự dùng broadcast join; denormalize có lợi nhất khi phía "nhiều" thật sự lớn.

Nguồn tham khảo

• BigQuery — Nested & repeated data: cloud.google.com/bigquery/docs/nested-repeated
• BigQuery — Query plan and timeline (execution details): cloud.google.com/bigquery/docs/query-plan-explanation
• BigQuery — Best practices, performance overview: cloud.google.com/bigquery/docs/best-practices-performance-overview
• BigQuery documentation (overview): cloud.google.com/bigquery/docs
• "Dremel: Interactive Analysis of Web-Scale Datasets" — Melnik et al., VLDB 2010
• "Google BigQuery: The Definitive Guide" — Lakshmanan & Tigani, O'Reilly