대량 로드 시 구분자와 데이터 포맷 설정하기

데이터가 여러 시스템에서 오면, 포맷이 다를 수밖에 없어. 어떤 파일은 콤마(,)를 구분자로 쓰고, 또 어떤 파일은 세미콜론(;)을 쓰거나, 탭(tab)이 컬럼을 나누는 경우도 있지. 데이터 로드할 때 구분자를 잘못 설정하면 에러가 나거나 데이터가 이상하게 들어갈 수 있어.

게다가 실제로는 CSV 헤더 없이 텍스트 포맷으로 데이터를 로드해야 하거나, 빈 값을 처리해야 하고, 빈 줄을 NULL로 해석해야 하는 상황도 생겨. 그래서 구분자랑 포맷 설정은 대량 데이터 로드 작업에서 필수야.

주요 구분자: 어떻게 다뤄야 할까?

PostgreSQL에서 COPY 명령어는 구분자 설정을 꽤 유연하게 해줘. 어떻게 쓰는지 한번 보자.

구분자 설정하기

기본적으로 COPY는 CSV 파일에서 컬럼을 콤마(,)로 구분한다고 생각해. 근데 이게 항상 편한 건 아니지. 누군가는 세미콜론(;), 파이프(|), 심지어 탭(\t)을 쓸 수도 있어.

구분자를 DELIMITER 파라미터로 지정하는 방법은 이래:

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

만약 콤마 대신 세미콜론을 쓴다면:

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

탭으로 구분된 파일도 이렇게 로드할 수 있어:

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

여기서 E'\t'는 탭 문자가 구분자라는 뜻이야.

비표준 구분자 파일 로드하기

실전 예시: 코스 정보가 담긴 파일이 있는데, 파이프(|)로 구분돼 있어. 파일 데이터는 이렇게 생겼어:

course_id|course_name|credits
1|SQL Basics|3
2|Advanced SQL|4
3|PostgreSQL Masterclass|5

이 데이터를 courses 테이블에 넣으려면 이렇게 하면 돼:

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

여기서 PostgreSQL한테 파이프가 구분자라고 명확히 알려주는 거야.

데이터 포맷 설정하기

구분자는 작업의 일부일 뿐이야. 파일 안 데이터 포맷도 엄청 중요하지. 포맷 설정하는 기본 방법들을 보자.

빈 줄 무시 & NULL 값 지정하기

대량 데이터에는 종종 빈 줄이나 값이 없는 컬럼이 있어. PostgreSQL은 따로 지정 안 하면 그냥 빈 문자열로 받아들여. 이런 값을 NULL로 해석하려면 NULL AS 파라미터를 써야 해:

예시. 파일에 빈 값이 있는 경우:

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

email 컬럼의 빈 값을 NULL로 해석해서 로드하려면:

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

이렇게 하면 파일의 빈 값이 테이블에 NULL로 저장돼.

빈 줄 무시하기

가끔 파일에 빈 줄이 있는데, 이런 건 굳이 로드할 필요 없어. PostgreSQL은 이런 줄을 자동으로 무시할 수 있어.

예시. 빈 줄이 있는 파일:

id,first_name,last_name,email
1,John,Doe,john.doe@example.com

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

IGNORE_BLANK_LINES 파라미터를 써보자:

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

이제 빈 줄은 로드할 때 무시돼.

비표준 데이터 포맷 다루기

가끔 CSV 대신 텍스트 포맷으로 데이터가 오기도 해. 예를 들어, 줄마다 파이프(|)로 구분돼 있고, 헤더 줄도 없어.

파일 예시:

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

이럴 땐 이렇게 쿼리 쓰면 돼:

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

헤더가 없으면 HEADER 파라미터는 빼면 돼.

데이터 포맷 설정 실전 예시

시나리오: grades.tsv 파일에 학생 점수가 들어있어. 데이터는 이렇게 생겼어:

student_id  course_id   grade
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

해야 할 일:

1. 빈 값을 NULL로 해석해서 파일을 로드해.
2. 데이터가 제대로 들어갔는지 확인해.

해결 방법:

1. grades 테이블을 만들어:

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

2. 파일에서 데이터 로드해:

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

3. 데이터가 잘 들어갔는지 확인해:

SELECT * FROM grades;

예상 결과:

팁 & 자주 하는 실수들

실수: 구분자 불일치. 구분자를 제대로 지정 안 하면 PostgreSQL이 에러를 내거나 데이터를 이상하게 넣어. 예를 들어, 파일에 세미콜론이 있는데 DELIMITER ';'를 안 쓰면, COPY가 한 줄 전체를 한 컬럼으로 받아들여.

실수: NULL 해석 잘못함. NULL AS '' 파라미터를 안 쓰면, 파일의 빈 값이 그냥 빈 문자열로 들어가서 계산이나 필터링할 때 문제 생길 수 있어.

실수: 데이터 포맷 오류. 구분자 설정이 잘못됐거나 파일에 에러(예: 탭 대신 스페이스)가 있으면 이런 에러가 나올 수 있어: ERROR: null value in column violates not-null constraint.