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

Google BigQuery - Bài học 20: Đóng gói Stored Procedure và Xây dựng "Áo giáp" chống lỗi ETL

Chào anh em! Ở Bài học 19, chúng ta đã biết cách "lập trình hóa" BigQuery bằng các câu lệnh Procedural Scripting như khai báo biến (DECLARE), rẽ nhánh (IF/ELSE) và vòng lặp (WHILE).

Tuy nhiên, kịch bản script đó mới chỉ chạy trực tiếp một lần rồi trôi đi. Trong một hệ thống vận hành thực tế (Production), khi bạn có hàng chục luồng ETL (Extract - Transform - Load) chạy tự động mỗi đêm, chuyện gì sẽ xảy ra nếu vào lúc 2h sáng, một bước xử lý dữ liệu bị lỗi (ví dụ: bảng nguồn bị khóa, định dạng file thay đổi, hoặc tràn dữ liệu)?

Nếu không có cơ chế bắt lỗi, toàn bộ pipeline sẽ sập nguồn câm lặng, đội ngũ trực vận hành không hề hay biết cho đến khi sáng ra sếp gọi điện phàn nàn vì Dashboard trống trơn.

Bài học hôm nay sẽ giúp anh em giải quyết triệt để bài toán này bằng cách đóng gói logic vào các Stored Procedures và xây dựng "áo giáp" chống sập nguồn với khối BEGIN...EXCEPTION (tương tự cơ chế Try/Catch trong các ngôn ngữ lập trình Backend).

1. Stored Procedures là gì? Tại sao cần đóng gói thành Procedure?

Trong các hệ quản trị cơ sở dữ liệu như MySQL hay PostgreSQL, Stored Procedure đã quá quen thuộc. Trên Google BigQuery, Stored Procedure là một tập hợp các câu lệnh Procedural SQL được biên dịch và lưu trữ trực tiếp bên trong một Dataset.

Lợi ích tuyệt vời khi đưa code ETL vào Stored Procedure:

Tái sử dụng code (Reusability): Thay vì copy-paste một đoạn code SQL dài dằng dặc vào nhiều công cụ lập lịch khác nhau, bạn chỉ cần gọi một câu lệnh duy nhất: CALL my-project.dataset.sp_run_daily_etl()``.
Đóng gói bảo mật (Encapsulation & Security): Bạn có thể cấp quyền cho một tài khoản hoặc một dịch vụ bên ngoài được phép gọi (EXECUTE) Stored Procedure đó, nhưng tuyệt đối không cho phép họ nhìn thấy hoặc sửa đổi cấu trúc bên trong các bảng dữ liệu gốc.
Tích hợp lịch chạy tự động (Scheduled Queries): Bạn có thể cấu hình trực tiếp BigQuery giao nhiệm vụ cho một Stored Procedure chạy định kỳ hàng ngày, hàng tuần mà không cần dựng thêm Airflow hay Jenkins bên ngoài.

2. Cú pháp cơ bản: Tạo (`CREATE PROCEDURE`) và Gọi (`CALL`)

Hãy xem cách chúng ta đóng gói một đoạn code tổng hợp doanh thu theo ngày thành một Stored Procedure có nhận tham số đầu vào (IN).

Bước 1: Tạo Stored Procedure trong Dataset

SQL

-- Chỉ định dataset chứa Procedure
CREATE OR REPLACE PROCEDURE `my-project.mart_sales.sp_calculate_daily_revenue`(process_date DATE)
BEGIN
    -- Xóa dữ liệu cũ của ngày đó nếu chạy lại (Idempotency)
    DELETE FROM `my-project.mart_sales.daily_summary` 
    WHERE summary_date = process_date;

    -- Chèn dữ liệu mới được tổng hợp vào bảng
    INSERT INTO `my-project.mart_sales.daily_summary` (summary_date, total_orders, total_revenue)
    SELECT 
        process_date,
        COUNT(order_id),
        SUM(total_amount)
    FROM `my-project.core_dwh.fact_orders`
    WHERE DATE(DATETIME(created_at, 'Asia/Ho_Chi_Minh')) = process_date;

    -- In thông báo thành công ra log
    SELECT CONCAT('Thành công tổng hợp doanh thu cho ngày: ', CAST(process_date AS STRING)) AS log_status;
END;

Bước 2: Gọi Stored Procedure từ bất cứ đâu

Để thực thi procedure vừa tạo, bạn chỉ cần dùng từ khóa CALL:

SQL

CALL `my-project.mart_sales.sp_calculate_daily_revenue`('2026-10-18');

3. Khối `BEGIN...EXCEPTION` – "Túi khí an toàn" cho hệ thống ETL

Khi Stored Procedure chạy hàng loạt vào ban đêm, việc đối mặt với lỗi ngoại lệ là điều không thể tránh khỏi. Nếu không có cấu trúc bắt lỗi, bất kỳ dòng lệnh nào gặp sự cố sẽ làm dừng toàn bộ Procedure ngay lập tức và trả về mã lỗi khó hiểu cho hệ thống gọi nó.

