Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

putHiveQL error

avatar
Expert Contributor

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?

1 ACCEPTED SOLUTION

avatar

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.

View solution in original post

2 REPLIES 2

avatar

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.

avatar
Master Guru

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.