[PostgreSQL] COPY FROM CSV 사용 시 Date, Time Format 지정하기

[PostgreSQL] COPY FROM CSV 사용 시 Date, Time Format 지정하기

  오전 12:25    홍다슬

---

개발프로그램

Postgresql 9.6

csv파일의 값 형태가 컬럼형에 맞지 않아 직접 넣을 수 없을 때, 

formatting을 해야하는 경우가 있다.
예를 들면 timestamp형 데이터로 09:30:10을 넣어야 하는데
csv에 093010라고 적혀있어서 오류가 뜨는 경우이다.

결론만 말하자면 to_timestamp()를 이용하는 간편한 방법은 없다.

총 3가지가 있는데,
2, 3번의 경우 임시 데이터를 생성하므로 사용할 데이터가 대용량일 경우 

작업 PC의 하드 용량에 어느 정도 여유분이 필요하다는 주의사항이 있다.

사용할 table의 컬럼은 대충
test_table (
id INT4,
date DATE,
time TIMESTAMP)
라는 전제로 하겠다. (time 외 다른 컬럼에 별 의미는 없다)
csv 파일명은 test.csv, 테이블과 동일한 컬럼 형태에 ','로 구분됐다는 전제를 두겠다.

1. 초반에 VARCHAR형으로 선언한 후 ALTER문으로 TYPE 변경하기
일단 VARCHAR형으로 선언하면 문자열이기 때문에 어떤 형식이든 입력은 성공한다.
그리고 to_timestamp()를 써서 컬럼형을 변경하는 방법이다.

장점은 임시적인 컬럼이나 파일이 필요없다는 것,
단점은 동일한 형식의 csv파일을 추가적으로 COPY할 수 없기 때문에
필요할 때는 결국 2 또는 3번 방법을 이용해야 한다는 것이다.

(HEADER는 헤더를 없애고 읽는다는 뜻, ENCODING은 말 그대로 csv 파일의 인코딩이다.)

COPY test_table(id, date, time) FROM 'test.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

ALTER TABLE test_table ALTER COLUMN time TYPE TIME USING (to_timestamp(time_char, 'HH24MISS')); 

2. VARCHAR형 임시 컬럼 생성 후 본래 컬럼에 다시 UPDATE하기

예를 들어 time_char라는 임시 컬럼을 만들고 거기에 csv의 자료형을 넣고,
본래 써야 할 time 컬럼은 null로 둔 뒤
UPDATE문을 써서 변경하는 방법이다.

time_char은 이후에 컬럼을 제거해도 되지만
이후에 또 동일한 형식의 csv파일을 추가적으로 COPY할 수도 있을 경우에는
일단 냅두고 UPDATE문에 null 조건을 달아 동일한 작업을 하면 되지 않을까 싶다.

COPY test_table(id, date, time_char) FROM 'test.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

UPDATE dtg_data SET time = to_timestamp(time_char, 'HH24MISS');

3. csv 파일 자체를 해당 포맷에 맞게 변환하기
데이터의 용량이 클 경우 무조건 이 방법을 택하기를 권한다.
실제로 나는 csv파일만 약 30GB인 파일들로 작업을 했는데,
하나당 COPY에도 약 3일이 걸리고 UPDATE에도 마찬가지로 3일정도가 걸려서
실제로 많은 시간을 소모했으나
이 방법을 마지막에서야 깨닫고 시도해보니 훨씬 시간이 단축되었다.
(하지만 COPY할때 걸리는 3일은 어쩔 수가 없었던...)

언어는 무얼 쓰든 자유이나, 나는 이전에 Python으로 csv 관련 작업을 한 적이 있어 익숙한 편이라 이것을 택했다.

지금 예제의 프로그램을 써보자면 (csv파일의 위치는 C:/ 바로 아래라고 하자)

import csv
with open('C:\\test.csv', 'r') as csvfile:
    reader = csv.DictReader(csvfile)
    with open('C:\\test2.csv', 'w', newline='') as csvfile2:
        writer = csv.writer(csvfile2, delimiter = ',')
        writer.writerow(reader.fieldnames)
        for row in reader:
            split_time = row['time']
            time = split_time[0] + split_time[1] + ':' + split_time[2] + split_time[3] + ':' + split_time[4] + split_time[5]
            row['time'] = time
            writer.writerow(row.values())

test2의 newline='' 속성은 개행을 없앤다는 뜻이다.
쓰지 않을 경우 각 행들이 모두 한 줄씩의 공백(즉 엔터를 두번 친 모양)을 가진 채 생긴다.

단순히 time값을 한글자씩 쪼개어 사이사이에 ':'를 입력하고 time행을 수정,
그 후 행 단위로 csv를 입력해나가는 간단한 프로그램이다.

이렇게 하면 포맷이 설정된 새로운 test2.csv라는 파일이 형성된다.
(정확히는 측정하지 못했으나 30GB 처리 소요시간이 2~3시간쯤으로 추정된다.)

그리고 앞의 예제들과 마찬가지로 COPY를 쓰면 된다.

COPY test_table(id, date, time) FROM 'test2.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

데이터 추가시에도 이와 같이 새로운 csv 파일을 생성하고, COPY하면 된다.

참고 사이트: https://www.postgresql.org/message-id/55BA474E.7080605%40aklaver.com