Loads and unloads using external tables

You can unload data from a user table into an external table and load data from an external table into a user table by using the text-delimited format. Unloading for the fixed-length format is not supported.

Note: If an empty datafile is passed to nzload, it errors out for Netezza Performance Server versions lower than 11.2.1.3.
When you load data into a system from an external table, you can structure the loading operation to manipulate the data by using casts or joins, by dropping columns, and by using other features.

The power of external tables is that the entire extract, transform, and load (ETL) process is mapped to SQL. You can initiate an SQL-based ETL process from any SQL client that can communicate with the Netezza Performance Server system, which reduces or avoids the requirement of specialized ETL tools.

To load an external data file into the Netezza Performance Server system as an external table, you can use either of the following clauses:
  • A FROM clause of a SELECT SQL statement, as with any normal table
  • A WHERE clause of an UPDATE or DELETE SQL statement
To unload a user table into an external data file, use the table as the target table in any of the following SQL statements:
  • INSERT SQL statement
  • SELECT INTO SQL statement
  • CREATE TABLE AS SELECT SQL statement

References to columns in the external table can be complex SQL expressions that are used for transforming external data during a load or unload process.

Before loading your data, ensure that it is properly formatted and that the systems are available.

Bulk load using external tables or nzload

nzload is a bulk data load utility available in Netezza Performance Server. This utility supports loading data from a flat file to a Netezza Performance Server database table.

Examples

In the following examples, a user table is unloaded into an external table, and an external table is loaded into a user table, using the text-delimited format:
  • The following command creates a text-format external table: 
    CREATE EXTERNAL TABLE extemp SAMEAS emp USING (DATAOBJECT 
('/tmp/emp.dat'));
  • The following command unloads data in user table EMP into the external table EXTEMP: 
    INSERT INTO extemp SELECT * FROM emp;
  • The following command loads data into user table EMP from external table EXTEMP:
    TRUNCATE TABLE emp;
INSERT INTO emp SELECT * FROM extemp;