python을 이용해서 테스트 목적으로 쓸 db 파일 엑셀 형태로 작성했다. 대량의 데이터가 필요해서 100만 건을 생성했는데, 양이 커서 세이브 로드에 시간이 꽤 걸린다. 이를 csv 파일로 만들어서 MySQL에 등록하고자 하였다.
1. import records 버튼을 사용하기
등록하고자 하는 데이터의 테이블을 불러왔을 때, 데이터를 저장할 수 있어 보이는 아이콘이 있었다. 아이콘을 가져다 대니 import records from external file 이라는 문구가 내 예상이 옳았음을 알려주었다. 그 왼쪽에 있는 버튼은 테이블의 데이터를 csv 파일로 저장해 준다.
그 버튼을 누르면 아래와 같은 창을 볼 수 있다.
입력 가능한 파일의 확장자는 csv, 혹은 json밖에 등록이 되지 않았다. 그 전에 그 사실을 알고 있었기에 미리 csv 파일로 저장을 해 두어서 browse를 통해 해당 파일을 불러왔다.
불러온 데이터를 어떤 테이블에 저장할 지 선택지가 뜬다. 새로 생성할 수도 있고 기존의 데이터에 입력할 수도 있다.
survey에 넣을 목적으로 만든 데이터이니 survey 테이블을 선택하였다.
기존 파일을 분명 utf-8로 인코딩했는데 utf-8이 아니라는 경고가 나와서 수정하고 다시 등록했다.
csv의 첫 행을 기준으로 db column과 값들을 비교한다. 각자 알맞은 column을 설정한 후 등록하였다.
문제가 있다. 정말 미칠 듯이 오래 걸린다. 엘레베이터는 무조건 닫힘, 에스컬레이터도 걸어가는 빨리빨리의 한국인에게는 견디기 어려운 속도다. 데이터가 100만개이긴 하나 30분은 기다린 것 같은데도 완료가 안되어서 다른 방법을 찾아보기로 했다. 취소하니 워크밴치가 강제로 종료되었다.
2. SQL Query를 이용하기
찾아보니 query를 이용한 방법도 찾을 수 있었다. 그렇게 찾은 query를 그대로 복사해 붙여서 실행하였다.
LOAD DATA LOCAL INFILE "C:\Users\1004k\Downloads\testtest.csv"
INTO TABLE test.survey FIELDS TERMINATED BY ",";근데 2068 에러가 떳다. 이를 해결하는 방법으로 SET GLOBAL local_infile = 1;을 실행하라는데, 이 방법으로 해결도 안되고 무슨 의미인지도 이해가 되지 않았다. 다른 방법을 찾는데, local이 문제라는 글을 보아 이를 지우고 실행해 보았다.
그런데 또 에러가 떳다. 그래도 에러 코드는 1290으로, 뭔가 다른 에러인 듯하다.
이 또한 열심히 찾아보았는데, mySQL 버전이 업그레이드됨에 따라서 데이터에 저장할 수 있는 파일의 위치를 강제로 정한 것이라고 한다. 이 위치를 바꿀 수도 있지만 정한 이유가 있을 것이라 생각하고 기본적으로 설치할 때 설정된 위치에 파일을 이동해 준다.
LOAD DATA INFILE "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\testtest.csv"
INTO TABLE test.survey FIELDS TERMINATED BY ",";그런데도 해결이 되지 않았다. 이상하다. 왜 해결이 되지 않는 것일까.
답은 폴더를 구분하는 문자의 차이였다. 윈도우에서 directory는 \ 문자로 구분된다. 그러나 MySQL에서는 / 문자로 구분해 주어야 한다. 이를 변경한 후 다시 실행해 보았다.
LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/testtest.csv"
INTO TABLE test.survey FIELDS TERMINATED BY ",";또 에러가 났다. 그래도 이번에도 숫자가 변경되었고, 이번 경고문은 쉽게 알아볼 수 있었다.
Error Code: 1366. Incorrect integer value: 'survey_id' for column 'survey_id' at row 1
csv 파일의 첫 번째 문자열에 필드명을 작성해 두었는데, 위에서 사용했던 방법은 이들을 db의 컬럼명과 비교하였으나 query문 방법은 첫째 줄부터 데이터 값으로 판단하는 듯하다.
그럼 첫번째 줄을 지우는 등의 방식으로 빼면 된다. query문을 사용한 김에 query문으로 생략해 보았다.
LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/testtest.csv"
INTO TABLE test.survey FIELDS TERMINATED BY ","
IGNORE 1 ROWS;
30분을 기다려도 안되던게 13초만에 모든 데이터들을 입력하는데 성공했다. 양이 많으면 이 방법을 주로 사용해야겠다.