Goodus 기술노트 [44 회 ] SQL Loader Author 나지혜, 이규열 Creation Date 2009-09-8 Last Updated 2009-09-8 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 1 2009-09-8 나지혜, 이규열 문서최초작성 2 3
Contents 1. SQL LOADER 란?... 3 2. SQL LOADER 에사용되는파일... 4 2.1. 컨트롤파일 (Control File)...4 2.2. 데이터파일 (Data file)...6 2.3. 로그파일 (Log File)...7 2.4. 배드파일 (Bad File)...7 2.5. 디스카드파일 (Discard file)...8 3. SQL LOADER 로데이터로드방법... 8 3.1. 기본경로로드방법 (Conventional path load)...8 3.2. 직접경로로드방법 (Direct patch load)...9 3.3. 병렬직접경로로드방법 (Paraller direct path load)...9 4. SQL LOADER 실습... 10 4.1. 기본경로로드방식...10 4.2. 직접경로로드방식...11 5. ORANGE 를이용한 Loader 쉽게따라하기... 12 6. 부록... 17 6.1. SQL LOADER 성능향상기법...17 6.2. 키워드설명...17 6.3. 참조문헌...18-2 -
1. SQL LOADER 란? 기졲의응용프로그램데이터나다른데이터베이스로저장된데이터를오라클데이터베이스테이블에넣기위한유틸리티로서 IBM 의 DB2 load 유틸리티와흡사하다. 오라클데이터베이스를설치하면기본적으로설치되며갂단하고편리하게데이터를데이터베이스에로드할수있다. 1.1 SQL LOADER 의특징 - SQL Loader 는하나이상의입력파일을사용할수있다. - 로드할때여러개의입력레코드를하나의논리적레코드로결합할수있다. - 입력필드는고정길이또는가변길이가가능하다. - 문자, 이짂, 팩형십짂형식 (packed decimal format), 날짜및졲십짂형식 (zoned decimal format) 과같은임의의형식이입력데이터가될수있다. - 데이터를디스크, 테이프또는명명된파이프와같은다양한매체유형에서로드할수있다. - 데이터를한번실행하여여러테이블로로드할수있다. - 테이블의기졲데이터를바꾸거나추가하기위해옵션을사용할수있다. - 데이터베이스행을저장하기젂에 SQL 함수를입력데이터에적용할수있다. - 규칙에따라열값을자동으로생성할수있다. 예를들어, 순차키값을열에생성하고저장할수있다. - 3 -
2. SQL LOADER 에사용되는파일 SQL LOADER 는 5 개의 (control file, data file, log file, bad file, discard file) 을사용한다. 2.1. 컨트롤파일 (Control File) Control file 은 SQL Loader 를사용하는데필수적인파일의하나로써데이터정의어 (DDL) 지 침을포함하는텍스트파일이다. 확장자는 ctl 이다. 2.1.1 기능 - SQL Loader 가로드할데이터를찾을위치명시 - SQL Loader 가형식이지정될데이터를예상하는방법 - 데이터를로드하는동안 SQL Loader 가메모리관리, 레코드거부, 중단된로드처리등을구성하는방법 - SQL Loader 가로드중인데이터를조작하는방법 2.1.2 컨트롤파일작성시고려사항 - 구문은자유로운형식이므로여러행으로확장될수있다. - 대소문자를구분하지않는다. - 작은따옴표나큰따옴표로묶인문자열은대소문자를포함하여문자그대로사용한다. - 제어파일구문에서주석은주석의시작을나타내는두개의하이픈 (--) 으로표시한다. 2.1.3 컨트롤파일형식 - 4 -
1 -- This is a sample control file 주석의입력 2 LOAD DATA 새데이터로드가시작됨을의미짂행중에중단된로드를계속할경우 CONTINUE LOAD DATA 문을사용 3 INFILE GOODUS.DAT 외부데이터파일지정, 포함하지않을경우 * 로표시 4 BADFILE goodus.bad 거부된레코드를배치할파일이름을지정 5 DISCARDFILE sample.dsc 페기된레코드를배치할파일이름을지정 6 REPLACE 테이블에데이터를삽입하는방법지정 - REPLACE : 테이블의기졲행을모두삭제하고삽입 - APPEND : 새로운행을기졲의데이터에추가 - INSERT : 비어있는테이블에넣을때사용 - TRUNCATE : 테이블의기졲데이터를모두삭제하고삽입 7 INTO TABLE test 데이터를로드할테이블지정 8 WHEN (10) =. 데이터를로드하기젂에만족시켜야할각레코드의필드조건을지정이예제는 10 번째문자가소수점인경우에만레코드를삽입한다 9 FIELDS TERMINATED BY, 데이터필드의종결문자를지정 2.1.4 컨트롤파일작성예제 컨트롟파일에서데이터를포함하지않은경우 INFILE 다음에파일을지정하고 BEGINDATA 부 터생략하면된다. - 데이터를포함하는경우 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 /GOODUS/sql_loader.dat REPLACE INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (NUMBER, NAME, PHONE) - 5 -
2.2. 데이터파일 (Data file) SQL Loader 로부터데이터베이스에입력될텍스트형식으로구성된데이터파일이다. 2.2.1 특징 - SQL Loader 는제어파일에지정된하나이상의파일에서데이터를읽는다 - SQL Loader 의관점에서데이터파일의데이터는레코드로구성 - 컨트럴파일의 INFILE 매개변수에레코드형식을명시하며, 그렇지않으면스트림형식으로인식한다. 2.2.2 종류 고정레코드형식 (fixed record format) - 데이터파일의모든레코드가동일한바이트일경우를의미 - 가장융통성이적지만가변형식이나스트림형식보다더나은성능을제공 - 고정레코드형식 : INFILE 데이터파일이름 fix_n ex) 모든길이를바이트로해석한다. 데이터파일은 3 개의레코드로이루어져있다. 빈칸은공백을의미한다. 컨트롤파일 : load data infile 'example.dat' "fix 16" 16byte임을명시 into table example fields terminated by ',' optionally enclosed by '"' (name,message) 데이터파일 : lee, hi goodus goodus, hi lee,goodus lock 가변레코드형식 (variable record format) - 각레코드의길이가데이터파일의각레코드시작부분에포함될경우 - 고정레코드형식보다더나은융통성을제공, 스트림레코드형식보다더나은성능상 이점을제공 - 가변레코드형식 : INFILE 데이터파일이름 var n - n 이지정되지않으면기본값 5 로가정함 - n 을 40 보다큰수로지정하면오류발생 ex) 컨트롤파일 : load data infile goodus.dat var 3 into table test 3 개의필드로구성되어있음명시 - 6 -
데이터파일 : fields terminated by, optionally enclosed by " (col1 char(5),col2 char(7)) 003hi, 011my name is 007goodus 스트림레코드형식 (Stream record format) - 레코드의크기가지정되지않은경우 - 융통성은가장뛰어나지만성능은떨어짐 - 스트림레코드형식 : INFILE 데이터파일이름 문자열 terminator_string - terminator_string 은 char_string 또는 X hex_string 중하나로지정됨 (char_string : 로둘러싸인문자열의미 Hexadecimal_string : 16 짂수형식의바이트문자열의미 ) ex) 를기준으로레코드를읽어들이는예제컨트롤파일 : load data infile goodus.dat "str \n into table example fields terminated by, optionally enclosed by " (col1 char(5), col2 char(7)) 데이터파일 : hello,goodus, hello,di 기술팀, 2.3. 로그파일 (Log File) 로그파일은데이터로드상태정보, 로드된행의수, 로드짂행중데이터에러때문에거부된행의수, 버려짂행의수등자세한테이블로드정보가포함된다. 즉, 로그파일은읽어들인레코드의수, 데이터로드시갂, 입력되지않은레코드의수와같은정보를포함하고있다. 기본적으로컨트럴파일과동일한이름으로생성된다. 2.4. 배드파일 (Bad File) BAD 파일은 SQL*Loader 또는오라클데이터베이스가거부한레코드를포함한다. 이파일의포맷은물롞원래의데이터파일과동일하게되며, 이러한 Bad File 은이파일내의잘못된부분을수정한뒤에다시데이터파일로사용할수있으며필요할경우에만생성이된다. - 문법구조 : 컨트럴파일내에서 badfile 파일명.bad - 배드파일에기록되는경우 : 입력형식이부적합할경우 (ex : 구분자누락, 필드의최대길이초과 ) : 제약조건을위배하였을때 (ex : 고유하지않은키값, 필수필드가 null, oracle 데이터유형에부적합한데이터가포함 ) - 7 -
2.5. 디스카드파일 (Discard file) - SOL*Loader 가실행되는동안폐기파일이라는파일을생성할수있다. - 이파일은필요한경우에만생성된다. - 컨트롟파일에명시한 WHEN 젃의조건에맞지않는데이터를저장하는파일 - 배드파일처럼입력형식으로저장되므로오류를수정하여재사용할수있다. - 문법구조 : 컨트럴파일내에서 discardfile 파일명.dsc 명령행에서 discard( 파일명 ) 3. SQL LOADER 로데이터로드방법 SQL*Loader 를사용하여 LOAD 하는방식에는기본경로 (conventional), 직접경로 (direct path), 병렬직접경로 (paraller direct path) 로드방식이있다. 3.1. 기본경로로드방법 (Conventional path load) - 삽입할행배열을생성하고 SGA 메모리영역에저장한다음 SQL INSER 문을사용하여데이터를로드한다. - 기본경로로드동안에는필드사양을기준으로입력레코드의구문이분석되고레코드배열은제어파일에서지정한테이블에생성및삽입된다. - 필드사양을준수하지않는레코드는거부되고선택기준에맞지않는이러한레코드는폐기된다. - 8 -
- 기본경로로드를사용하여데이터를클러스터화된테이블및클러스터화되지않는테이블 모두에로드할수있다. - 로드되는테이블에대한로그기록속성은리두생성을제어한다. 3.2. 직접경로로드방법 (Direct patch load) - 직접경로로드는데이터블록을메모리에생성하고이블록을로드되는테이블에대해할당된확장영역에직접저장한다. - 데이터베이스가 ARCHIVELOG MODE 가아니면온라인리두로그항목이생성되지않는다 - 필드사양을사용하여젂체데이터블록을생성하고이블록을데이터파일에직접쓴다. - 확장영역관리및고수위조정을위해데이터베이스버퍼캐시를통과하고 SGA 를액세스한다. - 직접경로로드는대게기본경로로드보다빠르지만모든상황에서사용할수는없다. - 다음의표에서기본경로로드와직접경로로드를비교하여각로드방식을사용할수있다. 일반경로 직접경로 Commit 문을사용하여영구적으로변경항상리두항목생성모든제약조건적용클러스터화된테이블로로드할수있음 INSERT 트리거실행다른사용자가테이블을변경할수있음 DATA SAVE 사용특정조건에서만리두생성기본키, 유일키, not null 제약만적용클러스터화된테이블로로드불가능로더중에 INSERT 트리거사용불가능다른사용자가로드되는테이블사용할수없음 3.3. 병렬직접경로로드방법 (Paraller direct path load) - PARALLEL DIRECT LOAD 는 LOAD 할때여러개의 LOAD SESSION 이한 TABLE 로데이터를 LOAD 하여속도가더증가한다. - 각각의 SESSION 은서로다른 DATA log 를사용해야한다. 따라서 CONTROL log 파일이름도달라야한다. - PARALLEL 옵션을사용할때 SQL LOADER 는 TEMPORARY SEGMENT 에 DATA 를 LOAD 하여 LOAD 가끝나는시점에서 TEMPORARY SEGMENT 를하나로묶고이것을 TABLE SEGMENT 로추가하는것이다. - LOAD 시에 INDEX 가걸려있다면 DISABLE 한후작업이끝나면 ENABLE 해야한다. - 컨트롟파일명시젃의 APPEND mode 에서만가능하다. 즉, INSERT, REPLACE, TRUNCATE mode 에서는지원되지않는다. - 9 -
4. SQL LOADER 실습 4.1. 기본경로로드방식 - 컨트롤파일에데이터까지함게포함되어있는경우 SQL> create table test -> 로드할테이블생성 (id number(38), name varchar(20), phone varchar(20)); Table created. [starlee:ora10:/oracle/sqlldr]vi test.ctl -> 컨트롤파일작성 load data infile * append into table test fields terminated by ',' (id,name,phone) begindata 1, 이규열,011-9401-2222 2, 장동건,011-9823-1231 3, 원빈,018-555-4744 4, 정우성,010-9876-1234 5, 전지현,019-325-9876 6, 신민아,010-2345-9123 [starlee:ora10:/oracle/sqlldr]ls -alrt 합계 12 -rw-r--r-- 1 oracle dba 231 9 월 2 15:56 test.ctl drwxr-xr-x 9 oracle dba 4096 9 월 2 15:56.. drwxr-xr-x 2 oracle dba 4096 9 월 2 15:56. [starlee:ora10:/oracle/sqlldr]sqlldr userid=lee/lee control=test.ctl -> sqlloader 실행 SQL*Loader: Release 10.2.0.4.0 - Production on Wed Sep 2 15:57:09 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 7 [starlee:ora10:/oracle/sqlldr] test.bad test.ctl test.log -> badfile과 logfile은지정안해도 controlfile 명과같게생성됨 SQL> select * from test; -> 로드된내용확인 ID NAME PHONE ---------- -------------------- -------------------- 1 이규열 011-9401-2222 2 장동건 011-9823-1231 3 원빈 018-555-4744 4 정우성 010-9876-1234 5 전지현 019-325-9876 6 신민아 010-2345-9123 6 rows selected. - log file 을열어보면 Path used : Conventional 인것을확인할수있다. - 10 -
4.2. 직접경로로드방식 - 컨트롤파일과데이터파일이분리되어있는경우 [starlee:ora10:/oracle/sqlldr]vi goodus.ctl -> 컨트롤파일작성 load data infile * /oracle/sqlldr/goodus.dat append into table test append mode적용 fields terminated by ',' (id,name,phone) [starlee:ora10:/oracle/sqlldr]vi goodus.dat -> 데이터파일작성 10, 호나우딩요, 브라질 phone 20, 리오넬메시, 아르헨티나 phone 30, 사비올라, 아르헨티나 phone 40, 아드리아누, 브라질 phone 50, 크리스티아누호날두, 포르투칼 phone [starlee:ora10:/oracle/sqlldr]sqlldr userid=lee/lee control=goodus.ctl direct=true ->sqlldr 실행 SQL*Loader: Release 10.2.0.4.0 - Production on Wed Sep 2 19:54:38 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Load completed - logical record count 5. SQL> select * from test; -> 기본경로로드방식다음에로드된것을확인할수있음 ID NAME PHONE ---------- -------------------- -------------------- 1 이규열 011-9401-2222 2 장동건 011-9823-1231 3 원빈 018-555-4744 4 정우성 010-9876-1234 5 전지현 019-325-9876 6 신민아 010-2345-9123 10 호나우딩요 브라질phone 20 리오넬메시 아르헨티나phone 30 사비올라 아르헨티나phone 40 아드리아누 브라질phone 50 크리스티아누호날두포르투칼phone 11 rows selected. - log file 을열어보면 Path used : Direct 인것을확인할수있다. - 11 -
5. ORANGE 를이용한 Loader 쉽게따라하기 DBA 나개발자들이흔히사용하는 TOOL 에는 ORANGE 나 TOAD 같은것들이있다. 이같은 TOOL 을사용하면보다더쉽게 LOADER 를다루는것이가능하다. GUI 홖경의 TOOL 을이용함으로써 LOADER 의기능을자유자재로홗용가능하고쉽게접근이가능하며결과값을엑셀파일이나텍스트파일등다양한포맷으로변홖해필터등을이용하여쉽게자싞이원하는값을추출할수있다. 여기에서는국산 TOOL 인 ORANGE 를이용하여쉽게 loader 하는법에대해서알아보겠다. 1. ORANGE 시작후 Loader 시작 - Loader 할유저로접속한다음 TOOL -> Loader 를클릭한다. 클릭하면다시한번 Logging 창 이나오는데다시한번접속을해준다. - 12 -
2. Load 할 datafile 작성 - 이번예제에서는업무에서흔히데이터입력용으로작성하는엑셀의 csv 파일 ( 쉼표로분리 ) 의 예제를들어보겠다.( 해당유저는 Loader 젂에미리테이블정의가되어져있어야한다.) - 테이블생성 SQL> create table fifa_ranking( 2 순위 varchar2(100), 3 국가 varchar2(100), 4 포인트 varchar2(100), 5 순위변동 varchar2(100)); Table created. - Data file 작성 3. Loader 의화면구성 - TABLE 그룹에서스키마나 loader 할테이블을지정한다. Refresh 누르면테이블을갱싞한다. 테이블을선택하면옆의화면서테이블컬럼정보가표시된다. - Data file 그룹에서는 loader 할데이터파일을선택하고파일포맷에컬럼구분기호가있으면 Symbol Separated, 일정한폭으로되어있다면 Fixed Width 를선택한다. Symbol Separated 를선택하면 Separator 그룹에서 Tab, Comma, User( 입력창에직접입력 ) 선택창이홗성화된다. - Data file 을선택하면 Loader 화면아래에파일포맷이제대로입력되었나확인할수있다. 선택한 Datafile 의첫번째행에컬럼명이표시되어있으면 1st row is col name, 첫번째컬럼이행번호면 1 st col is row# 을체크하면된다. - Pre-process 그룹에서 Truncate before loading 을체크하면 loader 되기젂테이블의데이터를삭제하고 loader 한다. - 실습에사용된 data file 은쉼표로분리되어저장되는 csv 로했기때문에나머지사항은선택안해도무관하다. - 13 -
4. 로더실행화면 - 로더메뉴중실행아이콘 ( 사람달리는아이콘 ) 을클릭하면실제로더가수행되며다음과같 이총 rows 와실제 loader 된 rows 를보여준다 - 14 -
5. 옵션버튺사용 (General) - 옵션버튺을클릭하면 Loader Tool Options 가열릮다. General 탭에서 Data/Time Formats 그룹에서포맷을입력해야성공적으로 loader 할수있다. - String Formats 그룹은데이터파일에사용된 string data 포맷을지정한다. 6. 옵션버튺사용 (Load) - Load 탭을이용해서데이터파일의일부분만 loader 할수있다. - Lines to Load 에서는 loader 할행수를입력 Commit Rows 에서는몇행마다 commit 을실행할것인지를지정한다. - Error 그룹에서는몇번의 error 가발생했을때작업을중지할것인지설정하며 badfile 을지정할수있다. - 15 -
7. Loader 된데이터확인 - 정상적으로데이터가 loader 된것을확인할수있다. - 16 -
6. 부록 6.1. SQL LOADER 성능향상기법 - 제어파일과데이터파일을분리하면여러로드세션에서제어파일을다시사용할수있다. - 예상되는데이터볼륨을기반으로공간을미리할당하면로드시확장영역의동적할당을방지하여로드속도가향상된다. - 테이블에인덱스가걸려있을경우, 먼저인덱스를 drop 시키고 SQL Loader 로데이터를올린후인덱스를생성한다. - 데이터의양이많을경우 Redo log size 를충분히크게설정한다. Redo log size 가작을경우 Redo log switch 할때마다 LGWR 에서 timeout 이발생하여 SQL Loader 에서 hang 현상이발생할수있다. - 데이터를로딩하기전데이터를삭제후로딩하기위해서는 REPLACE 옵션대신 TUNCATE 옵션을사용한다. REPLACE 옵션은 redo log 및 rollback 데이터가발생하게되지만 TRUNCATE 옵션은그렇지않다. - Commit 문의실행을자주하지않을경우 SQL Loader 가보다더빨리실행된다 - DIRECT PATH 옵션을사용면데이터가 SQL engine 을거치지않고바로데이터파일에 write 됨으로속도가빨라진다. - PARALLERL 옵션을사용한다. 다중 SQL Loader 세션을사용할경우한개의테이블도 parallel 하게 loading 될수있다. 6.2. 키워드설명 키워드설명 USERID CONTROL LOG BAD DATA SQL LOADER 로데이터로드할오라클사용자및패스워드컨트롤파일이름, SQL*Loader을수행하기위해서는항상지정해주어야함로그파일이름을지정거부된레코드모두를저장하는배드 (Bad) 파일이름을지정입력데이터파일이름을지정 DISCARD 로드시선택되지않은레코드가저장되는디스카드파일 ( 선택사항 ) DISCARDMAX discard의최대허용개수지정 ( 기본값 : all ) SKIP 스킵할논리적레코드수 ( 기본값 : 0 ) LOAD 로드할논리적레코드수 ( 기본값 : all ) ERRORS 허용하는배드레코드의최대수지정 ( 기본값 : 50 ) - 17 -
ROWS 일반경로나직접경로에의해저장되는행수 ( 기본값 : 일반경로 -> 64, 직접경로 : all) BINDSIZE 일반경로에의한 BIND ARRAY 크기 ( 기본값 : 256000 바이트 ) DIRECT PARFILE PARALLEL FILE TRUE 로설정되면 SQL*Loader 는직접경로 (Direct Path) 사용생략할경우는기본값인일반경로 (Conevntional Path) 사용 추가적으로파라미터파일을지정 직접 (DIRECT) 경로방법에서만사용하며, 다중병렬직접경로에의해수행되도록지정 병렬직접경로에의한로드의경우임시세그먼트가생성될파일을지정 6.3. 참조문헌 - http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html - http://kr.forums.oracle.com/forums/thread.jspa?messageid=1593858? - ORACLE Database Administration Fundamentals - 18 -