Trino SQL Bài học 41: Khởi tạo bảng siêu tốc với CTAS (Create Table As Select)

Mục tiêu bài học:

• Hiểu và vận dụng thành thạo cú pháp CTAS để tự động hóa quá trình định nghĩa cấu trúc bảng.

• Khai báo các thuộc tính vật lý của bảng (Table Properties) như định dạng lưu trữ (Parquet/ORC) và phân vùng (Partitioning) thông qua mệnh đề WITH.

• Kiểm soát chặt chẽ kiểu dữ liệu nội suy (Implicit Data Types) bằng kỹ thuật ép kiểu CAST.

• Nắm vững các kịch bản ứng dụng CTAS trong xây dựng Data Mart và sao lưu dữ liệu (Snapshot).

1. CTAS là gì? Sự kết hợp giữa DDL và DML

Thông thường, để tạo một bảng mới và đổ dữ liệu vào, bạn phải trải qua 2 bước thủ công tốn thời gian:

1. Viết lệnh CREATE TABLE (DDL) và gõ từng tên cột, từng kiểu dữ liệu (Ví dụ: id BIGINT, name VARCHAR). Nếu bảng có 50 cột, đây là một cực hình.

2. Viết lệnh INSERT INTO ... SELECT (DML) để bơm dữ liệu vào bảng vừa tạo.

CTAS (Create Table As Select) sinh ra để gộp 2 bước này làm một. Trino sẽ chạy câu lệnh SELECT của bạn, tự động nội suy (infer) tên cột và kiểu dữ liệu từ tập kết quả, sau đó tạo luôn bảng đích và đổ toàn bộ dữ liệu vào đó trong một giao dịch (Transaction) duy nhất.

2. Cú pháp cơ bản và Ứng dụng

Giả sử bộ phận Marketing yêu cầu bạn tạo một bảng riêng biệt chỉ chứa danh sách khách hàng VIP (số dư tài khoản > 5000) để họ chạy chiến dịch quảng cáo. Bạn không cần phải dò xem bảng gốc có cấu trúc thế nào.

SQL

-- Khởi tạo bảng mới có tên vip_customers trong schema mkt
CREATE TABLE iceberg.mkt.vip_customers AS 
SELECT 
    custkey, 
    name, 
    phone, 
    acctbal
FROM tpch.tiny.customer
WHERE acctbal > 5000.0;

Chỉ với một câu lệnh, bảng vip_customers đã được tạo ra trên Data Lake với 4 cột và chứa sẵn toàn bộ dữ liệu khách hàng VIP.

3. Quyền năng của mệnh đề WITH (Table Properties)

Tạo bảng thì dễ, nhưng trên môi trường Big Data, việc lưu trữ bảng đó dưới định dạng vật lý nào mới là điều sống còn đối với hiệu năng. Nếu bạn chỉ viết lệnh CTAS cơ bản, Trino sẽ dùng các cấu hình mặc định của Connector. Để làm chủ hệ thống, bạn phải dùng mệnh đề WITH ngay sau tên bảng để định nghĩa các thuộc tính vật lý.

SQL

CREATE TABLE iceberg.sales.daily_summary 
WITH (
    format = 'PARQUET',                 -- Định dạng lưu trữ dạng cột (Columnar), siêu nén và siêu nhanh
    partitioning = ARRAY['order_date']  -- Chia thư mục vật lý theo ngày để tăng tốc độ truy vấn sau này
) 
AS 
SELECT 
    CAST(orderdate AS VARCHAR) AS order_date, -- Ép kiểu ngày thành chuỗi để làm Partition Key (tùy chuẩn hệ thống)
    mktsegment,
    SUM(totalprice) AS daily_revenue
FROM tpch.tiny.orders
GROUP BY orderdate, mktsegment;

(Các thuộc tính bên trong WITH sẽ khác nhau tùy thuộc vào bạn đang dùng Iceberg, Delta Lake hay Hive Connector).

4. Giải quyết cạm bẫy "Kiểu dữ liệu ngầm định"

Bản chất của CTAS là đoán kiểu dữ liệu dựa trên kết quả trả về. Tuy nhiên, đôi khi máy móc đoán không đúng ý đồ thiết kế của con người.

Trường hợp rủi ro:

SQL

CREATE TABLE test_table AS 
SELECT 
    1 AS status_id,       -- Trino sẽ nội suy là INTEGER, nhưng có thể bạn muốn BIGINT hoặc SMALLINT
    'VN' AS country_code, -- Trino nội suy là VARCHAR, dù độ dài thực tế luôn là 2 ký tự (CHAR(2))
    NULL AS empty_column  -- Trino báo lỗi ngay lập tức vì không thể đoán kiểu dữ liệu của chữ NULL
FROM tpch.tiny.customer;

Cách khắc phục chuẩn Kỹ sư (Best Practice): Luôn sử dụng hàm CAST hoặc khai báo định kiểu rõ ràng ở câu SELECT để ép Trino tạo cấu trúc bảng chính xác đến từng byte.

SQL

CREATE TABLE iceberg.core.standardized_users AS 
SELECT 
    CAST(custkey AS BIGINT) AS id,
    CAST(1 AS TINYINT) AS status_id,
    CAST(NULL AS VARCHAR) AS note_field  -- Khai báo rõ NULL này thuộc kiểu chuỗi
FROM tpch.tiny.customer;

5. Góc nhìn Kỹ sư: Xử lý rủi ro và Vận hành

A. An toàn với IF NOT EXISTS Trong các luồng chạy tự động (Airflow / Cronjob), nếu bảng đã tồn tại, lệnh CTAS sẽ báo lỗi sập luồng. Hãy thêm IF NOT EXISTS để hệ thống chạy êm ái, bỏ qua lệnh tạo nếu bảng đã có sẵn.

SQL

CREATE TABLE IF NOT EXISTS iceberg.reports.monthly_snapshot AS SELECT ...

B. Tính nguyên tử (Atomicity) Khi chạy CTAS, nếu câu lệnh SELECT của bạn mất 1 tiếng để chạy, nhưng đến phút thứ 59 bị lỗi (ví dụ do chia cho 0), Trino sẽ hủy bỏ (Rollback) toàn bộ giao dịch. Bảng đích sẽ không được tạo ra và không để lại các file rác dở dang trên Data Lake.

C. Không có Ràng buộc (No Constraints) Data Engineer chuyển từ MySQL sang Trino cần lưu ý: Các hệ thống như Hive hay Iceberg phần lớn KHÔNG HỖ TRỢ các ràng buộc cơ sở dữ liệu truyền thống như Khóa chính (PRIMARY KEY), Khóa ngoại (FOREIGN KEY) hay UNIQUE, NOT NULL khi tạo bảng. Dữ liệu đổ vào bằng CTAS hoàn toàn là dữ liệu phẳng. Việc đảm bảo không trùng lặp (Deduplication) là trách nhiệm của bạn (sử dụng ROW_NUMBER như ở Bài 27).