MySQL bulk insert

https://www.youtube.com/watch?v=qYM6d-0gW4w&ab_channel=DavidJung
영상 보면서 그대로 따라하는 포스트 입니다.

먼저 mysql 설치하고 db를 만듭니다

table 만들기

다음과 같이 테이블을 만듭니다

mysql> CREATE TABLE btest (
    -> a INT PRIMARY KEY,
    -> b varchar(16)
    -> );
Query OK, 0 rows affected (0.01 sec)

record insert

다음과 같이 레코드를 삽입 할 수 있습니다

INSERT INTO btest VALUES (1, 'ASDADASDA');
INSERT INTO btest VALUES (2, 'FWWASGSDA');
INSERT INTO btest VALUES (3, 'EGENTEWQE'), (4, 'GERGWASVD');

하지만 이렇게 일일이 삽입하면 10만개, 100만개 단위를 넣어서 테스트 할 수 없습니다.

CSV 파일 활용하기

CSV (Comma Seperated Values)는 이름에서 알 수 있듯이 콤마 단위로 필드를 나눠 저장한 파일입니다.

CSV 파일 내에 다음과 같이 파일이 저장 될 수 있습니다.

a(int),b(varchar16)
1,gregergggg
2,gkerppoe
3,agjiogoiw
4,gniorgn
5,vneirobniej
...
1000000,jriofwjeifo

이런 식으로 CSV 파일을 생성해서 LOAD DATA 하여 사용하면 됩니다.

자바로 테스트 데이터 만들기

먼저 테이블의 Field, Type 등을 한 눈에 파악하기 위해서 정보를 불러오겠습니다.

mysql> desc btest;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| b     | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

자바로 다음과 같은 코드를 작성합니다.

import java.util.Random;

public class Test {

    /**
     * +-------+-------------+------+-----+---------+-------+
     * | Field | Type        | Null | Key | Default | Extra |
     * +-------+-------------+------+-----+---------+-------+
     * | a     | int(11)     | NO   | PRI | NULL    |       |
     * | b     | varchar(16) | YES  |     | NULL    |       |
     * +-------+-------------+------+-----+---------+-------+
     */
    private static Random r = new Random();

    public static void main(String[] args) {
        Test test = new Test();
        int count = Integer.parseInt(args[0]);
        test.genData(count, 16);

    }

    private void progress(int curr, int tot) {
        if (tot < 1000)
            return;
        else if (curr == tot - 1) {
            System.err.println("");
            return;
        }
        else if (curr % (tot / 10) ==  0)
            System.err.print("*");
    }

    private void genData(int count, int clen) {
        for (int i = 0; i < count; i++) {
            progress(i, count);
            System.out.printf("%d,%s\n", i, genStr(clen));

        }
    }

    private Object genStr(int clen) {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < clen; i++) {
            sb.append((char) ('a' + r.nextInt(24)));
        }
        return sb.toString();

    }
}

터미널에서 이 코드를 컴파일 하고, 실행해봅니다.

$ java Test                                                          
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
	at Test.main(Test.java:17)

인자를 입력해주지 않았기 때문에 에러가 납니다.
생성하고 싶은 갯수만큼 인자를 주고 실행해봅니다.

$ java Test 1000000                                       
0,jqlcngwnnmdtpanv
1,gmptuqnlbjgtmihd
2,wrusgoploattfcrc
3,wsbuwuoddmqthavb
4,mqrbrtjltxqaxmjp
...생략
999999,jgmqhlpfaqxmlakl

잘 나옵니다.

실제로 CSV 파일로 생성하기

방금 만든 코드를 가지고 CSV 파일을 만들어봅니다.
터미널에서 리다이렉션 기능을 이용하여 다음과 같은 명령어를 실행합니다.

$ java Test 1000000 > big.csv                           
**********

*이 진행상황을 보여주면서 10개가 출력되면 완료되는 것을 볼 수 있습니다.
파일을 확인해봅니다.

$ vi big.csv

0,jqlcngwnnmdtpanv
1,gmptuqnlbjgtmihd
2,wrusgoploattfcrc
3,wsbuwuoddmqthavb
4,mqrbrtjltxqaxmjp
...생략
999999,jgmqhlpfaqxmlakl

잘 만들어진 것을 확인 할 수 있습니다.
참고로 대문자 G 를 누르면 문서 맨 하단으로 이동할 수 있습니다.

mysql에 insert하기

먼저 테이블을 확인해봅니다.

mysql> show tables;
+------------------+
| Tables_in_testDB |
+------------------+
| btest            |
+------------------+
2 rows in set (0.01 sec)

mysql> select * from btest;
Empty set (0.00 sec)

현재는 아무것도 없는 것을 확인 할 수 있습니다.

다음과 같이 LOAD DATA 를 이용해서 테이블에 csv 파일을 넣어봅니다.

mysql> LOAD DATA LOCAL INFILE 'big.csv'
    -> INTO TABLE btest
    -> FIELDS TERMINATED BY ',';
Query OK, 1000000 rows affected (2.77 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

1. LOAD DATA LOCAL INFILE 'big.csv' 로컬에 있는 big.csv를 로드합니다.
2. INTO TABLE btest btest테이블 안으로 넣습니다.
3. FIELDS TERMINATED BY ','; , 단위로 필드를 나눕니다.

혹시 로컬(호스트)에서 csv파일을 생성하셨나요?

1. 호스트에서 컨테이너로 파일 전송하는 방법
   docker cp /path/foo.txt container_name:/path/foo.txt

2. 컨테이너에서 호스트로 파일 전송하는 방법
   docker cp container_name:/path/foo.txt /path/foo.txt

로컬에 있는 csv파일을 my.cnf 파일이 있는 위치로 파일을 복사하시면 됩니다.

마지막으로, 데이터가 잘 들어갔는지 다시 확인해봅니다.

mysql> select * from btest;
...
...
...
| 999997 | airpanlwtbtbbudj |
| 999998 | ctfueaxepknovron |
| 999999 | vkpmgtuvjnfqptfk |
+--------+------------------+
1000000 rows in set (0.45 sec)