SQL_LOADer로 db쳐박기 쉘 or bat

1. SQL*Loader

 

SQL*Loader은 엑셀 같은 응용 프로그램 데이터나 다른 데이터베이스로 저장된 데이터를 오라클 데이터베이스 테이블에 입력하기 위한 유틸리티다. 비교적 대량의 외부파일로부터 데이터를 로딩하는 데 사용할 수 있으며 오라클서버제품이나 클라이언트 제품이 설치될 때 같이 포함되기 때문에 별도의 비용이 필요하지도 않고 오라클 서버와 연동에 있어서는 최고라고 할 수 있다.  SQL Loader 유틸리티를 이용하여 다음 같은 작업들을 할 수 있다.

 

 - OS 파일 시스템 상에 존재하는 데이터 파일을 로딩한다.

 - 서버머신에서 뿐만 아니라 네트워크 상의 다른 장비에서 자업가능.

 - disk, tape, and named pipes 등 다양한 소스의 데이터를 처리 가능.

 - 원시 데이터 파일에서 선택적으로 로딩할 수 있다.

 - SQL 함수를 이용하여 로딩하는 데이터 편집가능.

 - 특정 카럼에 유니크한 연번을 입력 할 수 있다.

 - 문자셑 지정 가능.

 - Can automate the load process, so it runs at scheduled times.

 

 

 

 

2. SQL*Loader 요약

 

일반적으로 SQL*Loader 유틸리티 세션은 컨트롤 파일로 부터 업로딩 할 데이터 및 작업과정에 대한 설정 내용을 입력받는다. DBMS에 로드될 데이터는 컨트롤 파일에 포함 될 수도 있고 하나 또는 여러개의 파일일수 있다. SQLLDR 유틸리티의 Output은 당연히 DataBase Table에 저장되며 하나의 log file를 생성한다.  입력 데이터에 오류가 있을 경우 bad file이 생성되며, 입력에서 제외되는 내역에 대해서는 필요한 경우 discard file을 생성 할 수 있다.  내부적인 데이터 처리 방식에 따라서 "Conventional data loading", "Direct-path loading", "External data loading" 의 세가지 업로드 methods 를 제공한다.

참고: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_concepts.htm#autoId0

 

 

 

 

 

 

3. SQL*Loader 실행

 

SQLLDR keyword=value [,keyword=value,. . .]

 

SQL Loader를 실행하는 문법은 위와 같다. 가장 단수한 형태는 연결문자열 다음에 컨트롤 파일을 지정하는 방법이다.

Command-Line Parameters를 사용하는 경우 키워드와 값 순으로 나열하면 되고 ","는 생략가능하다. "\" 문자를 사용하여 다음줄에 연속하여 명령속성을 지정 할 수 있다. 장비에 여러버전의 오라클 바이너리가 설치 되어 있거나 패스등록이 안되어 있으면 실행파일의 전체 경로를 사용 하면 된다. 

 

$ sqlldr user/password@db regions.ctl
$ sqlldr control=regions.ctl userid=system/password
$ sqlldr system/password control=regions.ctl

$ sqlldr USERID=usr/pass CONTROL=/u01/app/oracle/finance/finance.ctl \
         DATA=/u01/app/oracle/oradata/load/finance.dat \
         LOG=/u01/aapp/oracle/finance/log/finance.log \
         ERRORS=0 DIRECT=true SKIP=235550 RESUMABLE=true RESUMABLE_TIMEOUT=7200
D:\>C:\oracle\product\10.2.0\client_1\BIN\sqlldr userid=usr/pass@db control=STREETCODES.CTL DIRECT=true

 

파라미터 파일을 사용하는 것도 가능하다. 위의 명령을 파라미터 파일을 사용한 경우 다음 과 같다.

$ sqlldr PARFILE=/u01/app/oracle/admin/finance/load/finance.par

 

# finance.par

USERID=usr/pass

CONTROL='u01/app/oracle/admin/finance/finance.ctl'
DATA='/app/oracle/oradata/load/finance.dat'
LOG='/u01/aapp/oracle/admin/finance/log/finance.log'
ERRORS=0
DIRECT=true
SKIP=235550
RESUMABLE=true
RESUMABLE_TIMEOUT=7200

 

가장 편리한 방법은 커맨드 파일을 작성하여 더블클릭 하거나 배치파일 명을 입력하는 방법이다.

