Posted inDatabase

ELT Có Khả Năng Mở Rộng: Cách Chúng Tôi Thay Thế 2.500 Dòng SQL Bằng dbt và Postgres

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Cuộc gọi lúc 2:14 sáng: Khi các Stored Procedure bị hỏng

Vào lúc 2:14 sáng, thông báo từ PagerDuty như một cú giáng mạnh. Tôi đang nhìn chằm chằm vào một stored procedure cũ kỹ dài 2.500 dòng trong PostgreSQL vừa bị timeout đêm thứ ba liên tiếp. Logic là một mớ hỗn độn của các bảng tạm (temporary tables), vòng lặp lồng nhau và các phụ thuộc ngầm định. Không ai trong nhóm muốn động vào nó vì một chỉnh sửa sai lầm có thể làm hỏng toàn bộ lớp báo cáo.

Kinh nghiệm của tôi với MySQL, PostgreSQL và MongoDB đã dạy tôi rằng mỗi công cụ đều có thế mạnh riêng. Đối với các chuyển đổi phân tích có cấu trúc, PostgreSQL là một cỗ máy cực kỳ mạnh mẽ—nếu bạn tôn trọng trình lập kế hoạch truy vấn (query planner) của nó. Điểm nghẽn thường không nằm ở bản thân cơ sở dữ liệu mà ở quy trình làm việc. Chúng tôi đã giấu logic nghiệp vụ quan trọng bên trong một ‘hộp đen’. Để khắc phục điều này, chúng tôi cần hướng tới phương pháp ELT (Extract, Load, Transform) chuyên nghiệp bằng cách sử dụng dbt (data build tool).

Kết nối dbt với Postgres trong chưa đầy năm phút

Bạn có thể để dbt giao tiếp với instance Postgres của mình trước khi uống xong tách cà phê đầu tiên. Hãy coi dbt như một lớp điều phối (orchestration layer). Nó nằm trên cơ sở dữ liệu, cho phép bạn viết mã SQL dạng module mà nó sẽ biên dịch và thực thi theo đúng thứ tự.

Bắt đầu bằng cách cài đặt adapter cho Postgres. Tôi luôn sử dụng môi trường ảo (virtual environment) để tránh xung đột phụ thuộc:

python3 -m venv dbt-env
source dbt-env/bin/activate
pip install dbt-postgres

Tiếp theo, khởi tạo dự án của bạn. Lệnh này sẽ tạo cấu trúc thư mục mà bạn sẽ sử dụng để tổ chức các data model của mình:

dbt init analytics_pipeline

Cấu hình diễn ra trong tệp profiles.yml (thường nằm trong ~/.dbt/). Tệp này cho dbt biết cách xác thực. Đối với môi trường production, đừng bao giờ viết cứng mật khẩu. Thay vào đó, hãy sử dụng các biến môi trường (environment variables).

analytics_pipeline:
  outputs:
    dev:
      type: postgres
      threads: 4
      host: localhost
      port: 5432
      user: transform_user
      pass: "{{ env_var('DB_PASSWORD') }}"
      dbname: analytics_db
      schema: analytics_main
  target: dev

Chạy lệnh dbt debug. Nếu terminal trả về văn bản màu xanh lá cây, bạn đã sẵn sàng để ngừng viết các kịch bản nguyên khối và bắt đầu xây dựng các model.

Chuyển sang Mô hình hóa dạng Module

Về cơ bản, dbt coi mỗi tệp .sql là một model. Một model đơn giản chỉ là một câu lệnh SELECT. Bạn viết logic; dbt sẽ tự động xử lý các mã boilerplate CREATE TABLE AS hoặc CREATE VIEW AS.

Quản lý phụ thuộc từng là một nỗi đau đầu. Giờ đây, hàm ref() sẽ xử lý việc đó. Thay vì đoán xem bảng nào phải được tạo trước, bạn tham chiếu trực tiếp đến tên model. Điều này tạo ra một sơ đồ phả hệ (lineage) rõ ràng mà dbt sẽ tuân theo trong quá trình thực thi.

Hãy xem xét một staging model cho dữ liệu e-commerce thô (models/staging/stg_orders.sql):

with raw_orders as (
    select * from {{ source('raw_data', 'orders') }}
)

