MySQL CSV 인코딩 오류, 메모장 대신 DBeaver 연결로 해결한 과정

작성자:날아라키위 마지막 수정일:

취업 시장 공고문에서 데이터 분석가 역량 중에 가장 많이 요구하는 것이 SQL이다. 그래서 많은 취준생들이 데이터 분석 캠프에서 SQL을 많이 공부하지만, 플랫폼 밖에서는 잘 사용 안 하는 것 같다. 오늘은 MYSQL CSV 인코딩 오류를 내가 어떻게 해결했는지 보여 드리려 한다


개인 프로젝트 할 때 SQL로 데이터 가공은 어떻게 할래?

데이터리안에서는 solvesql을 이용해서 SQL 쿼리를 짜고, 결과값을 확인한다. 테이블 이름만 클릭하면, 컬럼 데이터 타입과 컬럼명이 나와서 엄청 편리하다.

만약, 개인 프로젝트를 한다고 생각해 보자. 온라인 플랫폼에서 제공하는 sql 플랫폼은 사용할 수가 없다. 취준생들은 회사 소속이 아니라서, 회사에서 사용하는 sql 공간도 사용할 수도 없다.

집에 내 컴퓨터에 설치해야 한다. 그것은 MySQL 될 수도 있고, 다른 SQL이 될 수도 있다. 나는 MySQL로 배웠기에 MySQL을 설치하고 싶었다. 마침 데이터리안에서 그런 나를 위해 설치 방법을 블로그 글로 발행한 것이 있었고 나는 그 글을 참고해서 설치를 마쳤다.

참고로, MySQL은 대량의 데이터를 체계적으로 저장하고, 필요할 때 빠르게 꺼내 쓸 수 있도록 관리해 주는 데이터베이스 엔진이다.

위의 방법대로 MySQL 설치를 하면 아래와 같은 MySQL Workbench 화면이 뜬다.


MySQL Workbench

MYSQL WORKBENCH 화면

MySQL은 데이터가 꽉 차 있는 공간이라고 생각하면 된다. 그냥 공간 그 자체라면, 우리는 어떻게 데이터를 보고, 가져오고 할까?

바로 데이터를 볼 수 있게 도와주는 클라이언트 프로그램을 이용하면 된다. MySQL Workbench는 MySQL를 만든 오라클이라는 회사에서 데이터를 볼 수 있게 만든 클라이언트 프로그램이다.

MySQL Workbench(클라이언트)에서 ‘나 이런 이런 데이터를 보고 싶어. 가져와줘’ 라고 MySQL(서버)에 요청을 하면 조건에 맞는 데이터를 가져올 수 있다.


1. 스키마(폴더, Schema) 생성하기

MySQL Workbench에서 csv 파일을 가져오기 위한 첫번째는 바로 스크린 왼쪽 부분 SCHEMAS 아래 아무 빈 공간에서 오른쪽 마우스 클릭을 하면 Create Schema를 볼 수 있는데 그것을 클릭해야 한다

스키마(Schema)는 최상위 폴더를 의미한다. 우리가 파일을 로드할 때 어디에는 위치해야 하는데 여기 폴더 안에 넣으면 된다. 아래는 스키마(폴더) 이름을 어떻게 정할 것인지를 물어보고 있다.

나는 passorder_data라고 입력을 했다. 이 때 패스오더 데이터 분석을 하고 있었기 때문이다.

가지고 올 파일 이름 넣기

2. 데이터 파일을 어디에서 불러오지?

데이터를 불러오는 과정

자세히 보면 최상위 폴더에 내가 이름을 붙였던 passorder_data가 보인다. 옆의 화살표를 누르면, Tables 등 또 다른 것들이 보이는 데 이는 데이터와 기능을 종류별로 담는 전용 서랍이라고 생각하면 된다

  • Tables : 이번에 넣으신 passorder_event_log 같은 데이터 표가 바로 이 Tables 서랍 안에 보관
  • Views : 원본 데이터를 복사해서 새로 만드는 것이 아니라, “이 모양으로 필터링해서 보여줘”라는 조회용 창문만 만들어둔 것
  • Stored Procedures : 버튼 하나 누르면 여러 단계의 작업이 순서대로 실행되도록 짜놓은 작업 묶음
  • Functions : 엑셀에서 =SUM(), =AVERAGE()를 쓰듯이, 내가 직접 공식을 정의해서 만든 나만의 계산 공식들을 보관

