[MySQL] csv 파일 import

 [01 들어가기에 앞서] 

• 프로젝트를 하다 보면 Database에 대용량의 데이터셋을 추가해야 할 때가 있다.
• 그런데 MySQL Workbench에서 편하게 import 하는 방법은 데이터셋이 클수록 오류가 날 확률이 높다고 한다...
• 실제로 우리팀이 사용할 데이터셋도 계속해서 'cp949~' 오류가 나서 포기하고 다른 방법을 찾았다.
• 그 방법인 MySQL query를 이용해 데이터셋을 import하는 방법은 아래와 같다.

 

 [02 MySQL에 csv 파일 import ] 

 (1) 필요한 파일 SCP 

• 윈도우에서 리눅스로 파일을 이동, 복사할 때는 SCP(파일 전송 프로토콜) 명령어를 사용하면 된다.
• 하지만 MobaXterm과 같은 터미널 프로그램을 사용하면 드래그앤드롭으로 편하게 파일을 이동할 수 있다.

• 참고로 파일을 이동하는 대상 VM은 MySQL 데이터베이스를 구축해 둔 Oracle VM이다.

 (2) MySQL 접속 

• 접속을 할 때 주의할 점은 VM으로 이동한 로컬 파일을 import에 사용할 것이기 때문에  --local-infile=1 이라는 옵션을 하나 추가해줘야 한다.

sudo mysql --local-infile=1 -u super -p

• 그리고 접속한 뒤 혹시 모르니 다음 명령어도 실행시켜 로컬 파일을 사용가능하도록 해준다.

SET GLOBAL local_infile=1;

 (3) csv 파일 데이터 추가하기 

• 다음은 사용할 데이터베이스를 선택한다.

use <데이터베이스명>

• 그리고 다음과 같은  LOAD DATA  명령어를 사용해  데이터를 추가한다. (참고)
  • 주의할 점은 csv 데이터는 기본적으로 구분자가 ,인데 데이터에  , 가 포함된 경우  TERMINATED BY 로 ,를 사용할 수 없다. 따라서 우리팀은 csv 파일을 추출할 때 구분자를  ; 로 바꿔 저장했다.
    • 파이썬에서 구분자를 변경하는 명령어는  csv.writer(file, delimiter=';') 를 사용하면 된다.
  • 또 데이터에 개행문자가 포함된 경우  LINES TERMINATED BY '\r' 을 사용하고  " 는  ENCLOSED BY='"' 로 처리한다.
    • 개행문자 처리시 해당 csv 파일을 추출한 환경에 따라 Mac은 '\r'이지만 Windows는 '\r\n'을 사용해야 한다.

LOAD DATA LOCAL INFILE '<파일경로>' INTO TABLE <테이블명>
CHARACTER SET UTF8 FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r'
IGNORE 1 ROWS (컬럼명1, 컬럼명2...);