Skip to content

오라클 대용량 서열 정보 import(SQL LODER) #
Find similar titles

개요 #

Oracle 데이터베이스에 데이터를 import 하려면 insert 쿼리를 이용하여 데이터를 하나씩 추가하거나, SQLDEVELOPER 또는 SQL GATE 같은 GUI 프로그램을 이용하는 방법이 있다. 하지만 서열 데이터가 너무 길거나 많을 경우 GUI 프로그램을 이용하면 메모리가 충분하지 않아 import가 완료되지 않고 프로그램이 다운된다. 그리고 보안 등의 이유로 접속한 클라이언트(PC)를 데이터 import가 완료되기 전에 전원을 꺼야 하는 경우도 있다. 이럴 경우 Oracle에서 제공하는 SQL LOADER를 이용하면 빠른 속도로 안정적으로 데이터를 업로드 할 수 있다.

실행방법 #

이 문제를 해결하기 위한 포인트 두 가지가 있다. 하나는 데이터가 너무 크거나 많더라도 메모리 에러 등으로 다운되지 않아야 한다. 이 문제는 SQLDEVELOPER 나 SQL GATE 같은 GUI 프로그램 대신 SQL LOADER 같은 CLI 프로그램을 이용하는 것으로 해결 가능하다. 다른 하나는 클라이언트에서 접속이 끊기더라도 세션이 유지 되어야 한다. 이는 위에서 언급한 SQL LOADER를 백그라운드에서 실행 시키면 되는데 리눅스의 경우 nohup 또는 screen과 같은 명령어로 실행 가능하고, 윈도우의 경우 start 명령어로 실행 가능하다. 두 경우 모두 오라클 DBMS가 실행중인 서버 또는 PC 장비의 전원은 유지 되어 있어야 한다.

정리해서 말하자면 오라클 DBMS가 실행중인 서버에 ssh 접속 후 아래 언급할 dat 파일과 ctl 파일을 준비하고 SQL LOADER를 실행하면 된다. 실행이 완료되면 로그 파일이 생성되며, 몇 건의 데이터가 import 되었는지와 에러 발생 여부 등을 알 수 있다.

준비사항 #

SQL LOADER를 이용하기 위해서는 import 할 데이터를 모아 놓은 텍스트 파일과, import 할 동작을 정의한 control(.ctl) 파일이 필요하다.

데이터 파일 #

일반적으로 콤마로 구분된 csv 파일을 이용하지만 경우에 따라 데이터 필드에 콤마가 포함된 경우도 있기 때문에 탭(\t) 으로 데이터를 구분하는 것을 권장한다.

컨트롤 파일 #

컨트롤 파일은 SQL LOADER에서 수행할 동작, 데이터 파일 위치, 데이터가 들어갈 대상 테이블과 대상 필드, 데이터 파일의 필드 구분자 등을 정의한다

load data
infile `c:\test.csv`
append
into table 대상테이블명
fields terminated by `,`
(ID, LOCUS_ID, SPECIES_NM, TYPE, SEQ.......)

SQL LOADER 실행 #

SQL LOADER 실행은 Oracle 계정과 패스워드 SID와 함께 컨트롤 파일의 위치를 파라미터로 전달하여 실행한다.

sqlldr userid=testid/testpawd@SID control=`c:\test.ctl`

SQL LOADER가 빠르지만 그래도 입력 데이터의 양이 많을 경우 시간이 오래 걸릴 수 있기 때문에 위의 명령어를 nohup 과 같은 명령어와 함께 background에서 실행하거나, screen 과 같은 서브 터미널에서 실행하여 터미널을 종료해도 실행이 지속되도록 한다.

로그 #

