DB에 csv파일 넣기 - MySQL Workbench 활용



01. 내용

업무를 진행하면서 외부 데이터를 DB에 저장할 일이 종종 생긴다.
보통 외부 업체가 본인들 데이터를 제공할 경우에는 DB접속 정보를 주고 알아서 마이그레이션 해라는 경우보다는 아무래도 자기들이 샘플 데이터를 직접 말아서 .xlsx나 .csv 형식으로 제공해주는 경우가 일반적일 것이다.

데이터를 받은 후에는 그걸 가지고 로컬환경에서 띡.띡. 돌리는 것도 좋겠지만, 이미 서버에 분석 환경을 구축해놨기 때문에 RDB에 해당 데이터를 저장해놓고 서버에 올려놓은 Zeppelin이나 Jupyter Notebook에서 Python이든 MySQL이든 분석하는 것이 관리도 쉽고 공유 측면에서 훨씬 편하다.
만약 데이터 크기가 크고 간단한 쿼리 결과도 시간이 좀 걸린다 싶으면 아예 Hadoop에 올려서 Scala로 분석하기도 편하다.


따라서 일단 프로젝트 초기에 데이터를 받고나면 DB에 저장하는 것이 우선 수행할 작업이었고, 지금까지 나에게 주어진 데이터는 정말정말 간단한 엑셀 파일들이었기 때문에 MySQL Workbench의 Table Data Import Wizard를 사용해서 손쉽게 임포트 해왔다.

하지만 지난 주 모 웹사이트의 e-commerce 데이터를 DB에 임포트하는 도중 기존의 방법에 대한 문제를 인식해버렸고, 본 포스트를 통해 이와 관련한 초기 시행과정과 문제인식단계 그리고 그 해결방안을 모색해본다.

참고: Import할 데이터의 크기가 그다지 크지 않은 경우에는 하단에 서술할 과정을 따라 Workbench의 Import Wizard를 사용하면 될 것이다.



02. 과정

  1. 데이터 처리 :
    데이터에 콤마(,)가 있는 경우 구분자로 인식되기 때문에 다른 특수문자로 변환하거나 csv 구분자를 콤마가 아닌 다른 특수문자로 변환하는 방법을 적용해야한다.
    데이터에 따라 다르겠지만 현재 임포트할 데이터는 콤마에 연연해 할 필요가 없었고 그 양이 많지 않았기 때문에 그냥 ,를 없애버리는 방법 을 취했다.
    만약 ,가 데이터 상에서 중요한 역할을 해서 없앨 수 없다라고 한다면 다음의 방법을 적용할 수 있다.
    (직접 해본적은 없음.)
    • ,를 ';', ' ' 등 다른 특수문자로 변환
    • .csv 의 구분자를 ,가 아닌 다른 특수문자로 설정

  2. 데이터 변환:
    Import Wizard를 통해 데이터를 임포트할 때는 .csv와 .json 만 임포트 가능하다. 따라서 기존에 저장되어있는 형식인 .xlsx의 경우 각 시트를 분리하여 .csv형식으로 저장한다. (.csv에서는 데이터 내에 존재하는 ,도 구분자로 인식하기 때문에 임포트 과정에서 데이터가 밀려서 들어간다. 1.데이터 처리 에서 콤마를 제거한 것이 바로 이 때문이다.)

  3. 데이터 저장:
    준비한 .csv 파일을 MySQL Workbench Data Import Wizard를 통해 RDBMS에 저장한다. 스키마를 미리 생성할 필요 없이 컬럼명 자동 생성에 숫자형은 int로 문자형은 text 형식으로 지정해준다. 001 같은 숫자의 경우 int로 저장한다면 DB에서 그냥 1로 저장되기 때문에 우편번호와 같은 숫자는 text로 지정해주는 것이 좋다. Datetime 형식도 따로 지정해주지 않으면 -, : 때문에 text로 인식 되므로 Datetime으로 지정해주어야한다.



03. 문제 상황

문제의 시발점은 데이터 저장이었다. 말 그대로 시발 점.
데이터가 DB에 30초 당 290row 정도 들어가는데에 비해 전체 데이터가 17년도 46만 건, 18년도 78만건, 19년도 30만건으로 꽤 많다. 단순 계산으로 17년도 데이터만 약 13시간 걸리고, 전체 3개 년도를 임포트 하는데 대충 44시간 정도 걸린다. 너무 오래 걸리자너~
게다가 중간에 네트워크 끊기면 어디까지 들어갔는지 찾아내서 그 나머지 부분 데이터만 따로 분할 저장해서 다시 임포트 시켜야 할 수도 있다. 데이터를 임포트하는 새로운 방법이 필요한 시점이다.



04. 해결 방안 모색

RDB 태생적으로 문제인 것인지 지금 case가 문제인 것인지 사실 경험이 부족해서 잘 모르겠는데..
임포트 마법사 쓰지 않고 할 수 있는 방법으로는 Python이나 Scala로 로컬의 .csv 읽고 DataFrame이나 qarquet 형식으로 저장한 후 DB connect해서 직접 쌓는 것이 가장 최선인 것 같다.
다만 지금처럼 Hadoop이 아니라 Mysql에 저장하는 경우는 row 단위로 저장될 것이므로 오히려 Python에서 처리하는데 시간이 더 걸릴 것 같아 지금 방법보다 시간상 이득을 얻기는 어렵지 않을까 싶다.
그나마 장점을 하나 찾자면 중간에 에러 뜰 때 다시 이어서 작업하기 수월하다 정도?

빠르게 저장하고 싶다면 Parquet 형식으로 변환한 다음 HDFS에 저장 하는 것이 최선일 것 같고, 이후 추가적인 분석을 Spark로 처리하거나 table을 저장하는 것이 좋을 것이다.

아직 Scala에 대한 지식이 부족해서 parquet든 뭐든 때려치웠고,지금은 그냥 컴퓨터 3개로 테이블 하나씩 맡아서 Workbench로 MySQL에 넣는 중이다. 너무 슬프네.

자꾸 이런거 땜에 화나니까 올 해 안에는 Python만큼 Spark를 할 줄 아는 사람이 되어야겠다. (다짐)

멍청이의 작업일지 끝.