MySQL를 배우던 도중 학습용 CSV 파일을 Import 할 일이 생겼습니다. Import 방법은 크게 두가지로 나뉩니다.

• LOAD DATA Statement
• 클라이언트 프로그램을 사용해 Import

클라이언트 프로그램을 사용하면 몇번의 클릭에 Import가 가능하겠지만, 저는 CLI를 사용하고 있기도 하고 나중에 배치 프로그램을 만들어 처리를 할 가능성도 있기 때문에 가능하면 SQL 을 통해 적재를 하고 싶었습니다.

실행 환경

OS : MacOs
MySQL: Docker Image (tag: latest)
DB manage tool : MySQL-cli

데이터 샘플

실습

1st. 테이블 생성

워크벤치에서는 Table Data Import 기능으로 CSV 파일을 자동으로 테이블을 Import가 가능합니다. 하지만 _CLI 에서는 테이블을 직접 생성해주어야 합니다.

CREATE TABLE customer(
	id int,
    gender varchar(10),
    age int,
    created_at date
);

2nd. MySQL 컨테이너 볼륨에 CSV 파일 넣기

컨테이너 볼륨에 CSV 파일을 넣으면 됩니다.
컨테이너 내부에서는 var/lib/mysql 경로로 파일을 찾을 수 있습니다.

3rd. 쿼리 작성

LOAD DATA 
    INFILE '/var/lib/mysql/sample_datas/tbl_customer.csv'	-- 해당 파일을...
    INTO TABLE customer										-- customer 테이블에 넣는다.
    FIELDS TERMINATED BY ','								-- 각각의 필드는 ,으로 나뉜다.
    LINES TERMINATED BY '\n'								-- 각각의 row는 줄건너뛰가로 나뉜다.
    IGNORE 1 LINES											-- 가장 첫번째 row는 필드명이기 때문에 무시한다.
    (id, created_at, gender, age); 							-- csv 데이터 순서에 맞게 입력하자.

trobleShooting : --secure-file-priv option 에러 발생

보안을 위해 허용된 prefix 내의 파일만 읽게 정하는 설정인 secure_file_priv 을 확인해봐야 합니다.

select @@global.secure_file_priv;

+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

넣은 파일의 경로와 해당 환경변수의 경로가 다르다면 에러가 발생하게 됩니다. 이를 해결하기 위한 방법은 두가지 입니다.

• 파일을 해당 경로로 이동
• 환경 변수 변경

환경 변수 변경하기

우선 해당 환경변수는 읽기전용이기 때문에 running 환경에서는 변경이 불가합니다.

mysql> set global secure_file_priv="";
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

그래서 설정파일로 이동해 직접 변경을 해주어야 합니다.

$ vi /etc/mysql/my.cnf     # mysql 환경변수 설정

[mysqld]
secure-file-priv = ""      # 경로 삭제
secure-file-priv = "/var/lib/mysql/sample_datas"  # 또는 현재 데이터가 있는 경로로 변경

다시 한번 데이터를 입력하면 성공하게 됩니다.

참고자료

https://dev.mysql.com/doc/refman/8.0/en/load-data.html