You can load data from a flat file (external table) on a remote client into a database
table on a Netezza Performance Server host system.
Before you begin
If you want the database table to contain only the records from the external table, make sure
that the database table is empty before you load the data. If you load from an external table into a
database table that is not empty, the load is successful, but the new records are appended to the
existing records.
About this task
You can load data from any of the supported Netezza Performance Server clients, which include Windows, Linux®, Solaris, AIX®, and HP-UX clients. You can load all data types (including
Unicode) and file types (uncompressed and compressed formats).
To create an external table, you must be the admin user or have the Create External Table
administration privilege. You must also have permission to read from the path of the data
object.
Procedure
-
Establish a connection between the client machine and the Netezza Performance Server system host in one of the following
ways:
- Create an ODBC, JDBC, or OLE DB connection by using an application.
- Create a connection by issuing the nzsql command with the
-host option.
-
Create an external table by issuing the CREATE EXTERNAL TABLE command with
the REMOTESOURCE option.
An example follows:
CREATE EXTERNAL TABLE emp_backup SAMEAS emp USING (
DATAOBJECT ('/tmp/emp.dat')
REMOTESOURCE 'ODBC');
The
DATAOBJECT file specification must refer to a valid file on the client machine.
For the REMOTESOURCE option, specify the value that corresponds
to your connection method: ODBC, JDBC, OLE-DB,
NZSQL, or YES (the YES value is equivalent to the
NZSQL value). If you do not specify a remote source, the system searches for a
source file on the Netezza Performance Server system
host.
-
Insert data from the external table into the table on the Netezza Performance Server system host. An example
follows:
INSERT INTO emp SELECT * FROM emp_backup;