Oracle 외부 테이블(External Table) 완벽 가이드: 외부 파일 데이터를 데이터베이스처럼 활용하기

외부 테이블(External Table)은 데이터베이스 외부에 저장된 플랫 파일(flat file)의 데이터를 읽어 들여, 마치 데이터베이스 내부의 테이블인 것처럼 Oracle®에서 조회할 수 있도록 해주는 강력한 기능입니다.

개요

Oracle은 SQL*Loader가 지원하는 모든 파일 형식을 파싱할 수 있습니다. 외부 테이블에는 DML(데이터 조작 언어)을 직접 실행할 수는 없지만, 조회(SELECT), 조인(JOIN), 정렬(SORT)과 같은 작업에는 DML을 활용할 수 있습니다. 또한 외부 테이블을 대상으로 뷰(View)나 동의어(Synonym)를 생성하는 것도 가능합니다.

외부 테이블 기능을 제대로 활용하려면 ORACLE_LOADER 액세스 드라이버를 사용해야 하며, 데이터 파일은 텍스트 형식이어야 합니다. 아울러 외부 테이블을 생성하고 이에 대한 쿼리를 수행하려면 SQL(Structured Query Language)에 대한 기본적인 이해가 필요합니다.

Oracle 데이터베이스에서 외부 테이블 만들기

Oracle Loader를 사용해 외부 테이블을 생성하려면 다음 단계를 순서대로 진행하세요.

1단계: 디렉터리 생성

다음 코드를 사용하여 사용자가 제공한 CSV(쉼표로 구분된 값) 또는 텍스트 파일이 위치한 경로를 가리키는 디렉터리 객체를 생성합니다.

create directory vk_dir as '/opt/oracle/app/admin/je1prd/mods/151214_CHG0004529';

2단계: 외부 테이블 생성

다음 SQL 문을 사용해 외부 테이블을 생성합니다:

create table kumarv5.VK_4529_ext
(
  IBMCU   NCHAR(12),
  IBLITM   NCHAR(25),
  IBANPL   NUMBER
)
organization external
(
   type oracle_loader
   default directory vk_dir
   access parameters
   (
     records delimited by newline
     logfile vk_dir:'VK_4529_ext.log'
     badfile vk_dir:'VK_4529_ext.bad'
     discardfile vk_dir:'VK_4529_ext.dcf'
     skip 1
     fields
     terminated by '~'
     missing field values are null
     reject rows with all null fields
     (
       IBMCU,
       IBLITM,
       IBANPL
     )
   )
   location
   (
     'data14.csv'
   )
)
reject limit unlimited;

3단계: 로컬 테이블 생성

외부 테이블에서는 DML 작업을 수행할 수 없으므로, 다음 코드를 사용해 외부 테이블을 기반으로 하는 로컬 테이블을 생성합니다:

--create
create table kumarv5.VK_4529_int
(
  IBMCU   NCHAR(12),
  IBLITM   NCHAR(25),
  IBANPL   NUMBER
)
tablespace proddtat;

4단계: 외부 테이블에서 로컬 테이블로 데이터 복사

다음 코드를 사용해 외부 테이블의 데이터를 로컬 테이블로 복사합니다:

--insert
Insert into kumarv5.VK_4529_int   select * from kumarv5.VK_4529_ext;
commit;
select count(*) from kumarv5.VK_4529_int;

5단계: 메인 운영 테이블 업데이트

외부 테이블을 기반으로 로컬 테이블을 생성했다면, 이제 다음 코드를 사용해 로컬 테이블의 데이터로 메인 운영(production) 테이블을 업데이트할 수 있습니다:

update PRODDTA.F43090 a
    set a.PCMCU='         1AM'
    WHERE (a. IBMCU, a. IBLITM, a. IBANPL) in
       (select b. IBMCU, b. IBLITM, b. IBANPL from kumarv5.VK_4529_int b );

6단계: 외부 테이블에서 일반 테이블로 데이터 적재

다음 이미지는 외부 테이블에서 일반 테이블로 데이터를 적재하는 과정을 보여줍니다:

Oracle 외부 테이블(External Table) 완벽 가이드: 외부 파일 데이터를 데이터베이스처럼 활용하기

이미지 출처: https://dba.fyicenter.com/faq/oracle/Load-Data-from-External-Tables.html

결론

외부 테이블을 활용하면 외부 소스에 있는 데이터를 마치 데이터베이스 내부의 테이블처럼 자유롭게 접근할 수 있습니다. 사용자가 스프레드시트나 CSV 형식으로 데이터를 제공하고, 해당 엑셀 시트 내용을 바탕으로 데이터베이스 테이블을 갱신해야 하는 상황이라면 외부 테이블이 매우 유용한 해결책이 됩니다.

궁금한 점이나 의견이 있다면 피드백 탭을 통해 남겨 주세요. 지금 바로 채팅을 시작할 수도 있습니다.

데이터베이스에 대해 더 알아보세요.