Database 대용량 데이터 이관 예제
Database 대용량 데이터 이관 방법의 예제를 설명합니다.
데이터 이관은 다양한 방법을 이용하여 수행할 수 있고 이로 인해 수행 속도를 최적화 시킬 수 있습니다.
아래는 예제에서 사용하는 테이블 정보입니다.
CREATE TABLE DBTECH.LINEITEM (
L_ORDERKEY NUMBER,
L_PARTKEY NUMBER,
L_SUPPKEY NUMBER,
L_LINENUMBER NUMBER,
L_QUANTITY NUMBER(15,2),
L_EXTENDEDPRICE NUMBER(15,2),
L_DISCOUNT NUMBER(15,2),
L_TAX NUMBER(15,2),
L_RETURNFLAG CHAR(1),
L_LINESTATUS CHAR(1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT CHAR(25),
L_SHIPMODE CHAR(10),
L_COMMENT VARCHAR(44)
) NOLOGGING;Table Migrator 예제
Table Migrator 방법으로 대용량 데이터를 이관하는 순서입니다.
migrator.properties 파일에 속성값을 설정합니다.
$ cat migrator.properties
#######################
# Source DB Connection
#######################
# Source DB type (Optional, generic | default | ORACLE)
# ORACLE option needs SOURCE_LOGIN_AS property
# GENERIC option needs SOURCE_DRIVER property
SOURCE_TYPE=ORACLE
# Source DB JDBC driver class name
# Use with SOURCE_TYPE=GENERIC option
SOURCE_DRIVER=oracle.jdbc.OracleDriver
#SOURCE_DRIVER=com.mysql.jdbc.Driver
# Source DB JDBC connection URL (Optional,
Default=jdbc:oracle:thin:@localhost:1521:orcl)
SOURCE_URL=jdbc:oracle:thin:@localhost:1539:orcl
#SOURCE_URL=jdbc:mysql://127.0.0.1:3306/mysql
# Source DB login ID (Optional, Default=sys)
#SOURCE_USER="user1"
SOURCE_USER=tibero
# Source DB login password (Optional, Default=oracle)
SOURCE_PASSWORD=tmax
# Oracle source DB login option (Optional, Default=sysdba)
# Use with SOURCE_TYPE=ORACLE option
SOURCE_LOGIN_AS=NORMAL
# Source table schema (Optional)
# Default value is the same with SOURCE_USER
#SOURCE_SCHEMA=SCOTT
#SOURCE_SCHEMA="dbo"
#SOURCE_SCHEMA=
# Source table name (Mandatory)
#SOURCE_TABLE=tableName1
#SOURCE_TABLE="tableName2"
#SOURCE_TABLE=bt_scen_tran
SOURCE_TABLE=t1
# Ignore deadlock error that occur during migration for the tables created using the dedup option
# Use when BATCH_THRESHOLD=1
# If not, Data integrity is not guaranteed
SOURCE_TABLE_DEDUPLICATE=N
#######################
# Target DB Connection
#######################
# Target DB type (Optional, DEFAULT)
TARGET_TYPE=DEFAULT
# Target DB JDBC driver class name
#TARGET_DRIVER=com.tmax.tibero.jdbc.TbDriver
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
# Target DB JDBC connection URL (Optional,
Default=jdbc:internal:thin:@localhost:8629:tibero)
#TARGET_URL=jdbc:tibero:thin:@192.1.5.63:39208:tibero
TARGET_URL=jdbc:internal:thin:@localhost:59166:tibero
# Target DB login ID (Optional, Default=sys)
#TARGET_USER="user1"
TARGET_USER=tibero
# Target DB login password (Optional, Default=tibero)
TARGET_PASSWORD=tmax
# Target table schema (Optional)
# Default value is the same with TARGET_USER
# TARGET_SCHEMA=TIBERO
# Target table name (Optional)
# Default value is the same with SOURCE_TABLE
# TARGET_TABLE=t1
#################
# Data Extractor
#################
# Use parallel Extracting (Optional, Y | N Default=N)
EXTRACT_PARALLEL=N
# Number of data extractor thread (Optional, Default=1)
EXTRACT_THREAD_COUNT=1
# Use TSN(or SCN) option (Optional)
#SELECT_TSN=17322052
# Where conditions (Optional)
# ex. column1 > 10 AND column2 = 'abc'
#SELECT_CONDITION= c1>1
# Fetch size of source table cursor (Optional, Default=1024)
# Some DB is not supported this feature.
#SELECT_FETCH_SIZE=1024
# Source table partition name (Optional)
#SELECT_PARTITION=
# Source table subpartition name (Optional)
#SELECT_SUBPARTITION=subpar1
#SELECT_SUBPARTITION="subpart2"
#SELECT_SUBPARTITION=
# Ignore source/target DB character set (Optional, y | N )
SELECT_AS_BYTE=N
# db_file_multiblock_read_count (Optinal, Option for Oracle), Defalut=-1)
DB_FILE_MULTIBLOCK_READ_COUNT=-1
# Use parallel excution of SQL statements (Optinal, Option for Oracle), Defalut=N)
USE_PARALLEL_HINT=N
# Use with USE_PARALLEL_HINT=Y (Defalut=-1)
PARALLEL_HINT_CNT=-1
##############
# Data Loader
##############
# Use direct path loading (Optional, cpl | DPL)
# CPL means conventional path loading
# DPL means direct path loading I
NSERT_METHOD=dpl
# Use commit size option(Optinal, Default = commit once after loading)
# Use with INSERT_METHOD=CPL option
# set commit_size-1 to commit only once
COMMIT_SIZE=-1
# Use index disable option(Optional, Default = N, Y/N)
INDEX_DISABLE=N
# Use batch insert (Optional, Y | n)
# Use with INSERT_METHOD=CPL option
INSERT_BATCH=Y
# batch threshold size (Optional, Default=32)
BATCH_THRESHOLD=128
# Use parallel loading (Optional, Y | n)
INSERT_PARALLEL=N
# Number of data loader thread (Optional, Default=4)
# Use with INSERT_PARALLEL=Y option
INSERT_THREAD_COUNT=1
# Target table partition name (Optional)
#INSERT_PARTITION=
# Specifies whether to keep the column names of the source table case-sensitive.
(Optional, Y | n )
# If set to Y, the original column name format will be preserved by enclosing it in
double quotes.
# If set to N, the column name format will follow the database naming rules.
INSERT_COLUMN_NAME_CASE_SENSITIVE=N
# Use zero length string data migration as null (Optional, Y | n, Default=Y, Option ignored for Oracle)
# If set to N, the zero length data will be inserted as ' '.
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
# Trim leading and trailing pad character of CHAR type data. (Optional, Y | n, Default=N)
#TRIM=
##############
# Common
##############
# Use MetaData Checking for DPL. (Optional, Default=Y)
CHECK_COLUMN_METADATA=N
# Log
LOG_LEVEL=INFO
LOG_FILE_NAME=tableMigrator.log
#LOG_DIR=Background Process를 이용하여 병렬 프로세싱으로 migrator를 수행합니다.
$ cat run.sh
# dbtech.lineitem 테이블 (조건 l_linestatus='0'에 해당하는 데이터)
nohup migrator.sh SOURCE_TYPE=ORACLE \
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL \
SOURCE_USER=dbtech SOURCE_PASSWORD=dbtech SOURCE_LOGIN_AS=NORMAL \
SOURCE_SCHEMA=dbtech SOURCE_TABLE=lineitem \
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero \
TARGET_USER=dbtech TARGET_PASSWORD=dbtech SELECT_CONDITION="l_linestatus='O'" >
migrator_a_set.log &
# dbtech.lineitem 테이블 (조건 l_linestatus='F'에 해당하는 데이터)
nohup migrator.sh SOURCE_TYPE=ORACLE \
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL
\ SOURCE_USER=dbtech SOURCE_PASSWORD=dbtech SOURCE_LOGIN_AS=NORMAL \
SOURCE_SCHEMA=dbtech SOURCE_TABLE=lineitem \
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero \
TARGET_USER=dbtech TARGET_PASSWORD=dbtech SELECT_CONDITION="l_linestatus='F'" >
migrator_b_set.log &수행 결과와 로그 파일을 확인합니다.
# dbtech.lineitem 테이블의 조건 l_linestatus='0' migrator 수행 로그 확인
$ cat migrator_a_set.log
=== Parameters ===
PROPERTY_FILE=migrator.properties
SOURCE_TYPE=ORACLE
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL
SOURCE_USER=dbtech
SOURCE_PASSWORD=************
SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech
SOURCE_TABLE=lineitem
TARGET_TYPE=DEFAULT
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero
TARGET_USER=dbtech
TARGET_PASSWORD=************
SELECT_CONDITION=l_linestatus='F'
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
INSERT_METHOD=DPL
INSERT_BATCH=Y
INSERT_PARALLEL=Y
INSERT_THREAD_COUNT=4
==================
[E0]1373322363068 - STARTED
[L0]1373322363189 Loader started
[L1]1373322363192 Loader started
[L2]1373322363196 Loader started
[L3]1373322363198 Loader started
[L1] 1000
[L0] 1000
[L3] 1000
[L2] 1000
... 중략 ...
[E0]1373322793890 - TOTAL Extracted ROWS: 3004998
[L3]1373322793913 TOTAL Loaded ROWS: 617366
[L0]1373322793921 TOTAL Loaded ROWS: 893541
[L1]1373322793924 TOTAL Loaded ROWS: 898889
[L2]1373322793935 TOTAL Loaded ROWS: 595202
Loading is ended.
Elapsed Time : 431636
ADD BATCH : 0
EXECUTE BATCH : 0
[VERIFICATION] Source Info : dbtech.lineitem: 3004998 Rows, Target Info :
dbtech.lineitem : 3004998 Rows [OK]
# dbtech.lineitem 테이블의 조건 l_linestatus='F' migrator 수행 로그 확인
$ cat migrator_b_set.log
=== Parameters ===
PROPERTY_FILE=migrator.properties
SOURCE_TYPE=ORACLE
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL
SOURCE_USER=dbtech
SOURCE_PASSWORD=************
SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech
SOURCE_TABLE=lineitem
TARGET_TYPE=DEFAULT
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero
TARGET_USER=dbtech
TARGET_PASSWORD=************
SELECT_CONDITION=l_linestatus='O'
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
INSERT_METHOD=DPL
INSERT_BATCH=Y
INSERT_PARALLEL=Y
INSERT_THREAD_COUNT=4
==================
[E0]1373322363042 - STARTED
[L0]1373322363168 Loader started
[L1]1373322363172 Loader started
[L2]1373322363176 Loader started
[L3]1373322363215 Loader started
[L1] 1000
[L0] 1000
[L3] 1000
[L2] 1000
...(중략)
[E0]1373322795287 - TOTAL Extracted ROWS: 2996217
[L1]1373322795290 TOTAL Loaded ROWS: 767331
[L3]1373322795303 TOTAL Loaded ROWS: 759888
[L2]1373322795312 TOTAL Loaded ROWS: 739667
[L0]1373322795314 TOTAL Loaded ROWS: 729331
Loading is ended.
Elapsed Time : 432951
ADD BATCH : 0
EXECUTE BATCH : 0
[VERIFICATION] Source Info : dbtech.lineitem: 2996217 Rows, Target Info :
dbtech.lineitem : 2996217 Rows [OK]DB Link 예제
DB Link로 insert ... select ... 구문을 수행할 때 parallel 힌트를 사용하면 select는 serial하게 동작하고, insert일 때만 parallel로 동작합니다. 따라서 DB Link 원본 데이터가 존재하는 서버의 사양이 좋을 경우 parallel 힌트를 사용하여 view를 생성하고 해당 view를 Tibero에서 select하는 방식으로 이관하면 성능 향상을 기대할 수 있습니다.
아래는 DB Link 방법으로 대용량 데이터를 이관하는 순서입니다.
전체 건수를 균등하게 나눌 수 있는 조건을 검토합니다.
만족하는 조건이 있는 경우 SQL문을 나눈 후에 병렬 세션(터미널 또는 다른 컴퓨터)으로 수행하며 Temp와 Undo Tablespace 사용량의 모니터링이 필요합니다.
SQL문에 append, parallel 힌트를 사용합니다.
append Insert하는 대상 테이블에 대해서 nologging 옵션이 적용되있어야 합니다.
-- nologging 테이블 확인
select logging from user_tables where table_name = 'LINEITEM';
-- DB Link를 통해 ITAS 수행 (appned)
insert /*+ append */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='O';parallel 힌트를 적용하기 위해 해당 세션에 parallel dml 옵션을 enable해야 합니다.
-- dml 병렬 파라미터 활성화
alter session enable parallel dml;
-- DB Link를 통해 ITAS 수행 (parallel)
insert /*+ parallel(b 4) */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='O';아래는 세션이 2개일 경우 DB Link를 수행하는 예입니다.
-- 세션1과 세션2에서 입력한 최종 데이터
SQL> select l_linestatus, count(*) from dbtech.lineitem@olink group by l_linestatus;
L_LINESTATUS COUNT(*)
------------ --------
F 2996217
O 3004998
2 rows selected세션 1수행 L_LINESTATUS가 '0'에 대해서 Insert합니다.
SQL> set timing on
SQL> alter session enable parallel dml;
Session altered.
Total elapsed time 00:00:00.005235
SQL> insert /*+ append parallel(b 4) */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='O';
3004998 rows inserted.
Total elapsed time 00:07:07.806974
SQL> commit;
Commit completed.
Total elapsed time 00:00:00.000307세션 2수행 L_LINESTATUS가 'F'에 대해서 Insert합니다.
SQL> set timing on
SQL> alter session enable parallel dml;
Session altered.
Total elapsed time 00:00:00.005235
SQL> insert /*+ append parallel(b 4) */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='F';
2996217 rows inserted.
Total elapsed time 00:07:01.692934
SQL> commit;
Commit completed.
Total elapsed time 00:00:00.000307COMMIT된 데이터 확인
SQL> select count(*) from dbtech.lineitem;
COUNT(*)
-----------
6001215
1 row selected.tbLoader 예제
Load할 SAM 파일을 n등분하고 직접 로딩과 병렬 프로세싱을 동시에 사용합니다.
데이터를 로딩하는 방식으로 디스크에서 wait만 발생하지 않는다면 최적의 수행 속도가 보장됩니다.
아래는 tbLoader 방법으로 대용량 데이터를 이관하는 순서입니다.
Unload(spool 또는 unload 유틸을 이용하여 데이터 unload) 및 SAM 파일을 전송합니다.
Load할 SAM 파일을 n등분 하여 n개의 SAM 파일을 준비합니다.
각 SAM 파일을 읽는 n개의 컨트롤 파일을 작성하고, 작성할 때 append 옵션을 사용합니다.
아래는 2개의 컨트롤 파일을 작성하는 방법입니다.
# SAM 파일을 특정 기준으로 N개로 파일을 나눈다.
# 나눈 파일 마다 tbLoader Control 파일을 만들어준다.
# aa 컨트롤 파일
$ cat lineitem.ctl.aa
load data
infile 'lineitem.tbl.aa'
logfile 'lineitem.tbl.aa.log'
badfile 'lineitem.tbl.aa.bad'
append
into table lineitem
fields terminated by '|'
(L_ORDERKEY,
L_PARTKEY,
L_SUPPKEY,
L_LINENUMBER,
L_QUANTITY,
L_EXTENDEDPRICE,
L_DISCOUNT,
L_TAX,
L_RETURNFLAG,
L_LINESTATUS,
L_SHIPDATE,
L_COMMITDATE,
L_RECEIPTDATE,
L_SHIPINSTRUCT,
L_SHIPMODE,
L_COMMENT)
# ab 컨트롤 파일
$ cat lineitem.ctl.ab
load data
infile 'lineitem.tbl.ab'
logfile 'lineitem.tbl.ab.log'
badfile 'lineitem.tbl.ab.bad'
append
into table lineitem
fields terminated by '|'
(L_ORDERKEY,
L_PARTKEY,
L_SUPPKEY,
L_LINENUMBER,
L_QUANTITY,
L_EXTENDEDPRICE,
L_DISCOUNT,
L_TAX,
L_RETURNFLAG,
L_LINESTATUS,
L_SHIPDATE,
L_COMMITDATE,
L_RECEIPTDATE,
L_SHIPINSTRUCT,
L_SHIPMODE,
L_COMMENT)Background Process를 이용하여 병렬 프로세싱으로 tbloader를 실행하고, tbloder 명령어에 'direct=y dpl_parallel=y' 옵션을 사용합니다.
$ cat run.sh
# aa 파일 tbLoader 명령
nohup tbloader userid=dbtech/dbtech@tibero control=lineitem.ctl.aa
direct=y dpl_parallel=y &
# ab 파일 tbLoader 명령
nohup tbloader userid=dbtech/dbtech@tibero control=lineitem.ctl.ab
direct=y dpl_parallel=y &아래는 2개의 SAM 파일 및 컨트롤 파일을 가지고 tbLoader를 수행하는 예입니다.
세션 1 수행
-- lineitem.tbl.aa.log:
tbLoader 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Data File : lineitem.tbl.aa
Bad File : lineitem.tbl.aa.bad Discard File : lineitem.tbl.aa.dsc
Table 'LINEITEM' was loaded from the data file.
COLUMN_NAME POSITION DATATYPE
--------------------- ----------- -----------------
L_ORDERKEY 1 CHARACTER
L_PARTKEY 2 CHARACTER
L_SUPPKEY 3 CHARACTER
L_LINENUMBER 4 CHARACTER
L_QUANTITY 5 CHARACTER
L_EXTENDEDPRICE 6 CHARACTER
L_DISCOUNT 7 CHARACTER
L_TAX 8 CHARACTER
L_RETURNFLAG 9 CHARACTER
L_LINESTATUS 10 CHARACTER
L_SHIPDATE 11 DATE
L_COMMITDATE 12 DATE
L_RECEIPTDATE 13 DATE
L_SHIPINSTRUCT 14 CHARACTER
L_SHIPMODE 15 CHARACTER
L_COMMENT 16 CHARACTER
Table LINEITEM :
------------------
0 Rows were skipped.
3000608 Rows were requested to load.
3000608 Rows were loaded successfully.
0 Rows were failed to load because of some errors
0 Rows were discarded.
0 Rows were failed to load because of some errors
Elapsed time was: 00:00:33.670898세션 2 수행
-- lineitem.tbl.ab.log:
tbLoader 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Data File : lineitem.tbl.ab
Bad File : lineitem.tbl.ab.bad
Discard File : lineitem.tbl.ab.dsc
Table 'LINEITEM' was loaded from the data file.
COLUMN_NAME POSITION DATATYPE
----------------------------- ----------- -----------
L_ORDERKEY 1 CHARACTER
L_PARTKEY 2 CHARACTER
L_SUPPKEY 3 CHARACTER
L_LINENUMBER 4 CHARACTER
L_QUANTITY 5 CHARACTER
L_EXTENDEDPRICE 6 CHARACTER
L_DISCOUNT 7 CHARACTER
L_TAX 8 CHARACTER
L_RETURNFLAG 9 CHARACTER
L_LINESTATUS 10 CHARACTER
L_SHIPDATE 11 DATE
L_COMMITDATE 12 DATE
L_RECEIPTDATE 13 DATE
L_SHIPINSTRUCT 14 CHARACTER
L_SHIPMODE 15 CHARACTER
L_COMMENT 16 CHARACTER
Table LINEITEM :
------------------
0 Rows were skipped.
3000607 Rows were requested to load.
3000607 Rows were loaded successfully.
0 Rows were failed to load because of some errors
0 Rows were discarded.
0 Rows were failed to load because of some errors
Total Elapsed Time: 00:00:33.795058Last updated