본문 바로가기
SW 개발/Data 분석 (RDB, NoSQL, Dataframe)

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

by Kibua20 2021. 5. 24.
728x170

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

database를 생성하고 결과 확인

 

# 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 정리

 

# table 생성하고 결과 확인

 

# 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 파일 Import 하기

 

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

 

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

#최근 5개만 불러오기


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

#내림차순 정렬에서 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;

# Where 문 사용

 

# Table 삭제
Maria DB >  DROP TABLE csv_table;

 

# DB 삭제

Maria DB >  DROP DATABASE csv_db;

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

#  mariadb data 경로 확인


※ 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에서 데이터를 읽어 드릴 수 있습니다. 

 

 

관련 글:

[개발환경/우분투] - 대용량 파일을 작은 크기로 분할하는 방법: split

[SW 개발/Data 분석 (RDB, NoSQL, Dataframe)] - 우분투 20.04에서 MariaDB 설치 및 기본 동작 확인 명령어

[SW 개발/Data 분석 (RDB, NoSQL, Dataframe)] - Jupyter Notebook의 업그레이드: Jupyter Lab 설치 및 extension 사용법

[SW 개발/Android] - 안드로이드 스마트 폰 화면 미러링 방법: scrcpy 사용법

[Cloud/Microsoft Azure] - Microsoft 공인 자격증 Azure Fundamentals(AZ-900)과 Developer Associate(AZ-204) 시험 후기

[SW 개발/REST API] - 자주 사용하는 curl 명령어 옵션과 예제

[SW 개발/Python] - Python: xmltodict를 활용하여 XML를 JSON으로 변환하는 방법

[Cloud/Oracle Cloud] - 오라클 클라우드 '평생' 무료 VM 만들기 (Google Cloud 무료 조건 비교)

[SW 개발/Python] - Python: JSON 개념과 json 모듈 사용법

 

 

그리드형



댓글0