Hive: converting Netezza datatype to Hive datatypes

PROBLEM: 

Dynamically create target Hive tables and load them with data exported from Netezza. 

SOLUTION: 

To create Hive tables dynamically you need to convert Netezza to Hive datatypes, then proceed with the load.  Here is a datatype conversion table: 

SELECT

DISTINCT

FORMAT_TYPE as NZ_ORIG_DATATYPE,

SQLX..REPLACE(

SQLX..REPLACE(

SQLX..REPLACE(

SQLX..REPLACE(

SQLX..REPLACE(

SQLX..REPLACE(

SQLX..REPLACE(

SQLX..REPLACE(

SQLX..REPLACE(FORMAT_TYPE

,'CHARACTER VARYING' ,'VARCHAR'      )

,'NATIONAL VARCHAR'  ,'VARCHAR'      )

,'REAL'              ,'FLOAT'        )

,'NUMERIC(38,0)'     ,'BIGINT'       )

,'NATIONAL CHARACTER','CHAR'         )

,'INTEGER'           ,'INT'          )

,'NUMERIC'           ,'DECIMAL'      )

,'BYTEINT'           ,'TINYINT'      )

,'DOUBLE PRECISION' ,'DECIMAL(14,10)')

FROM _v_relation_column

WHERE OBJCLASS=4905;

---

RELATED LINKS:

• How to identify max precision scale of factional numeric datatypes
• Data types and aliases, IBM® PureData™ System for Analytics, Version 7.2.1
• Hive Data Types: Numeric Types