Transient external tables

Transient external tables (TETs) provide a way to define an external table that exists only for the duration of a single query.

TETs have the same capabilities and limitations as normal external tables. A special feature of a TET is that you do not need to define the table schema when you use the TET to load data into a table or when you create the TET as the target of a SELECT statement.

Syntax

Following is the syntax for a TET:

INSERT INTO <table> SELECT <column_list | *>
FROM EXTERNAL 'filename' [(table_schema_definition)]
[USING (external_table_options)];

CREATE EXTERNAL TABLE 'filename' [USING (external_table_options)]
AS select_statement;

SELECT <column_list | *> FROM EXTERNAL 'filename' (table_schema_definition)
[USING (external_table_options)];

For information about the values that you can specify for the external_table_options variable, see External table options.

Explicit table schema definition

You can explicitly define the table schema of a TET in a query. The table schema that you define in a query is the same as the definition that you use when defining a table schema by using the CREATE TABLE command. An example follows:

SELECT x, y, NVL(dt, current_date) AS dt FROM EXTERNAL '/tmp/test.txt' 
( x integer, y numeric(18,4), dt date ) USING (DELIM ',');

You can use the explicit schema definition feature to specify fixed-length formats. An example follows:

SELECT * FROM EXTERNAL '/tmp/fixed.txt' ( x integer, y numeric(18,4), 
dt date ) USING (FORMAT 'fixed' LAYOUT (bytes 4, bytes 20, bytes 10));

You can use the SAMEAS keyword to specify that the schema of the external table is identical to that of some other table that exists in the database. An example follows:

SELECT * FROM EXTERNAL '/tmp/test.txt' SAMEAS test_table 
USING (DELIM ',');

Implicit table schema definition

If you do not explicitly define the TET schema, the schema is determined based on the query that is executing. When you use a TET as a data source for an INSERT statement, the external table uses the schema of the target table.

The external table in the following INSERT statement uses the schema of the target table. The columns in the external data file must be in the same order as in the target table, and every column in the target table must also exist in the external table data file.

INSERT INTO target SELECT * FROM external '/tmp/data.txt' 
USING (DELIM '|');

Unloading data by using TETs

You can use a TET to unload data from a database. In this case, the schema of the external table is based on the query that is executing. An example follows:

CREATE EXTERNAL TABLE '/tmp/export.csv' USING (DELIM ',') AS
SELECT foo.x, bar.y, bar.dt FROM foo, bar WHERE foo.x = bar.x;

Remote TETs

If you establish a connection to the system from a client machine by using ODBC, JDBC, OLE DB, or the nzsql command, you can unload data into a remote TET or load data from a remote TET. When creating the remote TET, specify the REMOTESOURCE option in the USING clause.

For example, the following SQL statement loads data from a file on a Windows system into the TEMP table on the Netezza Performance Server system by using an ODBC connection:

CREATE TEMP TABLE mydata AS SELECT cust_id, upper(cust_name) as name 
from external 'c:\customer\data.csv' (cust_id integer, cust_name 
varchar(100)) USING (DELIM ',' REMOTESOURCE 'ODBC');

Remote external table loads work by sending the contents of a file from the client system to the Netezza Performance Server system where the data is then parsed. This method minimizes CPU usage on the client system.