DB별 CSV 파일 로딩 방법

개발하면서 심심치 않게 CSV 파일을 데이터베이스에 부어 넣어야 하는 경우가 생긴다. 시스템을 처음 구축하면서 초기 데이터를 만들어 넣는다든가 뱃치성 작업으로 외부 시스템과 연동한다든지 할 때 등이다. 그래서 한번 DB별 CSV 파일 로딩 방법을 정리해야겠다는 생각이 들었다. 보다 체계적인 설명이나 자세한 옵션들은 역시나 사용 설명서를 참고하도록 한다.

한 가지 주의할 것은 데이터베이스에서 로딩할 수 있는 CSV 파일 형식이 꽤 제한적이라는 점이다. 데이터 값은 콤마로 구분되었고 큰따옴표로 감싼다는 기본적인 사항은 지원하지만 숫자를 문자열로 인식하게 하기 위한 작은따옴표, 수식이나 문자열 수식을 나타내는 등호(=), 큰따옴표 안의 문자열 이스케이프를 위한 2중 큰따옴표 등 다양한 CSV 형식을 모두 인식하지는 않는다. 따라서 CSV 파일을 만들 때부터 로딩이 잘 될 수 있도록 형식에 신경써야 한다. (CSV 파일 형식은 아직 표준화 노력이 진행 중이다. 그래서 일단은 가장 간단한 규칙만 고려해야할 필요도 있다.)

Oracle

오라클은 SQL*Loader라는 번들 프로그램을 사용하며 로딩 명령은 "컨트롤 파일"이라고 하는 별도의 스크립트로 작성해야 한다. 준비가 되면 다음과 같이 실행한다.

sqlldr 사용자명/비밀번호@DB명 CONTROL=컨트롤파일

로딩할 데이터 파일이나 작업 기록, 로딩 실패 로그를 컨트롤 파일에서 지정할 수도 있지만 아래처럼 명령 인자로 지정할 수도 있다.

sqlldr 사용자명/비밀번호@DB명 CONTROL=컨트롤파일 LOG=로그파일 BAD=오류로그파일 DISCARD=오류데이터로그파일 DATA=입력데이터파일

컨트롤 파일의 내용은 가장 간단하게는 다음과 같이 작성한다. SQL의 특성상 대소문자는 구별하지 않는다.

load data
append
into table my_table
fields terminated by ',' optionally enclosed by '"'
(
    col1, col2, col3, col4, col4, ...
)

위의 예는 CSV 파일에서 순서대로 col1, col2, col3,…컬럼 값들을 추출해서 my_table 테이블에 "append"하라는 것이다. 2행의 append 대신 insert, replace, truncate를 사용할 수 있는데 append는 테이블에 데이터가 있더라도 추가하기, insert는 테이블에 데이터가 전혀 없는 경우에만 추가하기, replace는 delete from 테이블 구문으로 먼저 데이터 삭제 후 추가하기(트리거가 있는 경우 실행, 참조 제약 고려), truncate는 truncate table 테이블 reuse storage 구문으로 데이터 삭제 후 추가하기(참조 제약 조건이 중지된 상태여야 하며 replace보다 훨씬 빠름)를 의미한다. 아무 것도 없을 경우 insert가 기본 값이다.

다음은 좀더 복잡한 예시다.

load data
characterset utf8
infile '/path/data_file' "str '\r\n'"
badfile '/path/bad_file'
discardfile '/path/discard_file'
replace into table my_table
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
when col2 = 'Y'
(
    col1 sequence (max), col2, col3 sysdate, ...
)

2행에서는 데이터 파일의 문자셋이 utf-8임을 지정하고 있다.

3행의 str '\r\n'은 CSV 파일의 레코드 구분자를 윈도에서의 줄바꿈 형식을 사용한다는 것이다. 생략하면 유닉스 계열 OS에서는 \n을, 윈도에서는 \r\n을 기본으로 사용하며 ;와 같은 임의의 글자를 사용할 수도 있다.

4행에서는 INSERT 구문 실행시 오류가 발생한 레코드를, 4행에서는 아예 데이터가 없거나 형식이 안 맞아서 INSERT 구문도 실행할 수 없는 레코드를 로그 파일로 남기도록 한다.

8행에서는 어떤 레코드에서 컬럼 개수가 안 맞는 경우 뒷 컬럼들의 값은 모두 NULL로 넣도록 한다.

11행에서는 col1의 값을 순열 값으로 만들어 넣되 테이블의 기존 col1 값 중 가장 큰 값을 처음 값으로 시작해서 넣도록 한다. sequence (10000)과 같이 임의의 숫자 값부터 시작하도록 지정할 수도 있고 sequence (10000, 10)과 같이 10씩 증가하도록 지정할 수도 있다. 또한 col3 값에는 현재 시간을 넣고 있다.

참고로 값이 탭으로 구분되었고 큰따옴표로 필드를 감싼 파일을 읽으려면 fields 절을 다음과 같이 지정한다.

fields terminated by '\t' optionally enclosed by '"'

사실 SQL*Loader는 CSV 형식 데이터만 읽을 수 있는 것이 아니다. 오라클 문서에서는 고정 폭 데이터라든가 길이 지정 레코드를 읽는 방법도 나와 있으니 필요하면 참고한다.

MySQL

