Loading data from a remote client system
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.
Establish a connection between the client machine and the Netezza Performance Server system host in one of the following
- 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
INSERT INTO emp SELECT * FROM emp_backup;