비개발자가 데이터를 쉽게 준비하기위해 기존 데이터를 csv로 export해서 전달했고

이제 추가된 데이터와 함께 다시 csv를 import 시켜야한다.

SQL Loader를 이용하면 되었고 간단하게 사용법을 정리한다.

 

1. 사용방법

LOAD DATA

INFILE '파일경로'

APPEND

INTO TABLE 테이블명

fields terminated by ","

(
COL1
COL2
....
)

간단하게 구성해보았다.

1. INFILE에 적은 파일을 읽어

2. 기존 데이터는 두고 새로운 행을 추가하여

2. 타겟 테이블에

3. 각각 필드 구분자는 ,로 인식해

4. (co1, co2...) 컬럼에 각각 넣는다

 

 

2. 옵션

LOAD DATA
CHARACTERSET UTF8

INFILE '파일경로'
BADFILE '파일경로'
LOGFILE '파일경로'
DISCARDFILE '파일경로'

--(REPLACE, APPEND, INSERT)
TRUNCATE

INTO TABLE 테이블명

WHEN (5) = 'test'

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

TRAILING NULLCOLS

(
COL1,
COL2,
COL3,
....
)

몇가지 옵션을 더 추가했다

 

인코딩 설정을 하고 

 

 BADFILE은 데이터가 잘못되었을때 그 행을 기록하는 파일

 LOGFILE은 해당 Load 작업 로그파일

 DISCARDFILE은 WHEN절에 맞지 않아 로드 못한 행을 기록하는 파일이다

모두 설정을 안해주면 

컨트롤 파일과 같은 경로, 이름으로 확장자만 다르게 저장된다.

 

 

INSERT : 비어있는 테이블에 데이터를 로드한다. (테이블이 비어있지 않으면 에러)

APPEND : 새로 데이터를 추가한다

REPLACE : 기존 데이터가 있으면 덮어쓴다

TRUNCATE : 해당 테이블을 truncate 한 후 데이터를 로드한다

 

 

WHEN (5) = 'test'는 5번째 필드 데이터가 test인 경우만 로드한다는 뜻으로 조건절이다.

여기서 조건이 맞지 않은 행은 DISCARDFILE에 기록된다.

 

 

FILEDS TERMINATED BY ',' 

필드 구분자를 ,로 설정하고

OPTIONALLT ENCLOSED BY '"'

" (큰따옴표) 로 감싸진 필드를 인식한다

위 2개 설정을하면 데이터파일은 다음과같이 준비해야한다

"col1","col2","col3".......
"col1-1","col2-1","col3-1".......

 

 

TRAILING NULLCOLS : 실제 필요한 필드수보다 데이터파일의 데이터가 적을경우 나머지는 Null로 등록하는 설정

 

 

더 자세한 내용, 옵션은 아래 링크 참조

https://docs.oracle.com/cd/B19306_01/server.102/b14215/part_ldr.htm

 

 

3. 실행

위와 같이 컨트롤파일을 작성 후 sqlldr 명령어로 다음과 같이 실행하면 된다.

sqlldr userid=loader/loader control='/파일경로/load.ctl'

 

 

Load 작업을 하며 삽질했던 내용을 공유하며 글을 마무리한다.

1. 마지막 필드는 반드시 값이 있어야한다 (행의 끝이 구분자로 끝나면 안됨)
2. 윈도우에서 csv파일을 만들고 리눅스 환경에서 load를 진행하였는데 자꾸만 invalid number가 나오며 로드가 안됐는데 알고보니 윈도우 개행문자가 각 행 끝에 포함되어 있어서 발생한 문자였다. 윈도우 개행문자 삭제 후 해결
윈도우 개행문자는 다음 링크 참조
https://sleepyeyes.tistory.com/83?category=799682

 

'데이터베이스 > Oracle DB' 카테고리의 다른 글

[Oracle DB] csv import (SQL Loader)  (0) 2021.10.01
[Oracle DB] SQL Plus csv export (spool)  (0) 2021.09.30

oracle db 데이터를 엑셀파일로 만들어야하는 상황이 생겼는데 tool을 이용하지도 못하는 상황이라 이리저리 찾아보는 도중 sqlplus에서 실행한 쿼리 결과를 파일로 만들어주는 spool 명령어를 알게 되었다.

 

1. 사용방법

set 옵션 값
spool 파일명(경로까지)
내보낼 sql문(select문)
spool off

 

예시

--컬럼 구분자 
set colsep ,

-- pagesize의 default는 14이며 그대로 하면 14줄마다 1줄씩 공백이 생기므로 
-- 그런 현상을 방지하기 위해 크게 지정
set pagesize 50000	

-- linesize도 데이터 길이만큼 지정하여 
-- 아래로 구분되지 않도록 크게 지정
set linesize 10000

-- linesize 이하로 행이 짧을 때 나머지부분을 자른다. 줄 끝 공백제거
set trimspool on

-- 컬럼명 헤더 출력 여부
set heading on

-- 결과 파일 경로
spool /home/oracle/test.csv

SELECT *
FROM TB_TABLE;

spool off

 

 

이외에도 여러 옵션이 있다. 아래에서 참조

https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2683501

 

 

편하게 .sql 스크립트 파일을 작성하고

sqlplus에 로그인해서 해당 스크립트 파일을 아래와 같이 불러주면 된다.

@test.sql

 

'데이터베이스 > Oracle DB' 카테고리의 다른 글

[Oracle DB] csv import (SQL Loader)  (0) 2021.10.01
[Oracle DB] SQL Plus csv export (spool)  (0) 2021.09.30

+ Recent posts