MySQL에서는 CSV 로딩 기능이 일반 SQL 구문(DML)처럼 취급된다. 따라서 CUI나 GUI 같은 클라이언트 프로그램에서 로딩 구문을 실행할 수도 있고 클라이언트 프로그램의 명령 인자에 로딩 구문을 지정할 수도 있다. 그러나 명령 인자로 로딩 구문을 지정하는 것은 상당히 제한적이다. 또한 mysqlimport 유틸리티를 사용할 수도 있는데 마찬가지로 제한적인 면이 있다.

다음은 DML 방식인 경우의 간단한 예시다.

load data
local infile '/path/data_file'
replace into table my_table
fields terminated by ',' optionally enclosed by '"'
;

위 예시에서는 필드를 지정하지 않았다. CSV에서 찾은 필드를 테이블 컬럼에 1:1 대응되게 집어넣겠다는 의미가 된다.

2행의 local은 CSV 데이터 파일이 클라이언트 머신에 있음을 의미한다. 지정되지 않은 경우는 MySQL 서버가 있는 디스크에서 읽게 되며 상대 경로인 경우 데이터 폴더에서 CSV 파일을 찾는다.

3행의 replace는 CSV의 데이터와 테이블에 PK 값이 중복되는 경우 CSV 데이터로 바꿔 넣겠다는 의미다. ignore를 지정하면 원래 데이터를 유지하고 CSV 데이터의 레코드는 무시된다.

다음 예시를 보자.

load data
local infile '/path/data_file'
ignore into table my_table
character set utf8
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n'
ignore 1 rows
(field_no, @val1, @val2, @val3)
set code = case when @val3 > '' then @val3
                when @val2 > '' then @val2
                else @val1 end,
    name = trim(concat(@val1, @val2, @val3))
;

6행에서는 레코드의 끝을 지정하고 있다. MySQL에서 레코드 끝은 \n이 기본값이므로 윈도에서 만든 CSV 파일을 읽으려면 이 줄이 꼭 필요할 수 있다.

7행에서는 CSV 파일의 첫 줄은 무시하도록 지정한다.

8행에서는 CSV 파일의 필드를 field_no라는 테이블 컬럼에 들어갈 값과 @val1, @val2, @val3라는 변수 세 개로 받은 다음 그 아래 set 절에서 테이블의 나머지 컬럼 값을 지정하고 있다. CSV 파일의 필드와 테이블의 컬럼들이 1:1 대응되지 않는 경우 이렇게 필드를 변수로 대치하고 일반적인 SQL 구문을 다 사용할 수 있다.

값이 탭으로 구분되었고 큰따옴표로 필드를 감싼 파일의 경우 오라클과 똑같은 fields 절을 지정하면 된다. 보다 자세한 사항은 MySQL 참조 설명서를 참고한다.

H2

자바 데이터베이스인 H2에서는 csvread 명령이 테이블과 똑같이 취급되므로 insert ... select ... from csvread ... 구문을 사용하면 CSV 파일을 테이블에 읽어들일 수 있다.

insert into my_table
select * from csvread('/path/data_file')
;

좀더 복잡한 예시를 보자.

insert into my_table
select col1, parsedatetime(col2, 'yyyyMMdd'), substr(col3, 1, 4)
from csvread('http://www.address.co.kr/file', 'col1|col2|col3', 'charset=UTF-8')
where col2 = 'Y'

3행에서는 데이터 파일을 URL로 지정하고 있다. 즉, 로컬 파일 뿐 아니라 네트웍으로 데이터를 읽어들일 수 있다. 또한 col1, col2, col3와 같은 CSV 필드를 선언함으로써 4행의 where 절에 필드 명을 사용할 수 있다. 이렇게 필드명을 지정한 경우는 CSV 파일의 첫 행이 데이터인 것으로, 필드명이 없는 경우는 CSV 파일 첫행을 필드명이 나열된 행인 것으로 처리한다.

csvread의 세번째 인자에는 구분자가 무엇인지 등 여러 옵션을 지정할 수 있다. 탭 구분 텍스트 파일을 읽으려면 다음과 여러 옵션을 빈칸으로 띄어써서 지정할 수 있다. SQL에서 CHAR(9)는 탭 문자다.

csvread('...', NULL, 'charset=euckr fieldDelimiter=\" fieldSeparater=' || CHAR(9))

보다 자세한 설명은 H2 설명 문서를 참고한다.

기타

데이터베이스에 CSV 파일 로딩 기능이 없다든가 원본 데이터가 CSV가 아니라 엑셀 파일이라면 엑셀에서 SQL Insert 문을 만드는 방법을 활용할 수도 있을 것이다. 예를 들어 다음과 같은 구문을 빈 열에 입력하고 모든 데이터 행마다 복사하면(셀 오른쪽 아래 모퉁이를 두번 클릭하거나 잡아끌면) INSERT SQL문을 간단히 만들어낼 수 있을 것이다.

="insert into my_table (col1, col2, col3) values ('" & A1 & "', '" & B1 & "', '" & C1 & "');"

실제 프로젝트에서는 CSV 뿐 아니라 다양한 형식의 플랫(flat) 파일이 데이터 파일로 사용되기 때문에 오라클 SQL*Loader 같은 도구가 없는 데이터베이스라면 직접 파싱해야 하는 경우가 종종 있다. 문자열 파싱을 순수하게 프로그램적으로 처리하려면 쉽지 않은 작업이므로 위에서 설명한 경우를 활용할 수 있는지 먼저 확인한 다음 그런 도구 개발을 고려해보는 것이 좋다.