Cấu trúc BEGIN...EXCEPTION trong GoogleSQL cho phép bạn "bẫy" lỗi, thực hiện các hành động khẩn cấp (như ghi log lỗi, gửi cảnh báo) thay vì để ứng dụng sập nguồn:

SQL

CREATE OR REPLACE PROCEDURE `my-project.mart_sales.sp_safe_etl_runner`(process_date DATE)
BEGIN
    -- Mở khối an toàn
    BEGIN
        -- Đoạn code ETL chính dễ xảy ra lỗi
        -- Ví dụ: Cố tình thực hiện một phép tính chia cho 0 hoặc gọi một bảng không tồn tại
        IF process_date > CURRENT_DATE('Asia/Ho_Chi_Minh') THEN
            RAISE USING MESSAGE = 'LỖI NGHIỆP VỤ: Không thể tổng hợp dữ liệu cho ngày trong tương lai!';
        END IF;

        -- Chạy logic tính toán bình thường
        CALL `my-project.mart_sales.sp_calculate_daily_revenue`(process_date);

    -- Khối bắt lỗi (Được kích hoạt tự động nếu có bất kỳ lỗi nào xảy ra ở phần trên)
    EXCEPTION WHEN ERROR THEN
        -- Ghi lại thông tin lỗi vào bảng log hệ thống để kỹ sư kiểm tra vào buổi sáng
        INSERT INTO `my-project.logs.etl_error_logs` (error_time, procedure_name, error_message)
        VALUES (
            CURRENT_TIMESTAMP(),
            'sp_safe_etl_runner',
            @@error.message -- Biến hệ thống chứa nội dung chi tiết của lỗi
        );

        -- In thông báo lỗi ra màn hình console (hoặc trigger webhook thông báo qua Slack)
        SELECT CONCAT('ĐÃ BẮT ĐƯỢC LỖI: ', @@error.message) AS alert;
        
        -- (Tùy chọn) Ném lại lỗi nếu muốn hệ thống cha biết tiến trình đã thất bại
        -- RAISE; 
    END;
END;

Điểm ăn tiền trong khối Exception:

@@error.message: Biến hệ thống tích hợp sẵn của GoogleSQL, chứa nội dung thông báo lỗi chi tiết giúp bạn biết chính xác câu lệnh nào hoặc dòng nào vừa gây ra sự cố.
RAISE USING MESSAGE = '...': Cho phép bạn chủ động ném ra một ngoại lệ tùy chỉnh khi dữ liệu đầu vào vi phạm quy tắc nghiệp vụ của công ty.

4. Quy chuẩn thiết kế Stored Procedure "Chuẩn Senior" cho hệ thống Production

Khi viết Stored Procedures cho doanh nghiệp lớn, hãy tuân thủ 3 nguyên tắc vàng sau đây để tránh các thảm họa dữ liệu:

Tính bất biến và lặp lại được (Idempotency): Một Stored Procedure tốt là procedure mà dù bạn có CALL lại cùng một tham số ngày 5 lần liên tục, kết quả trong bảng đích vẫn hoàn toàn chính xác và không bị nhân bản dòng (nhờ luôn có bước DELETE hoặc dùng lệnh MERGE / UPSERT thay vì chỉ INSERT mù quáng).
Ghi Log chi tiết (Audit Logging): Mỗi Procedure chạy nên có các bước ghi lại thời điểm bắt đầu (start_time), thời điểm kết thúc (end_time), số lượng dòng đã xử lý (rows_affected) vào một bảng etl_audit_log để đội ngũ Data Engineering dễ dàng theo dõi hiệu năng hệ thống qua từng ngày.
Quản lý phân quyền chặt chẽ: Không cấp quyền Data Editor toàn bộ cho các lập trình viên. Hãy cấu hình chế độ Authorized Procedures – cho phép một Stored Procedure có quyền đọc/ghi vào bảng bảo mật, nhưng người dùng gọi nó thì hoàn toàn không có quyền trực tiếp đụng tới bảng đó.

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

Stored Procedures và khối BEGIN...EXCEPTION là chìa khóa nâng tầm kỹ năng của bạn từ một "người viết SQL đơn thuần" thành một "Kiến trúc sư hệ thống dữ liệu":

Đóng gói & Tái sử dụng: Đưa logic phức tạp vào CREATE PROCEDURE và gọi nhanh gọn bằng CALL.
Bắt lỗi tự động: Sử dụng BEGIN...EXCEPTION kết hợp biến hệ thống @@error.message để ghi log và bảo vệ hệ thống không bị sập nguồn ngầm.
Vận hành an toàn: Thiết kế quy trình ETL mang tính Idempotency và chuẩn hóa kiểm soát lỗi cho môi trường Production.

Nhưng đôi khi, các hàm toán học hoặc xử lý logic tùy chỉnh bằng SQL trở nên quá cồng kềnh khi bạn muốn xử lý các thuật toán nâng cao, mã hóa, hoặc xử lý chuỗi phức tạp. BigQuery cho phép bạn nhúng thẳng mã nguồn JavaScript trực tiếp vào bên trong các câu lệnh SQL!

Làm thế nào để viết các hàm tự định nghĩa bằng cả SQL và JavaScript trong BigQuery? Hãy đón xem 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!

bigquery