MySQL에 csv 임포트 - MySQL Workbench

본 포스트는 Windows 환경에서 Mysql Workbench를 사용하여
csv 파일을 Mysql DB에 임포트 하는 방법을 설명합니다.

 

상세한 임포트 방법을 확인하실 경우 본 포스트의 2.과정 부분부터 참고해 주세요.

 

 

2.과정 요약 (Import 절차)
    2.1. MySQL Workbench 설치
    2.2. DB 연결
    2.3. csv 파일 확인 (중요)
    2.4. 파일 임포트   
    2.5. 결과 확인

 

 

 

 

1. 도입

---

1.1. 데이터 마이그레이션?

외부 프로젝트를 진행하다보면 외부 데이터를 자신의 주 분석 환경에 저장할 일이 생긴다. 이러한 데이터 이식 작업을 데이터 마이그레이션, 좀 더 흔한 표현으로는 데이터 저장이라고 말한다. 

 

사실 데이터 마이그레이션이라는 것은 기존의 환경에서 다른 환경으로 데이터를 이주시키는 과정을 말하는데, DB to DB 혹은 Local File to DB 의 경우에 적용할 수 있는 개념이다.

 

마이그레이션을 할 때 가장 쉽게 생각할 수 있는 방법은 데이터를 전송하는 통신 채널을 개방하여 DB to DB로 전송하는 것이다. 예를 들어 기업에서 보유중인 데이터를 제공받는 경우, 고객사의 DB접속 정보를 인계받고 중간 매개단을 설계하여 우리쪽 개발계 DB로 마이그레이션 하는 방법이 그것이다. 하지만 이 경우 민감 정보 유출에 대한 우려(정보보호서약서를 쓰더라도 말이다)와 기업내 보안절차 일부를 해제하여 통신 채널을 개통해야 하는 등의 번거로움 때문에 실제 사용하기엔 제약이 많은 것이 사실이다.

 

 

1.2. 어떤 방식의 마이그레이션이 효율적일까?

가장 편한 방법은 마이그레이션 자체를 하지 않는 것이다.

 

무슨 말이냐 하면, 기업들은 사내에 작업공간을 마련하여 개발 인원이 프로젝트 기간동안 상주하도록 하는 방법을 제안한다. 독립적으로 운영되는 내부망을 통해 데이터에 대한 보안을 어느정도 보장할 수 있고, 해당 계정에 권한을 제한하여 DB 접속에 대해 관리하기도 용이하다. 프로젝트 관리 차원에서도 통제, 감독하기 쉬워지고 요청사항을 전달하기 쉬워진다. 그래서 많은 기업들은 위의 방법처럼 개발인원을 상주시키는 방법을 선호하는 듯 하다.

 

 

그러나 기타 여건 때문에 위의 방식을 진행하기 힘든 경우에는 데이터를 기타 방법으로 가공해서 전달할 수 밖에 없다. 수집기간이 정해져있어 추가적인 데이터 수집이 필요없는 정적 데이터(예, 실험, 관찰, 설문 등의 데이터)인 경우 이동식 저장매체에 데이터를 담아 파일로 제공할 수 있다. 시의성이 중요하고 실시간 요소가 적용되는 데이터(예, 센서, 로그 데이터)의 경우에는 기업이나 디바이스에서 해당 데이터를 제공하는 API를 통해 데이터를 전송(트래픽 부하에 대한 염려가 없다면)하는 방식을 택할 수 있다.

 

 

모 기업에서는 운영계 Oracle DB에 존재하는 데이터를 개발계 DB로 마이그레이션 할 때 전체 데이터 덤프 파일을 생성하여 그대로 overwrite 하는 모습을 볼 수 있었다(전체 데이터 write하는데만 순수 4~5시간 걸린다던데, 중간에 에러나서 처음부터 다시 하기를 몇 번 반복하느라 실제로는 3일 정도 소요됨). 사실 이런 방법보다 좋은 방식이 있을 것도 같은데 나는 잘 모르겠다...

 

필자의 경우는 국가정보포털 국토부에서 제공하는 아파트 매매 실거래가 API로부터 매일매일의 실거래 데이터를 사내 분석 DB에 저장해 본 경험이 있다. 이 경우에는 매일 특정시간에 특정 기간동안의 데이터를 OpenAPI를 통해 호출하고, 해당 데이터와 개발 DB의 동기간 동지역구 아파트 데이터 row를 비교하여 일치하지 않는 경우 해당 데이터를 DB에 overwrite하는 방식으로 배치프로그램을 작성했다. 지금은 해당 PoC가 종료되어 서버 내 cron 작업을 내려놓은 상태이다. 

 

 

이렇듯 데이터를 DB에 저장하는 방법은 여러가지가 있겠지만 모든 케이스에 적용 가능한 최적의 마이그레이션 방법은 존재하지 않을 것이다. 데이터의 크기, 형태등 정량적 특성, 저장 목적과 같은 정성적 측면 그리고 현재 여건등을 종합적으로 검토하여 적절한 마이그레이션 방법을 선택하여야 한다.

 

 

 

 

 

2. 과정

---

서론이 엄청 길었는데..

사실 1.도입에 적은 내용은 프로젝트 진행하면서 데이터 저장 과정중에 떠올렸던 생각을 정리하는 차원에서 작성한 것 뿐이다.

저 글을 누가 읽어보겠나 싶다..

 

이제 본론으로 들어가서 csv 파일을 MySQL DB에 임포트 하는 방법에 대해서 알아보자.

 

 

 

2.1. MySQL Workbench 설치

돌고래가 귀여운 MySQL Workbench

 

MySQL 워크벤치는 SQL 개발과 관리, 데이터베이스 설계, 생성 그리고 유지를 위한 단일 개발 통합 환경을 제공하는 비주얼 데이터베이스 설계 도구이다. - 위키백과


MySQL Workbench is the official graphical user interface (GUI) tool for MySQL. It allows you to design, create and browse your database schemas, work with database objects and insert data as well as design and run SQL queries to work with stored data. You can also migrate schemas and data from other database vendors to your MySQL database. - Welcome to MySQL Workbench

 

MySQL Workbench 설치는 무척 쉽다. 아래의 링크로 들어가 설치 환경에 맞는 Workbench를 다운로드 받은 후 절차를 따라 설치하시면 되겠다.

 

MySQL Workbench 설치 링크 https://www.mysql.com/products/workbench/

 

 

 

 

2.2. DB 연결

MySQL Workbench에 DB를 연결해보자.

 

기본적으로 DB 접속 정보를 알고 있어야 한다.

 

필수 DB 접속 정보
    1) 호스트
    2) 포트
    3) 접속계정 이름
    4) 접속계정 비밀번호

 

 

설치 후 MySQL Workbench를 실행하면 다음과 같은 화면이 뜬다. (나의 경우는 기존에 생성해놓은 connection이 표시되고 있다.)

여기서 + 버튼을 클릭하여 새로운 connection을 추가해보자

1. connection 추가

 

 

+버튼을 누르면 다음과 같이 새 팝업창이 뜨게 된다

붉게 강조 표시된 부분에 필요한 정보를 적어넣는다.

정보를 채워넣은 후 초록색으로 강조 표시된 Test Connection을 클릭하여

Connection이 정상적으로 연결되는지 확인해야한다. 

2. Setup New Connection

 

 

정상적으로 연결되었다면 아래의 창을 확인할 수 있다.

정상적으로 연결되지 않은 경우 접속 정보를 다시 확인하여 입력한다.

3. Successfully made the Connection

 

2.3. csv 파일 확인

MySQL Workbench로 csv 파일을 임포트할 때 가장 중요한 부분이 파일의 데이터를 확인하는 것이다.

원본 데이터를 DB에 write하기 원하는 형식으로 처리하는 것이 중요하다.

csv 파일의 경우 스키마 구조가 정해져있지 않기 때문에 다음의 내용을 체크하자.

 

임포트 하기 전 csv 파일에서 확인해야 하는 부분
    1) csv 데이터 내에 콤마(,)가 존재하는가?
    2) csv 파일이 아니라 xlsx파일인 것은 아닌가?
    3) 숫자형 데이터에서 그 값이 001과 같은 경우는 없는가? (숫자가 0으로 시작하는 경우)

 

 

1) csv 데이터 내에 콤마(,) 가 존재하는 경우

만약, 데이터 내에 콤마가 존재한다면 해당 콤마를 csv파일의 구분자로 인식하여 셀이 하나씩 밀리게 된다.

따라서 최종 임포트 결과에 문제가 발생한다...

 

💡해결방법 
 콤마를 전부 다른 문자로 치환 (공백( ), 세미콜론(;), 대시(-), 혹은 그 밖의 다른 특수문자로 변경 등) 
 혹은 csv 파일의 구분자를 콤마(,)가 아닌 특수문자로 변경하여 저장한다 

 

 

 

2) csv 파일이 아니라 xlsx파일인 경우

MySQL Workbench의 Import Wizard는 csv 혹은 json 형식의 파일만을 지원하기 때문에 xlsx와 같은 엑셀 통합문서 형식은 임포트를 원하는 시트만을 따로 csv 파일로 분리하여 저장해야 한다.

 

💡해결방법 
 xlsx 파일에서 임포트하기 원하는 시트만 csv 파일로 변환한다

 

 

 

3) 숫자의 값이 0으로 시작하는 데이터가 존재하는 경우

zipcode 혹은 우편번호가 존재하는 데이터나 혹은 자체적으로 생성한 코드 정보가 있는 경우 코드의 첫 째 자리가 0인 경우가 존재한다. 그럴 경우 실제 csv 파일에서는 0이 생략되어 부정확한 데이터로 변환되는 경우가 종종 발생한다. 그 경우 셀의 서식을 숫자가 아닌 문자 형식으로 지정해주어야 한다.

 

💡해결방법 
 csv의 데이터의 셀 서식을 숫자가 아닌 문자로 지정한다 

 

 

 

 

 

2.4. 데이터 임포트

다음은 csv 파일을 DB의 테이블로 임포트 하는 방법이다.

 

 

1) 2.2. DB연결에서 생성한 MySQL Workbench Connection을 클릭하여 선택한다.

1) Connection 선택

 

 

2) csv 파일을 임포트할 스키마에서 마우스 우클릭 -> Table Data Import Wizard 클릭

2) Table Data Import Wizard 실행

 

 

3) csv 파일 선택

3) csv 파일 선택

 

 

4) 기존에 존재하는 테이블에 데이터를 write할 지 새로운 테이블을 생성할지 선택

4) 테이블 설정

 

 

5) import data의 field type을 설정한다. 붉게 강조된 부분을 클릭하면 csv 파일의 구분자 등의 설정을 조정할 수 있다

5) 필드 설정

 

 

6) 설정을 확인하고 next를 클릭하여 데이터를 임포트

6) 데이터 임포트