Netezza External Table and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:4 mins read

A Netezza external table allows you to access the external file as a database table, you can join the external table with other database table to get required information or perform the complex transformations. External table script can be used to access the files that are stores on the host or on client machine. If the files are stored on the client machine, Netezza uses REMOTESOURCE option to access those files.

Netezza External Table

Netezza External Table Syntax

Below are the various syntax types that you can use to create external table in Netezza:

CREATE EXTERNAL TABLE <table_name>
[sameas <db_table_name>] | ([column_name datatype [,...]])
USING (DATAOBJECT (<source_flat_file_name>) DELIMITER <field_delimiter>) [options];

Other possible syntax is:

SELECT * FROM EXTERNAL '/export/home/nz/stud_detls1.csv' ( col datatype,... ) USING ( DELIMITER char );

An external table has a definition or table schema, but the actual data exists outside of the Netezza appliance database.

Related Reading:

Once external table is created then you can use INSERT INTO statements to load data from the external flat file into a Netezza database table, or SELECT FROM statements to query the external table to validate data. External tables are mainly used for loading, unloading and taking Netezza database table backups.

Netezza External Table Options

Below are the some of the commonly used Netezza External table options:

Options Description
dateDelim ‘-‘ for all dateStyle except MONDY. The default is space
dateStyle The date style settings ‘YMD’, ‘MDY’, ‘DMY’, ‘MONDY’. The default is YMD
delimiter The default is the tab character. You can use any ASCII character you can type as a delimiter. For example, to enter the tab character, press the Tab key. You cannot specify the tab character as \t
logDir The directory to write the nzlog and nzbad files
Maxerrors The amount of error records allowed
nullValue The string that represents a null value for the column. The default is ‘NULL’.
timeDelim The default is ‘:’
timeStyle ‘24HOUR’, ‘12HOUR’. The default is ‘24HOUR’

Netezza External table Restrictions

Certain restrictions apply to external tables:

  • You cannot update an external table.
  • You cannot delete or truncate an external table. However, you can alter and drop the table definition. Dropping external table doesn’t delete source file.
  • You cannot select from more than one external table at a time in a query or subquery. Multiple external table references are not allowed.
  • You cannot use the nzload command to load data into an external table
  • You cannot use a union operation that involves two or more external tables
  • Using the nzbackup command to back up external tables backs up the schema but not the data.
  • You cannot use external tables in complex SQL statements

Related Reading:

Tags: , , ,