#ldrstart.cmd

C:\oracle\product\10.2.0\client_1\BIN\sqlldr userid="wkkjm/wkkjm@wkkjm" control=STREETCODES.CTL

 

 

Command-Line Parameters

 

USERID

데이터베이스 연결 문자열 (scott/tiger@prod).

CONTROL

컨트롤 파일을 명칭 및 경로.
LOG

컨트롤 파일의 명칭 및 경로. 생략시 컨트롤 파일과 같은 명칭 (.log 확장자)

DATA

데이터 파일의  명칭 및 경로. 생략시 컨트롤 파일과 같은 명칭 (.dat 확장자)

BAD

오류 파일의 명칭 및 경로. 생략시 컨트롤 파일과 같은 명칭 (.bad 확장자)

이 파일의 포맷은 데이터 파일과 동일하며 이 파일내의 오류를 보완하여 재작업 가능.

DISCARD

제외데이터 파일의 명칭 및 경로. 기본값은 데이터파일과 같은 명칭 (.dsc 확장자)

WHEN 절의 조건에 맞지 않는 데이터를 원시 데이터 파일과 동일한 포맷을 저장함.

PARFILE

파라미터 파일의 경로와 이름.
discardmax

The maximum number of discards to allow before failing. The default is all.
skip

The number of records to skip before starting to load. The default is none.
load

The number of records to load. The default is all.
errors

The number of errors to allow before failing. The default is 50.
rows

The number of rows in a conventional path bind array or between direct path data saves.

The default is 64 rows in conventional path mode and allrows in direct path mode.
bindsize

The size of the conventional path bind array in bytes. The default is 256KB.
DIRECT

If TRUE, use direct path. The default is FALSE, indicating conventional path.

기본경로는 일반적인 SQL INSER 문의 처리 방법과 같다고 보면 된다. 직접경로 방식은 데이터를 파일에 직접 기록하는 방식으로 빠른 작업 속도를 내지만 기본키, 유일키, not null 제약만 적용 되며 INSERT 트리거는 사용이 불가능 하다.

 

 

 

 

 

4. 컨트롤파일(Control File)

   SQL*Loader 컨트롤 파일은 입력할  데이터 정의어(DDL)로 작성된 작업명령들을 포함하는 텍스트 파일이다. SQL*Loader를 이용한 Bulk 데이터 입력 작업의 대부분은 컨트롤 파일 작성이라고 해도 틀린말이 아니다. 주요 기능은 SQL Loader가 로드할 데이터가 존재하는 위치를 지정하고, 로드될 데이터의 형식을 알려준다. 다음으로 데이터를 로드하는 동안 SQL Loader가 메모리 관리, 제외되는 레코드 지정 하거나 업로드할 원시 데이터를 변환하는 방법을 지정 할 수 있다. 일반적으로 컨트롤 파일은 "Session-wide information", "Table and field-list information", "Input data" 의 세개의 Section이 순차적으로 나타난다. 컨트롤 파일은 대소문자를 구분하지 않으며 작은 따옴표나 큰 따옴표로 묶인 문자열은 그대로 사용한다. 일반적인 SQL문에서 처럼 두 개의 하이픈(--) 에서 라인의 끝까지 존재하는 문자열을 주석으로 인식한다.

 

 

 

 컨트롤파일의 형태

 

-- 연속된 두개의 하이픈은 주석

LOAD DATA           --  로드가 시작됨을 의미

-- 중단된 로드를 계속할 경우 CONTINUE LOAD DATA 사용

 

INFILE='/a01/app/oracle/oradata/load/myData.dat'   -- 외부 데이터 파일 지정

-- 컨트롤 파일에 데이터를 포함하는 경우 INFILE *

--Variable record 데이터 포맷인 경우 INFILE 'example1.dat' "var 2"

--Fixed record 데이터 포맷인 경우 INFILE 'example1.dat' "fix 12"

BADFILE='myData.bad'            -- 오류로 저장이 실패한 레코드를  저장할 파일
DISCARDFILE = 'myData.dsc'   -- 입력작업에서 제외한 레코드가 저장될 파일

 

REPLACE         -- 테이블의 기존 행을 모두 삭제 후 작업

     -- APPEND:기존테이블에 추가,  INSERT : 비어 있는 테이블에 입력

     -- TRUNCATE : 테이블을 비우고 작업

 

