Netezza Unload Table using External Tables and Examples

You can unload the tables for number of reasons such as take backup of table, send data to external customer or copy data to other appliance. In this article, we will check on Netezza Unload table using external tables and some examples.

Netezza Unload Table using External Tables

You can unload data from a user table into an external table and load data from an external table into a user table by using the text-delimited format. If you want to create the fixed-width file using external tables then you will be disappointed as Unloading for the fixed-length format is not supported in Netezza.

Read:

• Export Netezza Data with Quoted Values into Flat File and Example
• Netezza Fixed-Width Data loading and Examples
• Guide to Load data into Netezza Database
• nzsql command and its usage
• Netezza Export Table Data to CSV Format

Netezza Unload Table using External Tables Syntax

You can follow below steps to unload the data using external table:

• The following command creates a text-format external table:

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

• The following command unloads data in user table EMP into the external table EXTEMP:

INSERT INTO extemp SELECT * FROM emp;

Load Data back to user Table

The following command loads data into user table EMP from external table EXTEMP:

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

Netezza Unload Table using External Tables Example

Let’s see the example to unload data using Netezza external table:

[nz@netezza ~]$ nzsql -db TRAINING -e -f patient_1_ext.sql 
CREATE EXTERNAL TABLE patient_ext SAMEAS patient_1 USING (DATAOBJECT 
('/export/home/nz/patient.dat') DELIMITER '~' ); 
CREATE EXTERNAL TABLE 
INSERT INTO patient_ext SELECT * FROM patient_1; 
INSERT 0 5 
[nz@netezza ~]$

Netezza Unload Table using nz_unload Script

You can unload the table using IBM provided script nz_unload. This script uses the Netezza remote external tables to unload data from tables.

Below is the usage and examples:

nz_unload -sql '"<statement>"' -file <filename>

[nz@netezza bin]$ nz_unload -db training -sql "select * from PATIENT_1" -file "/export/home/nz/file1.txt" 
Rows Returned : 7 
[nz@netezza bin]$