SQL Loader

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:
 

    sqlldr USERID=scott/tiger CONTROL=<control filename> LOG=<Log file name>

 

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
)

이제 상단에 나열된 칼럼은 스킵될 것이다.