Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

PutHiveQL Error via NiFi (1.4)

Highlighted

PutHiveQL Error via NiFi (1.4)

New Contributor

Hi,

I want to use the PutHiveQL to execute the following INSERT (Content of the Incoming FlowFile:

INSERT INTO sensehat VALUES ('2018-01-24 05:26:21', 29.84, 30.78, 25.26, '192.168.16.3');

56542-insert.png

However, I get the following error:

2018-02-01 15:27:37,256 ERROR [Timer-Driven Process Thread-10] o.apache.nifi.processors.hive.PutHiveQL PutHiveQL[id=286bb204-0161-1000-9486-e320fcd50082] Failed to process session due to org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=b9e4155e-d7ef-4817-9835-721d7f108584,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1516880313990-13, container=default, section=13], offset=717915, length=89],offset=0,name=3026792823336151,size=89] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask: {}
org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=b9e4155e-d7ef-4817-9835-721d7f108584,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1516880313990-13, container=default, section=13], offset=717915, length=89],offset=0,name=3026792823336151,size=89] due 
to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.lambda$createOnGroupError$2(ExceptionHandler.java:226)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.lambda$createOnError$1(ExceptionHandler.java:179)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler$OnError.lambda$andThen$0(ExceptionHandler.java:54)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler$OnError.lambda$andThen$0(ExceptionHandler.java:54)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:148)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$new$4(PutHiveQL.java:199)
        at org.apache.nifi.processor.util.pattern.Put.putFlowFiles(Put.java:59)
        at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:101)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$onTrigger$6(PutHiveQL.java:255)
        at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:114)
        at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
        at org.apache.nifi.processors.hive.PutHiveQL.onTrigger(PutHiveQL.java:255)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1119)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:128)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask
        at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:296)
        at org.apache.hive.jdbc.HivePreparedStatement.execute(HivePreparedStatement.java:98)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$null$3(PutHiveQL.java:218)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)
        ... 18 common frames omitted

My DataFlow looks as follows:

56543-dataflow.png

The SelectHiveQL is receiving data from the table "sensehat", but the insert statement is not working. I am using NiFi 1.4. and HDP 2.6.

Kind regards,

Jan

5 REPLIES 5

Re: PutHiveQL Error via NiFi (1.4)

Hi @Jan, sometimes the log output from the processor is limited and won't show the exact error. Try looking at the nifi log file in /var/log/nifi on the nifi node.

Re: PutHiveQL Error via NiFi (1.4)

New Contributor

Hi @Scott Shaw,

the error is already stated in the first post. But here is another one:

2018-02-02 19:25:47,557 ERROR [Timer-Driven Process Thread-6] o.apache.nifi.processors.hive.PutHiveQL PutHiveQL[id=286bb204-0161-1000-9486-e320fcd50082] org.apache.nifi.processors.hive.PutHiveQL$$Lambda$228/655414801@168e589c failed to process due to org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=7cf6a2c4-8a46-4270-a5f1-3d0dde289eee,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1517497889134-1, container=default, section=1], offset=1156, length=89],offset=0,name=3026792823336151,size=89] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask; rolling back session: {}
org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=7cf6a2c4-8a46-4270-a5f1-3d0dde289eee,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1517497889134-1, container=default, section=1], offset=1156, length=89],offset=0,name=3026792823336151,size=89] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.lambda$createOnGroupError$2(ExceptionHandler.java:226)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.lambda$createOnError$1(ExceptionHandler.java:179)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler$OnError.lambda$andThen$0(ExceptionHandler.java:54)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler$OnError.lambda$andThen$0(ExceptionHandler.java:54)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:148)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$new$4(PutHiveQL.java:199)
        at org.apache.nifi.processor.util.pattern.Put.putFlowFiles(Put.java:59)
        at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:101)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$onTrigger$6(PutHiveQL.java:255)
        at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:114)
        at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
        at org.apache.nifi.processors.hive.PutHiveQL.onTrigger(PutHiveQL.java:255)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1119)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:128)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask
        at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:296)
        at org.apache.hive.jdbc.HivePreparedStatement.execute(HivePreparedStatement.java:98)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$null$3(PutHiveQL.java:218)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)
        ... 18 common frames omitted

Re: PutHiveQL Error via NiFi (1.4)

Are you able to successfully execute the INSERT statement outside of Nifi to verify the INSERT command is valid?

Re: PutHiveQL Error via NiFi (1.4)

New Contributor

I am also facing the same issue but for Alter table command. From hive console these commands are working fine but from nifi, it is providing same error.

ALTER TABLE a.a SET TBLPROPERTIES('EXTERNAL'='FALSE');

Alter table a.a partition(year='2018', month='03', day='31') CONCATENATE;

ALTER TABLE a.a SET TBLPROPERTIES('EXTERNAL'='TRUE');

Re: PutHiveQL Error via NiFi (1.4)

Is the NiFi user allowed to insert records and/or alter the tables? Often the Hive console (beeline, e.g.) is run as a user that exists on the system and as such, is allowed to affect the tables. However even if the Hive Connection Pool is set up with the correct authentication, if the job is "run as the end user instead of Hive" (a Hive config property), then I think your NiFi process owner ("user") might need write privileges for the given tables.