Netezza data load is transferring data into Netezza appliance. There are several ways to transfer the data into appliance.
Netezza Data Load using External tables
These are tables stored as flat files on the host or client systems and not in the Netezza appliance database. Create table on top of the flat file that needs to be loaded to Netezza tables, then use that external table to load target table in Netezza appliance. As a result data will be copied to the target table.
Flat Files
An external table allows Netezza to treat an external flat file as a database table. An external table has a definition (a table schema) that matches the columns in the flat file, but the actual data exists outside of the Netezza appliance database. Use External tables to access files which are stored on the Netezza host server or, in the case of a remote external table; Netezza can treat a file on a client system as an external table. In order to use external tables you should have the “create external table” permission on host or client system.
Read:
- Netezza Fixed-Width Data Loading and Example
- Netezza External Table and Example
- Netezza Storing Large data files to Local Systems
After you have created the external table definition, you can use INSERT INTO statements to load data from the external file into a database table, or SELECT FROM statements to query the external table.
Command Syntax
The CREATE EXTERNAL TABLE command has the following syntax.
1.To create an external table based on another table:
CREATE EXTERNAL TABLE table_name SAMEAS table_name USING external_table_options
2.To create an external table by defining columns:
CREATE EXTERNAL TABLE table_name
({
column_name type
[column_constraint [ ... ] ]} [, ... ]
)
[USING external_table_options]
Netezza Data Load Using nzload
nzload is bulk copy command available in Netezza. This is a command that provides an easy method for using external tables and getting data into the Netezza appliance.
The nzload command is a SQL CLI client tool that allows you to load data from the local or a remote client, on all the supported client platforms (Linux/windows).
The nzload command processes command-line load options to send queries to the host to create an external table definition, run the insert/select query to load data, and when the load completes, drop the external table. In order to use any of the netezza tool, you should first install nzodbc or nzjdbc drivers on the client or remote machines.
Since the nzload uses external tables, you should have CREATE EXTERNAL privilege in order to use nzload command.
Command Syntax
Use following command to load flat file to Netezza data warehouse
nzload -host <host IP/name> -db <database> -u <username> -pw <password> -t table –df ‘/home/dw/data/flat_file.csv’ -bf ‘/home/dw/data/flat_file.bad’ -lf ‘/home/dw/data/flat_file.log’ [optional args]
Default delimiter in nzload is pipe(|). Use the above syntax along with various nzload command line options.
Read:
nz_migrate
Finally, the nz_migrate, this is a separate tool, not part of the Netezza software package. This utility is a script that can migrate (copy) a database/table from one Netezza appliance to another, or make a copy of a database/table on the same server. Therefore, if you are upgrading to other version of Netezza appliance then this is the best available tool to sync up the data between two servers.
Related reading:
Command syntax
nz_migrate -sdb <dbname> -tdb <dbname> -thost <name/IP> [optional args]
The command can be run either from source or target host. Make sure all the parameter values are correct. Otherwise, existing data will be corrupted.
Backup and Restore
There are different methods for doing backups and restores to transfer data between systems. One method is to create external tables and use nzload.
Read: