Hello mọi người, ở bài trước mình có viết về chủ đề DBT ở đây tiếp tục series này hôm nay chúng ta sẽ cùng nhau tìm hiểu Migration PostgreSQL và DBT nhé ^^.
Giới thiệu
PostgreSQL là một hệ quản trị cơ sở dữ liệu mạnh mẽ và phổ biến cho OLTP (Online Transaction Processing). Tuy nhiên, khi nhu cầu xử lý dữ liệu phức tạp, đặc biệt trong phân tích dữ liệu (OLAP - Online Analytical Processing), ngày càng gia tăng, bạn có thể cần các công cụ chuyên biệt hơn. dbt (Data Build Tool) là một giải pháp mạnh mẽ giúp quản lý và tối ưu hóa quy trình ETL (Extract, Transform, Load).
Trong bài viết này, chúng ta sẽ cùng khám phá cách sử dụng dbt để tổ chức và cải tiến dữ liệu trong PostgreSQL, giúp bạn dễ dàng thực hiện các tác vụ từ phân tích dữ liệu đơn giản đến phức tạp.
DBT là gì và tại sao nên sử dụng?
Đầu tiên để mình nhắc lại 1 xíu về DBT nha. DBT (Data Build Tool) là một công cụ mã nguồn mở cho phép các nhà phân tích và kỹ sư dữ liệu chuyển đổi dữ liệu trong kho dữ liệu của họ bằng cách sử dụng SQL. Nó cung cấp:
Tự động hóa: dbt xử lý việc tổ chức các phụ thuộc giữa các bảng.
Tái sử dụng mã nguồn: Tái sử dụng các đoạn mã SQL qua các template (Jinja).
Quản lý phiên bản: Lưu trữ mô hình dữ liệu như mã nguồn, dễ dàng theo dõi và triển khai.
Tích hợp dễ dàng: dbt hỗ trợ nhiều nền tảng cơ sở dữ liệu, bao gồm PostgreSQL.
Các bước migration PostgreSQL với DBT
Cài đặt PostgreSQL
Bước đầu thì tất nhiên là phải cài đặt PostgreSQL rồi, nếu chưa cài đặt thì cùng cài đặt nha.
sudo apt update
sudo apt install postgresql postgresql-contrib
Tạo cơ sở dữ liệu
sudo -u postgres psql
CREATE DATABASE my_database;
Cài đặt DBT
pip install dbt
Tạo dự án DBT
Sau khi đã hoàn thành cài đặt và tạo cơ sở dữ liệu thì mình sẽ khởi tạo 1 project DBT nha
dbt init my_project
Thư mục dự án sẽ bao gồm các thành phần sau:
models/: Chứa các mô hình SQL.
dbt_project.yml: File cấu hình chính của dự án.
Cấu hình kết nối với PostgreSQL
Bước tiếp theo sẽ sửa file profile.yml để kết nối với PostgreSQL
my_project: outputs: dev: type: postgres host: localhost user: postgres password: your_password dbname: my_database schema: public threads: 4 target: dev Kiểm tra kết nối:
dbt debug
Xây dựng các mô hình SQL
Tổ chức dữ liệu
Dữ liệu nguồn (raw data): Dữ liệu thô từ PostgreSQL.
Dữ liệu xử lý (staging): Dữ liệu làm sạch và chuẩn hóa.
Dữ liệu phân tích (analytics): Dữ liệu tổng hợp phục vụ báo cáo.
Ví dụ: Tạo mô hình staging stg_sales.sql:
WITH cleaned_sales AS ( SELECT id, customer_id, product_id, CAST(order_date AS DATE) AS order_date, quantity, price FROM {{ source('postgres', 'raw_sales_data') }} WHERE order_date IS NOT NULL
)
SELECT *, quantity * price AS total_price
FROM cleaned_sales; Ví dụ: Chuẩn hóa khách hàng stg_customers.sql:
WITH formatted_customers AS ( SELECT id, LOWER(TRIM(email)) AS email, COALESCE(phone, 'UNKNOWN') AS phone, CASE WHEN country IN ('US', 'CA') THEN 'North America' WHEN country IN ('FR', 'DE') THEN 'Europe' ELSE 'Other' END AS region FROM {{ source('postgres', 'customers') }}
)
SELECT * FROM formatted_customers; Tạo Mô Hình Analytics
Từ các bảng staging, tạo các mô hình phục vụ báo cáo.
Ví dụ: Tổng hợp doanh thu theo khu vực:
WITH sales_by_region AS ( SELECT c.region, SUM(s.total_price) AS revenue FROM {{ ref('stg_sales') }} s JOIN {{ ref('stg_customers') }} c ON s.customer_id = c.id GROUP BY c.region
)
SELECT region, revenue, RANK() OVER (ORDER BY revenue DESC) AS rank
FROM sales_by_region; Sử Dụng Materialized Views và Incremental Models
Materialized Views
Cấu hình bảng vật lý trong dbt_project.yml:
Cấu hình bảng vật lý trong dbt_project.yml:
models: my_project: materialized: table Incremental Models
Tối ưu hóa việc tải dữ liệu lớn với incremental models.
{{ config( materialized='incremental', unique_key='id'
) }} SELECT id, customer_id, product_id, total_price, order_date
FROM {{ ref('stg_sales') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %} Tối Ưu Hiệu Suất
Partitioning và Indexing
Cải thiện hiệu suất query với partitioning:
CREATE TABLE sales_partitioned ( id SERIAL, customer_id INT, product_id INT, total_price NUMERIC, order_date DATE
)
PARTITION BY RANGE (order_date); Tiếp theo là tạo index
CREATE INDEX idx_order_date ON sales_partitioned (order_date); Kiểm tra chất lượng dữ liệu Thêm kiểm tra vào file schema.yml:
models: - name: stg_sales tests: - unique: column_name: id - not_null: column_name: order_date Ok tiếp theo sẽ là chạy test xem nha
dbt test Kết Quả Migration
Sau khi hoàn tất, bạn sẽ đạt được:
Quy trình ETL tối ưu: DBT xử lý phụ thuộc tự động, giúp pipeline dễ quản lý hơn.
Hiệu suất cao: Tận dụng Materialized Views và Incremental Models để cải thiện tốc độ query.
Quản lý chất lượng dữ liệu: Dễ dàng kiểm tra và giảm thiểu lỗi dữ liệu.
Kết luận
Migration PostgreSQL với dbt không chỉ giúp tổ chức dữ liệu hiệu quả mà còn mang lại sự tối ưu trong hiệu suất và quản lý quy trình ETL. Với các kỹ thuật nâng cao như incremental models, materialized views, và tích hợp CI/CD, bạn sẽ xây dựng được một pipeline dữ liệu mạnh mẽ và đáng tin cậy.
Nếu bạn có câu hỏi hoặc muốn trao đổi thêm, đừng ngại để lại bình luận nhé! 😊
Reference
https://docs.getdbt.com/docs/core/connect-data-platform/postgres-setup
https://community.getorchestra.io/dbt/how-dbt-uses-postgresql-for-data-transformation/
https://akatekhanh.github.io/olap-data-analytic-with-postgres/
https://discourse.getdbt.com/t/guide-notes-on-moving-from-postgres-to-snowflake/510