MySQL LOAD DATA 를 사용하며...

1. LOAD DATA ... INFILE 에 대하여

- LOAD DATA 문은 매우 빠른 속도로 텍스트 파일의 행을 테이블로 읽습니다. LOCAL 수정자가 지정되었는지 여부에 따라 서버 호스트 또는 클라이언트 호스트에서 파일을 읽을 수 있습니다. LOCAL은 데이터 해석 및 오류 처리에도 영향을 미칩니다.

 

- LOAD DATA로 읽어들인 데이터를 테이블에 작성하고, 테이블에서 파일에 데이터를 쓰기 위해 SELECT... INTO OUTFILE을 사용합니다. 파일을 테이블로 다시 읽으려면 LOAD DATA를 사용합니다. FILDS 절과 LINES 절의 구문은 두 문에서 동일합니다.

 

2. 사용법

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

2.1. LOAD DATA LOCAL INFILE ... INTO TABLE table_name

이는 DB Server에 있는 파일이 아니고, 클라이언트에 있는 파일을 업로드 하고자 할 때에 사용합니다.

-- Create Movie table
CREATE TABLE movie (
	`name` VARCHAR(60) NOT NULL,
	`director` VARCHAR(20),
	`actor` VARCHAR(20),
	`audience` BIGINT
);

-- LOAD DATA LOCAL INFILE문
LOAD DATA LOCAL INFILE 'c:\\dd\\m.txt'
INTO TABLE movie
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'

load data 도중에 다음과 같은 에러가 뜬다

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

이 때에는 다음 명령어를 실행해줍니다.

set global local_infile=1;

이 때에 또 이런 에러가 뜬다.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

이 에러는 my.cnf 파일에 다음 내용을 추가해준다.

secure-file-priv=""

 

m.txt 파일은 다음과 같습니다.

해운대|윤제균|설경구|1139
광해|추창민|이병헌|1232
국제시장|윤재균|황정민|1318
도둑들|최동훈|전지현|1302
변호인|양우석|송강호|1137

불러온 테이블을 파일로 작성하기 위해 다음과 같이 사용할 수 있습니다.

SELECT * INTO OUTFILE `file_name`

선택된 row들을 파일로 보냅니다. 여기서 file_name 파일이 이미 존재할 때에는 사용할 수 없습니다.

위의 m.txt파일과 같은 형식으로 작성하기 위해서는 field의 구분자를 지정해주어야 합니다. (기본 값은 \t입니다.)

SELECT * FROM movie INTO OUTFILE 'm.txt'
	FIELDS TERMINATED BY '|',
	LINES TERMINATED BY '\n';

 

3. LOAD DATA의 성능

https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html 를 참고하면

텍스트 파일에서 테이블을 로드할 때는 LOAD DATA를 사용합니다. 이것은 보통 INSERT 문을 사용하는 것보다 20배 더 빠르다고 합니다. 이는 열에 기본값이 있다는 것을 활용하여 삽입할 값이 기본값과 다를 경우에만 명시적으로 값을 삽입해주며, MySQL이 수행해야 하는 구문 분석(parsing)에 걸리는 시간을 줄여주고, 삽입 시간을 향상시켜준다고 합니다.

 

4. 느낀 점

LOAD 를 하기위해서 MySQL을 여러번 지웠다가 실행했다가도 해 보고 brew에서 설치해보기도 했다.

가장 깔끔했던 방법은 brew 로 MySQL을 설치하는것이었다. 그리고 my.cnf 파일을 수정할 필요도 있었는데 이 또한 찾아본 게시글마다 달라서 조금 애먹었다. secure-file-priv에 ""를 지정해주는것은 실제 database 에서는 안하는게 좋을 것 같다.

 

5. 참고자료

http://miconblog.com/archives/1663

Mac에서 MySQL 설치와 실행 그리고 몇가지 유의사항

https://myinfrabox.tistory.com/209

[MySQL] ERROR 1290 --secure-file-priv option 장애

https://snepbnt.tistory.com/89

[Mysql 에러] ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides 에