select
    id as order_id,
    user_id,
    order_date,
    lower(status) as order_status -- Chuẩn hóa các chuỗi thô
from raw_orders

Trong bước chuyển đổi cuối cùng (models/marts/fct_orders.sql), bạn chỉ cần lấy dữ liệu từ các tệp staging đó:

with orders as (
    select * from {{ ref('stg_orders') }}
),

order_payments as (
    select * from {{ ref('stg_payments') }}
)

select
    orders.order_id,
    orders.user_id,
    order_payments.amount
from orders
left join order_payments using (order_id)

Đằng sau hậu trường, dbt xây dựng một Đồ thị có hướng không chu trình (DAG). Nó đảm bảo stg_orders hoàn thành trước khi fct_orders bắt đầu. Bạn không còn phải lập lịch thủ công cho một chuỗi năm kịch bản Python khác nhau nữa.

Testing: Dấu chấm hết cho các Dashboard bị lỗi

Mã nguồn dạng module mới chỉ là một nửa cuộc chiến. Kiểm thử (Testing) mới là thứ thực sự giúp cho thiết bị báo động (pager) im lặng. Trước đây, chúng tôi chỉ nhận ra lỗi trùng lặp khóa chính khi dashboard hiển thị doanh thu định kỳ hàng tháng (MRR) tăng vọt lên 115%. Với dbt, tôi bắt được những lỗi đó ngay trong giai đoạn xây dựng (build phase).

Định nghĩa các bài kiểm thử của bạn trong tệp schema.yml:

version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']

Thực thi dbt test để xác minh tính toàn vẹn của dữ liệu. Nếu một bản ghi chứa ID bị null hoặc trạng thái không hợp lệ, pipeline sẽ dừng lại. Điều này ngăn dữ liệu bị hỏng tiếp cận các công cụ BI (Business Intelligence) trên môi trường production.

Tài liệu không nên là bước làm sau cùng. Bằng cách chạy dbt docs generate, bạn sẽ tạo ra một giao diện trên web. Nó hiển thị toàn bộ sơ đồ phả hệ dữ liệu và mô tả bảng, giúp bạn dễ dàng giải thích nguồn gốc dữ liệu cho Product Manager mà không cần đào sâu vào code.

Những bài học kinh nghiệm quý giá cho Production

Mở rộng quy mô Postgres đòi hỏi nhiều hơn là chỉ mã SQL sạch sẽ. Sau khi di chuyển ba hệ thống cũ sang stack này, tôi thấy bốn chiến lược sau đây là cực kỳ thiết yếu:

  1. Sử dụng Incremental Models: Đừng tạo lại các bảng 500 triệu dòng từ đầu. Sử dụng cơ chế incremental để chỉ thêm dữ liệu mới. Điều này đã cắt giảm thời gian chạy của chúng tôi từ 45 phút xuống còn 120 giây.
  2. Chiến lược Materialization: Hãy mặc định sử dụng views trong quá trình phát triển. Chúng được tạo ra tức thì. Chỉ chuyển sang tables khi hiệu năng của công cụ BI bắt đầu chậm lại.
  3. Cô lập dữ liệu thô: Giữ dữ liệu nguồn raw của bạn trong một schema riêng biệt. Cấp cho người dùng dbt quyền chỉ đọc (read-only) đối với raw và toàn quyền đối với analytics. Bức tường đơn giản này ngăn chặn các thảm họa DROP TABLE vô tình xảy ra với dữ liệu nguồn.
  4. Post-Run Hooks: Tự động chạy ANALYZE trên các bảng Postgres sau khi dbt hoàn tất. Điều này giúp trình lập kế hoạch truy vấn luôn được tối ưu hóa và đảm bảo các phép join vẫn nhanh chóng khi khối lượng dữ liệu tăng lên.

Kỹ thuật dữ liệu chuyên nghiệp là việc áp dụng các kỷ luật phần mềm vào SQL. Quản lý phiên bản (version control), kiểm thử tự động và tính module không còn chỉ dành cho các nhà phát triển ứng dụng. Mức độ căng thẳng của tôi đã giảm đáng kể kể từ khi chúng tôi loại bỏ các stored procedure. Nếu bạn vẫn đang quản lý các phụ thuộc trong Postgres một cách thủ công, bạn đang làm việc vất vả hơn mức cần thiết đấy.

Share:
Tags: