MariaDB/MySQL 에서 csv 파일로 저장하기 및 불러오기

mariadb 에서 데이터를 csv 로 저장하는 방법을 정리한다. 오늘 백업할 데이터는 약 680만건 정도인데, 데이터가 작으면 너무 빨리 끝나서 재미가 없을까봐 이번년도 카드명세서 내역을 여러번 insert 해놨다. 참고로, 평소 받았던 이메일 카드명세서를 python selenium 으로 열어서, R dplyr 을 이용해서 데이터 정제작업을하고, RMariaDB 패키지를 이용해서 insert 를 했다. 

mariadb 를 docker 이미지로 사용중이며, 현재 사용중인 상세버전은 아래와 같다. 

설치사항	상세버전
docker desktop	docker desktop version 3.5.2 (mac)
mariadb docker 이미지	mariadb:10.6.3-focal

 

SELECT INTO OUTFILE

SELECT INTO OUTFILE 명령어로 손쉽게 csv 파일을 만들수 있다. 자세한 설명은 https://mariadb.com/kb/en/select-into-outfile/ 에 설명되어 있으며, 헤더가 자동으로 포함되는 옵션이 없어 union all 로 헤더정보를 넣었다. 그리고 저장하는 위치가 중요한데, 나는 docker 로 mariadb를 사용중이며, /var/lib/mysql 아래가 기본 저장위치여서, 그 하위에 exportdata 폴더를 만들어서 저장하기로 한다. 안그러면 사용중인 databases 영역의 폴더내에 떨어져버리니, 뒤섞여서 찾기 애매해진다. 

-- outfile1.sql
SELECT 'card_cd', 'pay_date', 'details', 'pay_amt'
UNION ALL
SELECT * FROM bd_card_dtl
  INTO OUTFILE './exportdata/card_dtl.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
;

 

명령어가 길어서 파일로 저장해놓고 수행하니 26초 정도 걸렸다. 

MariaDB [test]> source outfile1.sql
Query OK, 6868993 rows affected, 1 warning (26.187 sec)

 

만들어진 csv 파일이다. OPTIONALLY ENCLOSED BY '"'  셋팅때문에 " 으로 감싸져있다. 

"card_cd","pay_date","details","pay_amt"
"SH","2021-01-14","(주)윈큐브마케팅","15000"
"SH","2021-01-15","플로리다","11000"
"SH","2021-01-16","(주)이마트","49000"
"SH","2021-01-16","(주)우아한형제들","28000"
"SH","2021-01-16","쿠팡","19000"
...

 

위치도 보면, test 이런 영역에 만들어진게 아니라 별도 폴더에 잘 만들어졌다. 371MB 정도의 용량이다. 

 

LOAD DATA INFILE

csv 파일로 저장을 해봤으니, 다시 데이터를 load 해보기로 한다. 우선 데이터를 지우기 위해서, truncate 처리를 하고, LOAD DATA INFILE 명령어로 스크립트를 작성한다. 

-- load_data.sql
LOAD DATA LOCAL INFILE
'/var/lib/mysql/exportdata/card_dtl.csv' into table bd_card_dtl
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES

 

실행하는 파일의 위치지정을 잘해야 하는데, 잘못지정하면 아래와 같은 메시지를 볼 수 있다. 

MariaDB [test]> source load_data.sql
ERROR 2 (HY000) at line 1 in file: 'load_data.sql': File 'exportdata/card_dtl.csv' not found (Errcode: 2)

 

아래와 같이 mariadb command-line 에서 스크립트를 실행한다. 38초 정도 걸렸다. 

 

추가로, mongodb 에도 동일한 csv 를 로드(mongoimport)해봤는데, 속도가 비슷하다.(약37초)

➜  doc ./mongoimort.sh
2021-08-07T21:45:09.681+0900	connected to: mongodb://localhost/
2021-08-07T21:45:12.681+0900	[........................] test.card_dtl	11.7MB/354MB (3.3%)
2021-08-07T21:45:15.683+0900	[#.......................] test.card_dtl	22.9MB/354MB (6.5%)
2021-08-07T21:45:18.658+0900	[##......................] test.card_dtl	34.0MB/354MB (9.6%)
2021-08-07T21:45:21.659+0900	[###.....................] test.card_dtl	45.3MB/354MB (12.8%)
...(중략)...
2021-08-07T21:46:46.378+0900	[########################] test.card_dtl	354MB/354MB (100.0%)
2021-08-07T21:46:46.380+0900	6868992 document(s) imported successfully. 0 document(s) failed to import.