Google BigQuery - 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
Chào anh em! Qua Bài học 13, chúng ta đã thu phục thành công những cục JSON vô tổ chức từ API và NoSQL đổ về. Tuy nhiên, khi đào sâu vào tầng dữ liệu thô (Raw Logs) của hệ thống — chẳng hạn như log truy cập Nginx, log lỗi ứng dụng từ Sentry, hay chuỗi URL yêu cầu từ phía Client — bạn sẽ nhận ra một sự thật phũ phàng: Dữ liệu văn bản (String) luôn là thứ hỗn độn nhất trên đời!
Một chuỗi User Agent dài lê thê chứa đủ mọi thông tin về trình duyệt, một đường dẫn URL phân mảnh chứa các tham số truy vấn (utm_source, order_id), hay một thông báo lỗi chứa mã code ngẫu nhiên nằm ở giữa câu. Làm sao để bóc tách, lọc và trích xuất những thông tin này từ hàng trăm triệu dòng log mà không làm server "gục ngã"?
Hôm nay, chúng ta sẽ mổ xẻ nghệ thuật xử lý chuỗi và làm chủ biểu thức chính quy (Regular Expressions - Regex) trong GoogleSQL, đồng thời điểm mặt những cạm bẫy chí mạng có thể "đốt sạch" ví tiền của bạn!
1. Cơ bản trước, Regex sau: Khi nào dùng hàm chuỗi thông thường?
Nhiều lập trình viên khi mới học Regex thường có thói quen "cầm dao mổ trâu để giết gà": Dùng biểu thức chính quy phức tạp cho cả những việc cực kỳ đơn giản mà các hàm xử lý chuỗi nguyên bản (Native String Functions) có thể làm tốt hơn gấp nhiều lần.
Trong GoogleSQL, thư viện hàm chuỗi cực kỳ phong phú và được tối ưu hóa phần cứng để chạy siêu tốc trên các cột dạng Capacitor:
-
CONCAT(str1, str2, ...)hoặc toán tử||: Nối chuỗi (ví dụ:first_name || ' ' || last_name). -
SUBSTR(str, position, length): Cắt chuỗi con. -
STRPOS(str, substring)/CONTAINS_SUBSTR(str, substring): Tìm kiếm vị trí hoặc kiểm tra xem chuỗi có chứa từ khóa hay không. -
SPLIT(str, delimiter): Tách chuỗi thành mộtARRAY<STRING>dựa trên ký tự phân cách (Cực kỳ hữu ích khi tách đường dẫn URL theo dấu/!). -
LOWER()/UPPER()/TRIM(): Chuẩn hóa định dạng chữ hoa/thường và cắt khoảng trắng thừa.
Quy tắc vàng về hiệu năng: Nếu bạn chỉ cần kiểm tra xem một đường dẫn URL có chứa từ khóa
checkouthay không, hãy dùngCONTAINS_SUBSTR(url, 'checkout')thay vì viết một biểu thức Regex dài dòng. Các hàm tìm kiếm chuỗi thô luôn tiêu tốn ít chu kỳ CPU (Slot time) hơn rất nhiều so với máy quét Regex!
2. Vũ khí hạng nặng: Regular Expressions (Regex) trong GoogleSQL
Khi các hàm chuỗi thông thường đầu hàng trước những định dạng biến đổi linh hoạt (ví dụ: tìm tất cả các chuỗi có dạng mã đơn hàng ORD-2026-XXXXX nằm ẩn đâu đó trong một câu log dài), đó là lúc Regex lên tiếng.
Điểm đặc biệt của Regex trong GoogleSQL: Sử dụng động cơ RE2
Khác với các ngôn ngữ lập trình như PHP hay JavaScript (thường dùng động cơ PCRE hỗ trợ các tính năng phức tạp như backreferences \1), GoogleSQL sử dụng động cơ RE2 do chính Google phát triển.
-
Ưu điểm cực lớn: RE2 được thiết kế để chạy trong thời gian tuyến tính (Linear Time). Nó triệt tiêu hoàn toàn hiện tượng Catastrophic Backtracking – nguyên nhân khiến các câu lệnh Regex viết lỗi làm treo cứng CPU hoặc gây lỗi bảo mật ReDoS trong các ứng dụng Backend truyền thống.
-
Lưu ý cú pháp: Trong GoogleSQL, để tránh việc phải "gấp đôi" dấu gạch chéo ngược (escaped backslashes) rườm rà, bạn nên sử dụng Raw Strings bằng cách đặt tiền tố
rtrước chuỗi biểu thức (Ví dụ:r'\d{3}-\d{4}').
3. Bộ tứ hàm Regex "sống còn" trong BigQuery
Hãy cùng điểm qua 4 hàm Regex mạnh mẽ nhất mà một Data Engineer phải bỏ túi:
3.1. REGEXP_CONTAINS(text, r'pattern') – Bộ lọc điều kiện (BOOLEAN)
Dùng trong mệnh đề WHERE để kiểm tra xem một chuỗi có khớp với mẫu Regex hay không. Trả về TRUE hoặc FALSE.
-
Ví dụ: Lọc ra tất cả các dòng log có chứa mã lỗi HTTP 5xx:
SQL
SELECT * FROM `my-project.logs.nginx_logs` WHERE REGEXP_CONTAINS(request_uri, r'HTTP/1\.[01]\" 5\d{2}');
3.2. REGEXP_EXTRACT(text, r'pattern') – Trích xuất nhóm cụ thể (STRING)
Dùng để bắt và lấy ra đúng đoạn chuỗi khớp với một Nhóm chụp (Capture Group – đặt trong dấu ngoặc đơn (...)) bên trong biểu thức.
-
Ví dụ: Trích xuất mã định danh đơn hàng có dạng
ORD-xxxxtừ một câu log mô tả lộn xộn:SQL
SELECT log_message, REGEXP_EXTRACT(log_message, r'order_id=([A-Z0-9-]+)') AS extracted_order_id FROM `my-project.logs.app_logs` WHERE REGEXP_CONTAINS(log_message, r'order_id=');
3.3. REGEXP_EXTRACT_ALL(text, r'pattern') – Trích xuất toàn bộ danh sách (ARRAY)
Nếu trong một chuỗi văn bản có xuất hiện nhiều đoạn khớp với mẫu Regex (ví dụ: tìm tất cả các địa chỉ IP hoặc tất cả các hashtag # trong một bài viết), hàm này sẽ gom toàn bộ kết quả trả về dưới dạng một ARRAY<STRING>.
3.4. REGEXP_REPLACE(text, r'pattern', 'replacement') – Thay thế và làm sạch dữ liệu
Cực kỳ hữu ích trong việc chuẩn hóa văn bản hoặc che giấu thông tin nhạy cảm (Data Masking) trước khi bàn giao bảng dữ liệu cho các phòng ban khác.
-
Ví dụ: Che một phần số điện thoại hoặc mã thẻ tín dụng trong log khách hàng:
SQL
SELECT REGEXP_REPLACE(user_input_note, r'\d{4}-\d{4}-\d{4}-\d{4}', 'XXXX-XXXX-XXXX-XXXX') AS masked_note FROM `my-project.customer.support_tickets`;
4. Cạm bẫy hiệu năng: Khi Regex "đốt tiền" của bạn trên dữ liệu lớn
Vì Regex phải thực hiện các thuật toán phân tích ký tự phức tạp trên từng dòng dữ liệu, việc sử dụng hàm Regex không đúng cách trên bảng hàng trăm triệu dòng sẽ biến câu lệnh của bạn thành một "cơn ác mộng chi phí". Hãy ghi nhớ 3 quy tắc tối ưu sau:
Quy tắc 1: Đừng bao giờ đặt Regex ở điều kiện quét đầu tiên (Full Table Scan)
Nếu bạn viết một câu lệnh:
SQL
-- CẢNH BÁO: CÂU LỆNH NÀY SẼ QUÉT TOÀN BỘ BẢNG!
SELECT COUNT(*)
FROM `my-project.core_dwh.huge_logs`
WHERE REGEXP_CONTAINS(payload, r'error_code=500');
-
Hậu quả: Dremel buộc phải thò tay đọc toàn bộ các khối dữ liệu của cột
payloadtrên Colossus, sau đó cho hàng ngàn Slots chạy thuật toán Regex trên từng dòng một. Bảng nặng 1 TB thì bạn sẽ mất tiền quét đúng 1 TB! -
Cách khắc phục: Luôn kết hợp với Partition Filter (lọc theo ngày tháng trước) hoặc điều kiện
WHEREtrên các cột số nguyên/index trước, sau đó mới dùng Regex thu hẹp ở bước lọc cuối cùng:SQL
SELECT COUNT(*) FROM `my-project.core_dwh.huge_logs` WHERE DATE(created_at) = '2026-10-18' -- Lọc Partition trước để cắt giảm 99% dữ liệu quét! AND status_code = 500 AND REGEXP_CONTAINS(payload, r'critical_exception');
Quy tắc 2: Tối ưu biểu thức Regex (Tránh ký tự đại diện mở rộng .*)
Biểu thức .* (dấu chấm kèm dấu sao) có nghĩa là "khớp với bất kỳ ký tự nào, bao nhiêu lần tùy ý". Khi bạn đặt .* ở đầu và cuối biểu thức (ví dụ: r'.*user_id.*'), động cơ RE2 sẽ phải thử thách qua rất nhiều bước hoán vị ký tự để tìm khớp. Hãy cố gắng định nghĩa ranh giới cụ thể cho biểu thức của bạn (ví dụ dùng r'user_id=([^&]+)' thay vì dùng .* lan man).
Quy tắc 3: Cân nhắc sử dụng Search Indexes thay cho Regex khi tìm kiếm từ khóa
Như đã hé lộ ở Bài 13, nếu mục đích của bạn chỉ là tìm kiếm các dòng chứa một từ khóa văn bản bất kỳ bên trong một cột lớn, hãy tạo Search Index và sử dụng hàm SEARCH() thay vì dùng REGEXP_CONTAINS. Search Index tận dụng cấu trúc chỉ mục đảo ngược (Inverted Index) giúp nhảy thẳng đến đích mà không cần duyệt qua từng ký tự của chuỗi!
Tổng kết Bài học 14
Xử lý chuỗi và biểu thức chính quy là kỹ năng không thể thiếu để biến những đống log thô ráp thành tri thức sạch sẽ trong BigQuery:
-
Ưu tiên hàm chuỗi nguyên bản: Dùng
CONTAINS_SUBSTR,SPLIT,SUBSTRtrước khi nghĩ đến Regex để tiết kiệm tài nguyên CPU. -
Làm chủ bộ tứ hàm Regex:
REGEXP_CONTAINS,REGEXP_EXTRACT,REGEXP_EXTRACT_ALL, vàREGEXP_REPLACEtrên nền tảng động cơ RE2 an toàn, tuyến tính. -
Phòng thủ chi phí: Luôn lọc Partition và index trước khi áp dụng Regex để tránh quét toàn bộ bảng và đốt ngân sách vô ích.
Tuy nhiên, trong các hệ thống doanh nghiệp hiện đại, dữ liệu không chỉ đứng yên một chỗ. Dữ liệu thay đổi liên tục theo thời gian thực (Time-series) và đòi hỏi chúng ta phải tính toán xu hướng, so sánh kỳ trước kỳ này hay chạy các cửa sổ trượt (Sliding Windows).
Làm thế nào để làm chủ các hàm phân tích theo thời gian và cửa sổ trượt đỉnh cao trong GoogleSQL? Hãy đón xem Bài học 15: Xử lý Thời gian (Time-series) – Phân biệt TIMESTAMP, DATETIME, DATE và bài toán lệch múi giờ!
All rights reserved
