반응형

먼저 시작하기에 앞서 오늘 예제로 쓰일 테이블

 

CREATE TABLE computerValue(

id int,
cpu_value int,
memory_value int,
graphics_card_value int,
time_value datetime

)

 

실험 대상 : MariaDB 10.1.41 설치 돼있는 DB 서버

목적 : 데모데이터를 위한 1000 rows 데이터가 들어있는 csv 파일 import 하기

 

LOAD DATA INFILE

 

개념


텍스트 파일을 읽어서 테이블로 데이터를 입력하는(import 하는) 명령어이다.

 

사용 이유


일일이 INSERT 구문을 쓰는 노가다(반복작업) 방지.

기본 INSERT 구문을 쓰는 것보다 15~20배 정도 속도가 빠름.

DB to DB 데이터 마이그레이션 작업 사용

 

 

조건


파일 이름은 알파벳 문자열로 주어져야 한다.

import 하려는 파일 ,데이터로 쓰려고 하는 파일의 권한을 가지고 있어야됨

 - 데이터베이스 디렉토리에 존재

 - 누구나 읽을수 있는 파일 권한

 

 

문법


표준 양식

LOAD DATA 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, ... )  # 컬럼명

 

상황1. Linux(리눅스)

/usr/local/garaDate.csv 라는 파일 import 하기

LOAD DATA INFILE '/usr/local/garaData.csv'
REPLACE INTO TABLE `testDB`.`computerValue` COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id, @cpu_value, @memory_value,@graphics_card_value,@time_value)
SET `id` = @id, `cpu_value` = @cpu_value, `memory_value` = @memory_value,
`graphics_card_value` = @graphics_card_value,
`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i');

주의 : LOAD DATA LOCAL INFILE 아니라 LOAD DATA INFILE 입니다.

 

LOAD DATA LOCAL INFILE

 

개념


Local 옵션을 넣을 있다.

그대로 Linux(리눅스) DB 서버에 있는 파일을 넣는게 아닌

Local 있는 파일, 사용자의 노트북,컴퓨터 디스크에 존재하는 파일을 넣을 사용한다.

 

오해의 소지가 있을 있는데 DB 서버에 로컬 파일을 넣을 있습니다.

 

조건


local 명령어를 추가해서 사용할 경우에는 서버와 클라이언트

모두 local-infile 옵션이 on으로 되어 있어야만 사용가능 

local-infile 옵션 설정 확인 커맨드

show variables like 'local%';

 local-infile 옵션 ON으로 설정 하는법

mysql -u 아이디 -p -h 서버명 DB명 --local-infile=1

 

문법


표준 양식

LOAD DATA LOCAL INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

 

상황2. 로컬 윈도우

바탕화면에 있는 "garaData.csv" 라는 파일 원격 DB 서버에 import 하기

LOAD DATA LOCAL INFILE 'C:/Users/juju(사용자이름)/Desktop/garaData.csv'
REPLACE INTO TABLE `localtest`.`computervalue`(테이블이름)
COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id, @cpu_value, @memory_value,@graphics_card_value,@time_value)
SET `id` = @id, `cpu_value` = @cpu_value, `memory_value` = @memory_value,
`graphics_card_value` = @graphics_card_value,
`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i');

주의 : 윈도우 파일경로는 \(역슬래쉬) 아니라 /(슬래쉬)이다.

        LOAD DATA INFILE 아니라 LOAD DATA LOCAL INFILE 입니다.

C:\Users\user\Desktop (X)

C:/Users/user/Desktop (O)

만약 \(역슬래쉬)를 사용하고 싶다면 \\
C:\\Users\\user\\Desktop (O)

 

 

 

LOAD DATA INFILE 최근 에러 확인 명령어


가장 최근 1건만 확인 가능하다.

show warnings;

 

 

대표 예외(오류)


권한 거부 오류


/* SQL 오류 (29): File 'C:\Users\juju\Desktop\garaData.csv' not found (Errcode: 13 "Permission denied") */

local-infile 옵션 설정 확인 커맨드

show variables like 'local%';

 

 local-infile 옵션 ON으로 설정 하는법

mysql -u 아이디 -p -h 서버명 DB명 --local-infile=1

 

 

데이터에 포맷 오류


String 타입을 date 넣거나

int 타입의 허용 범위를 넘거나 나는 오류이다.

 

주로 날짜 date , datetime 진짜 많이 나는 에러이다.

 

/* SQL 오류 (1292): Incorrect datetime value: '2018-11-23 10:47

' for column `localtest`.`computervalue`.`time_value` at row 1 */

 

잘못된 구분-String인데 Datetime 넣을려고 시도

`time_value`= @time_value (X)

 

올바른 구분-String to Datetime으로 변경 데이터 삽입

`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i'); (O)

 


 

LOAD DATA INFILE 사용법

MariaDB 공식 홈페이지

https://mariadb.com/kb/en/library/load-data-infile/

MySQL 공식 홈페이지

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

 

반응형

+ Recent posts