Truy vấn MySQL, MongoDB và CSV trực tiếp từ PostgreSQL bằng FDW

Table of Contents

Nỗi lo về các “ốc đảo dữ liệu” (Data Silo)

Sáu tháng trước, đội ngũ của tôi gặp bế tắc. Dữ liệu giao dịch nằm trong PostgreSQL, nhưng hồ sơ người dùng cũ lại bị kẹt trong MySQL. Bộ phận Marketing liên tục đổ dữ liệu khách hàng tiềm năng vào các file CSV khổng lồ 500MB, trong khi log ứng dụng thì nằm trong MongoDB. Để tạo ra một báo cáo hợp nhất, chúng tôi phải viết một script Python dài 400 dòng, tốn nhiều thời gian để gộp dữ liệu trong bộ nhớ hơn là thực sự phân tích chúng.

Quản lý những “hòn đảo” biệt lập này cực kỳ mệt mỏi. Tôi cần một cách để biến PostgreSQL thành một trung tâm điều phối mà không phải trải qua nỗi đau của việc di chuyển dữ liệu (migration) quy mô lớn. Đó là lý do chúng tôi triển khai Foreign Data Wrappers (FDW) trong môi trường production. Nó đã thay đổi mọi thứ.

Chính xác thì PostgreSQL FDW là gì?

Hãy coi FDW như một cây cầu. Nó tuân theo tiêu chuẩn SQL/MED (SQL Management of External Data), cho phép Postgres coi các nguồn dữ liệu bên ngoài—cho dù là SQL, NoSQL hay các tệp phẳng—như các bảng cục bộ. Khi bạn truy vấn một bảng ngoại (foreign table), Postgres sẽ gửi yêu cầu đến nguồn từ xa, lấy kết quả về và xử lý việc join hoặc lọc dữ liệu ngay tại máy cục bộ.

Sau sáu tháng vận hành thực tế, thiết lập này hầu như đã loại bỏ nhu cầu về các pipeline ETL nặng nề cho báo cáo hàng ngày. Nó biến cơ sở dữ liệu của bạn thành một công cụ truy vấn liên kết (federated query engine).

Thiết lập môi trường

Bạn cần cài đặt các extension cụ thể trước khi kết nối với các nguồn bên ngoài. Trong khi file_fdw thường có sẵn, các extension khác như mysql_fdw hoặc mongo_fdw thường yêu cầu cài đặt nhanh thông qua trình quản lý gói của hệ điều hành.

-- Trên Ubuntu/Debian
sudo apt-get install postgresql-15-mysql-fdw
-- Sau đó bên trong Postgres
CREATE EXTENSION mysql_fdw;
CREATE EXTENSION file_fdw;

Kết nối với MySQL

Extension mysql_fdw là một “cỗ máy” thực thụ. Theo kinh nghiệm của chúng tôi, nó xử lý các lệnh join hơn 10.000 dòng với độ trễ không đáng kể. Việc thiết lập bao gồm ba bước đơn giản: định nghĩa server, ánh xạ người dùng và liên kết bảng.

1. Định nghĩa Foreign Server

Bước này cho PostgreSQL biết instance MySQL nằm ở đâu. Hãy đảm bảo sử dụng IP nội bộ để giữ độ trễ ở mức thấp.

CREATE SERVER mysql_legacy_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '192.168.1.50', port '3306');

2. Tạo User Mapping

PostgreSQL cần thông tin đăng nhập để truy cập MySQL. Tôi khuyên bạn nên tạo một người dùng chuyên dụng chỉ có quyền đọc (read-only) trong MySQL để hạn chế rủi ro bảo mật.

CREATE USER MAPPING FOR postgres
SERVER mysql_legacy_server
OPTIONS (username 'fdw_user', password 'secure_password');

3. Định nghĩa Foreign Table

Bạn có thể lấy toàn bộ schema cùng lúc, nhưng tôi thích định nghĩa các bảng cụ thể hơn. Điều này giúp schema cục bộ của bạn sạch sẽ và có mục đích rõ ràng.

CREATE FOREIGN TABLE mysql_users (
    id INT,
    username TEXT,
    email TEXT
)
SERVER mysql_legacy_server
OPTIONS (dbname 'user_db', table_name 'users');

Tích hợp dữ liệu MongoDB

Việc ánh xạ một tài liệu BSON không có schema cố định sang một bảng SQL có cấu trúc phức tạp hơn một chút. Chúng tôi đã sử dụng cách này để liên kết bảng ‘orders’ trong Postgres với ‘activity_logs’ trong MongoDB để phát hiện gian lận.

