[MySQL] 대용량 csv 파일을 workbench에 업로드하기(Window OS)

지난 12월, workbench에 데이터를 업로드 할 때 무슨무슨 에러가 떠서... 이렇게 저렇게 했더니 해결되었다... 라는 글을 포스팅했었다.

 

[STUDY/SQL] - [MySQL] - Row import failed with error: ("Data truncated for column at row 1 1265")

[MySQL] - Row import failed with error: ("Data truncated for column at row 1 1265")

 

 

그런데 대용량 데이터(사실 그렇게 대용량도 아니었다. 고작해야 행 만개 정도?)를 올리려니 시간이 너무 걸리는 것이 아닌가.

 

그래서 대용량 데이터(csv)를 올리는 방법을 모든 것을 알고 있는 구글을 뒤져서 찾아내었다.

정말... 정말 많은 에러와 씨름한 끝에 성공적으로 업로드 했고, 관련해서 정리해보고자 한다.

 

대용량 데이터를 workbench에 올리는 방법은 여러가지가 있는 모양인데, 그 중 내가 성공한 방법은 cmd 창을 이용하는 것.

 

1. 환경 설정(이미 되어 있다면 건너뛰어도 된다)

파일 올리는 작업을 하기 전 설정해주어야 할 것들이 있다.

 

 1) 시스템(검색창에 검색하자) -> 고급 시스템 설정 -> 환경 변수 -> 시스템 변수의 Path 더블클릭 -> 새로 만들기

로 들어간 다음, mysqld.exe 파일이 들어있는 폴더 경로를 추가해준다.

 2) cmd창을 켜고 cmd에서 mysql --version을 입력했을 때 아래 사진과 같은 결과가 나오면 된다.

검색창에 검색

고급 시스템 설정

환경변수 - 시스템변수의 Path - 새로 만들기

 

위처럼 출력되면 성공 !

 

2. 업로드하기

2.1 cmd에서 MySQL 접속하기

mysql -u root -p --local-infile 코드를 입력한 후 root 계정의 비밀번호를 입력해서 접속한다. 만약 안된다면 뒤의 --local-infile을 제거하고 접속한다.

 

2.2 local_infile을 on으로 설정하기

show global variables like 'local_infile'; 를 입력하자. 입력 후 나온 박스 안에 local_infile이 off로 처리되어 있다면 set global local_infile=true; 를 입력하자. 그리고 다시 확인해보면 on으로 되어 있는 것을 확인할 수 있을 것이다.

 

2.3 DB와 테이블 만들기.

csv 데이터를 올릴 DB와 Table을 workbench에 만들어준다. 데이터를 올릴 곳이 있어야 올릴 것이 아닌가!

이 때, 업로드 될 데이터와 테이블의 데이터 형식이 일치하는지 꼼꼼히 확인해야 한다.

그렇지 않으면 엄청난 warning을 만나게 된다(경험담 1).

 

2.4 업로드하기

이제 데이터를 업로드 할 일만 남았다.

아래의 코드를 입력하자.

> LOAD DATA LOCAL INFILE "파일의 절대경로"  
> INTO TABLE dbName.tableName  
> FIELDS TERMINATED BY ","
> OPTIONALLY ENCLOSED BY '"'
> LINES TERMINATED BY "\n" STARTING BY ''
> IGNORE 1 ROWS;

 

이대로만 잘 따라왔다면 데이터가 잘 업로드 되었고, cmd창에 결과가 나올 것이다.

만약 warning이 떴고, 어떤 warning인지 알고 싶다면 show warnings; 를 입력해보자.

 

* 주의할 점

 1. 파일 절대경로 입력 시 역슬래시(\)를 모두 슬래시(/)로 바꿔주어야 한다. 아님 에러가 난다(경험담 2).

 2. csv파일을 올리는 것이므로 fileds terminated by ","(필드의 구분자를 지정해주는 코드. 여기선 콤마(,))로 써 준 것이다. 엉뚱한 구분자를 지정하면 역시나 엄청난 에러와 warning을 만나게 된다(경험담3).

 3. 혹시, 시 분 초가 표시된 데이터를 업로드 할 때 잘못된 게 없는데도 warning이 뜬다면, 해당 데이터가 올라갈 테이블의 컬럼의 속성을 timestamp로 바꿔보도록 하자. 잘 업로드 될 것이다(경험담4).

 

 

속도가 굉장히 빠르다. 백만건이 넘는 데이터도 10초 내외로 업로드 되었던 것 같다.

 

이제 Workbench에서 맘껏 데이터를 다뤄보도록 하자!