ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • MariaDB/MySQL 에서 csv 파일로 저장하기 및 불러오기
    개발/DB 2021. 8. 8. 01:12

    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.

    댓글 0

Designed by Tistory.