External table examples

To create an external table, use the CREATE EXTERNAL TABLE command. You can also use the INSERT, DROP, TRUNCATE, and other commands to work with external tables.

The following examples use external tables:

• The following command creates an external table:

  CREATE EXTERNAL TABLE ext_orders(ord_num INT, ord_dt 
  TIMESTAMP)USING(dataobject('/tmp/order.tbl') DELIMITER '|');

• The following command creates an external table that uses column definitions from an existing table:

  CREATE EXTERNAL TABLE demo_ext SAMEAS emp USING (dataobject 
  ('/tmp/demo.out') DELIMITER '|');

• The following command creates an external table, specifying the escape character ('\'):

  CREATE EXTERNAL TABLE extemp SAMEAS emp USING( dataobject 
  ('/tmp/extemp.dat') DELIMITER '|' escapechar  '\');

• The following command unloads data from a database into a file:

  INSERT INTO demo_ext SELECT * FROM emp;

• The following command drops an external table:

  DROP TABLE extemp

  The system removes only the schema information of the external table from the system catalog. The file that was defined for the dataobject option remains unaffected in the file system.

• The following command backs up a table by creating an external table:

  CREATE EXTERNAL TABLE '/path/extfile' USING (FORMAT 'internal' 
  COMPRESS true) AS SELECT * FROM source_table;

• The following command restores from an external table:

  INSERT INTO t_desttbl SELECT * FROM EXTERNAL'/path/extfile' 
  USING(FORMAT 'internal' COMPRESS true);