MYSQL에 한글 csv 파일 import하기 (전처리 없이!)

아카데미 수업시간에 https://jumin.mois.go.kr/ 에서 다운받은 csv 파일을 Mysql에 import  해야하는 실습이 있었다.

Datagrip으로 편하게 샥 넣어버리려고 했는데

csv 파일 컬럼명이 한글이어서 글자가 다 깨지고 난리가 났다.

 

그와중에 Mysql Workbench로 작업하는 학생분께서 질문을 하셔서

Datagrip같은 툴의 도움 없이 터미널 명령어만으로 해결하려고 이것저것 찾아보았다.

 

1️⃣ 파일명과 파일 인코딩 설정

우선 파일 이름부터 한글파일이라서 터미널에서 파일명을 입력하는 것부터 쉽지 않았다.

 

ㅎㅎ 그래서 다운받은 파일 이름을 영어(population_202410_1.csv)로 바꿔주고,

 

혹시 몰라서 아래 명령어로 파일 자체의 인코딩 또한 utf-8로 바꿔주었다.

iconv -f euc-kr -t utf-8 population_202410_1.csv > population_202410_utf8.csv

 

2️⃣ 도커 Mysql 컨테이너 내부 올바른 경로로 csv 파일 이동

인코딩 설정까지 마친 파일을 도커 컨테이너 내부의 임의 디렉토리로 파일을 복사해주었다.

그리고 나서 LOAD DATA INFILE ... 명령어를 사용해서 MYSQL 에 미리 만들어둔 테이블로 csv 파일을 import 하려고 했다.

그러자 [HY000][1290] The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 에러가 발생하였다.

 

이 에러 메시지는 MYSQL 서버가 --secure-file-priv 옵션으로 설정되어, 허용된 디렉토리로부터만 파일을 로드할 수 있다는 뜻이었다.

 

허용된 디렉토리들 목록은 아래 명령어를 통해 확인할 수 있다.

SHOW VARIABLES LIKE 'secure_file_priv';

실행 결과 현재 MySQL 설정상으로는 /var/lib/mysql-files/ 하위에 있는 파일들만 읽을 수 있다는 것을 알았다.

 

그래서 도커 컨테이너 내부 /var/lib/mysql-files/ 경로로 csv 파일을 다시 옮겨주었다.

 

3️⃣ 컬럼 타입과 데이터 타입을 일치시켜 csv 파일 import하기

드디어 csv파일을 올바른 경로로 옮겼으니, 아래 명령어를 실행하여 미리 만들어둔 테이블로 import 하려고 했다.

LOAD DATA INFILE '/var/lib/mysql-files/population_202410_utf8.csv'
INTO TABLE MonthlyPopulation
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(region, total_population, household_count, population_per_household, male_population, female_population, gender_ratio);

 

 

그러자 [01000][1265] Data truncated for column 'total_population' at row 1 에러가 발생하였다..

해당 에러는 데이터 타입이 일치하지 않기 때문에 발생하는 에러라고 한다.

 

📌 에러 원인

 

처음에는 csv 파일의 첫번째 줄 때문인 줄 알았다.

그러나 첫번째 줄은 IGNORE 1 ROWS 를 적어주었기 때문에 읽지 않고 넘어갈 것이다.

 

즉 에러 발생 원인은 첫 번째 줄 때문은 아니라는 것이다.

 

 

원본 csv 파일을 좀 더 자세히 뜯어보니 숫자데이터에 콤마(,)가 삽입되어 있었다.

csv 파일을 엑셀로 열어서 엑셀에서 콤마를 자동으로 삽입해준 건 줄 알았는데, csv파일 자체에 콤마가 있는 것이었다.

 

테이블 스키마에서는 총인구수 컬럼과 세대수 컬럼이 int로 정의되어 있지만,

csv 파일에서는 숫자들 사이사이에 콤마가 삽입되어 있다 보니 각 속성들을 숫자가 아닌 문자열로 인식해버린 것이다.

 

즉 String 데이터를 int 컬럼에 집어넣으려고 하니 데이터 타입이 안 맞아서 에러가 발생했던 것.

 

그래서 데이터 로드하는 명령어를 아래와 같이 수정하여

LOAD DATA INFILE '/var/lib/mysql-files/population_202410_utf8.csv'
INTO TABLE MonthlyPopulation
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@region, @total_population, @household_count, @population_per_household, @male_population, @female_population, @gender_ratio)
SET
    region = @region,
    total_population = CAST(REPLACE(@total_population, ',', '') AS UNSIGNED),
    household_count = CAST(REPLACE(@household_count, ',', '') AS UNSIGNED),
    population_per_household = @population_per_household,
    male_population = CAST(REPLACE(@male_population, ',', '') AS UNSIGNED),
    female_population = CAST(REPLACE(@female_population, ',', '') AS UNSIGNED),
    gender_ratio = @gender_ratio;

 

숫자로 들어가야 하지만 문자열로 인식된 속성값들을 일일히 숫자 타입으로 캐스팅해주도록 설정해주었다.

 

그러자 데이터가 정상적으로 import되었따.

 

 

이렇게 전처리되지 않은 csv 파일을 DB에 바로 삽입하는 것이 매우 복잡하고 불편하고 비효율적이라는 것을 알려주기 위해 진행해본 실습이라고 한다.