Apache Nifi encounter error when setup the PutHiveQL

[#1]

Hi All,

I have try to connect one simple insert workflow to hive.

But I encounter some error, could someone help on this ?

2021-01-14 21:30:14,245 ERROR [Timer-Driven Process Thread-4] o.apache.nifi.processors.hive.PutHiveQL PutHiveQL[id=f821e7aa-0176-1000-9088-506b00a72e66] org.apache.nifi.processors.hive.PutHiveQL$$Lambda$1066/699859650@1c9df9e2 failed to process due to org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=acf2bb1a-10fa-4dc5-8e5f-c12bf706f45f,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1610610213856-985, container=default, section=985], offset=89099, length=139],offset=0,name=acf2bb1a-10fa-4dc5-8e5f-c12bf706f45f,size=139] due to java.sql.SQLException: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed); rolling back session: org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=acf2bb1a-10fa-4dc5-8e5f-c12bf706f45f,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1610610213856-985, container=default, section=985], offset=89099, length=139],offset=0,name=acf2bb1a-10fa-4dc5-8e5f-c12bf706f45f,size=139] due to java.sql.SQLException: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed)
org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=acf2bb1a-10fa-4dc5-8e5f-c12bf706f45f,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1610610213856-985, container=default, section=985], offset=89099, length=139],offset=0,name=acf2bb1a-10fa-4dc5-8e5f-c12bf706f45f,size=139] due to java.sql.SQLException: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed)
        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:226)
        at org.apache.nifi.processor.util.pattern.Put.putFlowFiles(Put.java:60)
        at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:103)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$onTrigger$6(PutHiveQL.java:295)
        at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:120)
        at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
        at org.apache.nifi.processors.hive.PutHiveQL.onTrigger(PutHiveQL.java:295)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1174)
        at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
        at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
        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:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed)
        at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:262)
        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 sun.reflect.GeneratedMethodAccessor658.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.access$100(StandardControllerServiceInvocationHandler.java:38)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240)
        at com.sun.proxy.$Proxy214.execute(Unknown Source)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$null$3(PutHiveQL.java:254)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)
        ... 18 common frames omitted
Caused by: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed)
        at org.apache.thrift.transport.TIOStreamTransport.write(TIOStreamTransport.java:147)
        at org.apache.thrift.transport.TTransport.write(TTransport.java:107)
        at org.apache.thrift.transport.TSaslTransport.writeLength(TSaslTransport.java:391)
        at org.apache.thrift.transport.TSaslTransport.flush(TSaslTransport.java:499)
        at org.apache.thrift.transport.TSaslClientTransport.flush(TSaslClientTransport.java:37)
        at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:65)
        at org.apache.hive.service.cli.thrift.TCLIService$Client.send_ExecuteStatement(TCLIService.java:219)
        at org.apache.hive.service.cli.thrift.TCLIService$Client.ExecuteStatement(TCLIService.java:211)
        at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:253)
        ... 30 common frames omitted
Caused by: java.net.SocketException: Broken pipe (Write failed)
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
        at java.io.BufferedOutputStream.write(BufferedOutputStream.java:126)
        at org.apache.thrift.transport.TIOStreamTransport.write(TIOStreamTransport.java:145)
        ... 38 common frames omitted

Best Regards,

Choon Kiat

[#2]

There could be a problem with running the SQL in your Hive cluster. I would suggest checking the Hive logs for any relevant errors when NiFi flow is triggered. Another thing to check is the FlowFile itself to make sure it has the data that Hive table would be expected (i.e. schema matches). You can do this by forwarding the failed FlowFiles to a rejected flow.

[#3]

Hi Aakulov,

Thanks for your advice, I checked it could be the connection error after restart my hive services the error above resolve but I observe the new error code as below:

Could you advice ?

2021-01-15 13:40:44,503 WARN [Timer-Driven Process Thread-5] o.apache.nifi.processors.hive.PutHiveQL PutHiveQL[id=f821e7aa-0176-1000-9088-506b00a72e66] Administratively yielding PutHiveQL_Listings after rolling back due to org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=52458c75-cdb5-415d-968b-d7b65fe6e54b,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1610689175761-2338, container=default, section=290], offset=139, length=139],offset=0,name=52458c75-cdb5-415d-968b-d7b65fe6e54b,size=139] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask:
2021-01-15 13:40:44,503 ERROR [Timer-Driven Process Thread-5] o.apache.nifi.processors.hive.PutHiveQL PutHiveQL[id=f821e7aa-0176-1000-9088-506b00a72e66] Failed to process session due to org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=52458c75-cdb5-415d-968b-d7b65fe6e54b,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1610689175761-2338, container=default, section=290], offset=139, length=139],offset=0,name=52458c75-cdb5-415d-968b-d7b65fe6e54b,size=139] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask: org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=52458c75-cdb5-415d-968b-d7b65fe6e54b,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1610689175761-2338, container=default, section=290], offset=139, length=139],offset=0,name=52458c75-cdb5-415d-968b-d7b65fe6e54b,size=139] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=52458c75-cdb5-415d-968b-d7b65fe6e54b,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1610689175761-2338, container=default, section=290], offset=139, length=139],offset=0,name=52458c75-cdb5-415d-968b-d7b65fe6e54b,size=139] due to java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
        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:226)
        at org.apache.nifi.processor.util.pattern.Put.putFlowFiles(Put.java:60)
        at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:103)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$onTrigger$6(PutHiveQL.java:295)
        at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:120)
        at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
        at org.apache.nifi.processors.hive.PutHiveQL.onTrigger(PutHiveQL.java:295)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1174)
        at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
        at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
        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:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
        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 sun.reflect.GeneratedMethodAccessor789.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.access$100(StandardControllerServiceInvocationHandler.java:38)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240)
        at com.sun.proxy.$Proxy227.execute(Unknown Source)
        at org.apache.nifi.processors.hive.PutHiveQL.lambda$null$3(PutHiveQL.java:254)
        at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)
        ... 18 common frames omitted
2021-01-15 13:40:45,504 INFO [Timer-Driven Process Thread-8] o.a.nifi.dbcp.hive.HiveConnectionPool HiveConnectionPool[id=ffd816d0-0176-1000-e684-cec6f778cb48] Simple Authentication
2021-01-15 13:40:45,504 INFO [Timer-Driven Process Thread-8] hive.ql.parse.ParseDriver Parsing command: insert into transformed_db.tbl_airbnb_listing_transformed
select a.*, 20210113 partition_date_id from
staging_db.etbl_raw_airbnb_listing a
2021-01-15 13:40:45,505 INFO [Timer-Driven Process Thread-8] hive.ql.parse.ParseDriver Parse Completed

[#4]

Two things to check:

1. Does your nifi service user account have permissions on the table and hdfs location where it's trying to do the insert;

2. Your Hive SQL statement here looks a bit off to me:

insert into transformed_db.tbl_airbnb_listing_transformed
select a.*, 20210113 partition_date_id from
staging_db.etbl_raw_airbnb_listing a

Is 20210113 a column name? Are you missing a comma between that and parition_date_id? Is your source staging table partitioned? If you are trying to select only a specific date, than the syntax to do that is different.