Data loading in Netezza means
transferring data into Netezza appliance. There are several ways to transfer
the data into appliance
Read: Netezza Data Loading Guide
Read: Netezza Data Loading Guide
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 tables, then use that
external table to load target table in Netezza appliance.
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. External tables can be used 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.
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. [ Read
about external table command here ]
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]
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.
As
the nzload uses external tables, you should have CREATE EXTERAL privilege in
order to use nzload command.
Command Syntax
Following
command can be used 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(|). The above syntax is basic one and can be used
along with various nzload command line options.
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. 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.
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, you will end up with messing up
the with existing data.
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.
No comments:
Post a Comment