MYSQL 데이터를 마이그레이션 해야 하는 일이 발생 했다.
데이터를 밀어 넣기 위해 아래와 같이 sql alchemy를 이용해서 작업을 진행했는데.. 속도가 너무나 느렸다. 1건 당 commit을 진행하도록 코드를 작성한 것도 느린 속도에 큰 영향을 끼쳤을 것 같다.
for value in valuelist:
try:
item = db_session.query(TEST).filter_by(id = value['id']).first()
if item == None:
test = TEST(value)
db_session.add(test)
db_session.commit()
except ValueError as e:
log.error('insert error %s' %(e))
for 문으로 10000개씩 묶어서 commit을 진행할까 생각을 해보다가 MYSQL의 LOAD DATA란 것을 알아보게 되었다. 아래와 같이 입력하면 되는데 정말 빠르게 bulk insert가 된다.
$ mysql> LOAD DATA LOCAL INFILE '/work/data.txt' INTO TABLE TEST FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
일단 맨 처음에 mysql shell에 접속해서 위의 명령어를 치면 현재 mysql version에서는 해당 명령어를 지원하지 않는다는 에러 메시지가 발생하게 되는데, 이를 해결하기 위해서 우선 아래와 같이 명령을 쳐서 local_infile 설정을 확인해보자.
local_infile의 경우 원격지에서 file을 가져올 경우에 설정하기 위한 변수로 해당 값이 ON으로 되어 있어야 사용할 수가 있다. (만약에 mysql 서버 자체에 파일이 존재한다면 infile만 사용해도 된다. infile만 사용할 경우 따로 local_infile 설정은 안해줘도 되는 듯 하다.)
해당 변수가 ON으로 되어 있지 않다면 /etc/mysql/my.cnf 파일을 수정해야 한다. (우분투 기준)
my.cnf 파일을 열고 아래와 같이 내용을 추가하자.
local-infile=1
cnf 파일이 수정이 되었으면 mysql을 재시작 시키고 아래와 같이 접속하자.
$>mysql --local-infile -h[서버주소] -u[사용자이름] -p[암호]
그 후에 다시 LOAD DATA를 실행시키면 아~~주 동작이 잘 되는 것을 확인할 수가 있다.
$ mysql> LOAD DATA LOCAL INFILE '/work/data.txt' INTO TABLE TEST FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
위 명령에 대해 좀 더 자세히 설명해 보자면, LOCAL INFILE을 통해 우리는 현재 /work/data.txt에 있는 파일을 TEST라는 table에 옮길 것이며, 탭으로 필드를 구분할 것이고 new line으로 라인의 종료를 확인할 것이다.
아래와 같이 직접 필드를 지정해 줄 수도 있다.
$ mysql> LOAD DATA LOCAL INFILE '/work/data.txt' INTO TABLE TEST (c1,c2,c3,c4);
위와 같이 입력하게 되면 모든 field에 대해 입력하는게 아니라 지정된 필드 (c1, c2, c3, c4)에 대해서만 bulk insert를 하게 된다.
마지막으로 LOAD DATA를 하다보면 error 및 warning이 발생할 수 있다. bulk insert이기 때문에 error가 발생하면 모든 insert가 취소가 되지만 warning의 경우는 무시하고 insert를 진행한다. 이렇게 발생한 warning에 대해 확인하고 싶다면 아래의 명령어를 입력하면 된다.
$ mysql> show warnings;
위와 같이 입력함으로써 바로 이전에 발생한 warning에 대해서 볼 수가 있다. (모든 warning에 대해서는 보여주지 않으며, 일부만 확인할 수 있다는게 단점...)
