Đã đăng vào khoảng 5 giờ trước 8 phút đọc

Google BigQuery - Bài học 21: User-Defined Functions (UDFs) bằng SQL & JavaScript – Viết hàm xử lý logic tùy chỉnh ngay trong câu truy vấn

Chào anh em! Qua Bài học 20, chúng ta đã biết cách đóng gói các kịch bản ETL phức tạp thành các Stored Procedures và thiết lập "túi khí an toàn" với khối BEGIN...EXCEPTION.

Tuy nhiên, trong quá trình viết các câu lệnh truy vấn báo cáo, đôi khi bạn sẽ gặp phải những tình huống oái oăm: Có một đoạn logic nghiệp vụ cực kỳ phức tạp (ví dụ: thuật toán băm mật khẩu, hàm giải mã chuỗi định dạng độc quyền từ hệ thống cũ, hoặc xử lý biểu thức toán học rắc rối) mà các hàm nguyên bản (Built-in functions) của GoogleSQL không hỗ trợ sẵn.

Nếu bắt buộc phải viết lại đoạn logic đó bằng thuần SQL, câu lệnh của bạn sẽ phình to thành hàng trăm dòng code lằng nhằng, khó đọc và không thể tái sử dụng.

Giải pháp đỉnh cao của GoogleSQL cho bài toán này chính là User-Defined Functions (UDFs) – cho phép bạn tự định nghĩa hàm riêng bằng cả thuần SQL hoặc nhúng thẳng mã nguồn JavaScript ngay bên trong câu truy vấn! Hãy cùng mổ xẻ cách sử dụng cặp bài trùng này nhé!

1. SQL UDFs – Hàm tự định nghĩa bằng thuần GoogleSQL

Nếu logic tùy chỉnh của bạn có thể được giải quyết bằng các toán tử và hàm sẵn có của SQL nhưng cần được gói gọn lại cho sạch sẽ và dễ gọi đi gọi lại, SQL UDF là lựa chọn đầu tiên.

Ví dụ thực chiến: Viết hàm tính giá tiền sau khi áp dụng thuế và chiết khấu linh hoạt

Giả sử công ty bạn có một công thức tính giá sau thuế khá phức tạp dựa trên danh mục sản phẩm:

SQL

-- Tạo hoặc thay thế một SQL UDF tạm thời (hoặc lưu vĩnh viễn trong Dataset)
CREATE OR REPLACE FUNCTION `my-project.mart_sales.calculate_final_price`(
    base_price NUMERIC, 
    discount_rate FLOAT64, 
    is_vip BOOL
) 
RETURNS NUMERIC AS (
    -- Thân hàm là một biểu thức SQL trả về kết quả trực tiếp
    CASE 
        WHEN is_vip THEN (base_price * (1 - discount_rate)) * 0.9 -- Giảm thêm 10% cho khách VIP
        ELSE base_price * (1 - discount_rate)
    END
);

Cách sử dụng trong câu truy vấn:

Bây giờ, bạn có thể gọi hàm vừa tạo y hệt như một hàm có sẵn (SUM, COUNT) của hệ thống:

SQL

SELECT 
    product_id,
    unit_price,
    `my-project.mart_sales.calculate_final_price`(unit_price, 0.15, TRUE) AS final_vip_price
FROM `my-project.core_dwh.products`;

2. JavaScript UDFs – Khi SQL "bó tay", JavaScript lên tiếng!

Có những bài toán mà viết bằng SQL cực kỳ khó hoặc bất khả thi, chẳng hạn như:

Phân tích cú pháp các chuỗi định dạng độc quyền (Custom string parsing) đòi hỏi vòng lặp (for), mảng (array), và điều kiện phức tạp.
Sử dụng các thư viện xử lý chuỗi / mã hóa có sẵn trong hệ sinh thái JavaScript.
Xử lý các đối tượng JSON lồng nhau cực kỳ phức tạp mà cú pháp JSON thông thường của SQL không với tới.

GoogleSQL cho phép bạn viết thân hàm hoàn toàn bằng JavaScript, chạy trực tiếp trên động cơ V8 Engine được nhúng ngay bên trong các nút tính toán (Dremel slots) của BigQuery!

Cú pháp JavaScript UDF chuẩn mực:

SQL

CREATE OR REPLACE FUNCTION `my-project.utils.parse_custom_user_agent`(ua_string STRING)
RETURNS STRUCT<browser STRING, os STRING, device_type STRING>
LANGUAGE js AS """
    // Đoạn code JavaScript thuần túy chạy trực tiếp bên trong BigQuery!
    let browser = "Unknown";
    let os = "Unknown";
    let device = "Desktop";

    if (!ua_string) return { browser: browser, os: os, device_type: device };

    if (ua_string.includes("Mobile")) {
        device = "Mobile";
    }
    if (ua_string.includes("Chrome")) {
        browser = "Chrome";
    } else if (ua_string.includes("Safari")) {
        browser = "Safari";
    }

    if (ua_string.includes("Windows")) {
        os = "Windows";
    } else if (ua_string.includes("Mac OS")) {
        os = "MacOS";
    } else if (ua_string.includes("Android")) {
        os = "Android";
    }

    // Trả về kết quả dưới dạng một Object khớp với cấu trúc STRUCT đã định nghĩa ở phần RETURNS
    return { browser: browser, os: os, device_type: device };
""";

