SQL LOADER란?

SQL LOADER란?

원본 : 기술노트[43회]_SQLloader.pdf

 

기존의 응용 프로그램 데이터나 다른 데이터베이스로 저장된 데이터를 오라클 데이터베이스 테이블에 넣기 위한 유틸리티로서 IBM의 DB2 load 유틸리티와 흡사하다.
오라클 데이터베이스를 설치하면 기본적으로 설치되며 간단하고 편리하게 데이터를 데이터베이스에 로드할 수 있다.

 

SQL LOADER의 특징
- SQL Loader는 하나 이상의 입력 파일을 사용할 수 있다.
- 로드 할 때 여러 개의 입력 레코드를 하나의 논리적 레코드로 결합할 수 있다.
- 입력 필드는 고정 길이 또는 가변 길이가 가능하다.
- 문자, 이진, 팩형 십진 형식(packed decimal format), 날짜 및 존 십진 형식(zoned decimal format)과 같은 임의의 형식이 입력 데이터가 될 수 있다.
- 데이터를 디스크, 테이프 또는 명명된 파이프와 같은 다양한 매체 유형에서 로드 할 수 있다.
- 데이터를 한번 실행하여 여러 테이블로 로드 할 수 있다.
- 테이블의 기존 데이터를 바꾸거나 추가하기 위해 옵션을 사용할 수 있다.
- 데이터 베이스 행을 저장하기 전에 SQL 함수를 입력 데이터에 적용할 수 있다.
- 규칙에 따라 열 값을 자동으로 생성할 수 있다. 예를 들어, 순차 키 값을 열에 생성하고 저장할 수 있다.

 

 

SQL LOADER는 5개의 (control file, data file, log file, bad file, discard file)을 사용한다.

 

컨트롤파일(Control File)
Control file 은 SQL Loader를 사용하는데 필수 적인 파일의 하나로써 데이터 정의어(DDL) 지침을 포함하는 텍스트 파일이다. 확장자는 'ctl'이다.

 

기능
- SQL Loader가 로드할 데이터를 찾을 위치 명시
- SQL Loader가 형식이 지정될 데이터를 예상하는 방법
- 데이터를 로드하는 동안 SQL Loader가 메모리 관리, 레코드 거부, 중단된 로드 처리 등을 구성하는 방법
- SQL Loader가 로드 중인 데이터를 조작하는 방법

 

컨트롤파일 작성시 고려사항
- 구문은 자유로운 형식이므로 여러 행으로 확장 될 수 있다.
- 대소문자를 구분하지 않는다.
- 작은 따옴표나 큰 따옴표로 묶인 문자열은 대소문자를 포함하여 문자 그대로 사용한다.
- 제어 파일 구문에서 주석은 주석의 시작을 나타내는 두 개의 하이픈(--)으로 표시한다.

 

컨트롤파일 형식

-- This is a sample control file 주석의 입력
LOAD DATA 새 데이터 로드가 시작됨을 의미 진행 중에 중단된 로드를 계속할 경우 CONTINUE LOAD DATA 문 사용
INFILE 'SAMPLE.DAT' 외부 데이터 파일 지정, 포함하지 않을 경우 '*'로 표시
BADFILE 'sample.bad' 거부된 레코드를 배치할 파일 이름을 지정
DISCARDFILE 'sample.dsc' 페기된 레코드를 배치할 파일 이름을 지정
REPLACE 테이블에 데이터를 삽입하는 방법 지정
- REPLACE : 테이블의 기존 행을 모두 삭제 하고 삽입
- APPEND : 새로운 행을 기존의 데이터에 추가
- INSERT : 비어 있는 테이블에 넣을 때 사용
- TRUNCATE : 테이블의 기존 데이터를 모두 삭제하고 삽입
INTO TABLE test 데이터를 로드 할 테이블 지정
WHEN (10) = '.' 데이터를 로드하기 전에 만족시켜야 할 각 레코드의 필드 조건을 지정
이 예제는 10번째 문자가 소수점인 경우에만 레코드를 삽입한다
FIELDS TERMINATED BY ',' 데이터 필드의 종결 문자를 지정

 

컨트롤파일 작성 예제

- 데이터를 포함하는 경우

LOAD DATA

INFILE *

REPLACE

INTO TABLE test

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

( NUMBER, NAME, PHONE)

BEGINDATA

1, “이규열”, "011-9401-0001"

2, “장동건”, "010-777-7777"

3, “신민아”, "010-555-4744"

- 데이터를 포함하지 않는 경우

LOAD DATA

INFILE „/SAMPLE/sql_loader.dat‟

REPLACE

INTO TABLE test

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(NUMBER, NAME, PHONE)

데이터파일(Data File)

SQL Loader로부터 데이터베이스에 입력될 텍스트 형식으로 구성된 데이터 파일

특징

- SQL Loader는 제어 파일에 지정된 하나 이상의 파일에서 데이터를 읽는다.

- SQL Loader는 관점에서 데이터 파일의 데이터는 레코드로 구성

- 컨트롤 파일의 INFILE 매개변수에 레코드 형식을 명시하며, 그렇지 않으면 스트림 형식으로 인식

로그파일(Log File)

데이터 로드 상태 정보, 로드 된 행의 수, 로드 진행중 데이터 에러 때문에 거부된 행의 수, 버려진 행의 수등 자세한 테이블 로드 정보가 포함

즉, 로그 파일은 읽어들인 레코드 수, 데이터 로드 시간, 입력되지 않은 레코드의 수와 같은 정보 포함

기본적으로 컨트롤 파일과 동일한 이름으로 생성

배드파일(Bad File)

SQL Loader또는 오라클 데이터베이스가 거부한 레코드를 포함

문법구조 - 컨트롤 파일 내에서 badfile '파일명.bad'

배드파일에 기록되는 경우

- 입력 형식에 부적합할 경우 (ex. 구분자 누락, 필드의 최대 길이 초과 등)

- 제약조건을 위해 하였을 경우 (ex. 고유하지 않은 키 값, 필수 필드가 null, oracle 데이터 유형에 부적합 등)

디스카드파일(Discard File)

SQL Loader가 실행되는 동안 폐기 파일이라는 파일을 생성 가능

필요한 경우만 생성

컨트롤 파일에 명시한 WHEN절의 조건에 맞지 않는 데이터를 저장

배드 파일처럼 입력 형식으로 저장되므로 오류를 수정하여 재사용이 가능

문법구조 - 컨트롤 파일 내에서 discardfile '파일명.dsc'

- 명령 행에서 discard(파일명)