CSV 파일에서 MariaDB(또는 MySQL)로 데이터 가져오는 방법

CSV(Comma Serparated Values) 파일을 MariaDB나 MySQL DB로 가져오는 방법을 설명합니다. CVS 파일을 관계형 DB 파일로 만들어서 SQL Query문을 사용하고자 할 때 유용합니다.

로드할 데이터가 1GB 보다 많거나 데이터를 한번에 처리하기 힘든 경우 CSV 파일을 적당한 크기로 파일을 분할하고 DB에 로드할 수 있습니다. CSV 파일을 분할은 split 유틸리티를 사용할 수 있으면 이에 대한 사용 방법 이전 포스팅의 링크를 참조해주세요.

# CVS 파일을 1024MB 단위로 나눔

$ split -C 1024m --additional-suffix=.csv --numeric-suffixes=0 Myfile.csv Myfile_part_

위의 명령을 실행하면 Myfile.csv 파일이 1GiB 미만의 여러 파일로 분할되며, 줄 바꿈에서만 분할됩니다(-C 1024m). 새 파일의 이름은 MyFile_part_00, MyFile_part_01 등으로 지정됩니다.

만일 DB가 운영 중에 있다면 DB 인스턴스의 백업 snapshot을 만들고, DB를 중지한 상태에서 CSV 파일을 DB로 로드 후 다시 활성화합니다.

사용할 명령어: LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [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,...]

CSV file Imprt Sample 확인

설명을 위해서 CSV 파일은 링크의 파일을 사용합니다. 샘플 CSV 파일은 아래와 같이 data time, float형 value, string 으로 구성되어 있습니다.

2014-01-01 01:00:00,23.648648648648624,client_1
2014-01-01 01:00:00,0.0,client_2
2014-01-01 01:00:00,144.81707317073176,client_3
2014-01-01 01:00:00,75.0,client_4
...

터미널에서 mariadb client 콘솔로 접속해서 DB를 먼저 만든 후에 Table의 Schema를 만들어 CSV 파일을 읽어 DB로 구성합니다.

# maria db 콘솔에 접속하고 database 생성

Maria DB > CREATE DATABASE IF NOT EXISTS csv_db;

Maria DB > SHOW DATABASES;

Maria DB > USE csv_db

# table 생성하고 결과 확인

Maria DB > CREATE TABLE csv_table (date_time DATETIME, value FLOAT, item VARCHAR(100));

Maria DB > SHOW TABLES;

→ Table 생성 시 CSV 파일의 column과 동일하게 구성해 줍니다.

※ MariaDB또는 MySQL에서 사용하는 Data Types은 아래 포스팅을 확인해주세요.

[SW 개발/Data 분석 (RDB, NoSQL, Dataframe)] - MariaDB 또는 MySQL에서 사용하는 Data type 정리

# csv 파일 Import 하기

Maria DB > LOAD DATA LOCAL INFILE '/home/ubuntu/csv_test/electricityusagedata.csv'

INTO TABLE csv_table

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

→ 원본 파일이 | or tab으로 분리되어 있는 파일은 '|' 또는 '\t' 로 변경합니다.

# csv_table 전체 불러오기
Maria DB > SELECT * from csv_table;

#최근 5개만 불러오기
Maria DB > SELECT * from csv_table order by date_time DESC limit 0, 5 ;

#내림차순 정렬에서 5개만 불러오기
Maria DB > SELECT * from csv_table order by date_time ASC limit 0, 5 ;

# Where 문 사용
Maria DB > SELECT * FROM csv_table WHERE item='client_10' order by date_time ASC limit 0, 5;
Maria DB > SELECT * FROM csv_table WHERE date_time >= '2014-02-01 00-00-00' and item='client_10' ORDER BY date_time ASC LIMIT 0, 5;

# Table 삭제
Maria DB > DROP TABLE csv_table;

# DB 삭제

Maria DB > DROP DATABASE csv_db;

# mariadb data 경로 확인
Maria DB > SHOW VARIABLES LIKE 'datadir';

※ mariadb 데이터 경로 위치 변경하는 방법은 링크를 확인해주세요.

mariadb-import/mysqlimport 와 사용하기

별도의 유틸리티로 MariaDB에서는 mariadb-import (or mysqlimport before MariaDB 10.5)를 제공합니다. mariadb-import는 LOAD DATA INFILE 명령어를 사용합니다. Compression 옵션 (--compress)을 사용하면 느린 Network에 대해서 보다 나은 성능을 제공하고, --local 옵션을 사용해서 Local File system에서 데이터를 읽어 드릴 수 있습니다.