Problem Description:


The REST API ingestion fails with the below error while creating the hive table.


[ERROR] 2019-12-18 19:11:26,833 [pool-2-thread-1] infoworks.tools.hive.HiveUtils:510 :: Error while executing hive queryorg.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 16:23 cannot recognize input near 'database' ':' 'string' in column specification
at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:279)
at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:265)
at org.apache.hive.jdbc.HiveStatement.runAsyncOnServer(HiveStatement.java:303)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:244)
at infoworks.tools.hive.HiveUtils.execStmts(HiveUtils.java:506)
at infoworks.tools.hive.HiveUtils.createHiveTable(HiveUtils.java:740)



Root cause: This issue happens if there is a hive keyboard database as any key name in the REST API JSON response with name as "database" which is a keyword in the hive and Infoworks will try to create a column with struct as a data type.


 CREATE EXTERNAL TABLE `Test8` (

`ziw_row_id` string,

`ziw_active` string,

`ziw_is_deleted` string,

`ziw_status_flag` string,

`ziw_source_start_date` date,

`ziw_source_start_timestamp` timestamp,

`ziw_target_start_date` date,

`ziw_target_start_timestamp` timestamp,

`ziw_source_end_date` date,

`ziw_source_end_timestamp` timestamp,

`ziw_target_end_date` date,

`ziw_target_end_timestamp` timestamp,

`credentials` struct<database:string,sessionid:double,username:string>,

`path` string,

`securitytoken` struct<database:string,sessionid:double,username:string>

)

STORED AS  orc 

LOCATION '/iw/sources/AR_pepsico_rest/iw/sources/test6/merged//orc/' 


As 'database' is a hive keyword, the above create table statement fails with the mentioned error.



Solution:


To resolved this issue, set the below Global Advanced configuration in Infoworks.

Key: ADD_BACKTICK_FOR_HIVE_NESTED_DATATYPE
value: true



Setting the above config at the Global level will append backticks to the struct element names and the hive create table statement will be valid as shown below.


 CREATE EXTERNAL TABLE `Test8` (

`ziw_row_id` string,

`ziw_active` string,

`ziw_is_deleted` string,

`ziw_status_flag` string,

`ziw_source_start_date` date,

`ziw_source_start_timestamp` timestamp,

`ziw_target_start_date` date,

`ziw_target_start_timestamp` timestamp,

`ziw_source_end_date` date,

`ziw_source_end_timestamp` timestamp,

`ziw_target_end_date` date,

`ziw_target_end_timestamp` timestamp,

`credentials` struct<`database`:string,`sessionid`:double,`username`:string>,

`path` string,

`securitytoken` struct<`database`:string,`sessionid`:double,`username`:string>

)

STORED AS  orc 

LOCATION '/iw/sources/AR_pepsico_rest/iw/sources/test6/merged//orc/' 



Applicable Infoworks Versions:

IWX v2.4.x,v2.5.x,v2.6.x,v2.7.x,v2.8.x,v2.9.x