[MySQL] 대용량 text 파일(.txt) DB 에 업데이트 하는 방법 (feat.행안부)

이번엔 text 파일을 DB 에 업데이트 하는 방법에 대해 알아보자!
정확히는 그 방법에 대해서 공유하고자 하는 게 아니라, 시도하면서 겪은 에러케이스를 공유하고자 포스팅 한다.. (역시 또 에러)

 

우선 검색하면 나오겠지만,
MySQL 에서 대용량 데이터를 insert 하는 방법은 파일을 파싱하여 아래와 같이 'LOAD DATA~' 명령어로 하는 방법이 제일 나은 것 같다.

LOAD DATA
[LOCAL] INFILE ${파일위치}
INTO TABLE ${테이블명}
CHARACTER SET utf8
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'

자세한 문서는 공홈 참고 > https://dev.mysql.com/doc/refman/8.0/en/load-data.html

옵션	의미
LOAD DATA	데이터 로드 시작
[LOCAL] INFILE ${파일위치}	업데이트 할 파일위치를 지정 (상대경로/절대경로 가능) - DB 가 로컬이 아닌 AWS 등 타서버에 존재한다면 앞에 'LOCAL' 옵션을 넣어주자.
INTO TABLE ${테이블명}	업데이트 할 테이블을 지정
CHARACTER SET utf8	한글로 된 데이터를 넣기 위해 캐릭터셋 지정
FIELDS TERMINATED BY '|'	컬럼 구분 값 지정 (알맞게 변경 필요)
LINES TERMINATED BY '\n'	파일 내 줄바꿈 구분 값 지정 (알맞게 변경 필요)

 

사실.. 이렇게만 하면 되는 줄 알았는데 ㅋㅋㅋㅋㅋㅋㅋ 아니었다. 역시 이런게 시간을 다 잡아먹는구나 😩..
이제부턴 이걸 진행하면서 겪은 에러에 대해 끄적끄적한다...

---

1# Error Code: 1148. The used command is not allowd with this MySQL version

현재 사용하고 있는 MySQL 버전은 5.7 이었는데, 아 저 명령어(LOAD DATA~)는 5.7 버전에서 안먹는구나. 하고 검색해봤는데,
결론은 아니었다. 구글링해보면,

- SELECT @@global.secure_file_priv;
- SELECT @@global.local_infile;
- SHOW VARIABLES LIKE 'local_infile';

등 이렇게 생긴 명령어들을 해보라고 하는데 그냥 mysql 접속할 때 추가 권한달라고 하면 된다.

 

나같은 경우에는, 로컬에 설치된 DB 가 아니라서 MySQL WorkBench 로 접근했었는데
로컬에 MySQL 을 설치하고 터미널에서 추가옵션 주면서 DB 에 접속했다. 

> brew install mysql

로컬에 homebrew 가 있어서 MySQL 은 쉽게 설치함

MySQL 을 설치했으면, 아래 명령어로 접속하되 "--local-infile=1" 옵션은 꼭 넣어주자.

$ mysql -u ${계정아이디} -p -h ${호스트주소} -P ${포트주소} --local-infile=1

그러고 맨 위에 써논 명령어를 입력하면 잘 된다.

---

2# ERROR 1300 (HY000): Invalid utf8 character string: ''

LOAD DATA~ 명령어를 입력했는데, 이런 에러가 떨어졌으면 지정한 캐릭터셋을 utf8 말고 euckr 로 해보세요. 데이터도 잘 들어갑니다 ㅎ

---

그외# 일부가 성공하고, 일부는 에러가 났다면? (Skipped)

MySQL 이 데이터를 insert 하다가, PK 값이 중복된게 있으면 skip 해버린다.

"갑자기 웬 PK 가 중복이라는거야?" 싶다면 아래 쿼리로 확인해보자.

mysql> select count( ${PK컬럼명} ) AS num_duplicates, ${PK컬럼명}
             from ${테이블명}
             group by ${PK컬럼명}
             having num_duplicates > 1;

---

그외# 모두 성공했지만, 일부는 경고가 났다면? (Warnings)

실제 insert 하려는 값과 테이블에 선언한 컬럼의 데이터타입이 안맞는 것 일 수 있다.

그럴 땐, 'show warnings' 쿼리로 무엇이 안맞는지, 경고인지 확인가능하니 참고해서 수정하자 :)

mysql> show warnings;

+---------+------+--------------------------------------------------------+
| Level       | Code  | Message                                                                    |
+---------+------+--------------------------------------------------------+
| Warning  | 1261    | Row 43 doesn't contain data for all columns            |
..
+---------+------+--------------------------------------------------------+