Gọi hàm JavaScript UDF trong SQL:

SQL

SELECT 
    user_agent_raw,
    `my-project.utils.parse_custom_user_agent`(user_agent_raw).browser AS detected_browser,
    `my-project.utils.parse_custom_user_agent`(user_agent_raw).device_type AS detected_device
FROM `my-project.logs.web_traffic`
LIMIT 100;

3. Persistent UDFs (Hàm vĩnh viễn) vs Temporary UDFs (Hàm tạm thời)

Trong BigQuery, bạn có thể tạo UDF theo 2 hình thức tùy thuộc vào mục đích sử dụng:

Temporary UDFs (Hàm tạm thời):
- Chỉ tồn tại trong suốt phiên làm việc (session) của câu lệnh hiện tại.
- Không cần chỉ định project hay dataset, bắt đầu bằng từ khóa CREATE TEMP FUNCTION.
- Phù hợp cho: Các đoạn logic ngắn dùng nhanh trong một báo cáo độc lập.
SQL
```
CREATE TEMP FUNCTION MultiplyByTwo(x INT64) RETURNS INT64 AS (x * 2);
SELECT MultiplyByTwo(5); -- Trả về 10
```
Persistent UDFs (Hàm vĩnh viễn):
- Được lưu trữ trực tiếp bên trong một Dataset cụ thể (ví dụ my-project.utils.my_function).
- Tồn tại vĩnh viễn, có thể được gọi bởi bất kỳ ai có quyền truy cập Dataset đó, dùng chung cho toàn bộ công ty.
- Phù hợp cho: Các tiêu chuẩn nghiệp vụ cốt lõi (hàm tính thuế, hàm giải mã mã định danh chung, hàm chuẩn hóa số điện thoại theo chuẩn Việt Nam).

4. Cạm bẫy hiệu năng: Khi nào KHÔNG NÊN dùng JS UDF?

Mặc dù JavaScript UDF cực kỳ mạnh mẽ và uyển chuyển, nhưng dưới góc độ của một Data Engineer, bạn phải cực kỳ cẩn trọng khi sử dụng chúng trên quy mô dữ liệu lớn:

Chi phí hiệu năng của V8 Engine: Mặc dù Google đã tối ưu hóa rất tốt, việc gọi một hàm JavaScript thực thi trên từng dòng dữ liệu (Row-by-row execution) sẽ chậm hơn đáng kể so với việc sử dụng các hàm Native (hàm có sẵn bằng mã máy của GoogleSQL).
Quy tắc vàng:
- Nếu bài toán có thể giải quyết bằng các hàm có sẵn của GoogleSQL (REGEXP_EXTRACT, JSON_VALUE, SUBSTR), hãy ưu tiên dùng hàm có sẵn.
- Chỉ dùng JS UDF khi logic quá phức tạp, bắt buộc phải dùng vòng lặp for/while hoặc cấu trúc điều kiện rắc rối mà SQL thuần không thể viết nổi.
- Luôn lọc dữ liệu (WHERE / Partition Filter) để thu hẹp tập dữ liệu xuống mức nhỏ nhất trước khi truyền vào hàm JS UDF, tránh việc bắt động cơ V8 phải xử lý hàng tỷ dòng rác không cần thiết!

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

User-Defined Functions (UDFs) mang đến sự tự do tuyệt đối cho lập trình viên khi làm việc với GoogleSQL:

SQL UDFs: Đóng gói các biểu thức tính toán phức tạp thành một hàm thuần SQL gọn gàng, dễ bảo trì.
JavaScript UDFs: Nhúng thẳng mã nguồn V8 JavaScript vào câu truy vấn để giải quyết các bài toán phân tích chuỗi, cấu trúc dữ liệu hoặc thuật toán độc quyền.
Phân loại linh hoạt: Dùng CREATE TEMP FUNCTION cho các tác vụ nhanh, hoặc lưu vĩnh viễn trong Dataset qua CREATE FUNCTION để chia sẻ cho toàn doanh nghiệp.

Tuy nhiên, trong các hệ thống kiến trúc hiện đại, đôi khi logic nghiệp vụ không nằm trực tiếp trong cơ sở dữ liệu mà nằm ở các Microservices bên ngoài (như gọi API xác thực danh tính, tính toán phí vận chuyển thời gian thực từ đối tác bên thứ ba). Chẳng lẽ mỗi lần query lại phải đổ dữ liệu ra ngoài rồi gọi API?

BigQuery cho phép bạn gọi thẳng một API bên ngoài từ bên trong câu lệnh SQL thông qua Remote Functions!

Làm thế nào để tích hợp Cloud Run / Cloud Functions vào trong câu lệnh SQL của BigQuery? Hãy đón xem Bài học 22: Remote Functions – Gọi trực tiếp API bên ngoài từ trong câu lệnh SQL!

bigquery