꿈꾸는 개발자, DBA 커뮤니티 구루비

가. 개요

tbLoader 유틸리티는 텍스트 파일 형태로 저장되어 있는 데이터를 티베로 데이터베이스로 저장하는 기능을 제공한다. 다른 응용프로그램에서 사용하는 데이터 혹은 이기종 데이터베이스의 데이터를 티베로 데이터베이스로 이동하고자 할 경우 텍스트 파일 형태로 저장한 이후 티베로 데이터베이스 서버로 이동하면 tbLoader를 통해 손쉽게 저장이 가능하다.

다만 매우 큰 텍스트 파일의 경우 notepad 등의 에디터로 open하는 것 자체가 매우 어려운 일이어서 데이터베이스로 이동한 데이터의 신뢰성에 의심이 생기면 텍스트 파일의 해당 내용을 찾아 검증하는 데 시간이 오래 걸리므로 원본 데이터의 누락 없이 정확히 이관하는 것이 매우 중요하다.

다음 고려 사항을 통하여 어떠한 점을 주의해야하는지 알아보자.

나. tbLoader 특징 및 사용 시 고려 사항

1. 이관 대상 파일은 반드시 텍스트 파일 형태로 수령한다. 엑셀과 같은 텍스트 외의 형태로 수령한 파일을 텍스트로 변환하는 과정에서 원본 데이터가 변경되는 경우가 많아 정확한 데이터 이관이 어려우며 문제 발생 시 처음부터 다시 작업 해야 하는 일도 종종 발생한다(예를 들어 엑셀의 셀 서식에 따라 ‘-1’로 표기된 정보가 텍스트로 변환할 경우 ‘01’로 표기되기도 한다).

2. 이관 대상 데이터를 저장할 테이블 생성 시 제약 조건은 원본 데이터의 누락이 발생할 수 있으므로 모두 제외하도록 한다. 이후 SELECT문을 통해 여러 가지 필요한 제약 조건에 원본 데이터가 부합되는지 검증하고 제약 조건을 최종 생성 하도록 한다.

3. tbLoader의 성능 향상을 위해 다음 사항을 고려한다

3-1. 인덱스는 데이터가 테이블에 저장이 모두 완료된 이후 생성한다.

3-2. 업로드 시 메모리를 거치지 않고 데이터 파일에 직접 쓰도록 direct=y 옵션(Direct Path Load)을 부여하고 해당 테이블은 nologging을 설정하고 업로드 이후 logging으로 변경한다(Direct Path Load 사용 시에는 CHECK 제약조건, 참조키 제약 조건, 트리거 등이 작동하지 않으니 모두 제거하고 업로드 이후 수동 생성해야 한다).

다. tbLoader 시작 및 컨트롤 파일

tbLoader는 OS 프롬프트에서 $ tbloader userid=tibero/password control=sample.ctl data=sample.data와 같이 데이터를 저장할 데이터베이스 사용자명, 컨트롤 파일 그리고 실제로 업로드할 데이터가 저장되어 있는 데이터 파일을 지정하는 것으로 실행한다.

tbLoader의 세부 옵션은 tbLoader 명령어를 통해 조회가 가능하며 컨트롤 파일은 tbLoader 수행 시 필요한 각종 정보들을 지정한 파일로써 그 형식과 상세 정보는 다음과 같다.

[참고 6-13] tbLoader Option

tibero:/tibero # tbLoader
tbLoader 6
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Usage : tbloader [options] [controls]

Options:
 -h|--help Display the more detailed information.
 -c|--charset Display usable character sets in this version.
 -v|--version Display the version information.

Controls:
 userid Username/password@dbname
 control The name of the control file
 data The name of the data file
 --- (이하생략)

* 컨트롤 파일에 지정하는 정보
1) 데이터 파일에 포함된 문자 집합
2) 데이터를 포함하는 데이터 파일
3) 로드 수행 중에 발생한 로그를 기록할 로그 파일
4) 로드에 실패한 데이터를 기록하는 오류 파일
5) 오류 파일에 남기지 않을 에러 번호 지정
6) 테이블에 존재하는 기존 데이터에 대한 처리 방법(APPEND|REPLACE|TRUNCATE|MERGE)
7) 필드 종료자 및 기타 옵션(TERMINATOR, ENCLOSED BY STRING, ESCAPED BY STRING)
8) 라인의 시작 문자열과 종료 문자열
9) 데이터 파일에서 무시할 라인의 개수
10) 테이블의 특정 컬럼에 대한 옵션

[참고 6-14] 컨트롤 파일 작성 형식(출처 : http://technet.tmaxsoft.com)

라. tbLoader 수행

가장 기본적인 형태의 tbLoader 수행 예시 두 가지를 살펴보자. tbLoader를 통해 텍스트 파일을 업로드할 때 매우 다양한 옵션이 존재할 수 있겠지만 다음 예제를 크게 벗어난 업무 요건은 없을 것이라고 생각된다.

1. 콤마(,) 구분자의 텍스트 파일을 tbLoader 수행하며 Conventional Path Load 방식과 Direct Path Load 방식의 성능 비교하기

가. Sample 텍스트 파일 준비 (tbLoaderSample.data) & 서버 이동

[ 3개 컬럼, 6,056,764 rows, 콤마 구분자, 약 137MB ]
-------------------------------------------
tbLoaderTest,Y,Y1
tbLoaderTest,Y,Y2
tbLoaderTest,Y,Y3
tbLoaderTest,Y,Y4
tbLoaderTest,Y,Y5
tbLoaderTest,Y,Y6
tbLoaderTest,Y,Y7
---(중략)
tbLoaderTest,Y,Y6056763
tbLoaderTest,Y,Y6056764

나. 테이블 생성

CREATE TABLE TIBERO.TBLOADERTEST
(COL1 VARCHAR(12), COL2 VARCHAR(1), COL3 VARCHAR(8))
TABLESPACE TIBERODATA;

다. 컨트롤 파일 구성 (tbLoaderSample.ctl)

LOAD DATA                    <-- 데이터 업로드 시작
INFILE 'tbLoaderSample.data' <-- 업로드할 데이터가 저장되어 있는 파일
LOGFILE 'tbLoaderSample.log' <-- 데이터 로딩과정에서 발생하는 로그 기록 파일
BADFILE 'tbLoaderSample.bad' <-- 데이터 로딩에 실패한 레코드를 기록할 오류파일
APPEND                       <-- 기존 데이터가 삭제하지 않고 추가한다는 옵션
INTO TABLE TBLOADERTEST      <-- 데이터를 업로드할 테이블
FIELDS TERMINATED BY ','     <-- 콤마 구분자 지정
ESCAPED BY '\\'            <-- \이후의 문자[\]는 특수문자가 아닌 문자로 인식
LINES TERMINATED BY '\n'    <-- 지정된 문자(line feed, \n)를 1line(rows)으로 취급
(
COL1,
COL2,
COL3
)

APPEND 옵션외 REPLACE(기존 데이터 DELETE), TRUNCATE(기존 데이터 TRUNCATE) 및 MERGE(사용자 지정 컬럼을 키값으로 존재하면 UPDATE, 없으면 INSERT) 등이 존재한다.

라. Direct Path Load 방식으로 tbLoader

1) 업로드 대상 테이블 NOLOGGING 처리
ALTER TABLE TIBERO.TBLOADERTEST NOLOGGING;

2) tbLoader 수행전 redo log size 확인(약 900MB)
select name, trunc(value/1024/1024) 
  from v$sysstat 
 where name like '%redolog size%';

3) tbLoader 수행
$tibero:/tbTest # tbloader userid=tibero/tibero control=tbLoaderSample.
ctl data=tbLoaderSample.data direct=Y

4) 수행 완료 확인
tbLoader 6
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Start loading...
Committed and Completed successfully.

5) tbLoaderSample.log 로그 확인(약 26초 수행)
Table TIBERO.TBLOADERTEST :
 6056764 Rows were requested to load.
 6056764 Rows were loaded successfully.
 0 Rows were failed to load because of some errors
Elapsed time was: 00:00:26.470580

6) tbLoader 수행후 redo log size 확인(약 904MB, 거의 생성되지 않았음을 확인)
select name, trunc(value/1024/1024) 
  from v$sysstat 
 where name like '%redolog size%';

7) 업로드 대상 테이블 LOGGING 처리
ALTER TABLE TIBERO.TBLOADERTEST LOGGING;

마. Conventional Path Load 방식으로 tbLoader

1) tbLoader 수행전 redo log size 확인(약 904MB)
select name, trunc(value/1024/1024) 
  from v$sysstat 
 where name like '%redolog size%';

2) tbLoader 수행
$tibero:/tbTest # tbloader userid=tibero/tibero control=tbLoaderSample.
ctl data=tbLoaderSample.data

3) 수행 완료 확인
tbLoader 6
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Start loading...
Committed and Completed successfully.

4) tbLoaderSample.log 로그 확인 (약 47초 수행)
Table TIBERO.TBLOADERTEST :
 6056764 Rows were requested to load.
 6056764 Rows were loaded successfully.
 0 Rows were failed to load because of some errors