CREATE EXTENSION mongo_fdw;

CREATE SERVER mongo_server
FOREIGN DATA WRAPPER mongo_fdw
OPTIONS (address '127.0.0.1', port '27017');

CREATE USER MAPPING FOR postgres
SERVER mongo_server
OPTIONS (username 'admin', password 'password');

CREATE FOREIGN TABLE mongo_logs (
    _id NAME,
    user_id INT,
    action TEXT,
    timestamp TIMESTAMP
)
SERVER mongo_server
OPTIONS (db 'logs_db', collection 'user_actions');

Mẹo hiệu năng: các phép toán tổng hợp (aggregation) phức tạp trên MongoDB không phải lúc nào cũng được “đẩy xuống” (push down) hoàn hảo. Nếu một truy vấn mất hơn vài giây, hãy kiểm tra xem Mongo đang thực hiện phần việc nặng nhọc hay Postgres đang phải lấy toàn bộ collection về để lọc cục bộ.

Đọc trực tiếp các tệp CSV

Các nhà phân tích dữ liệu rất thích file_fdw. Thay vì phải chạy lệnh COPY FROM thủ công mỗi khi có bảng tính mới, bạn chỉ cần trỏ một bảng ngoại vào đường dẫn tệp. Nếu tệp CSV được cập nhật, kết quả SQL của bạn sẽ được làm mới ngay lập tức.

CREATE SERVER csv_files_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE marketing_leads (
    lead_name TEXT,
    lead_email TEXT,
    source TEXT
)
SERVER csv_files_server
OPTIONS (filename '/var/lib/postgresql/data/leads.csv', format 'csv', header 'true');

Sức mạnh của việc JOIN liên cơ sở dữ liệu

Giá trị thực sự nằm ở ‘Mega-Join’. Giờ đây chúng ta có thể liên kết một người dùng MySQL với danh sách khách hàng tiềm năng từ CSV và lọc theo log của MongoDB chỉ trong một truy vấn duy nhất. Nó trông như thế này:

SELECT 
    u.username, 
    m.lead_name, 
    l.action
FROM mysql_users u
JOIN marketing_leads m ON u.email = m.lead_email
JOIN mongo_logs l ON u.id = l.user_id
WHERE l.timestamp > NOW() - INTERVAL '1 day';

Truy vấn này lấy dữ liệu từ ba môi trường khác nhau cùng một lúc. Không cần Python. Không cần Spark. Chỉ với 10 dòng SQL.

Hiệu năng thực tế và những bài học kinh nghiệm

Sau sáu tháng chạy FDW trong môi trường thực tế, đây là những sự thật phũ phàng. Độ trễ mạng là “kẻ giết người thầm lặng”. Nếu instance MySQL của bạn nằm ở một region khác, hãy chuẩn bị tinh thần cho việc tăng thêm 100ms độ trễ trên mỗi yêu cầu. Hãy cố gắng giữ các cơ sở dữ liệu của bạn trong cùng một VPC bất cứ khi nào có thể.

Luôn kiểm tra kế hoạch thực thi (Execution Plans). Postgres cố gắng “đẩy xuống” (push down) các bộ lọc như WHERE id = 10 tới server từ xa để xử lý nhanh hơn. Tuy nhiên, nếu nó không thể dịch một hàm nào đó, nó có thể sẽ kéo 1 triệu dòng dữ liệu qua đường truyền mạng chỉ để lọc chúng tại máy cục bộ.

Bảo mật cần được quan tâm đặc biệt. Việc lưu trữ thông tin đăng nhập trong CREATE USER MAPPING khiến chúng có thể bị nhìn thấy trong các bảng hệ thống (system catalogs). Hãy sử dụng các công cụ quản lý bí mật (vaulted secrets) hoặc biến môi trường cho các môi trường production. Cuối cùng, hãy nhớ rằng nếu server từ xa bị sập, truy vấn Postgres của bạn sẽ bị treo. Hãy cấu hình thời gian chờ (timeout) nghiêm ngặt trong các tùy chọn CREATE SERVER để ngăn một hệ thống chậm chạp làm hỏng toàn bộ dashboard của bạn.

FDW không phải là giải pháp thay thế cho một Kho dữ liệu (Data Warehouse) quy mô Petabyte. Nhưng đối với các báo cáo vận hành và để tránh các script ETL lộn xộn, nó là một công cụ thiết yếu. Nó cho phép bạn tận dụng toàn bộ sức mạnh của SQL trên toàn bộ hạ tầng của mình với chi phí vận hành gần như bằng không.