Google BigQuery - Bài học 13: Xử lý kiểu dữ liệu JSON nguyên bản (Native JSON) – Truy xuất, đánh index và tối ưu hóa payload từ API/NoSQL đổ về

Chào anh em! Qua các bài học trước, chúng ta đã làm chủ các cấu trúc dữ liệu có schema rõ ràng từ phẳng (Flat Tables) cho đến lồng nhau (ARRAY và STRUCT). Nhưng trong thế giới Backend thực chiến, cuộc sống không phải lúc nào cũng màu hồng và sạch sẽ như vậy.

Hãy tưởng tượng cảnh đội ngũ Mobile App bắn Webhook về hệ thống, hoặc bạn đồng bộ dữ liệu từ các NoSQL database (như MongoDB, DynamoDB) sang BigQuery. Đội ngũ bên nguồn cứ vô tư đẩy lên những cục Payload JSON khổng lồ, hỗn loạn, không có Schema cố định, nay thêm một trường, mai bớt một trường.

Trước đây, khi BigQuery chưa hỗ trợ kiểu Native JSON, anh em Backend phải lưu chúng dưới dạng chuỗi văn bản thô (STRING) rồi mỗi lần query lại phải cắn răng dùng các hàm cắt chuỗi thủ công vừa chậm vừa tốn CPU. May mắn thay, Google đã ra mắt kiểu dữ liệu Native JSON để giải cứu chúng ta.

Hôm nay, hãy cùng mổ xẻ cách thu phục những cục JSON cứng đầu này ngay bên trong BigQuery!

1. Sự tiến hóa: Lưu JSON dạng STRING truyền thống vs Kiểu JSON nguyên bản

Cách làm cũ (Lưu dạng STRING):

• Cột payload được khai báo là kiểu STRING. Khi bạn muốn lấy giá trị user_id bên trong chuỗi JSON {"user_id": 123, "event": "click"}, bạn phải gọi hàm:

  SQL

  SELECT JSON_EXTRACT_SCALAR(payload, '$.user_id') FROM my_table;
  
  

• Điểm yếu chí mạng:

  1. Mỗi lần chạy query, Dremel phải dùng CPU đi parse lại từ đầu toàn bộ chuỗi văn bản đó dưới dạng text. Cực kỳ tốn tài nguyên (Slot time).

  2. Không có cơ chế kiểm tra tính hợp lệ (Validation). Ai lỡ tay đẩy một chuỗi rác không phải JSON vào, câu lệnh sau đó sẽ lăn ra chết vì lỗi cú pháp.

Kiểu dữ liệu Native JSON hiện đại:

• BigQuery cung cấp kiểu dữ liệu JSON độc lập. Khi bạn nạp dữ liệu vào, BigQuery sẽ tự động phân tích và lưu trữ nó dưới một định dạng nhị phân tối ưu hóa (Optimized binary representation).

• Lợi ích vượt trội: Tốc độ truy xuất nhanh hơn gấp nhiều lần, tự động từ chối các chuỗi JSON sai định dạng ngay từ khâu INSERT, và hỗ trợ cú pháp truy xuất cực kỳ sang chảnh!

2. Cú pháp chấm điểm thần thánh (JSON Dot Notation)

Với kiểu dữ liệu JSON nguyên bản trong GoogleSQL, bạn không cần phải nhớ các hàm JSON_EXTRACT lằng nhằng nữa. Bạn có thể dùng trực tiếp dấu chấm (.) giống hệt như khi đang code JavaScript hay Python ở backend!

Giả sử chúng ta có một bảng webhook_logs chứa một cột kiểu JSON tên là event_data với cấu trúc như sau:

JSON

{
  "event_type": "purchase",
  "user": {
    "id": 8899,
    "email": "hieu@example.com"
  },
  "items": [
    {"sku": "SKU-01", "qty": 2},
    {"sku": "SKU-02", "qty": 1}
  ]
}

Cách truy xuất các trường bên trong JSON:

SQL

SELECT 
    -- 1. Truy xuất trường cấp 1
    event_data.event_type AS event,
    
    -- 2. Truy xuất trường lồng nhau bằng dấu chấm (Dot notation)
    event_data.user.id AS user_id,
    event_data.user.email AS user_email,
    
    -- 3. Truy xuất phần tử trong mảng JSON bằng chỉ mục (Index)
    event_data.items[0].sku AS first_item_sku
FROM `my-project.logs.webhook_logs`;

Điểm khác biệt tinh tế cần lưu ý:

• Khi bạn dùng dấu chấm (event_data.user.id), BigQuery trả về kết quả dưới dạng một JSON scalar (có thể là số, chữ hoặc boolean tùy thuộc vào giá trị bên trong).

• Nếu bạn muốn ép kiểu kết quả trả về này thành các kiểu dữ liệu chuẩn của BigQuery (ví dụ chuyển thành INT64 hoặc STRING để đem đi JOIN hoặc GROUP BY), hãy bọc nó trong hàm SAFE_CAST():

  SQL

  SELECT SAFE_CAST(event_data.user.id AS INT64) AS user_id
  FROM `my-project.logs.webhook_logs`;
  
  

  (Sử dụng SAFE_CAST thay vì CAST thông thường giúp câu lệnh không bị sập nguồn khi gặp một vài dòng dữ liệu JSON có trường id bị lỗi định dạng thành chuỗi chữ).

