Configure Sqoop for Netezza and Import table to Hive Metastore

Our scenario is a below:

- DWH: Netezza (IBM Puredata)

- Hadoop : Cloudera (CDH5.4) and we are using Cloudera Manager. 

- Hadoop Cluster configuration consideration: All the worker nodes are behind a private network. Can not be accessed with our edge/management nodes.

- Objective: Import data from Netezza to a Hive table.

I followed below steps to meet the objective. 

As pr our current stats, we are now able to migrate 700 GB data in 1 hour (using 4 channels or mappers).

1. Install netezza connectors:

1.1 Install netezza connector for sqoop following steps in link: http://cloudera.com/content/cloudera/en/documentation/connectors/latest/Netezza/Cloudera-Connector-for-Netezza/ccnt_topic_3.html

1.2 (for Oozie)download "sqoop-nz-connector-1.2c5.tar" from below link and upload sqoop-nz-connector-1.2c5.jar to HDFS: /user/oozie/share/lib/lib_/sqoop/

http://www.cloudera.com/content/cloudera/en/downloads/connectors/sqoop/netezza/1-2c5-for-cdh-5-x.html

      1.3 sudo -su oozie hadoop fs -put /tmp/sqoop-nz-connector-1.2c5.jar /user/oozie/share/lib/lib_/sqoop/

2. In addition to above, 

2.1 Netezza JDBC driver directory /var/lib/sqoop should have below permissions:

  This directory should have set permission as (chmod 755  /var/lib/sqoop)

  full path up to the driver file /var/lib/sqoop/nzjdbc3.jar should be accessible by others (chmod 444 /var/lib/sqoop/nzjdbc3.jar)

 

2.2 (for Oozie)upload netezza jdbc driver to   HDFS: /user/oozie/share/lib/lib_/sqoop/

sudo -su oozie hadoop fs -put /var/lib/sqoop/nzjdbc3.jar /user/oozie/share/lib/lib_/sqoop/

2.3 restart oozie

3. Configure proxy on edge node/s to connect to netezza for data nodes. (in case your datanodes are in private network).

[sqoop will launches several map jobs on data nodes and data nodes connect directly to netezza]

3.1 install haproxy on the edge node/s

  yum install haproxy

 

3.2 Configure the proxy for netezza.

Append below in '/etc/haproxy/haproxy.cfg'. Here the proxy will listen to 25501 on the edge node/s and bypass the incoming connection to netezza host:port.

# Proxy For Netezza

listen netezza : 25501

    mode tcp

    option tcplog

    balance leastconn

    server netezza :

    

3.3 service haproxy restart

4. To import use below

*** the user should have create external table privilege on the target database on netezza

- use username in upper case

- use direct ( Each map task of Netezza connector’s import job works on a subset of the Netezza partitions and transparently creates and uses an external table to transport data)

- use "--P" for password prompt

- we can pass mapreduce params like "-D mapreduce.task.timeout=0" just after import/export command

Here:

: netezza host/IP or host/IP of the edge node running the proxy for netezza.

: netezza port(default 5480) or port listening for netezza in the edge node running the proxy for netezza.

4.1     to import data on a HDFS directory use below:

sqoop import  \

--direct \

--connect jdbc:netezza://:/ \

--table D_ACCOUNT \

--fields-terminated-by '|' \

--target-dir /user/etl/temp/ \

--username \

--password '' \

-- --nz-logdir /tmp/

4.2     to import data on Hive table use below (it will prompt for password as we used “--P”):

sqoop import  \

--connect jdbc:netezza://:25501/  \

--table .  \

--where   \

--target-dir ""  \

--username   \

--P \

--hive-import  \

--hive-table ""  \

--hive-overwrite  \

--delete-target-dir  \

--num-mappers 4  \

--verbose \

--direct  \

-- --nz-logdir   --nz-uploaddir

4.3     Then use hive to copy data from temp table(text or serial format) to final table(parquet + snappy) (select ..insert). Sqoop has some limitation to load data directly to a parquet table.

Migrate Large table from Hive/Impala to Netezza

We can follow below steps to migrate a table from Hive to Netezza/IBM Puredata (without sqoop).
It is very useful when the table size if large (more that 100 GB) as the full operation doesn't required local filesystem.


0. Create the target table in netezza.

1. create the target directory in HDFS
hadoop fs -mkdir /user/etl/temp/my_table

2. Login to hive/beeline export the target table/query to the hdfs lcation created above. Here, we can are using csv-serde to put customised quote string [https://drone.io/github.com/ogrodnek/csv-serde/files/target/csv-serde-1.1.2-0.11.0-all.jar]


beeline -u jdbc:hive2://hiveSeverHost:10000/db_name -n etl

> add jar /var/lib/hive/jars/csv-serde-1.1.2-0.11.0-all.jar;

> create table exp_my_table 
row format serde 'com.bizo.hive.serde.csv.CSVSerde'
with serdeproperties(
"separatorChar" = "\,",
"quoteChar" = "\"",
"escapeChar" = "\\")
STORED AS textfile 
LOCATION '/user/etl/temp/my_table/' 
as select * from my_table;

3. Create a unix named pipe:
mkfifo /stage/AIU/ETL/temp/mypipe1

4. Issue below command in background of in a seperate session, loading data from the named pipe create above(this command will stuck until data inserts to the named pipe). Make sure -delim & -quotedValue matching with previous step. Put other nzload options as per requirements.

nzload -host nzhost_IP -u NZ_User -db DB_NAME -t my_table -delim "," -quotedValue DOUBLE -escapeChar '\' -df /stage/AIU/ETL/temp/mypipe1 -ignoreZero YES -ctrlChars -crInString &

To allow loading un-printable strings and ignore "null byte", specially in URLs:
-ignoreZero YES : "|<nul>AB<nul>CDEF|" will become "|ABCDEF|"
-ctrlChars : accept control chars in char/varchar fields
-crInString : allow unescaped carriage returns (cr), ine feed (LF) becomes the default end-of-row indicator

ref: https://www-304.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.load.doc/c_load_handle_legal_characters.html

5. Now from a separate session(if step 4 is not in background), copy the hive table HDFS location(step 2) to the named pipe(created in step 3).

hadoop fs -cat /user/etl/temp/my_table/* > /stage/AIU/ETL/temp/mypipe1

6. It will start flowing data to the named pipe and naload was waiting to get data in the named pipe(step 4). So, at this point data starts flowing to netezza. When data loading done or any error encountered both nzload and hdfs copy commands will exit. Please check nzload log and on screen unix related error for further problem solving.