Commonly used Netezza Utilities

It is very easy to perform the ETL (Extract, Transform and Load) functionalities in Netezza. Netezza supports utilities those can be used to perform the ETL/ELT operations. Below are some of commonly used Netezza utilities:

Read:

• Commonly used Netezza Basic Commands

nzload utility

nzload is bulk data load utility available in Netezza. This utility supports loading data from flat file to Netezza database tables. Below is the syntax and usage:

Usage:

nzload -host <host> -db <database> -u <username> -pw <password> -t <target_table_name > -df /user/home/flat_file.txt

by default, nzload supports ‘|’ delimiter. You can overwrite it y using -Delim options.

Form more information read:

• nzload command and its usage in Netezza

nzsql utility

nzsql is one of the Netezza utilities. The Netezza nzsql command invokes a SQL command interpreter on the Netezza host or any Unix/Linux system that can act as a edge node to connect to Netezza host. In order to use nzsql utility first driver should be installed on the machine.

You can use the nzsql command to perform all the sql related stuffs. E.g. create database objects, run queries, and manage various databases created on that particular server. You cannot manage the object created in different host or server.

Usage:

nzsql -u [username] -pw [password] -d [database] -host [Netezza_host]

For more information read:

• Netezza nzsql command and its Usage

nz_migrate utility

If you want to migrate data from one netezza server to other, then you can use the Netezza nz_migrate utility. All the tables mentioned in the tablelist parameter are migrated to the target database. You can also set the option to create target tables or truncate target tables before migrating the data. NZ_Migrate utility is faster then nzload utility as it does the migration by running many parallel threads.

Usage:

nz_migrate -shost <src_host> -thost <tgt_host> -sdb <src_db> -tdb <tgt_db> -suser <src_user> -spassword <src_password> -tuser <tgt_user> -tpassword <tgt_password> -t <table1, table2, ...> -cksum fast -genStats Full -TruncateTargetTable YES >> $log

Related reading:

• Netezza nz_migrate Script Usage and Examples

External tables utility

Another way to migrate data in Netezza is to unload the table using create external table and then insert the data from that external table into the target table.

Usage:

CREATE EXTERNAL TABLE '/data/export.csv' USING (DELIM '~') AS
SELECT * from <table>;

INSERT INTO <target_table> SELECT * FROM EXTERNAL '/data/export.csv' USING (DELIM '~');