csv나 txt 파일에 데이터 양이 많아서, DB에 직접 업로드해서 볼려고 하면,
업로드를 해야하는데, DBever나 기타 프로그램들을 통해서 업로드 하려면 너무 오래 걸린다.
그래서 오라클의 경우 DB로더를 사용해서 업로드를 하게 되는데....
파일 세팅해야되는 부분이 있고, 옵션들이 많이 있어서....
일단 한번 정리해 놓으려고 한다.
DB로더를 사용하기 위해서는 업로드 테이블, 필드값들에 대한 정보가 있는 control 파일(.ctl)을 작성해야 된다.
그리고, 실행하기 위해서 실행명령어를 작성하는데... 작성 내용이 길기 때문에 대부분 배치파일 (.bat) 파일을 생성해서
거기에 명령어를 작성하고 한번에 실행하는게 편하다.
예를 들어서 upload.ctl 파일을 생성하고, upload_run.bat 파일을 만들어서 bat파일안에 upload.ctl 파일을 작성해서 로드해주는거다.
1. Control 파일에 대한 설명
options(skip =1)
LOAD DATA
CHARACTERSET UTF8
INFILE 'log_001.CSV'
INFILE 'log_002.CSV'
BADFILE 'log_bad.bad'
DISCARDFILE 'log_bad.dsc'
APPEND
INTO TABLE "디비명"."테이블명"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
(field01,
field02,
field03,
field04,
field05,
field06 char(350),
field07 char(350),
field08)
1) options(skip =1)
- 이 옵션은 첫번째 줄은 생략한다는 명령어임 대부분 첫째줄은 필드명이 작성되어 있어서 생략해서.. 이렇게 작성함.
- 하지만, 이 옵션은 여기control파일에 작성하는 것보다. bat 파일 실행 명령어에 작성하는 것을 추천한다. 이거는 뒤에서 다시 설명
2) CHARACTERSET UTF8
- 한국어가 깨지면 UTF8로 하면 안깨지고 들어간다.
- 가끔 DB 설정 언어랑 안되서 업로드 안될때 있는데...그때는 DB 언어를 UTF8로 맞춰주는것을 추천한다.
3) INFILE 'log_001.CSV'
- 입력할 데이터가 있는 파일명을 적어 주는 것이다.
- 하지만 파일이 많을때 여기에 적는 것보다, bat 파일에 개별로 실행하는 하는 것을 추천한다.
- 여기서는 INFILE 명령어를 생략하고 배치 파일 명령어에서 파일을 작성하는 것을 추천함.
4) BADFILE 'log_bad.bad'
- 업로드할때 필드 길이 등으로 인해 업로드 되지 않는 데이터를 따로 저장한다.
5)DISCARDFILE 'log_bad.dsc'
- 로그 업로드에 대한 정보 파일을 저장한다. 예) 업로드 파일 수, 실패수 등
6) INTO TABLE "디비명"."테이블명"
- 업로드될 디비유저명 과 테이블명을 작성해 줘야한다.
7) FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
- CSV 파일 업로드 할때 구분자를 지정해 주는거다.
- 기본 CSV파일은 콤마(,) 구분이고, 데이터들을 따옴표(")로 묶여 있기 때문에 위 명령어를 쓴다.
-> txt 파일의 탭 구분으로 할 경우에는 아래와 같이만 작성해준다. (9가 탭 코드 번호임)
-> FIELDS TERMINATED BY X'9'
8) trailing nullcols
- 필드값에 null (값없음) 있어도 그냥 빈값으로 넣고 넘어가라는 이야기임.
- 이게 없으면 null값의 경우 에러가 남.
9) (field01, field02)
- 이런식으로 괄호안에 DB 필드명을 순서대로 작성해주고, 마지막 콤마(,)는 빼준다.
10) field06 char(350),
- char(350) 또는 char(4000)으로 작성해준다.
- DB에 필드 사이즈를 4000으로 잡아도, 디비로더에서 디폴트로 255로 잡혀 있어서, 필드값 사이즈가 넘어가면, 에러가 나기 때문에 한 필드값이 긴거는 위처럼 하나씩 작성해준다.
-> 아래는 txt파일 탭 구분으로 사람들 전화번호랑 주소값 넣는거로 작성한거다.
위에 설명했지만 아래 없는 것들은 배치파일에서 명령어에서 처리한다.
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE "DBUSER"."LOG_TABLE"
FIELDS TERMINATED BY X'9'
trailing nullcols
(NAME,
MOBILE,
ADDRESS char(350),
ETC)
2. Control 파일
이제는 실행파일을 만들어야 한다.
이전에 작성한 글(https://tyson.tistory.com/234)을 참고해도 되지만 다시한번 정리하면,
메모장에서 uplaod_run.bat 파일을 생성하고 아래와 같이 작성해준다.
sqlldr db유저/암호@디비아이피:포트/SID CONTROL=콘트롤파일명 data=데이터파일명 LOG=결과로그파일 BAD=실패데이터파일
sqlldr user/password@192.168.0.100:1521/SID CONTROL=upload.ctl data=log_data_01.txt LOG=log_01.log BAD=bad_log_01.bad
sqlldr user/password@192.168.0.100:1521/SID CONTROL=upload.ctl data=log_data_02.txt LOG=log_02.log BAD=bad_log_02.bad
sqlldr user/password@192.168.0.100:1521/SID CONTROL=upload.ctl data=log_data_03.txt LOG=log_03.log BAD=bad_log_03.bad
sqlldr user/password@192.168.0.100:1521/SID CONTROL=upload.ctl data=log_data_04.txt LOG=log_04.log BAD=bad_log_04.bad skip=1
업로드 파일이 여러개일 경우 이전 control.ctl 파일 INFILE을 여러개 적는것보다
콘트롤파일에서는 생략하고, 위처럼 배치 파일에 개별로 작성해 주는것이 좋다.
그래야 각 로그가 생성되어 중간에 어디까지 들어갔는지, 어떤게 에러 났는지 확인하기 좋기 때문에 배치파일에
개별 파일을 적는 것을 추천한다.
그리고, 아까 말한 control 파일의 맨첫줄 제외하는 skip 옵션은 콘트롤 파일에서 제외하고,
명령어에서 마지막 파일처럼 skip=1 옵션으로 적어 주는 것이 좋다.
각 파일들마다 어떤거는 필드값이 있고, 없는게 다르기 때문에 파일에 작성해 주는 것이 낫다.
여기는 직접 아이피를 적어 주었는데, tns파일에 세팅이 되어 있으면 tns명으로 작성해주면 된다.
그리고, 위처럼 아이피를 직접적을때는 SID부분도 확인해서 작성해줘야 된다.
처음에 SID안쓰니, 에러가 나오더라..
이것 저것 대량 파일을 전달 받아보니, txt파일에 탭 구분이 제일 안전하다...ㅠㅠ
데이터 뽑을때 탭의 경우는 미리 스페이스로 치환해서 받으면 중간에 에러없이 대부분 잘 들어간다.