INTO TABLE  myTable        -- 데이터를 업로드할 테이블 지정
WHEN (10) = "."                 -- 입력할 레코드의 조건을 지정

               -- 10번째 문자가 "." 인경우 업로드  나머지는 DISCARDFILE 에 저장 저장됨

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY""   -- 필드 구분자 및 문자열 구분자 지정
(column1 POSITION (1:2) CHAR,
 column2 POSITION (3:9) INTEGER EXTERNAL,
 column3 POSITION (10:15) INTEGER EXTERNAL,
 column4 POSITION (16:16) CHAR
)

 

 

 데이터를 포함하는 컨트롤 파일

 

LOAD DATA
INFILE *
APPEND
INTO  myTable
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno,     -- - DATA Type을 지정하지 않으면 Default로 char(255)
 ename,
 hi_name "DECODE(:ename, 'Jones','hi Jones',:ename)",
 job,
 mgr,
 hiredate  DATE(20) "DD-Month-YYYY",
 sal,
 comm,
 deptno   CHAR TERMINATED BY ':',       -- " :" 문자를 만날 때 까지
 projno,
 loadseq  SEQUENCE(MAX,1))               -- 일련번호 부여
BEGINDATA
7782, "Clark",  "Manager"  , 7839,  09-June-1981       , 2572.50,           , 10:101
7839, "King",   "President" ,        , 17-November-1981, 5500.00,            , 10:102
7934, "Miller",  "Clerk"      ,  7782, 23-January-1982   , 920.00 ,            , 10:102
7566, "Jones", "Manager"  , 7839, 02-April-1981        , 3123.75,            , 20:101
7499, "Allen",   "Salesman", 7698, 20-February-1981  , 1600.00, 300.00  , 30:103
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, "Chan",  "Analyst"    , 7566, 03-May-1982         , 3450    ,           , 20:101
 

 

 

  데이터 편집이나 기본 값 지정을 위한 메서드 및 지시어  
CONSTANT  : 상수를 지정하여 특정 컬럼에 지정된 상수를 로딩한다.
RECNUM  : 로딩되는 레코드의 현재 Count를 특정 컬럼에 로딩한다.
SYSDATE   : 특정 컬럼에 현재 날짜를 로딩한다.
SEQUENCE(start,inc) : start부터 inc만큼씩 증가하는 수를 로딩한다.
-- start에는 MAX, COUNT와 같은 Keyword를 사용할 수 있다.
-- MAX는 현재 컬럼에 존재하는 값들 중 가장 큰 값에 inc를 더한 값이다.
-- COUNT는 로딩할 테이블에 이미 존재하는 레코드건수에 inc를 더한 값이다.
"DECODE(:fld1, 'hello','hi',:fld1)  : 문자 변경
           

 

 

 데이터 포맷과 필드 파싱

 

-  Stream  Record Format 

레코드의 크기가 지정되지 않았고 필드구분자를 통해 구분하는 가장 일반적인 데이터 포맷

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY"" 구문을 사용하여 파싱

 

Nicholas Alapati,243 New Highway,Irving,TX,75078
Shannon Wilson,1234 Elm Street,Fort Worth,TX,98765
Nina Alapati,2629 Skinner Drive,Flower Mound,TX,75028

 

- Variable  Record Format

가변레코드 포맷은 레코드의 길이가 각 레코드 시작부분에 표시되는 형태로 스트림 레코드 형식보다 성능이 우수하다. INFILE 'example1.dat' "var 2" 처럼 사용한다. 여기서 숫자 2는 문자열의 길이를 나타내는 디지트 값.
06sammyy12johnson,1234

 

- Fixed Record Format

데이터 파일의 모든 레코드가 동일한 길이를 가지는 데이터 형태로 작업 성능이 가장 우수 하다.
INFILE 'example1.dat' "fix 12"
sammyy,1234, johnso,1234

 

- 절대위치 지정 방법 (Absolute Position)

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY""
(column1 POSITION (1:2) CHAR,
 column2 POSITION (3:9) INTEGER EXTERNAL,
 column3 POSITION (10:15) INTEGER EXTERNAL,
 column4 POSITION (16:16) CHAR)
BEGINDATA
AY3456789111111Y

 

 

- 상대위치 지정방법 (Relative Position) - 선행하는 필드의 위치를 반영

employee_id POSITION(*) NUMBER EXTERNAL 6
employee_name POSITION(*) CHAR 30