대량의 데이터를 집어넣는 방법중 SQL*Loader 라는게 있어 정리.
(CSV 형식으로 되어있는 데이터를 import 한다.)
batch.sh (수행 파일)
1004lucifer
sqlldr DB_ID/DB_PW@HOST:PORT/SERVICE_NAME CONTROL=control.ctl skip=1
control.ctl (컨트롤 파일)
infile 'data.csv' "str '\n'" <= '파일명' "레코드 구분자"
append <= append형식
into table {_TABLE_NAME_} <= 데이터가 들어갈 Table명
fields terminated by ',' <= 데이터 구분자
OPTIONALLY ENCLOSED BY '"' AND '"' <= 데이터마다 콤마(")로 감싸져있음 - 옵션
trailing nullcols
(
{_FIELD_NAME1_} CHAR(4000), <= 첫번째 데이터가 들어갈 컬럼
{_FIELD_NAME2_} CHAR(4000), <= 두번째 데이터가 들어갈 컬럼
{_FIELD_NAME3_} "{_FIELD_NAME3_}.NEXTVAL" <= 시퀀스를 이용해 자동입력
)
data.csv (데이터 파일)
B0001316,2
B0001371,2
B0001344,2
B0001362,4
B0001335,2
B0001313,2
B0001316,4
B0001321,2
B0001326,3
... (생략)
결과 후 로그
1004lucifer
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Control File: control.ctl
Data File: data.csv
File processing option string: "str '
'"
Bad File: data.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table {_TABLE_NAME_}, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
{_FIELD_NAME1_} FIRST 4000 , O(") CHARACTER
O(")
{_FIELD_NAME2_} NEXT 4000 , O(") CHARACTER
O(")
{_FIELD_NAME3_} NEXT * , O(") CHARACTER
O(")
SQL string for column : "{_FIELD_NAME3_}.NEXTVAL"
value used for ROWS parameter changed from 64 to 30
Table {_TABLE_NAME_}:
1375721 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 247860 bytes(30 rows)
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 1375721
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Dec 09 11:59:01 2016
Run ended on Fri Dec 09 12:01:57 2016
Elapsed time was: 00:02:56.37
CPU time was: 00:00:07.11
PS.
137만건의 데이터를 입력하는데 3분정도 걸렸다.
확실히 수행속도는 괜찮은듯..
SQL*Loader 설명 잘 되어 있는 곳 (링크)
- DB별 CSV 파일 로딩 방법
댓글
댓글 쓰기