[Oracle] External Table - 외부 CSV 파일을 테이블로 만들기

External Table

외부 테이블이라고 불리우는 External Table은 Oracle의 여러 Object 중 하나로서,

Table에 쌓이는 데이터를 Block에 저장하는 것이 아닌, Local Host의 저장되어있는 데이터베이스 외부 파일에서 데이터를 불러와 Table 형식으로 보여주는 것이다.

 

형식화 된 대용량 CSV파일 등을 힘들게 Insert 하지 않고도 테이블 형식으로 만들어 사용 할 수 있어 편리한 External Table 생성에 대해 한번 알아보도록 하자.

 

Create External Table

External Table을 생성하는 방법은 아래와 같다.

create table TB_CSV_TEST
(
  col_1 NUMBER(10),
  col_2 VARCHAR2(100),
  col_3 VARCHAR2(100)
)
organization external
(
  type ORACLE_LOADER
  default directory [file directory name]
  access parameters 
  (
    records delimited by newline
       skip 1
       fields terminated by ','
       optionally enclosed by '"'
       lrtrim
       missing field values are null (
       col_1, col_2, col_3
    )
  )
  location ([file directory name]:'[csv_file_name.csv]')
)
reject limit 0;

 

위 create table문에 대해서 설명하자면 아래와 같다.

 

Directory

먼저 외부 csv파일을 읽어야하므로 csv파일의 위치를 Oracle Database에서 읽을 수 있어야 한다.

 

Local Host의 Directory를 마운트 해보도록 하자.

create directory [Directory Name] as '[경로]';

위와 같이 DATA_DIR이라는 명칭으로 /data 디렉토리를 마운트 하였다.

생성된 directory 오브젝트를 확인해보자.

반응형

select * from DBA_DIRECTORIES;

잘 생성 된 것을 확인하였다.

 

Query 설명

create table 밑의 external table을 구성하는 옵션에 대해 설명하도록 하겠다.

• type ORACLE_LOADER -- ORACLE_LOADER라는 모듈을 통해 오라클로 csv파일을 읽어옴
• default directory [file directory name] -- 위에서 생성한 csv파일이 위치할 논리적 디렉토리 명

 

그 밑으로는 파라미터를 정하는 값들이 있다.

이 파라미터는 꼭 하나씩 줄바꿈을 하여 적용하여야 한다.

• records delimited by newline -- csv파일의 한 행의 끝을 만나면 새로운 행을 쓴다.
• skip 1 -- 1행을 스킵한다 (csv파일의 맨위 컬럼명 같은 값을 스킵)
• fields terminated by ',' -- 컬럼의 구분은 , 로 한다.
• optionally enclosed by '"' -- 한정자는 " 로 한다. (컬럼의 묶음)
• lrtrim -- 양쪽 공백 제거
• missing field values are null (col_1, col_2, col_3)  -- 값이 없는 컬럼에는 null을 넣는다.

 

마지막으로 csv파일의 경로를 지정해준다.

• location ([file directory name]:'[csv_file_name.csv]') : 논리적 directory경로 : csv 파일 명

쿼리를 잘 정리하면 아래와 같다.

 

주의사항으로는 꼭 파라미터를 줄바꿈으로 넣어야하며,

csv파일의 한글의 경우 파일의 인코딩을 oracle에서 읽을 수있게 잘 맞춰줘야한다.

또한 DATE 타입은 사용하지 않도록 한다.