Netezza External Tables.

To create an external table, you must have List privilege on the database and Create External Table administration privilege.

Here’s how it works….

An external table is the information required in Netezza to read data from an external file on the OS into netezza.

Syntax.

The CREATE EXTERNAL TABLE command has the following syntax.

To create an external table based on another table:

CREATE EXTERNAL TABLE table_name
SAMEAS table_name
USING external_table_options

This will create the external table assuming the same format as the internal table.

To create an external table as a SELECT clause:

CREATE EXTERNAL TABLE [table_name] ‘filename’
[USING external_table_options]
AS select_clause

This will create the external table, mych like the sameas table_name option, but unload the data from the internal to external table at the same time.

To create an external table by selecting columns:

CREATE EXTERNAL TABLE table_name
({ column_name type
[ column_constraint [ … ] ]} [, … ]
)
[USING external_table_options]

This creates the external table with the column information included.

What are External Tables in Netezza ?

External Tables in Netezza

An external table is a data file that you can use to load or unload database data or you can use the external table as part of a query

Create external table syntax and its description:

CREATE EXTERNAL TABLE
[sameas table] | ([column_name [,…]])
USING (DATAOBJECT () DELIMITER ) [options];

table_name External table name
sameas Allows you to duplicate an existing table
table Specifies the source table to duplicate
column_name Specifies the name of the column in the new table
dataobject Allows you to specify a file
host_file_name Specifies the full file specification (path) of a host file
delimiter Specifies the column character delimiter. The default is | (pipe)

This table is just a template that contains metadata about data formats, filed delimiters, load preferences for the file. It does not physically exist inside the database.

After you have created the template, you can insert rows into the external table or query from this table. You cant use multiple external tables or the same external table more than once in an sql query. If you want to use multiple external tables in a sql query load the data into a non-external table.

External table options:

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’

System views _v_extobject, _v_external provides metadata about external tables.

Compression.

If you create an external table and place data in the external file, using the compression option, you have to re-create the table with compression, else each row of data will be rejected as a bad row.

The default setting for Compression on external tables is False.

Examples.

insert into testload(x,y,dt,tm,str)
select x, y, dt, tm, str
from external ‘c:temptest1.csv’
(
x integer,
dt date,
tm timestamp,
y numeric(18,4),
str nvarchar(100)
) using (
remotesource ‘odbc’
ctrlchars true
delim ‘,’
logdir ‘c:temp’
maxerrors 1
encoding ‘internal’
);

Standard Unloading and Reloading.

The following examples unload and load a user table to an external table in text format.

Text format is the default, so you can omit the format option.

To create a text-format external table, enter:

CREATE EXTERNAL TABLE extemp SAMEAS emp USING (
DATAOBJECT (‘/tmp/emp.dat’)
);

To unload data in user table EMP to the external table EXTEMP, enter:

  • INSERT INTO extemp SELECT * FROM emp;

To load data into user table EMP from external table EXTEMP, enter:

  • TRUNCATE TABLE emp;
  • INSERT INTO emp SELECT * FROM extemp;

Back up and Restore a User Table

The following examples show how to back up and restore the user table EMP to an external table in binary compressed format.

To create a compressed binary format external table definition called emp_backup for the table emp, enter:

CREATE EXTERNAL TABLE emp_backup SAMEAS emp USING (
DATAOBJECT (‘/tmp/emp.bck’)
COMPRESS true
FORMAT ‘internal’
);

To back up the emp table data into emp_backup, enter:

  • INSERT INTO emp_backup SELECT * FROM emp;

To restore the emp table from emp_backup, make sure that the emp table is empty and enter:

  • TRUNCATE TABLE emp;
  • INSERT INTO emp SELECT * FROM emp_backup;
This entry was posted in External Tables, Netezza and tagged , , , , , . Bookmark the permalink.