[MySQL] Workbench LOAD DATA LOCAL INFILE ERROR

배경

FastCampus 강의 수강 중, csv 형식의 데이터를 테이블로 적재하는 과정 중 내장된 Import Wizard의 성능 이슈로 다른 방법을 모색했다. 초기 설정의 workbench에서는 보안적인 이유로 제한되는 LOAD DATA LOCAL INFILE 문을 활용하여 문제를 해결한 과정을 정리하였다.

 

STEP 1: Workbench Server Connection 설정

Server side의 옵션으로 OPT_LOCAL_INFILE=1 을 추가한다.

참고: CLI 사용자들은 mysql --local-infile=1 -h [서버주소] -u [사용자이름] -p [암호] 형식으로 값을 변경하면 된다.
자세한 내용은 관련 키워드로 검색하기!

MySQL Workbench - Instance - Edit Connection

Connection - Advanced - Others: OPT_LOCAL_INFILE=1

 

STEP 2: Client 설정

기본적으로 전역변수 local_infile의 값은 0(FALSE)으로 설정되어 있다.

SET GLOBAL local_infile = TRUE;

쿼리를 실행한 후에는 OFF 값이 ON으로 바뀐다.

default: local_infile = 0

쿼리 SET GLOBAL local_infile = TRUE; 실행 후

 

STEP 3: Query

다음은 LOAD DATA LOCAL INFILE 문을 활용하여 CSV 파일을 테이블에 적재하는 쿼리문이다.

DROP TABLE IF EXISTS prac.tbl_customer;
CREATE TABLE IF NOT EXISTS prac.tbl_customer (
	customer_id INT,
	created_at CHAR(25),
	gender VARCHAR(6),
	age INT
);
SHOW TABLES;

LOAD DATA LOCAL INFILE "C:/Users/user/Documents/prac/sqlPrac/source/tbl_customer.csv"
INTO TABLE prac.tbl_customer
FIELDS TERMINATED BY ","
IGNORE 1 LINES;

SELECT * FROM tbl_customer;

** 실습에 사용한 csv 파일의 첫번째 행은 필드명이 들어가기 때문에 IGNORE 1 LINES 문으로 입력을 넘어갔다.

RESULT

총 14226개의 레코드 중

변수명이 들어간 첫번째 줄을 제외하고

총 14225개의 레코드가 정상적으로 삽입되었다.

실습 쿼리 전문

SHOW DATABASES;
USE fastcampus;
SHOW TABLES;

/*
 * START IMPORT CSV FILE
 */
SET GLOBAL local_infile = TRUE;

/*
 * TABLE: tbl_customer
 */
DROP TABLE IF EXISTS fastcampus.tbl_customer;
CREATE TABLE IF NOT EXISTS fastcampus.tbl_customer (
	customer_id INT,
	created_at CHAR(25),
	gender VARCHAR(6),
	age INT
);
SHOW TABLES;

LOAD DATA LOCAL INFILE "C:/Users/user/Documents/prac/sqlPrac/source/tbl_customer.csv"
INTO TABLE fastcampus.tbl_customer
FIELDS TERMINATED BY ","
IGNORE 1 LINES;

SELECT * FROM tbl_customer;

/*
 * TABLE: tbl_purchase
 */
DROP TABLE IF EXISTS fastcampus.tbl_purchase;
CREATE TABLE IF NOT EXISTS fastcampus.tbl_purchase (
	id INT,
    customer_id INT,
    purchased_at CHAR(25),
    category VARCHAR(20),
	product_id VARCHAR(20),
	price INT
);
SHOW TABLES;

LOAD DATA LOCAL INFILE "C:/Users/user/Documents/prac/sqlPrac/source/tbl_purchase.csv"
INTO TABLE fastcampus.tbl_purchase
FIELDS TERMINATED BY ","
IGNORE 1 LINES;

SELECT * FROM tbl_purchase;

/*
 * TABLE: tbl_visit
 */
DROP TABLE IF EXISTS fastcampus.tbl_visit;
CREATE TABLE IF NOT EXISTS fastcampus.tbl_visit (
	id INT,
    customer_id INT,
    visited_at CHAR(25)
);
SHOW TABLES;

LOAD DATA LOCAL INFILE "C:/Users/user/Documents/prac/sqlPrac/source/tbl_visit.csv"
INTO TABLE fastcampus.tbl_visit
FIELDS TERMINATED BY ","
IGNORE 1 LINES;

SELECT * FROM tbl_visit;

/*
 * END IMPORT LOCAL FILES
 */

SET GLOBAL local_infile = FALSE;
SHOW GLOBAL VARIABLES LIKE 'local_infile';