[MYSQL] 대용량 csv 파일 불러오기(import)

mysql 로 대용량 csv 파일을 불러오는 방법에는 여러가지가 있고, 테이블의 특성에 따라 적절한 방법을 선택해야 하는 것 같다.

잘못된 방법을 선택하면 꽤 긴 시간과 갖은 고생을 하면서 파일을 불러오는 첫 시작부터 난관에 부딪히게 된다...

 

시행착오와 여러 사람의 자문을 통해 해결한 방법을 잊지 않기 위한

초보자의 기록용 포스팅 - 시작!

 

 

※ 외부 데이터 파일을 불러오기 전에 점검해야 할 사항

1. csv 파일에서 1개의 row의 들어가야 할 값 모양 점검하기.

"띄어쓰기"나  ";" 또는 "," 와 같은 것이 들어가 있지 않는지 확인이 가능하다면 확인하여 미리 처리해주어야 오류가 없다. 위처럼 되어있는 지저분한 데이터의 경우, mysql에서 불러올 때 규칙으로 잘못 해석하여 이상하게 불러와지거나 시간이 한참 흘러도 불러와지지 않을 가능성이 매우 커진다.

 

2. 불러오려는 csv 파일의 인코딩 확인하기.

    UTF-8이 아닌 다른 형식으로 되어있다면 바꾸어 주어야 한다. 형식 바꾸는 방법은 아래와 같이 여러가지 방법이 있다.

  1) 엑셀에서 다른 이름으로 저장하기 -> 파일 형식을 CSV UTF-8(쉼표로 분리) 항목으로 저장

  2) 메모장으로 데이터 불러오기 -> 다른 이름으로 저장 - 인코딩 : UTF-8 으로 설정 후 저장

  3) 구글 스프레드 시트로 불러온 뒤, 저장 (자동으로 인코딩 형식 변환됨)

  4) 파이썬으로 불러온 뒤 인코딩 변환하여 저장하기

 

3. mysql 워크벤치에서 외부 파일을 열 수 있도록 설정하기

1) MySQL Workbench에서 홈 - MySQL Connections - 내 계정 우클릭 후 [Edit Connection...] 들어가기

 

2) [Advanced] 창으로 이동, [Others] 부분에 아래 Local 파일 옵션 관련 스크립트 복사-붙여넣기 후 창 닫기

OPT_LOCAL_INFILE=1

 

위 사항들을 점검하여 대충 불러올 준비가 되었다면,

Schemas 창에서 원하는 이름으로 스키마 생성하기 [Create Schema] 

그리고 이제 파일을 불러오자!

 

★ CSV 파일 데이터 불러오기(import) 방법 2가지

[1] 만든 스키마 우클릭 -> 테이블 데이터 마법사를 통해 불러오기 (Table Data Import Wizard)

[Browse...] 을 눌러 파일을 불러오기 - [Next] 누르기

기존 테이블를 이용하여 데이터를 집어넣을 것인지, 새로운 테이블을 만들 것인지 선택하는 단계. 확인 후 [next] 버튼을 누르고 다음 단계로.

컬럼과 필드타입 확인 - 필드 타입이 적절하게 지정되어 있지 않다면 바꾸어 주어야 한다. 보다 자세한 설정을 위해 빨간색 표시된 스패너 모양을 클릭하여 확인.

자세한 옵션에서는 영역의 구분/ 라인 구분 등에 대해 이 데이터에 어떤 식으로 표시되어 있는지 확인한다. 만약 필드 구분이 , 로 되어 있다면 ,으로 바꾸어 주어야 마법사가 불러올때 데이터를 제대로 인식할 수 있다. 내 데이터를 확인하고 여기서 잘 설정해주어야 함. 설정 후 [Next]를 눌러 다음 단계로

이 부분이 마지막 단계로, 여기서 [Next]를 누르면 데이터 불러오는 작업이 실행된다..!

 

위 작업단계가 성공되면 다음 창에서 몇 개의 값이 최종 imported 되었는 지 설명이 나오면서 'Finish' 버튼을 클릭해서 나오면 끝이다. 

위 작업단계에서 무언가 문제가 생긴다면 다음 창에서 작업이 진행중인 상황으로 무한정 대기할 수 있고, 오류 창이 나올 수도 있다. 오류 창이 나온다면 오류 코드를 검색하여 해결 가능한지 확인해 볼 수 있지만, 무한정 작업 대기 상황이라면 시간 아깝게 기다리지 말고 그냥 빠져 나오고 다른 방법을 알아보는 것이 나은 것 같다.

 

 

[2] 만든 스키마 우클릭 -> 테이블 만들기 -> cmd로 파일 집어넣기

cmd를 이용하여 csv 파일을 import 하려는 경우에는, 시스템의 환경 변수를 설정해주고 시작해야 한다.

윈도우의 경우,

1. 제어판 -> 시스템 -> 고급시스템 설정 -> 환경 변수 클릭

시스템 변수 부분에서 'Path' 변수 클릭 후 [편집] 버튼 누르기

기존에 만들어져 있는 것들은 그대로 두고, 위 MySQL 경로만 새로 추가한 후 확인을 누른다. (C:\Program Files\MySQL\MySQL Server 8.0\bin)

2. MySQL Workbench 에서 신규 테이블 생성

 

1) 스키마 아래 테이블 우클릭 -> 테이블 만들기 클릭

2) column name 에 해당하는 컬럼명을 쓰고 datatype 부분에 타입 지정하기 (타입을 미리 확인해두자) -> 컬럼 특성에 따라 프라이머리 키에 해당되는지, not null 인지, 고유값을 가지고 있는지 등등의 특성에 따라 체크박스에 체크하기

3) 변수 모두 작성하였다면 우측 하단 'Apply'를 눌러 적용해준다.

 

2. 윈도우 + R 키를 누르고 실행에 들어가 cmd 를 입력 후 확인.

 

3. cmd 창에서 mysql 접속 명령을 입력하여 접속한다.

mysql --local-infile -u root -p

엔터를 치면 'Enter password:' 라는 말이 뜬다. 나의 local network connection에서 설정했었던 패스워드를 입력하면 됨.

Welcome이라는 말이 뜨면 잘 접속된 것이다.

4. Local infile 을 True 로 설정한다.

mysql> show global variables like 'local_infile';
mysql> set global local_infile=true;

5. 이제 진짜로 csv 파일 불러오기를 요청하자.

Mysql> LOAD DATA LOCAL INFILE "C:\\" --불러오려는 csv 파일의 절대 경로 입력해주기
INTO TABLE XXX.MM --데이터를 집어넣기 위해 만들었었던 스키마명.테이블명 입력
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
IGNORE 1 ROWS
;

정상적으로 불러와 진다면 아래와 같이 불러들인 레코드 총 수가 확인됨.

6. MySQL Workbench로 가서 잘 불러들어졌는지 쿼리문을 입력, 데이터 확인을 해보자.

SELECT *
FROM --테이블 명.스키마 명
LIMIT 10 --용량이 큰 데이터는 리밋을 걸어주자

 

 

나의 경우에는 두번째 방법이 가장 깔끔하고 빨랐다. 

이 외에도 파이썬을 이용하여 불러오는 방법 등이 있지만 이 기록에서는 생략함. 본인의 데이터와 상황에 맞는 가장 편리하고 빠른 방법을 쓰면 그게 제일 좋은 방법이겠다.