Data Load Using External Table

Data Load Using External Table

External Table command can be used either on the Linux host or can be executed remotely through different clients like Squirrel, Aginity work bench etc. The only difference in the command when using remote client will be addition of REMOTESOURCE in Using section.

For loading data from a file name Data.txt which is in D:\ and data is in comma delimited format as below

For creating new table Employee in the Database(Test) below is the query. Here I am using ODBC driver for connecting to netezza engine we can also use JDBC and OleDB as options to connect. Also we should keep in mind that netezza does not support cross database access for DDL and DML, so out database should be selected properly in the Drop box as below.

The below code should be run to create the Employee Table from the file D:\Data.txt

Same data if we need to load through the Linux host then we can connect to the host through putty. Load the file to the host using filezilla in a folder and very if the file exist in the folder.

Now run the below code to load the data.

NETEZZA ARCHITECTURE

System building blocks

A major part of the Netezza solution's performance advantage comes from its unique AMPP
architecture (shown in Figure 1), which combines an SMP front end with a shared nothing
MPP back end for query processing. Each component of the architecture is carefully chosen
and integrated to yield a balanced overall system. Every processing element operates on
multiple data streams, filtering out extraneous data as early as possible. More than a
thousand of these customized MPP streams work together to divide and conquer the
workload.

The components are below

• NETEZZA HOST

The SMP hosts are high-performance Linux® servers set up in an active-passive

configuration for high availability. The active host presents a standardized interface to

external tools and applications. It compiles SQL queries into executable code segments

called snippets, creates optimized query plans, and distributes the snippets to the MPP

nodes for execution.

• SNIPPET BLADE

S-Blades are intelligent processing nodes that make up the turbocharged MPP engine of

the appliance. Each S-Blade is an independent server containing powerful multi-core

CPUs, multi-engine FPGAs, and gigabytes of RAM, all balanced and working concurrently

to deliver peak performance. The CPU cores are designed with ample headroom to run

complex algorithms against large data volumes for advanced analytics applications.

• DISK ENCLOSURES

The disk enclosures' high-density, high-performance disks are RAID protected. Each disk

contains a slice of every database table's data. A high-speed network connects disk enclosures 

to S-Blades, allowing all the disks in a Netezza to simultaneously stream data

to the S-Blades at the maximum rate possible.

• NETWORK FEBRIC

A high-speed network fabric connects all system components. The Netezza appliance

runs a customized IP-based protocol that fully utilizes the total cross-sectional bandwidth

of the fabric and eliminates congestion even under sustained, bursty network traffic. The

network is optimized to scale to more than a thousand nodes, while allowing each node to

initiate large data transfers to every other node simultaneously.

NZSYSTEM

NZSYSTEM

nzsystem is a command line tool used by the DBA for various netezza  operation. Some of the uses of nzsystem is mentioned below.

• It can be used to take the system offline (nzsystem offline -u user -password password -host nzhost)
• Pause the system (Its important when we do some registry changes like setting gatekeeper) nzsystem pause -u user -password password -host nzhost
• To display system registry settings where we can find GRA/GK settings also   nzsystem showRegistry -u user -password password -host nzhost

We also can configure the system settings using nzsystem. please follow the system admin guide.