Nhập dữ liệu từ file CSV bằng lệnh `COPY`

Chào mọi người! Hãy tưởng tượng bạn có một bảng Excel với 10 000 dòng, và sếp bảo: "Tải cái này vào database đi!" Hừm... nếu bạn thử nhập tay từng dòng thì... chắc bạn không chỉ cần kỹ năng SQL mà còn cần nghỉ phép luôn đó. 😅

May mà PostgreSQL có một lệnh hoạt động như cổng dịch chuyển từ CSV vào database — đó là COPY. Nó tải dữ liệu siêu nhanh, hàng loạt và không lằng nhằng. Hôm nay tụi mình sẽ tìm hiểu cách COPY hoạt động, chuẩn bị dữ liệu ra sao, và phải làm gì nếu file có "dấu phẩy trôi nổi" hay ký tự lạ. Bắt đầu nhé!

Cơ bản về lệnh COPY

COPY — là lệnh của PostgreSQL cho phép di chuyển dữ liệu giữa bảng và hệ thống file. Nó hữu ích để tải dữ liệu hàng loạt vào database hoặc xuất dữ liệu ra ngoài.

Ví dụ cú pháp COPY để tải dữ liệu vào bảng:

COPY table_name FROM 'path/to/your/file.csv' DELIMITER ',' CSV HEADER;

Các tham số chính:

• FROM 'path/to/your/file.csv': chỉ đường dẫn tới file CSV của bạn.
• DELIMITER ',': chỉ ký tự phân tách (ở đây là dấu phẩy).
• CSV HEADER: báo là file CSV có dòng tiêu đề (dòng đầu tiên là tên cột).

Lệnh COPY chạy trực tiếp trên server, nên nó cực kỳ nhanh khi xử lý dữ liệu lớn.

Phân biệt COPY và \COPY

Đôi khi bạn sẽ gặp chút nhầm lẫn giữa COPY và \COPY.

• COPY: chạy phía server. Dùng để tải file nằm trên server.
• \COPY: lệnh phía client, dùng trong psql. Dữ liệu được tải từ máy tính cá nhân của bạn.

Nếu bạn làm việc trên máy cá nhân, khả năng cao bạn sẽ dùng \COPY. Tụi mình sẽ nói về nó sau nhé.

Ví dụ sử dụng COPY

Giờ vào ví dụ luôn cho dễ hình dung cách hoạt động.

Bước 1: Chuẩn bị bảng

Giả sử tụi mình tạo bảng để lưu thông tin sinh viên:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    grade FLOAT
);

Bước 2: Chuẩn bị file CSV

Định dạng file CSV của bạn phải khớp với bảng. Ví dụ file students.csv:

first_name,last_name,date_of_birth,grade
John,Doe,2001-05-15,85.5
Jane,Smith,2000-12-22,90.0
Alice,Johnson,2002-03-10,78.0
Bob,Brown,2001-08-30,NULL

Lưu ý: thứ tự cột trong CSV phải giống với bảng, và kiểu dữ liệu cũng phải khớp (ví dụ ngày tháng phải theo dạng YYYY-MM-DD).

Bước 3: Tải dữ liệu

Để tải file students.csv vào bảng students dùng lệnh sau:

COPY students (first_name, last_name, date_of_birth, grade)
FROM '/path/to/your/students.csv'
DELIMITER ',' 
CSV HEADER;

Ở đây có gì?

• Tụi mình chỉ tên bảng students và các cột sẽ nhận dữ liệu.
• Đường dẫn file chỉ vị trí file trên máy.
• Ký tự phân tách , nghĩa là dữ liệu cách nhau bằng dấu phẩy.
• Tham số CSV HEADER báo cho PostgreSQL biết dòng đầu là tên cột.

Kết quả:

Sau khi chạy lệnh, dữ liệu từ file sẽ vào bảng, bạn có thể kiểm tra ngay:

SELECT * FROM students;

Giới hạn và lưu ý

Để tránh lỗi, hãy chắc chắn file CSV của bạn đáp ứng các yêu cầu sau:

1. Không có ký tự ẩn hay khoảng trắng thừa trong dòng tiêu đề.
2. Dùng đúng encoding: PostgreSQL thường khuyên dùng UTF-8.
3. Ký tự phân tách phải trùng với tham số DELIMITER bạn chỉ định.

Lỗi thường gặp khi dùng COPY:

Cấu trúc dữ liệu không khớp. Ví dụ, bạn cố nhập giá trị text vào trường số:

ERROR: invalid input syntax for type numeric: "abc"

Để tránh lỗi này, hãy chắc dữ liệu đúng kiểu như mong đợi.

Lỗi đường dẫn file.

Nếu file nằm ngoài thư mục PostgreSQL có quyền truy cập trên server, bạn sẽ gặp lỗi:

ERROR: could not open file "/path/to/your/file.csv" for reading: Permission denied

Giải pháp là chỉnh lại quyền truy cập file cho đúng.

Làm sao kiểm tra dữ liệu đã tải đúng chưa?

Sau khi tải dữ liệu, nên kiểm tra lại cho chắc ăn. Ví dụ:

Kiểm tra tổng số dòng:

SELECT COUNT(*) FROM students;

Tìm giá trị rỗng (ví dụ trường grade có NULL):

SELECT * FROM students WHERE grade IS NULL;

Mẹo hay khi dùng COPY

Ghi log lỗi. Nếu muốn ghi lỗi ra file riêng, dùng tham số LOG ERRORS. (Từ PostgreSQL version 12+).

Tạm tắt index và trigger.

Khi tải dữ liệu lớn, bạn có thể tạm tắt index:

ALTER TABLE students DISABLE TRIGGER ALL;

Dùng transaction.

Cách này giúp tải dữ liệu "nguyên khối" — hoặc vào hết, hoặc rollback nếu có lỗi:

BEGIN;
COPY students FROM '/path/to/your/file.csv' CSV HEADER;
COMMIT;

Ứng dụng thực tế

Biết dùng lệnh COPY không chỉ admin database mới cần — dev cũng sẽ thấy nó cực kỳ tiện. Ví dụ bạn nhận được đống dữ liệu từ API bên ngoài: lưu ra file CSV, rồi nạp vào PostgreSQL — thế là có thể bắt đầu phân tích luôn. Hoặc đồng nghiệp nhờ chuyển database cũ sang hệ mới dùng PostgreSQL. Trong mấy tình huống này, COPY đúng là cứu tinh luôn.

File CSV xuất hiện liên tục trong dự án thực tế. Ví dụ:

• Bạn tải thống kê bán hàng vào hệ thống phân tích.
• Nhập danh sách user từ CRM ngoài.
• Chuyển dữ liệu từ Excel sang PostgreSQL mà không cần lằng nhằng.

Vậy là tụi mình vừa làm quen với lệnh COPY. Bài sau sẽ nói về cách chuẩn bị bảng để tải dữ liệu và thiết kế cấu trúc sao cho mọi thứ chạy nhanh, ổn định. Còn giờ đừng ngại thử nghiệm — COPY thực sự mở ra nhiều cách làm việc tiện lợi với dữ liệu lớn.