putHiveQL error

[#1]

Hello,

I try to ingest data in hive with nifi

(from json data =>convert jsontosql => puthiveql)

and I got this error message from the puthiveql processor:

Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.parse.ParseException:line 1:221 cannot recognize input near '?' ',' '?' in value row constructor 

if I look at the input flowfile of the puthiveql it has the correct insert query

INSERT INTO nifilog (objectid, platform, bulletinid, bulletincategory, bulletingroupid, bulletinlevel, bulletinmessage, bulletinnodeid, bulletinsourceid, bulletinsourcename, bulletinsourcetype, bulletintimestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 

each flowfile has all the needed attribute:

sql.args.N.type & . sql.args.N.value

Any idea how to debug/solve this?

[#2 · Accepted solution]

Hi @msumbul,

The ConvertJsonToSQL processor has been developed to work with the SQL processors. The Hive processor is expecting a different naming convention on the attributes, instead of:

sql.args.N.type/value

it is expecting:

hiveql.args.N.type/value

The syntax between SQL and HQL are close enough and the generated query is also valid for Hive but the attributes need to be renamed. What you can do is to use an UpdateAttribute processor to have the expected attributes before using the PutHiveQL processor.

Hope this helps.

[#3]

As of NiFi 1.5.0 (via NIFI-4684), you can now specify the prefix in ConvertJSONToSQL. The property defaults to "sql" to maintain existing behavior, but can be changed to "hiveql" for use with PutHiveQL.