Load HDFS file into Netezza Table Using nzload and External Tables

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:4 mins read

nzload command is bulk copy command available in Netezza data warehouse appliance. This Netezza native command provides an easy method for using external tables and getting data into the Netezza appliance. There is no straight forward option to load hdfs file into Netezza tables using nzload command. You must use some work around to get hdfs file into Netezza tables. In this article, we will check out methods to load HDFS file into Netezza Table Using nzload and external tables with some examples.

Load HDFS file into Netezza Table Using nzload

Install Netezza Drivers

Before attempting to load hdfs file into Netezza tables, you must install Netezza odbc drivers into the machine from where you are trying to connect to Netezza. This could be edge node or Hadoop machine.

Follow my other post to install Netezza drivers:

Load HDFS file into Netezza Table Using nzload

As HDFS is different file system, nzload will not recognise the file if you provide hdfs file path in -df option of Netezza nzload.

https://gist.github.com/812cac6121f9f8a4e75a2d9d90a86ffa

Below are some of work around that we can use to load hdfs file into Netezza tables.

Load Netezza Table from STDOUT

You can use nzload to load data from STDOUT. Good news is, Hadoop hdfs commands supports -cat option. You can use hdfs dfs -cat along with nzload to load Netezza table from hdfs directory. This is a simple and easy way to get hdfs data into Netezza tables without copying to local directory. Note that, hdfs file should be stored as text file to use this approach.

Below example demonstrates the approach:

https://gist.github.com/2d9b910bd4c88d914dce5ce93a8bc80a

Now check the Netezza table for loaded data:

TRAINING.ADMIN(ADMIN)=> select * from STUD_DTLS; 
 ID | DEPT_ID | NAME 
-----+---------+------ 
 101 | 100 | AAA 
 103 | 102 | CCC 
 105 | 102 | EEE 
 102 | 101 | BBB 
 104 | 100 | DDD 
 106 | 103 | FFF 
(6 rows) 
TRAINING.ADMIN(ADMIN)=>

Copy HDFS file to Local and Load using nzload Command or External Table

Another approach is to copy file from hdfs to local directory using hdfs -copyToLocal command. Once you have file in your local directory then you can use either nzload command or Netezza external table command to load data to Netezza table.

Below are the examples on usage of nzload and external table commands:

Related reading:

Copy HDFS file to Local Directory

Use -copyToLocal command to copy hdfs file to local directory. Once file is downloaded in the form of flat files than you can use either nzload or external tables to load the data into Netezza tables.

https://gist.github.com/a77e9eb58ebfc964721c4415084095eb

nzload

Now, you have the hdfs file copied to local directory as a flat file. Simply, use the Netezza nzload utility to load data into Netezza table.

Below is the example on usage of Netezza nzload on flat files:

https://gist.github.com/ca2ce4a8e7f84b7a7d3e0ba02ed2539b

You can read more about nzload in my other post:

External Tables

Another approach to load data from flat file into Netezza is to use Netezza external tables.

Below is the example of using Netezza external tables to load flat file.

https://gist.github.com/c01092dfe48b13394defcfb9e3f4421c

Related reading:

Tags: ,