SQL Loader
SQL LOADER은 오라클 유틸리티로 주어진 데이터 파일을 특정 테이블에 필요한 데이터 레코드를 로드할때 이용하는 목적으로 만들어 진 것이다. SQL*LOADER는 데이터 파일과 컨트롤 파일을 이용하여 테이블에 데이터를 입력한다. 컨트롤 파일은 수행될때 다음 3개의 파일을 만들어 낸다.
log file, bad file or reject file, discard file.
- LOG FILE 은 테이블의 상태와 인덱스 그리고 입력된 데이터파일에서 읽어들인 논리적 레코드의 수를 알려준다. 이것은 로드가 수행되고 난뒤에 만들어 지게 된다.
- BAD FILE과 REJECT 파일은 포맷 에러 혹은 오라클 에러로 인해 리젝트가 발생한 경우 생성된다.
- DISCARD FILE은 컨트롤 파일에 지정된 WHEN 절에 의해서 로딩될때 적합한 제약조건이 아닌경우 발생되는 파일이다. 이것은 reject된 레코드와 차이가 있다.
Structure of the data file:
데이터 파일은 고정된 레코드 포맷 혹은 가변 레코드 포맷으로 지정할 수 있다.
고정 레코드 포맷은 아래와 같다. 이번 케이스에서는 컨트롤 파일에서 요구하는 데이터 필드를 지정된 포지션을 이용하여 주게 된다.
7369 SMITH CLERK 7902 12/17/1980 800
7499 ALLEN SALESMAN 7698 2/20/1981 1600
7521 WARD SALESMAN 7698 2/22/1981 1250
7566 JONES MANAGER 7839 4/2/1981 2975
7654 MARTIN SALESMAN 7698 9/28/1981 1250
7698 BLAKE MANAGER 7839 5/1/1981 2850
7782 CLARK MANAGER 7839 6/9/1981 2450
7788 SCOTT ANALYST 7566 12/9/1982 3000
7839 KING PRESIDENT 11/17/1981 5000
7844 TURNER SALESMAN 7698 9/8/1981 1500
7876 ADAMS CLERK 7788 1/12/1983 1100
7900 JAMES CLERK 7698 12/3/1981 950
7902 FORD ANALYST 7566 12/3/1981 3000
7934 MILLER CLERK 7782 1/23/1982 1300
가변 레코드 포맷은 아래와 같이 딜리미터를 이용하여 데이터 필드를 나누고 있다.
Note : 딜리미터는 어느것이든 사용가능하며 이 예제에서는 "|"가 사용된다.
1196700|9|0|692.64
1378901|2|3900|488.62
1418700|2|2320|467.92
1418702|14|8740|4056.36
1499100|1|0|3.68
1632800|3|0|1866.66
1632900|1|70|12.64
1637600|50|0|755.5
Structure of a Control file:
CTL파일 예제는 가변 레코드 파일을 로드할때 사용하고 있다.
OPTIONS (SKIP = 1) -- 데이터 파일의 첫번째 row는 로딩없이 스킵된다.
LOAD DATA
INFILE '$FILE' -- 지정된 데이터 파일 패스와 이름이 온다.
APPEND -- 로딩 타입 (INSERT, APPEND, REPLACE, TRUNCATE)
INTO TABLE "APPS"."BUDGET" -- 로드해서 데이터를 넣을 테이블 이름
FIELDS TERMINATED BY '|' -- 가변포맷 데이터 파일인경우 지정된 딜리미터 값
OPTIONALLY ENCLOSED BY '"' -- 데이터 필드의 값은 아마도 "로 둘러 쌓여 있을 수 있다.
TRAILING NULLCOLS -- 널과 같은 칼럼은 사용할 수 없다.
(ITEM_NUMBER "TRIM(:ITEM_NUMBER)", -- 컬럼에 모든 SQL함수를 이용할 수 있다.
QTY DECIMAL EXTERNAL,
REVENUE DECIMAL EXTERNAL,
EXT_COST DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))" ,
MONTH "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,
DIVISION_CODE CONSTANT "AUD" -- 데이터파일에서 얻은값 대신에 특정 상수값을 이용할 수 있다.
)
OPTION 구문은 LOAD DATA 구문 앞에 오는 문장이다. OPTIONS 파라미터는 커맨드라인이 아닌 컨트롤 파일을 실행중에 수행될 수 있도록 해준다. 다음 아규먼트들은 OPTIONS에 지정가능한 파라미터들이다.
SKIP = n -- 스킵을 수행한 논리적 레커드의 수 (기본값 0)
LOAD = n -- 로드를 수행할 논리적 레코드의 수 (기본값은 all)
ERRORS = n -- 허용 가능한 에러의 수 (기본값 50)
ROWS = n -- conventional path 바이너리 배역이나 direct path 를 통한 데이터의 수를 지정한다. (기본값 : conventional path 64, direct path all)
BINDSIZE = n -- 바이트 내에서 conventional apth binary array 의 싸이즈 지정 (기본값 : 시스템 의존)
SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- 실행하는 동안 메시지를 금지한다.
(header, feedback, errors, discards, partitions, all)
DIRECT = {TRUE | FALSE} -- direct path 이용 (기본값은 false)
PARALLEL = {TRUE | FALSE} -- 병렬 로딩 수행 (기본값은 false)
LOAD
DATA
컨트롤 파일의 시작에 필요한 문장이다.
INFILE: INFILE keyword 는 데이터 파일이나 데이터파일들의 위치를 지정하기 위해서 사용한다.
INFILE
*
로 지정하면 컨트롤 파일내에서 데이터를 찾으며 외부 파일을 사용하지 않는다는 의미이다.
INFILE '$FILE', 현재 프로그램 상에서 파일패스와 파일 이름을 전송하기 위해서 사용한다.
INFILE '/home/vision/kap/import2.csv' 이것은 파일경로를 지정한 예이다.
외부 파일로 데이터 파일을 사요하는 예제 :
LOAD DATA
INFILE '/home/vision/kap/import2.csv'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
컨트롤 파일 내에 있는 데이터파일을 사용하는 예제 :
LOAD DATA
INFILE *
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
BEGINDATA
7369,SMITH,7902,Accounting
7499,ALLEN,7698,Sales
7521,WARD,7698,Accounting
7566,JONES,7839,Sales
7654,MARTIN,7698,Accounting
파일경로와 이름을 실항하는 프로그램에전달하는 예제 :
LOAD DATA
INFILE '$FILE'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
TYPE OF LOADING:
INSERT -- 테이블이 비어있는 경우 INSERT를 이용할 수 있다.
APPEND -- 데이터가 이미 존재하는 경우 SQL*Loader는 새로운 데이터를 추가한다. 만약 데이터가 존재하지 않는경우 새로운 로는 단순하게 로드 된다.
REPLACE -- 테이블에 존재하는 모든 로를 삭제하고 새로운 데이터를 로드한다.
TRUNCATE -- SQL TRUNCATE 명령어를 이용한다.
INTO
TABLE
은 로드될 테이블을 명시적으로 지정하도록 하는데 사용한다. 상단에 예에서 보면 INTO TABLE "APPS"."BUDGET" 로 사용하고 있는데 APPS는 스키마를 이야기 하고 BUDGET는 테이블 이름이 된다.
FIELDS TERMINATED BY 데이터파일에 데이터 필드를 구분하기 위한 지정자이다. FILE이 Comma 구분자나 Pipe 구분자로 지정한 경우에 유용할 것이다.
OPTIONALLY ENCLOSED BY '"' 이것은 데이터필드가 쿼데이션 마크로 둘러 쌓여 잇음을 의미한다.
TRAILING
NULLCOLS
SQL*Loader에게 상대적인 칼럼의 위치를 이용하여 구분할 수 있도록 하고 있다. 그러나 이것은 널 컬럼이 존재해서는 안된다.
Loading a fixed format data file:
LOAD DATA
INFILE 'sample.dat'
INTO TABLE emp
( empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL)
Steps to Run the SQL* LOADER from UNIX:
At the prompt, invoke SQL*Loader as follows:
SQL*Loader는 테이블에 데이터를 로드하고, 로그파일을 남긴다. 또한 시스템 프롬프트에 응답을 남긴다. 그러므로 로그 파일을 검사해보거나 수행될때 찍히는 결과를 확인해 볼 수 있다.
Register as concurrent Program:
Place the Control file in $CUSTOM_TOP/bin.
Define the Executable. Give the Execution Method as SQL*LOADER.
Define the Program. Add the Parameter for FILENAME.
Skip columns:
'FILTER'옵션을 이용하여 칼럼을 스킵할 수 있다.
Load Data
--
--
--
TRAILING NULLCOLS
(
name Filler,
Empno ,
sal
)
'DB > Oracle' 카테고리의 다른 글
테이블 데이터세트 업데이트 수행 (0) | 2009.04.27 |
---|---|
Oracle 랜덤하게 Row 뽑기 (0) | 2009.01.23 |
ORACLE > TRANSLATE (0) | 2008.11.25 |
01. ORACLE BASIC QUERY (NULL, ALIAS, CONCATENATION, WHERE, PLAN, INDEX_VIEW, ORDER BY) (0) | 2008.06.24 |
START WITH and CONNECT BY in Oracle SQL (0) | 2008.06.17 |