실행이 완료되면, .log 같은 확장자의 로그 파일이 생성된다. 로그 파일에는 SQL LOADER의 버전, 입력파일, 입력데이터 건수, 테이블 구조 등의 정보가 나타나고, 만약 데이터에 문제가 있어 에러가 발생하는 경우 해당 레코드의 위치(줄번호)가 기록된다. 그리고 최종적으로 성공적으로 import된 데이터의 건수와 데이터 error로 인해 입력되지 않은 데이터의 건수, 조건에 맞지 않거나, null 값이어서 입려되지 않은 데이터의 건수 등이 나타난다. 또한 입력 유형별 데이터의 논리적인 용량도 계산되어 나타나며 마지막으로 해당 프로세스의 실행시간과 종료시간이 출력된다.

SQL*Loader: Release 11.2.0.1.0 - Production on Tue Jun 25 17:11:09 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   /temp/GENBANK_SEQ.ctl
Data File:      /temp/GENBANK_SEQ.txt
  Bad File:     /temp/GENBANK_SEQ.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 2000000000 bytes
Continuation:    none specified
Path used:      Conventional

Table SEQDB.GENBANK_SEQ, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SEQ_ID                              FIRST     *  WHT      CHARACTER            
LOCUS_ID                             NEXT     *  WHT      CHARACTER            
SEQ_DESC                             NEXT     *  WHT      CHARACTER            
ORANISM                              NEXT     *  WHT      CHARACTER            
TYPE                                 NEXT     *  WHT      CHARACTER            
SEQ                                  NEXT *****  WHT      CHARACTER            
    Maximum field length is 408702976

value used for ROWS parameter changed from 5 to 4
Record 208737: Rejected - Error on table SEQDB.GENBANK_SEQ, column SEQ_DESC.
Field in data file exceeds maximum length
Record 208738: Rejected - Error on table SEQDB.GENBANK_SEQ, column SEQ_DESC.
Field in data file exceeds maximum length
.....
Record 2663351: Rejected - Error on table SEQDB.GENBANK_SEQ, column SEQ_DESC.
Field in data file exceeds maximum length
Record 2663352: Rejected - Error on table SEQDB.GENBANK_SEQ, column SEQ_DESC.
Field in data file exceeds maximum length

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table SEQDB.GENBANK_SEQ:
  2663301 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:               1634817080 bytes(4 rows)
Read   buffer bytes:2000000000

Total logical records skipped:          0
Total logical records read:       2663352
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Tue Jun 25 17:11:09 2015
Run ended on Sat Jun 29 23:45:27 2015

Elapsed time was:     102:33:22.32
CPU time was:         90:23:23.93

에러 유형 #

서열이 너무 길 경우 #

Record 208737: Rejected - Error on table TableName.SEQ, column SEQ_DESC.
Field in data file exceeds maximum length

이런 유형의 에러는 너무 긴 서열 데이터를 import 할 때 한 번에 읽고 넣을 수 있는 량을 초과 했을 때 발생하는 에러이다. SQL LOADER가 대용량의 데이터를 업로드 하는 도구이기는 하지만, 하나의 필드에 등록 할 수 있는 최대 길이 제한이 있다. 이럴 경우 readsize와 bindsize 옵션을 줘서 최대 길이를 늘릴 수 있지만, 이경우에도 최대 2G bp 까지만 늘릴 수 있다.

sqlldr userid=testid/testpawd@SID control=`c:\test.ctl` readsize=2000000000 bindsize=2000000000

테이블 스페이스가 꽉찬 경우 #

ORA-01691: unable to extend lob segment TableNAME.SYS_LOB0000290971C00005$$ by 8192 in tablespace DBNAME_DATA

서열 데이터의 경우 char와 같이 일반적인 문자열 타입으로 지정하면 길이 제한으로 전체 서열을 입력 할 수 없으므로 LOB 타입으로 정의하는데 이 경우 Oracle에서는 LOB 타입을 위한 영역을 별도로 생성한다. 또한 테이블 스페이스를 자동으로 확장하는 옵션이 설정되어 있더라도 자동 확장이 동작하지 않기도 한다. 이러한 경우 해당 테이블의 테이블 스페이스를 별도로 분리하여 생성하되 전체 데이터 크기와 디스크의 여유 공간을 고려하여 적절한 크기의 테이블 스페이스 파일을 생성해 주는 조치가 필요하다.

0.0.1_20140628_0