3. Xử lý mảng JSON (JSON Arrays) với UNNEST

Điều gì xảy ra nếu mảng items trong JSON của bạn có kích thước động (có thể chứa 1, 5 hoặc 10 sản phẩm khác nhau) và bạn muốn GROUP BY theo từng sku để tính tổng số lượng bán ra?

Đúng vậy, chúng ta lại kết hợp với toán tử UNNEST() mà chúng ta đã học ở Bài 12! Tuy nhiên, với kiểu JSON, bạn phải dùng hàm JSON_QUERY_ARRAY() để chuyển mảng JSON thành một tập hợp mảng chuẩn trước khi UNNEST:

SQL

SELECT 
    item.sku,
    COUNT(*) AS total_events,
    SUM(SAFE_CAST(item.qty AS INT64)) AS total_qty
FROM `my-project.logs.webhook_logs`,
-- Trải phẳng mảng JSON items ra thành các dòng độc lập
UNNEST(JSON_QUERY_ARRAY(event_data.items)) AS raw_item,
-- Ép kiểu cục JSON con thành dạng cấu trúc để dễ chấm gọi
LATERAL (SELECT JSON_VALUE(raw_item, '$.sku') AS sku, JSON_VALUE(raw_item, '$.qty') AS qty) AS item
GROUP BY item.sku;

4. Tăng tốc độ truy vấn JSON bằng Search Indexes

Một trong những lý do khiến nhiều kỹ sư e ngại khi dùng cột JSON là sợ hiệu năng tìm kiếm bị chậm khi bảng phình to lên hàng trăm triệu dòng. Nếu bạn viết câu lệnh tìm kiếm:

SQL

SELECT * FROM `webhook_logs` 
WHERE JSON_VALUE(event_data.user.email) = 'hieu@example.com';

Nếu không có cơ chế hỗ trợ, Dremel sẽ phải quét toàn bộ bảng và duyệt qua từng dòng JSON trên ổ đĩa (Full Table Scan).

Để giải quyết triệt để bài toán này, BigQuery cung cấp tính năng Search Indexes – cho phép bạn đánh index trực tiếp lên cột JSON hoặc toàn bộ các trường bên trong JSON:

SQL

-- Tạo Search Index cho toàn bộ cột JSON event_data
CREATE SEARCH INDEX idx_webhook_json 
ON `my-project.logs.webhook_logs`(event_data);

Sức mạnh của Search Index đối với JSON:

Khi bạn đã tạo Search Index, bất cứ khi nào bạn sử dụng hàm tìm kiếm chuỗi hoặc các toán tử kiểm tra khóa bên trong JSON (như hàm CONTAINS_KEY hoặc tìm kiếm giá trị), BigQuery sẽ tự động kích hoạt Index để nhảy thẳng đến các dòng chứa dữ liệu cần tìm (giống như B-Tree trong MySQL nhưng hoạt động trên quy mô Cloud Data Warehouse), giúp thời gian truy vấn giảm từ vài phút xuống còn vài mili-giây!

5. Mẹo thực chiến: Khi nào nên dùng Native JSON thay vì STRUCT?

Đến đây, chắc hẳn anh em sẽ thắc mắc: "Cả STRUCT lẫn Native JSON đều dùng để chứa dữ liệu lồng nhau, vậy khi nào nên dùng cái nào?"

Lời khuyên từ chuyên gia: Nếu dữ liệu của bạn nằm ở tầng Bronze (Raw Data) nhận trực tiếp từ API/NoSQL, hãy lưu dưới dạng JSON để linh hoạt đối phó với việc thay đổi schema. Sau đó, ở tầng Silver/Gold, hãy dùng lệnh SAFE_CAST hoặc phân tách chúng thành các kiểu dữ liệu chuẩn (STRING, INT64, STRUCT) để tối ưu hóa hiệu năng và chi phí quét truy vấn!

Tổng kết Bài học 13

Kiểu dữ liệu Native JSON trong GoogleSQL là cầu nối hoàn hảo giúp BigQuery dung hòa giữa thế giới NoSQL linh hoạt và thế giới Data Warehouse cấu trúc chặt chẽ:

1. Thay thế hoàn toàn chuỗi STRING cũ kỹ: Lưu trữ dạng binary tối ưu, hỗ trợ validate dữ liệu đầu vào.

2. Cú pháp Dot Notation trực quan: Dùng dấu chấm (.) để truy xuất dữ liệu lồng nhau mượt mà như code ứng dụng.

3. Đánh Index tăng tốc: Dùng CREATE SEARCH INDEX để biến việc tìm kiếm bên trong các payload JSON hỗn loạn trở nên chớp nhoáng.

Tuy nhiên, trong quá trình xử lý payload từ API hoặc log hệ thống đổ về, dữ liệu văn bản (String) thường hay xuất hiện các khoảng trắng thừa, định dạng lộn xộn hoặc cần phải cắt gọt, tìm kiếm bằng biểu thức chính quy (Regex).

Làm sao để làm chủ nghệ thuật xử lý chuỗi và Regex thượng thừa trong GoogleSQL? Hãy đón xem Bài học 14: Xử lý chuỗi và biểu thức chính quy – Tối ưu REGEXP_CONTAINS, REGEXP_EXTRACT và các hàm tìm kiếm văn bản!