Elapsed time was: 00:00:47.438237

6) tbLoader 수행후 redo log size 확인(약 1098MB)
select name, trunc(value/1024/1024)  
  from v$sysstat 
 where name like '%redolog size%';

2. 고정된 레코드 형태의 파일을 Direct Path Load 방식으로 tbLoader 수행하기

가. Sample 텍스트 파일 준비 (tbLoaderSamplefixed.data) & 서버 이동

[ 3개 컬럼, 6,056,764 rows, 고정길이, 약 137MB ]
-------------------------------------------
tbLoaderTest Y Y1
tbLoaderTest Y Y2
tbLoaderTest Y Y3
tbLoaderTest Y Y4
tbLoaderTest Y Y5
tbLoaderTest Y Y6
tbLoaderTest Y Y7
---(중략)
tbLoaderTest Y Y6056763
tbLoaderTest Y Y6056764

나. 테이블 생성

CREATE TABLE TIBERO.TBLOADERTEST_FIXED
(COL1 VARCHAR(12), COL2 VARCHAR(1), COL3 VARCHAR(8))
TABLESPACE TIBERODATA;

다. 컨트롤 파일 구성 (tbLoaderSamplefixed.ctl)

LOAD DATA                         <-- 데이터 업로드 시작
INFILE 'tbLoaderSamplefixed.data' <-- 업로드할 데이터가 저장되어 있는 파일
LOGFILE 'tbLoaderSamplefixed.log' <-- 데이터 로딩 과정에서 발생하는 로그 기록 파일
BADFILE 'tbLoaderSamplefixed.bad' <-- 데이터 로딩에 실패한 레코드를 기록할 오류 파일
APPEND                            <-- 기존 데이터가 존재하면 추가한다는 옵션
INTO TABLE TBLOADERTEST_FIXED     <-- 데이터를 업로드할 테이블
TRAILING NULLCOLS                 <--특정 포지션에 데이터가 없을 경우 NULL을 입력
(
COL1 POSITION (01:12),            <--각 레코드의 위치
COL2 POSITION (14:14),
COL3 POSITION (16:23)
)

TRAILING NULLCOLS을 지정하지 않고 특정포지션에 데이터가 없을 경우 해당 ROWS는 오류로서 업로드 되지 않는다

라. Direct Path Load 방식으로 tbLoader

1) 업로드 대상 테이블 NOLOGGING 처리
ALTER TABLE TIBERO.TBLOADERTEST NOLOGGING;

2) tbLoader 수행
$tibero:/tbTest # tbloader userid=tibero/tibero
control=tbLoaderSamplefixed.ctl data=tbLoaderSamplefixed.data direct=Y

3) 수행 완료 확인
tbLoader 6
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Start loading...
Committed and Completed successfully.

4) tbLoaderSamplefixed.log 로그 확인 (약 26초 수행)
Table TIBERO.TBLOADERTEST_FIXED :
 6056764 Rows were requested to load.
 6056764 Rows were loaded successfully.
 0 Rows were failed to load because of some errors
Elapsed time was: 00:00:25.467786

5) 업로드 대상 테이블 LOGGING 처리
ALTER TABLE TIBERO.TBLOADERTEST LOGGING;

만약 LINE FEED가 없는 경우는 LINES FIX 구문을 사용하여 업로드 할 수 있다.

가. LINE FEED가 없는 샘플데이터 tbLoaderSamplefixed2.data 준비

------------------------------------------------------------------
tbLoaderTest Y FIXTEST tbLoaderTest Y FIXTEST tbLoaderTest Y FIXTEST
....(반복 이하 생략)

나. 컨트롤 파일 구성 (tbLoaderSamplefixed2.ctl)

LOAD DATA
INFILE 'tbLoaderSamplefixed.data'
LOGFILE 'tbLoaderSamplefixed.log'
BADFILE 'tbLoaderSamplefixed.bad'
APPEND
INTO TABLE TBLOADERTEST_FIXED
LINES FIX 23 <- [LINES] [FIX] [1ROW길이]를 통해 레코드 구분
(
COL1 POSITION (01:12),
COL2 POSITION (14:14),
COL3 POSITION (16:23)
)

다. LINE FEED와 동일하게 수행 후 수행 로그 확인

Table TIBERO.TBLOADERTEST_FIXED :
 6056764 Rows were requested to load.
 6056764 Rows were loaded successfully.
 0 Rows were failed to load because of some errors
Elapsed time was 00:00:25.356224 <-- 유사한 속도로 업로드 완료