Skip to content

Instantly share code, notes, and snippets.

@sigridjineth

sigridjineth/mysql_tablecsv.md

Last active March 13, 2021 02:54
Show Gist options
  • Save sigridjineth/31a2979d2daca7bbf6625b75a3e5ad30 to your computer and use it in GitHub Desktop.
Save sigridjineth/31a2979d2daca7bbf6625b75a3e5ad30 to your computer and use it in GitHub Desktop.
Download ZIP
How to load csv files into MySQL tables (for Docker users)
Raw
mysql_tablecsv.md

How to load csv files into MySQL tables (for Docker users)

요구사항

  • MySQL 테이블 스키마에 맞는 대량의 데이터를 보유하고 있는 CSV 파일을 MySQL에 집어넣고자 한다.
  • Mac 환경에서 Docker를 기반으로 MySQL 8 이미지를 실행하고 있다.

파일 옮기기

  • 로컬에서 작업하고 있는 CSV 파일을 도커 컨테이너로 옮긴다.
  • docker ps -a 명령어를 통해 현재 도커 컨테이너의 아이디를 검색한다.
  • 다음의 cp 명령어를 실행하여 도커 컨테이너로 csv 파일을 복사한다.
docker cp /home/(name)/(folder_name)/(file_name)  (container_id):/(to_the_place_you_want_the_file_to_be)

데이터 삽입하기

  • 데이터가 들어가고자 하는 테이블을 만들어준다.
mysql> CREATE TABLE room (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(150) NOT NULL,
    -> type INT NOT NULL,
    -> price INT NOT NULL,
    -> maximum_guest INT NOT NULL,
    -> bedroom INT NOT NULL,
    -> bed INT NOT NULL,
    -> bath INT NOT NULL,
    -> review_rating DOUBLE NOT NULL,
    -> review_count INT NOT NULL,
    -> location VARCHAR(45) NOT NULL,
    -> latitude DOUBLE NOT NULL,
    -> longitude DOUBLE NOT NULL,
    -> address VARCHAR(45) NOT NULL,
    -> host_id INT NOT NULL,
    -> CONSTRAINT room_has_host FOREIGN KEY (host_id) REFERENCES host (id)
    -> );
  • 다음의 명령어를 통해 CSV 파일의 데이터를 테이블로 집어넣는다.
    • 유의할 점은 CSV 파일에서 column 이름을 나타내는 맨 처음 row를 데이터 row로 인식한다는 것으로 이에 주의한다.
    • 다시 말해 스키마에서 지정했던 column 순서대로 CSV 파일이 나열되어 있어야만 하며, 그래야 column에 맞게 테이블을 삽입할 수 있다.
mysql> LOAD DATA LOCAL INFILE '/mock_room.csv' INTO TABLE room FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 50 rows affected (0.01 sec)
Records: 50  Deleted: 0  Skipped: 0  Warnings: 0

오류 해결하기

한글 깨짐 문제 해결하기

원인 진단

  • Docker에서 한글 자료가 담긴 CSV 파일을 로드했을 때, MySQL에서 한글 깨짐 문제가 발생할 수 있다.
  • MySQL이 담긴 도커 컨테이너를 실행할 때 다음의 명령어를 사용한다. 여기서 jypthemiracle-mysql은 도커 컨테이너 이름이다.
docker exec -it jypthemiracle-mysql bash
  • locale 명령어를 사용하여 현재 도커 컨테이너의 로케일 설정을 확인할 수 있다.
    • 로케일에 관련된 다양한 환경변수의 값을 확인할 수 있다. LANG, LANGUAGE, LC_ALL은 설정되어 있지 않으며 이 로케일 설정 때문에 한글을 입력할 수 없음을 꼭 유의한다.
root@4d4524c460fc:/# locale
LANG=
LANGUAGE=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=
  • locale -a을 실행하여 현재 시스템에서 사용 가능한 로케일도 확인해본다.
    • ko_KR.UTF-8 과 같은 로케일이 보이지 않기 때문에, 별도로 설치를 해주어야 한다.
root@4d4524c460fc:/# locale -a
C
C.UTF-8
POSIX

문제 해결하기

  • 필자는 docker-compose.yml을 활용하여 도커에 한글 설정을 해주었다. 파일은 다음과 같다.