다 필요없고, 우리는 우리 데이터가 들어갈 Tables에만 신경 쓰면 된다. 그래서 Tables 위에 마우스 커스를 대고, 오른쪽 마우스 클릭을 하면 Table data import Wizard 메뉴를 클릭한다.


가져올 데이터 파일을 찾기

내 컴퓨터에 있는 파일을 불러오는 작업이다.


테이블 이름 만들기

csv 파일 가져오는 것이기에 새 테이블 만들기 작업을 한다. passorder_data 폴더 내 pass_event_log_90d 새 테이블을 만든다.


3. MySQL CSV 인코딩 오류

MySQL CSV 인코딩 오류

485번째 있는 특정 바이트 코드 0x9d를 지금 문자표로 해석할 수 없어 작업을 중단합니다 라는 에러 메세지가 떴다.

이 에러는 우리가 아무리 위의 사진과 같이 우리 파일을 utf-8로 설정하더라도, MySQL Workbench 파이썬 코드에서 encoding=’utf-8’이 누락되어 있는 것 부터 에러가 시작된다. 이게 없기에 한글 윈도우 인코딩 시스템 cp949 규칙을 가지고 데이터를 읽다가 이 규칙이 읽지 못하는 0x9d가 나오지 중단이 된 거다.

사실, 제미나이에 해결 방법을 물어보니, CSV 파일을 메모장을 통해 열어서 저장을 utf-8로 설정한 후 모든 파일 형태로 저장해서 다시 로드해라는 것이었다.

결과는 위와 마찬가지였다. 예전에도 파일을 로드해서 분석을 하려 다가 같은 MySQL CSV 인코딩 오류로 포기를 했던 기억이 났기에 더 짜증이 났다.

여러 가지 클라이언트 프로그램이 있지만, MySQL Workbench는 특히 규칙이나 이런 거에 엄격하다고 들었는데 융통성이 아예 없는 모양이다. 결국 이번에도 MySQL CSV 인코딩 오류로 진행을 못하게 했다.


나의 구세주 DBeaver 연결로 해결

MySQL CSV 인코딩 오류를 해결한 DBeaver

MySQL Workbench를 설치하였음에도 나는 SQL로 데이터 정제 및 가공을 할 수가 없었다. 다른 방법을 찾아야만 했는데, AI가 DBeaver를 추천해 줬다.

MySQL은 그냥 데이터가 쌓여 있는 공간으로 생각하면 된다고 했다. 그리고 MySQL Workbench가 MySQL를 만든 오라클에서 만든 그 데이터를 모니터링해서 데이터를 찾고, 추출할 수 있게 한 클라이언트 프로그램이다.

DBeaver도 데이터 공간을 모니터링 해서 데이터를 찾고, 추출할 수 있게 한 사제 클라이언트 프로그램이다.

DBeaver의 좋은 점은 MySQL 뿐만 아니라, Oracle, PostgreSQL 등 거의 모든 DB를 연결할 수 있는 만능형이며, 위에서 생겼던 MySQL CSV 인코딩 오류도 중간에서 알아서 처리해 준다.

위의 사진에서 보면, 여러 Database 연결을 할 수 있는데 나는 이미 MySQL를 설치 해 놓았기에 MySQL을 선택했다.


1) DBeaver를 내 컴퓨터의 MySQL 서버와 연결하는 방법

DBeaver를 내 컴퓨터의 MySQL 서버와 연결하는 방법

몇 개만 확인하면 된다. 이미 내 컴퓨터에 MySQL를 설치했고, 파일도 내 컴퓨터에 있기 때문에 그냥 localhost(내 컴퓨터) 내 MySQL의 방 번호 3306과 연결을 해줘 라고 설정하는 것이다.

하지만, MySQL은 데이터 베이스에 아무도 들어오는 것을 막기 위해서 아이디와 비밀번호 입력을 요청하는 데, 사용자 이름은 root(모든 접근 권한을 가진 관리자 아이디)를 입력하고, MySQL 비밀번호를 입력하면 된다

  • Server Host : 어디에 있는 DB를 가져올 지, 나는 내 컴퓨터에 MySQL에 설치해 놓았기에 localhost 있는 그대로 사용했다.
  • Port : MySQL이 상주하는 방 번호 같은 거다. 그대로 사용했다
  • Username : DB 최고 관리자 아이디. root로 하면 된다
  • Password : 처음 MySQL 서버에 연결할 때 지정하는 데, 그것을 사용하면 된다.

