[MySQL] CSV 파일 Import 및 Export: 간편 가이드

 

 

MySQL에서 데이터를 Import 및 Export 해보자!

 

 

들어가며

로컬 환경에서 작업한 데이터를 AWS 또는 다른 데이터베이스(DB)로 효율적으로 이관하는 방법을 LOAD DATA INFILE을 사용하는 방법이 있습니다. 이 방법은 데이터를 빠르게 로드할 수 있어 사용하는 방법을 한번 알아보도록 하겠습니다.

---

 

설정 확인하기

LOAD DATA INFILE을 사용하기 전에 먼저 환경 설정을 확인해야 합니다.

SHOW VARIABLES LIKE 'local_infile';

 

위 명령어를 통해 해당 Mysql의 설정 정보를 확인해 줍니다. 만약

 Value가 Off로 되어있다면 아래 명령어를 입력해 On으로 변경해줍니다.

SET GLOBAL local_infile=1;

 

설정파일 변경 시(Windows 기준)

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

설정 파일에서 직접 ' local_infile=1 ' 명령어를 작성해 줄수도 있습니다. 위와 동일합니다.

 

WorkBench 설정

1. workbench에서 접속할 connection의 edit connection에 들어갑니다.
2. Advanced탭의 Others에 OPT_LOCAL_INFILE=1 입력합니다.

OPT_LOCAL_INFILE=1

---

 

 

Export

Export란 MySQL 데이터베이스에서 데이터를 외부 파일로 내보내는 작업을 말합니다. csv와 같은 데이터파을을 외부로 추출해 내는 것을 말합니다.

SELECT column1, column2, ...
INTO OUTFILE '\경로\저장할 파일명.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM 테이블명
WHERE condition;

 

• INTO OUTFILE : 결과를 지정된 파일로 Export 합니다.
• FIELDS TERMINATED BY ', ' : 각 열을 쉼표로 구분합니다.
• OPTIONALLY ENCLOSED BY '"' : 필드 값이 문자열일 경우 따옴표로 묶습니다.
• LINES TERMINATED BY '\n' : 각 행을 개행 문자로 구분합니다.

 

파일 경로에서 (Windos 기준) 경로를 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/' 다음 경로를 작성 후 파일명을 작성해줘야 한다. 안 그러면 1209 에러가 뜨면서 사용할 수 없게 된다. 이 에러는 파일의 입출력 경로가 다를 경우에 발생하는 에러이다. 기본적으로 mysql의 구성파일을 보면 다음과 같이 기본 경로가 설정된 것을 볼 수 있다. (수정하면 수정한 경로로 저장이 된다.)

 

Export 성공 시

 

정상적으로 약 18만 개의 데이터가 0.5초 만에 csv파일로 만들어진 것을 볼 수 있다. csv파일 안에 내용은 인코딩 형식이 맞지 않아 정상적으로 출력 도지 않지만 db에 바로 Import 할 거 기 때문에 굳이 설정까지 안 해도 된다.

---

 

Import

Import란 외부 파일에서 데이터를 MySQL 데이터베이스로 가져오는 작업을 말합니다. 지금 csv로 뽑은 데이터를 다시 다른 db로 넣는 행위라고 할 수 있습니다.

 

Import 하는 방법이 두 가지가 있습니다. 워크벤치에서 Table Data Export 하는 방법과 LOAD DATA INFILE 명령어를 사용해서 하는 방법이 존재합니다. Table Data Export로 데이터를 Import를 하게 되면 outfile의 배해 속도가 엄청 차이 납니다. 그래서 여기서는 LOAD DATA INFILE을 사용해 Import를 해보도록 하겠습니다. 

 

Table Data Export

Table Data Export

Table Data Export는 간단하게 버튼 클릭으로 데이터를 Import 할 수 있습니다.

LOAD DATA INFILE

LOAD DATA INFILE '/경로/저장한 파일명.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

 

• INTO TABLE : 데이터를 지정된 테이블로 Import 합니다.
• FIELDS TERMINATED BY ',' : 각 열을 쉼표로 구분합니다.
• OPTIONALLY ENCLOSED BY '"' : 필드 값이 문자열일 경우 따옴표로 묶습니다.
• LINES TERMINATED BY '\n' : 각 행을 개행 문자로 구분합니다.
• IGNORE 1 LINES : CSV 파일의 첫 번째 줄은 보통 헤더이므로 Import 시 무시할 수 있습니다.

 

 

Import시에 약 18만 개의 데이터가 2초도 안돼서 삽입되는 것을 볼 수 있다. 

 

데이터도 db에 정상적으로 출력되는 것을 볼 수 있다!

 

 

aws rds에 로드 시!! 

LOAD DATA LOCAL INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/blog.csv'
INTO TABLE recipe
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

INFILE 종류

• LOAD DATA LOCAL INFILE: 클라이언트 로컬 파일 시스템에서 업로드.
• LOAD DATA INFILE: 서버 파일 시스템에서 업로드.

---

 

RDS IMPORT

먼저 RDS에서 Import를 하기 위해서는 파라미터 그룹을 직접 변경해줘야 한다.

파라미터 그룹을 생성하고 사용 중인 RDS와 연결을 한상태에서

파라티터 그룹 → 파라미터 편집 → local_infile 검색 → 값을 1로 변경

변경사항을 저장을 하고 RDS를 재부팅하고 나면 Infile 명령어를 통해서 import를 할 수 있을 것입니다.!