version: "3" # 파일 규격 버전
services: # 이 항목 밑에 실행하려는 컨테이너 들을 정의
  db: # 서비스 명
    image: mysql:8.0.17 # 사용할 이미지
    container_name: jypthemiracle-mysql # 컨테이너 이름 설정
    ports:
      - "3306:3306" # 접근 포트 설정 (컨테이너 외부:컨테이너 내부)
    environment: # -e 옵션
      MYSQL_ROOT_PASSWORD: "codesquad"  # MYSQL 패스워드 설정 옵션
    command: # 명령어 실행
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
    volumes:
      - /Users/jmlim/datadir:/var/lib/mysql # -v 옵션 (다렉토리 마운트 설정)

docker run -it -e LC_ALL=C.UTF-8 jypthemiracle-mysql bash
  • 만약 docker-compose.yml을 활용하고 싶지 않다면, 다음과 같이 명령어로 도커를 실행할 수 있다.
 docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password --name jypthemiracle-mysql -v /Users/Documents/docker/docker-mysql:/var/lib/mysql mysql:8.0.17 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
  • 모두 실행하였으면, 로케일을 확인하여 보자.
  • LANG과 LANGUAGE 값을 제외한 모든 값이 C.UTF-8로 덮여쓰여져 있음을 확인할 수 있고, 이 상태에서는 정상적으로 한글 입력이 가능하다.
root@77053c18f4af:/# locale
LANG=
LANGUAGE=
LC_CTYPE="C.UTF-8"
LC_NUMERIC="C.UTF-8"
LC_TIME="C.UTF-8"
LC_COLLATE="C.UTF-8"
LC_MONETARY="C.UTF-8"
LC_MESSAGES="C.UTF-8"
LC_PAPER="C.UTF-8"
LC_NAME="C.UTF-8"
LC_ADDRESS="C.UTF-8"
LC_TELEPHONE="C.UTF-8"
LC_MEASUREMENT="C.UTF-8"
LC_IDENTIFICATION="C.UTF-8"
LC_ALL=C.UTF-8

지금 MySQL 버전에서는 명령어를 활용할 수 없다는 메시지

문제 상황

  • LOAD DATA 명령어를 활용하다가 다음과 같은 메시지를 만날 수 있다.
mysql> LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

ERROR 1148 (42000): The used command is not allowed with this MySQL version

해결 방법

  • 다음의 명령어를 통해 local_infile이 0인지, 1인지 확인한다.
mysql> SHOW VARIABLES LIKE 'local_infile';
  • 만약 local_infile이 0이라면, MySQL 콘솔에서 나간다.
  • 다음의 명령어를 실행하여 MySQL에 다시 접속한다.
mysql -u root -p --local-infile=1
  • 다음의 명령어를 통해 local_infile이 1로 적절히 설정되었는 지 체크한다.
mysql> SHOW VARIABLES LIKE 'local_infile';
  • 명령을 실행하면 정상적으로 처리가 될 것이다.
mysql> LOAD DATA LOCAL INFILE ... INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Query OK, 300 rows affected (0.01 sec)

Records: 300  Deleted: 0  Skipped: 0  Warnings: 0

기타 Tip

  • LOAD DATA 명령을 실행해준 다음, 실행에 Warning이 있다면 다음과 같이 메시지가 발생할 것이다.
mysql> LOAD DATA LOCAL INFILE '/root/xxxx.dat' INTO TABLE table_name;
Query OK, 4613892 rows affected, 1 warning (30.47 sec)
Records: 4613892  Deleted: 0  Skipped: 0  Warnings: 1
  • 이 때, show warnings 명령어를 실행해주면 어떤 부분에서 Warning이 나는 지 확인할 수 있다.
    • 단, LOAD DATA 명령어를 실행하고 바로 show warnings를 하지 않으면 데이터가 날라간다.
    • 이후에 다른 명령어를 실행하고 show warnings를 하면 empty set으로 나타난다.
  • Column을 명시하는 별도의 row는 데이터를 담은 row로 인식하기 때문에 주의해야 한다.
    • column의 이름을 담은 row는 CSV 파일에서 제거하여 파일을 복사한다.
  • CSV 파일은 컴마 , 로 column을 구분한다. 각 필드에 컴마가 있어 CSV 파일이 정상적으로 파싱되지 않는지 확인해본다.
    • 필드 텍스트에 컴마가 없어야 한다.
  • 스키마에서 ID가 INT PRIMARY KEY AUTO_INCREMENT로 설정되어 있다고 하더라도 다음을 주의해야 한다.
  • 도커 설정할 때 다음의 명령어를 활용하면 텍스트에 이모지도 지원하도록 할 수 있다.
--character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

References

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment