개요

External 테이블은 DB 외부에 저장된 데이터 파일을 조작하기 위한 접근 방법의 하나로 읽기 전용 테이블입니다. 실제 데이터는 DB 외부에 존재하지만 External 테이블에 대한 메타데이터는 DB 내부에 존재하는 일종의 가상 테이블입니다.

External 테이블의 특징은 다음과 같습니다.

• 읽기 전용 테이블

• DML 작업이 불가능

• 인덱스 생성이 불가능

다음은 External 테이블의 장점과 단점에 대한 설명입니다.

방법

고정된 레코드 형태

고정된 레코드 형태 External 테이블의 생성 예시

1. 데이터 파일과 디렉터리를 생성합니다.

mkdir /home/tibero/samdata

2. DEPT 데이터 파일을 생성합니다. (DEPT.dat)

100 Administration   2000 2700
200 Marketing        2010 2800
300 Purchasing       1140 2700
400 Human Resources  2030 3400
500 Shipping         1210 2500
600 IT               1030 2400
700 Public Relations 2040 3700
800 Sales            1450 3500

3. tbsql에 접속합니다.

tbsql sys/tibero

4. 디렉터리 객체명을 생성합니다. (CREATE ANY DIRECTORY 권한이 필요함)

SQL> CREATE DIRECTORY SAMDATA AS '/home/tibero/samdata';

Directory 'SAMDATA' created.

5. 권한을 부여합니다.

SQL> GRANT READ,WRITE ON DIRECTORY SAMDATA TO TIBERO;

Granted.

6. 사용자 계정에 접속합니다.

SQL> conn tibero/tmax

Connected to Tibero.

7. External 테이블을 생성합니다.

SQL> CREATE TABLE DEPT_EXT_TBL
     (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     ORGANIZATION EXTERNAL -- External 테이블 생성 명시
     (
       DEFAULT DIRECTORY SAMDATA -- 디렉터리 객체명 지정
       ACCESS PARAMETERS
       (
         LOAD DATA INTO TABLE DEPT_EXT_TBL
         (
           DEPTNO POSITION(1:3), -- 데이터 파일내 위치 지정
           DEPTNAME POSITION(5:21),
           MGRNO POSITION(22:25),
           LOCNO POSITION(26:29)
         )
       )
     LOCATION ('DEPT.dat') -- 데이터 파일명
     );

Table 'DEPT_EXT_TBL' created.

8. 데이터를 조회합니다.

SQL> select * from DEPT_EXT_TBL;

    DEPTNO DEPTNAME                            MGRNO      LOCNO
---------- ------------------------------ ---------- ----------
       100 Administration                       2000        270
       200 Marketing                            2010        280
       300 Purchasing                           1140        270
       400 Human Resources                      2030        340
       500 Shipping                             1210        250
       600 IT                                   1030        240
       700 Public Relations                     2040        370
       800 Sales                                1450        350

8 rows selected.

분리된 레코드 형태

분리된 레코드 형태 External 테이블의 생성 예시

1. 데이터 파일 디렉터리를 생성합니다.

mkdir /home/tibero/samdata

2. DEPT 데이터 파일을 생성합니다. (DEPT1.dat)

"100","Administration","2000","2700"
"200","Marketing","2010","2800"
"300","Purchasing","1140","2700"
"400","Human Resources","2030","3400"
"500","Shipping","1210","2500"
"600","IT","1030","2400"
"700","Public Relations","2040","3700"
"800","Sales","1450","3500"
"3800","Payroll","2160","2700"

3. tbsql에 접속합니다.

tbsql sys/tibero

4. 디렉터리 객체명을 생성합니다. (CREATE ANY DIRECTORY 권한이 필요함)

SQL> CREATE DIRECTORY SAMDATA AS '/home/tibero/samdata';

Directory 'SAMDATA' created.

5. 권한을 부여합니다.

SQL> GRANT READ,WRITE ON DIRECTORY SAMDATA TO TIBERO;

Granted.

6. 사용자 계정에 접속합니다.

SQL> conn tibero/tmax

Connected to Tibero.

7. External 테이블을 생성합니다.

SQL> CREATE TABLE DEPT1_EXT_TBL
     (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     ORGANIZATION EXTERNAL -- External 테이블 생성 명시
     (
       DEFAULT DIRECTORY SAMDATA -- 디렉토리 객체명 지정
       ACCESS PARAMETERS
       (
         LOAD DATA INTO TABLE DEPT1_EXT_TBL
         FIELDS TERMINATED BY ',' -- 필드 구분자 지정
         OPTIONALLY ENCLOSED BY '"' -- 필드를 감싸고 있는 문자 지정
         ESCAPED BY '\\' -- 특수문자 사용을 위한 ESCAPE 문자 지정
         LINES TERMINATED BY '\n' -- 레코드 종료 문자 지정
         IGNORE 0 LINES -- 제외할 라인 지정 (5면 1~5행 제외)
           (
             DEPTNO,
             DEPTNAME,
             MGRNO,
             LOCNO
           )
         )
     LOCATION ('DEPT1.dat')
     );
     
Table 'DEPT1_EXT_TBL' created.

8. 데이터를 조회합니다.

SQL> select * from DEPT1_EXT_TBL;

    DEPTNO DEPTNAME                            MGRNO      LOCNO
---------- ------------------------------ ---------- ----------
       100 Administration                       2000       2700
       200 Marketing                            2010       2800
       300 Purchasing                           1140       2700
       400 Human Resources                      2030       3400
       500 Shipping                             1210       2500
       600 IT                                   1030       2400
       700 Public Relations                     2040       3700
       800 Sales                                1450       3500
      3800 Payroll                              2160       2700

9 rows selected.