IBM PureData System for Analytics, Version 7.1

Transient external tables

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

Transient external tables have the same capabilities and limitations as normal external tables. A special feature of a TET is that the table schema does not need to be defined when the TET is used to load data into a table or when the TET is created as the target of a SELECT statement.

Syntax

The 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)];

Explicit table schema definition

The table schema of a transient external table can be explicitly defined in a query. When defined this way, the table schema definition is the same as is used when defining a table schema by using CREATE TABLE.

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

The explicit schema definition feature can be used to specify fixed-length formats.

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

The SAMEAS keyword can also be used to specify that the schema of the external table is identical to some other table that currently exists in the database.

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

Implicit table schema definition

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

The external table in this INSERT statement uses the schema of the target table. The columns in the external data file must be in the same order as 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 '|');

Export data by using transient external tables

A transient external table can also be used to export data out of the database. In this case, the schema of the external table is based on the query that is executing. For example:

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 transient external tables

A session connected to IBM® Netezza® using ODBC, JDBC, or OLE DB from a client system can import and export data by using a remote transient external table, which is defined by using the REMOTESOURCE option in the USING clause.

For example, the following SQL statement loads data from a file on a Windows system into a TEMP table on Netezza 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 server where the data is then parsed. This method minimizes CPU usage on the client system during a remote external table load.