JAVA 자바 Mysql Load Data Infile

도로명주소 작업기와 매우 상관있는 내용입니다

   

도로명주소 전체DB 데이터를 DB에 밀어넣으려고

MVC구성에 iBatis 까지 연동했는데 보란듯이 실패했습니다

   

사실 실패의 원인은 데이터 입력 시간이 문제였는데...

140메가정도 되는 파일을 압축을 풀었더니 1.6G가 됨 ㅋㅋㅋㅋ

건물정보 10,658,708 건

관련지번 1,951,096 건

   

1.6G되는것도 작업돌려놓고 한참놀다오니 컴터가 뻣어서 확인...했네요

로그를 찍도록 만들어놔서 그런가 더 느려짐....

   

무튼 각설하고

대용량데이터를 select * insert & update 하도록 해놨더니 몇시간이 지나도 안끝나 패닉에 빠졌어요

테이블에 PK말고 해준게 없어서 그런것도 있겠지만 제공받은 데이터 레이아웃엔 PK라고만 있고

코드구성따윈 설명도 없음...

   

다시한번 각설하고

Mysql에는 데용량데이터 입력을 위해 load data infile 이란 멋진 기능이 있지만

전혀 몰랐네요 알았다면 좋았을것을...

   

초기 데이터는 이 기능을 쓰고 이후 수정되는 데이터에 대해서만

select * inset & update 를 수행하면 되겠다는 취지에서 iBatis를 아무리봐도 기능이 없네요

지원을 안해요 할필요가 없는거겠죠

   

결국 결론은 하드코딩으로 처리하는걸로

물론 bash shell 을 이용해서 처리해도 됨 근데 shell script를 잘 몰라서 답답함

   

iBatis가 안되면 직접 하면되지라는 생각...

기능사용을 위해서는 Mysql root 권한이 필요함 없으면.... bye bye

   

일단 Mysql 접속을 위해 JDBC가 필요 합니다.

나머진 기본 library

   

제공되는 데이터는 PSV로 pipe(|) 를 기준으로 데이터를 구분하니 | 를 구분자로 지정했습니다

CSV , TSV 등등 있는데 comma(,) , tab(\t) 이니 참고

데이터 구분자가 변경되면 FIELDS TERMINATED BY '|' 이부분만 수정하면 됩니다

   

당연한 이야기지만 데이터에 맞춰 테이블이 만들어져 있어야 하고요

핵심은

load data local infile 'fileNm' INTO TABLE tablename CHARACTER SET euckr FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\n'

   

17개나 되는파일명을 손수 적어주기 귀찮으니 이전에 짜놨던 DB connect 소스를 수정했습니다

단일파일이면 for안돌리셔도 되고 문자열 매칭도 안하셔도 됩니다

import java.io.File; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement;     public class MysqlLoadDataInfileExam { String url = "jdbc:mysql://host:port/dbnamd?characterEncoding=utf8&useUnicode=true"; //mysql 접속정보 ?뒤의 문자열은 utf8로 연결 String id = "root"; String pw = "passwd"; String driver = "com.mysql.jdbc.Driver"; protected Connection conn;     public static void LoadDataInfileExcute() throws Exception {     Connection conn = null;     Statement stmt = null;     Class.forName(driver); //간혹 jdbc 드라이버를 못찾을 경우가 있어 넣음 없어도 무방     int cnt = 0;     try {         conn = DriverManager.getConnection(url, id, pw);         stmt = conn.createStatement();         String path = "filepath"; //파일경로 및 파일명 ex) /User/Download/test.txt         File f = new File(path);         for(String fileNm : f.list()){             if(fileNm.contains("build_")){ //읽어드린 파일 중 build_ 로 시작하는 파일만 읽음                 String sql = "load data local infile '"+ path + fileNm +                 "' INTO TABLE tablename " +                 "CHARACTER SET euckr " + //인코딩                 "FIELDS TERMINATED BY '|' " + // | 기준으로 파싱                 "LINES TERMINATED BY '\\n'"; //라인끝 (윈도우는 \r\n 이라함)                 boolean result = stmt.execute(sql);             }         }     } catch(Exception e){         System.out.println("e: " + e);     } finally {         conn.close();         stmt.close();     } }         public static void main(String[] args){         try{             LoadDataInfileExcute();         } catch(Exception e){             System.out.println("me: " + e);         }     } }

파란색 글씨부분이 변동 되는 사항이니 확인이 필요해요 특히 굵은글씨

범용성을 위해 main을 분리해놨지만

결국 실행은 LoadDataInfileExcute(); 만 하니 Main에 직접 구현해도 무방합니다

   

잡설이지만 17개의 시도 건물정보를 하나의 테이블에 밀어넣고

테이터 총 개수를 세려고 count(*) 쿼리를 보냈더니 1분동안 응답이 없....네요

결국 파일명 별로 테이블을 각각 생성하는 쿼리를 수행하고 데이터를 넣도록 수정했어요

   

아래는 shell script 버전입니다

폴더에서 파일을 읽어와서 원하는 파일만 매칭해서 넣으려고 했는데

실패했네요... shell 문법공부를 더해야겠어요...

#!/bin/bash mysql -u root -p패스워드 DB명<<EOFMYSQL load data local infile '파일경로' INTO TABLE 테이블명 CHARACTER SET 인코딩종류 FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' EOFMYSQL

참고로 mysql -u root -p패스워드

Mysql 패스워드를 한번에 입력하는거라 바로 로그인됩니다

   

다양한 옵션들이 많이 있으니 Mysql Document를 참고하시면 좋을것 같네요

참고 : http://dev.mysql.com/doc/refman/5.7/en/load-data.html