Cài đặt dấu phân cách và định dạng dữ liệu khi import

Khi dữ liệu đến từ nhiều hệ thống khác nhau, định dạng của nó có thể không giống nhau. Một file có thể dùng dấu phẩy làm dấu phân cách, file khác lại dùng dấu chấm phẩy, còn file thứ ba thì tab lại là cách chính để tách cột. Nếu cài đặt dấu phân cách không chuẩn khi import dữ liệu thì dễ bị lỗi hoặc dữ liệu bị hiểu sai.

Chưa hết đâu, ngoài đời thực bạn sẽ gặp cảnh phải import dữ liệu ở dạng text mà không có header kiểu CSV chuẩn, rồi còn phải xử lý giá trị rỗng và cả trường hợp dòng trống cần được hiểu là NULL. Vì vậy, việc cài đặt dấu phân cách và định dạng là phần không thể thiếu khi làm việc với import dữ liệu hàng loạt.

Dấu phân cách cơ bản: xử lý thế nào?

Trong PostgreSQL, lệnh COPY khá linh hoạt cho việc cài đặt dấu phân cách. Cùng xem nó hoạt động ra sao nhé.

Cài đặt dấu phân cách

Mặc định, lệnh COPY sẽ nghĩ rằng các cột trong file CSV được phân tách bằng dấu phẩy. Nhưng thực tế thì không phải lúc nào cũng vậy: có người dùng dấu chấm phẩy (;), có người lại dùng dấu gạch dọc (|) hoặc thậm chí là tab (\t).

Đây là cách chỉ định dấu phân cách bằng tham số DELIMITER:

COPY students FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER;

Nếu thay vì dấu phẩy mà dùng dấu chấm phẩy:

COPY students FROM '/path/to/students.csv'
DELIMITER ';'
CSV HEADER;

Cũng có thể import file dùng tab:

COPY students FROM '/path/to/students.tsv'
DELIMITER E'\t' 
CSV HEADER;

Ở đây E'\t' nghĩa là dấu phân cách là ký tự tab.

Import file với dấu phân cách không chuẩn

Ví dụ thực tế: bạn có file dữ liệu về các khóa học, trong đó dùng dấu gạch dọc (|) làm dấu phân cách. Dữ liệu trong file như sau:

ma_khoa_hoc|ten_khoa_hoc|so_tin_chi
1|SQL Co Ban|3
2|SQL Nang Cao|4
3|PostgreSQL Masterclass|5

Đây là cách import dữ liệu này vào bảng courses:

COPY courses(course_id, course_name, credits)
FROM '/path/to/courses.csv' 
DELIMITER '|' 
CSV HEADER;

Ở đây mình nói rõ với PostgreSQL là dấu phân cách là dấu gạch dọc.

Cài đặt định dạng dữ liệu khi import

Dấu phân cách chỉ là một phần thôi. Định dạng dữ liệu trong file cũng quan trọng không kém. Cùng xem các cách cài đặt định dạng dữ liệu cơ bản nhé.

Bỏ qua dòng trống và gán NULL

Trong các file dữ liệu lớn, thường sẽ có dòng hoặc cột bị trống, không có dữ liệu. PostgreSQL sẽ hiểu nó là chuỗi rỗng nếu bạn không nói gì thêm. Để hiểu các giá trị này là NULL thì dùng tham số NULL AS:

Ví dụ. Giả sử file của bạn có dòng bị trống giá trị:

id,ten,ho,email
1,John,Doe,
2,Jane,Smith,jane.smith@example.com
3,,Brown,james.brown@example.com

Import dữ liệu và hiểu giá trị trống ở cột email là NULL:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '';

Kết quả là giá trị trống trong file sẽ được lưu thành NULL trong bảng.

Bỏ qua dòng trống

Đôi khi file có thể có dòng trống mà bạn không muốn import. PostgreSQL cho phép tự động bỏ qua các dòng này.

Ví dụ. File có dòng trống:

id,ten,ho,email
1,John,Doe,john.doe@example.com

2,Jane,Smith,jane.smith@example.com

Dùng tham số IGNORE_BLANK_LINES:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '' 
IGNORE_BLANK_LINES;

Bây giờ dòng trống sẽ bị bỏ qua khi import.

Làm việc với định dạng dữ liệu không chuẩn

Đôi khi bạn cần import dữ liệu ở dạng text thay vì CSV. Ví dụ, các dòng trong file được phân tách bằng dấu gạch dọc |, và không có dòng header.

Ví dụ file:

1|John|Doe|john.doe@example.com
2|Jane|Smith|jane.smith@example.com

Trường hợp này có thể dùng câu lệnh sau:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.txt' 
DELIMITER '|' 
NULL AS '' 
CSV;

Nếu file không có header thì chỉ cần bỏ tham số HEADER đi là được.

Ví dụ thực tế về cài đặt định dạng dữ liệu

Tình huống: bạn có file grades.tsv chứa điểm số của sinh viên. Dữ liệu như sau:

ma_sinh_vien  ma_khoa_hoc   diem
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

Yêu cầu:

1. Import file và hiểu giá trị trống là NULL.
2. Đảm bảo dữ liệu được import đúng.

Giải pháp:

1. Tạo bảng grades:

CREATE TABLE grades (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER
);

2. Import dữ liệu từ file:

COPY grades(student_id, course_id, grade)
FROM '/path/to/grades.tsv' 
DELIMITER E'\t' 
NULL AS '' 
CSV HEADER;

3. Kiểm tra dữ liệu đã import:

SELECT * FROM grades;

Kết quả mong đợi:

Khuyến nghị và lỗi thường gặp

Lỗi: sai dấu phân cách. Nếu bạn không chỉ đúng dấu phân cách, PostgreSQL sẽ báo lỗi hoặc import sai dữ liệu. Ví dụ, file dùng dấu chấm phẩy mà bạn quên DELIMITER ';' thì lệnh COPY sẽ hiểu cả dòng là một cột luôn.

Lỗi: hiểu sai NULL. Nếu không chỉ NULL AS '', giá trị trống trong file sẽ bị hiểu là chuỗi rỗng, dễ gây lỗi khi tính toán hoặc lọc dữ liệu.

Lỗi: định dạng dữ liệu sai. Cài đặt dấu phân cách không chuẩn hoặc file bị lỗi (ví dụ dùng tab thay vì space) có thể gây lỗi kiểu: ERROR: null value in column violates not-null constraint.