[2021.07.18] How to fast upload 1,000,000 data from CSV on MySQL?
해당 게시글은 데이터 분석을 위해 대용량 데이터가 담긴 CSV파일을 MySQL에 업로드할 때, 최대한 빠르게 올리는 방법을 알려주기 위해 작성하였다.
* 기존의 방법
-> Local PC <-> Server MySQL
-> 속도가 무진장 느리다. 10만데이터를 올리는데 4시간 정도 소요됨
해당 게시글에서 알려줄 방법
-> Server Linux <-> Server MySQL
->1 0만 데이터를 업로드 하는데 10초도 걸리지 않는다.
------------------------------------------------------------------------------------------------------------------------------
10만 데이터를 10초 이내에 업로드 하는 방법에 대해 알려주도록 하겠다. (꼭 순차적으로 진행할 것)
1. 파일 질라(FTP)를 통해, 로컬 PC에 있는 CSV 파일을 Linux로 옮김
2. 1번을 통해 옮긴 csv 파일의 경로를 저장한다.
경로 : /home/ubuntu/data/00000.csv
예시 파일들)
BMI.csv
국가건강검진_시력데이터.csv
가건강검진_혈압혈당데이터.csv
국가건강검진_혈액검사데이터.csv
'국민건강보험공단_22대 질병분류별 급여현황_20181231.csv'
국민건강보험공단_65세이상노인질병소분류별다빈도상병급여현황_20181231..csv
국민건강보험공단_고혈압의료이용률_20181231.csv
국민건강보험공단_당뇨병의료이용률_20181231.csv
'국민건강보험공단_만성질환자 건강지원서비스 교육프로그램 일정_20190909.csv'
국민건강보험공단_이상지질혈증의료이용률_20181231.csv
신장.csv
체중.csv
허리둘레.csv
2-1. 아래와 같은 에러가 발생할 시, MySQL에서 LOAD DATA 상태를 확인해줘야 한다.
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
loading local 상태 확인 (기존에는 OFF로 되어있을 것이다)
show global variables like 'local_infile';
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)
set global local_infile=true;-> loading local OFF -> ON으로 변경
-------------------------------------------------------------------------------------------------------------------------
★★★★★ MySql 접속할 때,(csv 파일을 mysql에 업로드) ★★★★★
mysql --local-infile=1 -u root -p-> 해당 명령어로 MySQL Connect
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.-> mysql -u -roop -p 로 접속 시 위와 같은 에러 발생
자 이제 처음부터 끝까지 대용량 데이터를 넣기 위해 진행과정을 정리하였다. (위에 과정을 제대로 했으면, 이대로 따라 하면 됨)
mysql --local-infile=1 -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| DATA |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use DATA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
★★★★★★★★★★★ 여기서 중요하다 ★★★★★★★★★★★★
window workbench를 이용하여, CSV를 이용하여, 조금만 데이터를 넣어준다 (그럼 자동으로 컬럼을 알아서 만들어 줌)
-> 그런 다음에 workbench를 일부로 종료하여,
-> 밑에 테이블 과 속성을 남겨두고 모든 데이터를 지우기 위해 TRUNCATE 사용하여 삭제
----> 이 방법을 통해 손으로 작업할 필요 없이 많은 칼럼과 테이블을 만들어 줄 필요가 없다. 위의 방법을 사용하면 자동으로 만들어 주니까. workbench를 응용한 것이다.
mysql> truncate table eyesight;
Query OK, 0 rows affected (0.04 sec)-> 테이블과 컬럼(속성)을 지우지 않고, 안에 있는 데이터들을 모두 지워주는 명령어
mysql> LOAD DATA LOCAL INFILE "/home/ubuntu/data/국가건강검진_시력데이터.csv"
-> INTO TABLE DATA.eyesight FIELDS TERMINATED BY ",";
-> 해당 명령어는 대용량의 CSV파일을 넣어주는 방법이다.
* 단 CSV 칼럼 속성 구조와 DB구조가 같을 때만 사용이 가능하므로, workbench를 통해 CSV 구조와 DB구조를 꼭 맞춰준다
-> 해당 넣으려는 파일 데이터도 1000001이었다는 것을 확인할 수 있다.
-> 결론적으로 위와 같은 방법으로 1000001 데이터를 넣는데 6초도 걸리지 않았다.
해당 방법은 자주 사용할 예정이니 꼭 숙지 하자.
---------------------------------------------------------------------------------------------------------------------------
즉, 위의 과정을 다하고, 할 때마다 하려면 이것을 보면 됨
ubuntu@ip-172-31-9-174:~$ mysql --local-infile=1 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
mysql> LOAD DATA LOCAL INFILE "/home/ubuntu/data/국가건강검진_혈압혈당데이터.csv" INTO TABLE DATA.blood_pressure_sugar FIELDS TERMINATED BY "," IGNORE 1 LINES;
Query OK, 1000000 rows affected, 65535 warnings (6.25 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 1000000mysql> LOAD DATA LOCAL INFILE "/home/ubuntu/data/국가건강검진_시력데이터.csv" INTO TABLE DATA.eyesight FIELDS TERMINATED BY "," IGNORE 1 LINES;
Query OK, 1000000 rows affected (5.40 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0
댓글