question Re: putHiveQL error in Support Questions

putHiveQL error

msumbul1 — Tue, 17 Oct 2017 19:25:38 GMT

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?

Re: putHiveQL error

pvillard — Tue, 17 Oct 2017 21:15:36 GMT

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.

Re: putHiveQL error

mburgess — Thu, 12 Apr 2018 20:35:12 GMT

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.