MySql: LOAD DATA INFILE 로 대용량 데이터 인서트하기

발생일: 2017.11.17

키워드: MySQL, LOAD DATA INFILE, insert large amount of dataset into mysql database, 대용량 데이터 추가

문제:

대용량 데이터를 MySQL 디비에 인서트하려고 한다. 가장 효율적인 방법이 뭘까?

해결책:

텍스트 파일을 읽어 테이블에 인서트하는 LOAD DATA INFILE 구문이 있다.

기본 INSERT 구문을 쓰는 것보다 20배 정도 빠르다고 한다.

대략 아래와 같은 포맷으로 실행할 수 있다.

    LOAD DATA LOCAL INFILE '{file_name}'

    INTO TABLE {table_name}

    CHARACTER SET utf8

    FIELDS

        TERMINATED BY '{field_terminator}'  # 각 필드 구분 문자 (예: CSV라면 컴마)

        OPTIONALLY ENCLOSED BY '"'  # 필요할 경우, 따옴표(")로 구분

    LINE TERMINATED BY '\n'

    IGNORE 1 LINES  # 제목이 포함된 첫 번째 줄은 생략

    (col1, col2, ... )  # 컬럼명

논의:

# 권한 이슈

LOAD DATA를 사용하려면 별도의 권한이 있어야 한다. DB에 접속할 때, load_infile=1 파라미터를 넘기면 된다.

----

# 인덱스와 인서트 성능

MyISAM 테이블에서는 인덱스를 끄면 성능이 향상된다고 한다.

        ALTER TABLE ... DISABLE KEYS;

InnoDB 테이블에서는 인덱스를 끄지 않아도 된다.

----

2018.03.09 추가

# 유니코드 캐릭터 인코딩 이슈

유니코드 형식의 데이터의 백슬래시가 사라지고 코드 자체가 인서트되는 경우가 있다.

예를 들어, 데이터에 '경기도'라는 문자가 있다면, 유니코드로 변환돼 "\uacbd\uae30\ub3c4"가 인서트되어야 하는데,

백슬래시가 생략되어 "uacbduae30ub3c4"와 같이 인서트된다.

CSV로 저장할 때 이스케이프 캐릭터였던 백슬래시를 문자열로 변경했는데,

MySQL에서 파일을 로드할 때 백슬래시를 다시 이스케이프 캐릭터로 인식한 것이 문제였다.

CSV로 저장할 때 이미 문자열된 상태이기 때문에 MySQL에서 로드할 땐 이스케이프 캐릭터를 무시하는 것으로 해결했다.

    LOAD DATA LOCAL INFILE '{file_name}'

    INTO TABLE {table_name}

    CHARACTER SET utf8

    FIELDS

        TERMINATED BY '{field_terminator}'

        OPTIONALLY ENCLOSED BY '"'

        ESCAPED BY ''  # 문자열 그대로 인서트될 수 있게 이스케이프 캐릭터를 빈 값으로 처리

    LINES TERMINATED BY '\n'

    IGNORE 1 LINES

    ({column_str})

----

2018.10.19 추가

# 빈 값 이슈

CSV 파일 내 빈 데이터가 NULL로 인서트 되지 않고, 컬럼 타입에 따라 empty string ('') 또는 0으로 들어간다.

기본값인 NULL로 넣고 싶다.

    예) 1,2,3,,5

MySQL 문서에 따르면, LOAD DATA의 타겟 파일의 빈 필드에 \N 을 넣으면 NULL로 인서트된다고 한다.

NULL를 넣어도 될 때가 있는데, 환경에 따라 다르다고 하니 \N을 넣는 게 좋겠다.

    예) 1,2,3,\N,5    

우린 pandas의 데이터프레임을 CSV로 만들어 인서트하고 있는데, pandas에 빈 값을 할당하는 인자가 있어 아래와 같이 처리했다.

    df.to_csv(filename, na_rep='\\N')

참고: https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data