완료를 하기 전에, 왼쪽 하단 제일 아래에 Test Connection으로 미리 한번 시험을 해 보는 것을 추천한다.


2) DBeaver를 쉽게 믿지 못하는 MySQL

allowpublickeyretrieval = true

1번과 같이 서버 연결을 하려고 시도(테스트)를 하는 중에 오류가 발생했다. 아무리 DBeaver가 비밀번호를 입력해도 들어오려고 해도 MySQL이 그것을 믿지 않기 때문이다.

이 때는 주인인 내가 보증 서주는 느낌으로 allowPublicKeyRetrieval Value(값)을 True로 수정하면 된다. 아마 처음에는 false로 되어 있을 것이다.

이러면 테스트를 해 보면 제대로 연결이 되는 것을 확인할 수 있다.


3) DBeaver에서 데이터 파일 가져오기

DBeaver에서 데이터 가져오기

DBeaver와 MySQL이 연결이 된 이후에는 이제 Dbeaver에서 MySQL 서버의 데이터를 볼 수 있다. world 밑 Databases 밑 passorder_data에서 오른쪽 마우스를 클릭하고 ‘데이터 가져오기’를 클릭한다.


Dbeaver에서 데이터 가져오기 설정

Target과 Source format이 이미 설정이 되어 있다. 다음 버튼을 누른다


Dbeaver에서 데이터 가져오기 설정1

Browse에서 가져올 데이터 파일을 선택하고, 다음 버튼을 누른다.


Dbeaver에서 데이터 가져오기 설정2

파일 가져올 때 특별하게 설정하고 가져 올 때 체크하는 부분이다. 우리는 그런 거 없기 때문에 그냥 진행을 누른다.


Dbeaver로 불려져 온 데이터,

MySQL CSV 인코딩 오류 때문에 여기 까지 왔다. 결국 분석을 하기 위한 데이터 CSV 파일을 가져왔다. 어떻게 아나면. 컬럼명이 이제 보인다.


데이터는 불러왔고, 이제 SQL 쿼리를 짜보자

이제는 SQL 쿼리를 짜기 위해, 우리는 쿼리를 짤 공간이 필요하다. 상단 메뉴 중에 SQL 편집기, 새 SQL 편집기를 선택한다


SQL 쿼리 짜고 실행까지 완료

간단하게 어떤 데이터가 있는 지 보기 위해 기본 쿼리를 짜고 실행해 보았다. 실행은 단축키로 ctrl + enter 이다.


마무리

MySQL CSV 인코딩 오류, 메모장 대신 DBeaver 연결로 해결한 과정에 대해서 이야기해 보았다. 이렇게 해서 나는 AI가 생성해 준 패스오더 교차 브랜드 적립 허용 A/B 테스트 대시보드에 나온 KPI 값들을 내가 배운 SQL로 검증을 할 수 있었다.

오랫동안 나는 이 부분이 궁금했었다. 우리가 SQL을 배워도 회사에 들어가기 전까지는 내 컴퓨터에서 사용할 수 밖에 없었다. 온라인 교육도 언젠가는 끝이 나기에 그 제공되어 지는 플랫폼을 영원히 쓸 수는 없다는 얘기다.

파이썬의 경우 Jupiter notebook 이나 Colab으로 파일을 가져오고, 추출, 가공 이어서 대시보드 생성까지 할 수 있는데, SQL은 배우면서 실제 우리가 집에서 할 수 있는 방법이 없는 것 같아서 답답했던 부분이 있다.

데이터 리안에서도 MySQL 설치해 주는 방법을 블로그 글로 알려 주긴 하는데, 나처럼 이런 MySQL CSV 인코딩 오류가 발생하면 어떻게 처리해야 하는 지에 대한 내용은 없었다. 아마도 케이스 바이 케이스인 것 같다. 아님 내 노트북이 해외 것(영문 설정)이기 때문일 수도 있다.

어쨌든 데이터 분석가 채용 공고에서 가장 많이 요구되어 지는 SQL 능력, 그것을 실제로 데이터를 수집해서 직접 가공을 할 수 있게 되어 기쁘다. 어떤 데이터 분석을 하게 될 지 앞으로 가 더 기대된다

관련된 글