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

Greenplum Database Bài học 22: External Tables (Readable & Writable): Truy vấn trực tiếp file CSV/Text bên ngoài mà không cần Import

Ở Bài học 21, chúng ta đã làm quen với tiến trình gpfdist nhằm phá vỡ nút thắt cổ chai của Master Node khi nạp dữ liệu. Tuy nhiên, công cụ kết nối đắc lực nhất giúp Greenplum biến các tệp tin bên ngoài thành "một phần của cơ sở dữ liệu" chính là hệ thống External Tables (Bảng ngoài).

Trong các cơ sở dữ liệu quan hệ truyền thống, để phân tích một file CSV dung lượng lớn, bạn bắt buộc phải tạo bảng rồi thực hiện câu lệnh IMPORT hoặc LOAD toàn bộ dữ liệu vào ổ cứng. Với Greenplum, nhờ cơ chế External Tables, bạn có thể trực tiếp truy vấn SQL lên các tệp tin CSV, Text nằm bên ngoài hệ thống mà không cần tốn dung lượng đĩa để Import trước.

Trong bài học này, chúng ta sẽ khám phá sức mạnh của cả hai loại: Readable External Tables và Writable External Tables.

1. External Tables là gì?

External Table là một đối tượng ảo trong Greenplum System Catalog. Về mặt logic, nó trông giống hệt như một bảng bình thường (có tên cột, kiểu dữ liệu, schema), nhưng về mặt vật lý, nó không lưu trữ bất kỳ dòng dữ liệu nào trên ổ cứng của cơ sở dữ liệu.

Thay vào đó, khi bạn chạy một câu truy vấn SELECT trên External Table, Greenplum sẽ trực tiếp điều phối các Segment đọc dữ liệu "on-the-fly" từ nguồn bên ngoài (file trên đĩa qua gpfdist, qua giao thức Web HTTP, hoặc từ các hệ thống lưu trữ phân tán).

┌────────────────────────────────────────────────────────────────────────┐
│                        KIẾN TRÚC EXTERNAL TABLES                       │
└───────────────────────────┬────────────────┬───────────────────────────┘
                            │                │
            1. READABLE EXTERNAL TABLE        2. WRITABLE EXTERNAL TABLE
            (Đọc file bên ngoài vào DB)       (Xuất dữ liệu từ DB ra file)
            [File CSV/Text] ──► [Greenplum]   [Greenplum] ──► [File CSV/Text]

2. Readable External Tables: Truy vấn file bên ngoài như bảng thật

2.1. Mục đích sử dụng

Truy vấn trực tiếp kiểm tra nội dung file dữ liệu trước khi quyết định nạp vào bảng chính.
Thực hiện các phép phân tích nhanh, báo cáo ad-hoc trên các tệp log hàng ngày mà không làm tốn dung lượng lưu trữ đĩa của Data Warehouse.
Phục vụ quá trình ETL (Extract - Transform - Load): Đọc dữ liệu từ file ngoài, làm sạch (Transform) rồi ghi vào bảng đích.

2.2. Cú pháp thực chiến Readable External Table

Để tạo một bảng đọc ngoài kết nối thông qua gpfdist (đã học ở Bài 21):

SQL

CREATE READABLE EXTERNAL TABLE ext_raw_orders (
    order_id BIGINT,
    customer_id BIGINT,
    status VARCHAR(20),
    order_date TIMESTAMP
) 
LOCATION ('gpfdist://10.10.10.10:8080/raw_orders_*.csv')
FORMAT 'TEXT' (DELIMITER ',' NULL '' ESCAPE '\')
ENCODING 'UTF8'
LOG ERRORS INTO err_log_orders SEGMENT REJECT LIMIT 10 PERCENT;

Điểm đặc biệt trong cú pháp:

LOCATION: Chỉ định giao thức và địa chỉ nguồn dữ liệu (hỗ trợ gpfdist://, gpfdists:// bảo mật, hoặc file:// nếu file nằm sẵn trên mọi Segment Host).
LOG ERRORS INTO ... SEGMENT REJECT LIMIT: Đây là tính năng xử lý dữ liệu bẩn cực kỳ thông minh của Greenplum. Nếu file CSV có các dòng bị lỗi định dạng (sai kiểu dữ liệu, thiếu cột), thay vì làm sập toàn bộ câu lệnh, Greenplum cho phép ghi nhận các dòng lỗi đó vào bảng log riêng và chỉ hủy bỏ nếu tỷ lệ lỗi vượt quá giới hạn cho phép (ví dụ 10%).

3. Writable External Tables: Xuất dữ liệu từ Greenplum ra file ngoài

Không chỉ đọc, Greenplum còn cung cấp Writable External Tables cho phép bạn xuất dữ liệu từ các bảng bên trong cơ sở dữ liệu ra thành các tệp tin bên ngoài thông qua tiến trình gpfdist.

3.1. Mục đích sử dụng

Trích xuất dữ liệu lịch sử (Archive) ra các file CSV để lưu trữ lạnh.
Xuất báo cáo, kết quả phân tích sang các hệ thống bên ngoài để phục vụ các bên liên quan.
Tối ưu hóa quy trình truyền tải dữ liệu giữa các cụm cơ sở dữ liệu khác nhau.

3.2. Cú pháp thực chiến Writable External Table

SQL

CREATE WRITABLE EXTERNAL TABLE ext_export_orders (
    order_id BIGINT,
    customer_id BIGINT,
    total_amount NUMERIC(12,2)
) 
LOCATION ('gpfdist://10.10.10.10:8080/exported_orders.csv')
FORMAT 'TEXT' (DELIMITER ',');

Cách sử dụng để xuất dữ liệu:

Sau khi tạo xong Writable External Table, việc trích xuất dữ liệu hàng triệu bản ghi từ bảng chính ra file chỉ đơn giản là một câu lệnh INSERT đảo chiều:

SQL

-- Dữ liệu từ bảng Fact sẽ được các Segment đồng loạt trích xuất và đẩy ra file qua gpfdist
INSERT INTO ext_export_orders 
SELECT order_id, customer_id, total_amount 
FROM fact_orders 
WHERE created_at < '2025-01-01';

4. Tóm tắt ưu điểm vượt trội của External Tables

Tiết kiệm tài nguyên đĩa: Không cần tốn không gian lưu trữ trùng lặp khi cần phân tích tạm thời dữ liệu từ file.
Hiệu năng song song hóa cao: Dữ liệu được đọc hoặc ghi đồng thời bởi tất cả các tiến trình Segment thông qua mạng nội bộ, tận dụng tối đa băng thông phần cứng.
Linh hoạt trong kiến trúc ETL: Giúp đơn giản hóa các luồng xử lý dữ liệu lớn, kết hợp hoàn hảo giữa thế giới tệp tin bên ngoài và cơ sở dữ liệu phân tán.

Tóm tắt Bài học 22

External Tables là bảng ảo trong Greenplum cho phép truy vấn hoặc ghi dữ liệu trực tiếp ra các tệp bên ngoài (CSV, Text) mà không cần Import vào đĩa cục bộ của database.
Readable External Tables dùng để đọc file bên ngoài kết hợp cơ chế lọc lỗi thông minh (LOG ERRORS).
Writable External Tables dùng để trích xuất dữ liệu từ bảng trong cơ sở dữ liệu ra file bên ngoài thông qua lệnh INSERT.

Ở bài học tiếp theo (Bài học 23), chúng ta sẽ bước sang một cổng kết nối đa năng hơn nữa trong hệ sinh thái Big Data: Greenplum Platform Extension Framework (PXF): Cổng kết nối đa năng với thế giới bên ngoài.

cloud database