외부(External) 테이블

외부 테이블은 마치 일반 테이블에 저장된 데이터와 동일하게 조회하는 데이터베이스에 외부에 위치한 데이터 파일입니다. 외부테이블은 Delimeter 나  고정길이 형식으로 된 파일이며, 사용자들은 액세스하는 데이터가 외부테이블인지 일반 테이블인지와 무관하게 사용할 수 있습니다. 

 

       

외부테이블은 사용자가 명시적으로 이름을 주고 생성하여 (카탈로그에 등록)  조회하거나. 쿼리 수행중 임시로 생성 된 후  수행이 종료되면 사라지는 임시성 외부 테이블이 있습니다. 외부 테이블은 일반 파일 시스템 혹은 오브젝트 스토리지에 위치 할 수 있습니다.  외부 테이블을 사용하면 테이블 데이터를  추출하거나 또는 테이블로 외부 데이터 파일을 적재를 빠르게 수행할 수 있습니다. 즉 일반 SQL로 간단히 ETL 작업이 가능합니다.   

 

<데이터 추출 >

• CREATE EXTERNAL TABLE 'order.tbl' USING (DELIMITER '|') AS SELECT * from orders;
• CREATE EXTERNAL TABLE 'export.csv' USING (DELIMITER ',')  AS SELECT foo.x, bar.y, bar.dt FROM foo, bar WHERE foo.x = bar.x;

<데이터 적재 >

• INSERT INTO target SELECT * FROM EXTERNAL 'data.txt' USING (DELIMITER '|');
• INSERT INTO orders
  SELECT * FROM EXTERNAL 'order.tbl'( order_num INT, order_dt TIMESTAMP) USING (DELIMITER '|’);

<데이터 조회>

• SELECT * FROM EXTERNAL 'order.tbl' (order_num INT, order_dt TIMESTAMP) USING (DELIMITER '|');
• SELECT * FROM EXTERNAL 'test.txt' LIKE test_table USING (DELIMITER ',’);
• SELECT x, y FROM EXTERNAL 'test.txt' ( x integer, y decimal(18,4) ) USING (DELIMITER ',');

< Object Storage의 파일로 생성>

• S3 Storage (AWS)
  CREATE EXTERNAL TABLE exttab2(a int) USING(dataobject 'datafile2.dat' s3('s3.amazonaws.com', 'XXX…456', 'bs07…df16', 'my_dev' ) )
•  IBM Cloud Cloud Object Store
  CREATE EXTERNAL TABLE exttab2(a int) USING (dataobject 'datafile2.dat' s3('s3-api.us-geo.objectstorage.softlayer.net', '1a2b…gYY9' 'my_dev' ) )
•  Azure
  CREATE EXTERNAL TABLE exttab1(a int) using (dataobject 'datafile1.dat'  azure('https://my_account.blob.core.windows.net',  'my_account',   'lW+oHjmZecPS++IKgThAHlMUOaFUA5C6Z2RlFmc9JPpK34RO/ZIOywzILxJnzGPHz6d/yDrcQDAwH5wySbOZMQ==',  'my_bucket'  ) )