CREATE EXTERNAL TABLE command

You can use the CREATE EXTERNAL TABLE command to create external tables. With external tables, an external file is treated as a database table.

Privileges for creating external tables

To create an external table, you must have the following privileges:

The CREATE EXTERNAL TABLE administration privilege.
The database user who issues the CREATE EXTERNAL TABLE command owns the external table.
The List privilege on the database where you are defining the table.
If the schema where you define the table is not the default schema, you must have the List privilege on the schema as well.

When you create an external table, you must specify the location where the external table data object is stored. The operating system user must have permission to read from the data object location to support SELECT operations against the table and to write to the location if commands such as INSERT are used to add rows to the external table.

Limitations

If DATA_VERSION_RETENTION_TIME is specified to a nonzero value, the command fails. For more information, see Netezza Performance Server time travel.

Syntax

To create an external table that is based on another table, use the following syntax:

CREATE EXTERNAL TABLE table name
[ON external data source name
USING (
   DATABOBJECT ('/example.parquet') 
   format 'PARQUET' 
);]
SAMEAS table name
USING external table options

To create an external table by defining columns, run:

CREATE EXTERNAL TABLE table name
({  column_name type
[ column_constraint [ ... ] ]} [, ... ]
)
[USING external_table_options]

To create an external table from a Parquet file, run:

CREATE EXTERNAL TABLE table name 
ON external data source name 
USING ( 
   DATAOBJECT ('/example.parquet') 
   format PARQUET 
);

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

Although you can specify the PRIMARY KEY, DEFAULT, UNIQUE, and REFERENCES options for column specifications for both the CREATE TABLE command and the CREATE EXTERNAL TABLE command, the UNIQUE, PRIMARY KEY, and REFERENCES options are ignored for the CREATE EXTERNAL TABLE command. No error is generated. Because constraint checks and referential integrity are not supported for external tables, be careful when you are inserting data into these tables.

Examples

See External table examples.