Netezza NZSQL, NZLOAD, NZ_MIGRATE, NZUNLOAD utility to Extract - Load - Migrate Files/Data

Netezza NZSQL, NZLOAD, NZ_MIGRATE, NZUNLOAD utility to Extract - Load - Migrate Files/Data 

Extract - Load - Migrate Files/Data to/from Netezza

It is very easy in Netezza to extract data from netezza tables, load data from files to Netezza target tables, or Migrate data from one Netezza database to another.Below are few commands that you can use this purpose.

NZSQL utility: This utility can be run from any machine on which it is installed i.e. it does not have be from netezza box itself. This utility can be used to run any sql statement like select or insert statement on the netezza database. In the below usage you see data extracted from the table into a output file and in the second mzsql command the data is also gzipped.

Usage:

nzsql -host <netezz_db_servername> -d <database> -u <username> -pw <password> -c  -c  "select * from tablename"  -o /root/home/outputfilename.txt;


nzsql -host <netezza_db_servername> -d <database> -u <username> -pw <password> -c  "select * from tablename" -F "|" -A -q -t | gzip > /root/home/outputfilename.txt.gz;

nzsql -host <netezza_db_servername> -d <database> -u <username> -pw <password> -c  'insert into tablename values (1 ,2 )'  -o /root/home/outputfilename.txt;

----------------------------------------------------------------------------------------------------

NZLOAD utility: This utility can be used to load data from files to netezza target tables. The nzload command usage is below. In the usage below the flat file which is delimited by | is loaded to a table mentioned with in <target_table_name>. Also user name and password can be provided for this utility.

nzload -host <netezzahost> -db <database> -u <user name>
       -pw <password> -delim '|' -t <target_table_name> -df /root/home/outputfilename.txt



----------------------------------------------------------------------------------------------------

NZ_MIGRATE utility: this utility is used to migrate data from source database to the target database. All the tables mentioned in the tablelist parameter are migrated to the target database. There are options to create target tables or truncate target tables before migrating the data. NZ_Migrate utility does the migration by running many parallel threads and hence is faster than nzload.


nz_migrate command usage is below:

./nz_migrate -shost <source_host> -thost <target_host> -sdb <source_database> -tdb <targetdatabase> -suser <source_user> -spassword <source_password> -tuser <target_user> -tpassword <target_password> -t <table1, table2, ...> -cksum fast -genStats Full  -TruncateTargetTable YES >> $log


./nz_migrate -shost <source_host> -thost <target_host> -sdb <source_database> -tdb <targetdatabase> -suser <source_user> -spassword <source_password> -tuser <target_user> -tpassword <target_password> -t <table1, table2, ...> -cksum fast -genStats Full  -CreateTargetTable YES>> $log

----------------------------------------------------------------------------------------------------

NZ_UNLOAD utility: if instead of nzsql you want to extract files from a netezza table faster by running multiple threads in parallel then use nz_unload utility. The nz_unload command usage is below:

./nz_unload -sql '"select * from tablename"' -file /nz/dba/output/outputfilename.txt

----------------------------------------------------------------------------------------------------

External tables: Another way to migrate data is to create external table and insert the data from external table into the targer table. External table usage would like shown below:

CREATE EXTERNAL TABLE '/tmp/export.csv' USING (DELIM ',') AS
SELECT * from <TABLENAME>;


INSERT INTO <targettable> SELECT * FROM external '/tmp/export.csv'
USING (DELIM ',');