Support Questions
Find answers, ask questions, and share your expertise

Unable to Send Data from Hive external to hive managed table using selectHiveQL and PutHiveStreaming Processors

New Contributor

Hi,
I tried to insert data from external table to another table .

And the attachments shows the properties settings of the processors

properties of selectHiveQL Processor

106445-screenshot-33.png

properties of putHiveStreaming

106446-screenshot-34.png

Hive Connection pool Properties

106451-screenshot-35.png

The error is as follows:

2019-02-19 10:01:19,398 ERROR [Timer-Driven Process Thread-1] o.a.n.processors.hive.PutHiveStreaming PutHiveStreaming[id=3b0d1da8-2021-122a-3bfe-1e7312bcfe87] Error connecting to Hive endpoint: table hive_emp_sample at thrift://ip-172-31-39-12.ap-southeast-1.compute.internal:9083

2019-02-19 10:01:19,399 ERROR [Timer-Driven Process Thread-1] o.a.n.processors.hive.PutHiveStreaming PutHiveStreaming[id=3b0d1da8-2021-122a-3bfe-1e7312bcfe87] Hive Streaming connect/write error, flow file will be penalized and routed to retry. org.apache.nifi.util.hive.HiveWriter$ConnectFailure: Failed connecting to EndPoint {metaStoreUri='thrift://ip-172-31-39-12.ap-southeast-1.compute.internal:9083', database='users', table='hive_emp_sample', partitionVals=[] }: org.apache.nifi.processors.hive.PutHiveStreaming$ShouldRetryException: Hive Streaming connect/write error, flow file will be penalized and routed to retry. org.apache.nifi.util.hive.HiveWriter$ConnectFailure: Failed connecting to EndPoint {metaStoreUri='thrift://ip-172-31-39-12.ap-southeast-1.compute.internal:9083', database='users', table='hive_emp_sample', partitionVals=[] }

org.apache.nifi.processors.hive.PutHiveStreaming$ShouldRetryException: Hive Streaming connect/write error, flow file will be penalized and routed to retry. org.apache.nifi.util.hive.HiveWriter$ConnectFailure: Failed connecting to EndPoint {metaStoreUri='thrift://ip-172-31-39-12.ap-southeast-1.compute.internal:9083', database='users', table='hive_emp_sample', partitionVals=[] }


at org.apache.nifi.processors.hive.PutHiveStreaming.lambda$onHiveRecordsError$1(PutHiveStreaming.java:629)

at org.apache.nifi.processor.util.pattern.ExceptionHandler$OnError.lambda$andThen$0(ExceptionHandler.java:54)

at org.apache.nifi.processors.hive.PutHiveStreaming.lambda$onHiveRecordError$2(PutHiveStreaming.java:647)

at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:148)

at org.apache.nifi.processors.hive.PutHiveStreaming$1.process(PutHiveStreaming.java:838)

at org.apache.nifi.controller.repository.StandardProcessSession.read(StandardProcessSession.java:2207)

at org.apache.nifi.controller.repository.StandardProcessSession.read(StandardProcessSession.java:2175)

at org.apache.nifi.processors.hive.PutHiveStreaming.onTrigger(PutHiveStreaming.java:791)

at org.apache.nifi.processors.hive.PutHiveStreaming.lambda$onTrigger$4(PutHiveStreaming.java:657)

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.PutHiveStreaming.onTrigger(PutHiveStreaming.java:657)

at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147)

at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175)

at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)

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:748)

Caused by: org.apache.nifi.util.hive.HiveWriter$ConnectFailure: Failed connecting to EndPoint {metaStoreUri='thrift://ip-172-31-39-12.ap-southeast-1.compute.internal:9083', database='users', table='hive_emp_sample', partitionVals=[] }

at org.apache.nifi.util.hive.HiveWriter.<init>(HiveWriter.java:79)

at org.apache.nifi.util.hive.HiveUtils.makeHiveWriter(HiveUtils.java:46)

at org.apache.nifi.processors.hive.PutHiveStreaming.makeHiveWriter(PutHiveStreaming.java:1137)

at org.apache.nifi.processors.hive.PutHiveStreaming.getOrCreateWriter(PutHiveStreaming.java:1048)

at org.apache.nifi.processors.hive.PutHiveStreaming.access$1000(PutHiveStreaming.java:112)

at org.apache.nifi.processors.hive.PutHiveStreaming$1.lambda$process$2(PutHiveStreaming.java:841)

at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)

... 18 common frames omitted

Caused by: org.apache.hive.hcatalog.streaming.StreamingException: Cannot stream to table that has not been bucketed : {metaStoreUri='thrift://ip-172-31-39-12.ap-southeast-1.compute.internal:9083', database='users', table='hive_emp_sample', partitionVals=[] }

at org.apache.hive.hcatalog.streaming.AbstractRecordWriter.<init>(AbstractRecordWriter.java:71)

at org.apache.hive.hcatalog.streaming.StrictJsonWriter.<init>(StrictJsonWriter.java:62)

at org.apache.nifi.util.hive.HiveWriter.getRecordWriter(HiveWriter.java:85)

at org.apache.nifi.util.hive.HiveWriter.<init>(HiveWriter.java:72)

... 24 common frames omitted


But there is thrift connection in hive which i checked in kylo


i tried another way which was provided in https://community.hortonworks.com/questions/196134/nifi-pull-data-from-hive-external-table-and-load-... .

But here im getting error at generate table fetch and the error is as follows:


ERROR [Timer-Driven Process Thread-1] o.a.n.p.standard.GenerateTableFetch GenerateTableFetch[id=20211238-1dc7-1b0d-a51b-3936d2b035e2] Unable to execute SQL select query SELECT COUNT(*) FROM users.hive_emp due to java.sql.SQLException: Method not supported: java.sql.SQLException: Method not supported

java.sql.SQLException: Method not supported

at org.apache.hive.jdbc.HiveStatement.setQueryTimeout(HiveStatement.java:739)

at org.apache.commons.dbcp.DelegatingStatement.setQueryTimeout(DelegatingStatement.java:249)

at org.apache.commons.dbcp.DelegatingStatement.setQueryTimeout(DelegatingStatement.java:249)

at org.apache.nifi.processors.standard.GenerateTableFetch.onTrigger(GenerateTableFetch.java:275)

at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147)

at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175)

at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)

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:748)

2019-02-19 10:11:48,226 ERROR [Timer-Driven Process Thread-1] o.a.n.p.standard.GenerateTableFetch GenerateTableFetch[id=20211238-1dc7-1b0d-a51b-3936d2b035e2] Error during processing: Method not supported: java.sql.SQLException: Method not supported

java.sql.SQLException: Method not supported

at org.apache.hive.jdbc.HiveStatement.setQueryTimeout(HiveStatement.java:739)

at org.apache.commons.dbcp.DelegatingStatement.setQueryTimeout(DelegatingStatement.java:249)

at org.apache.commons.dbcp.DelegatingStatement.setQueryTimeout(DelegatingStatement.java:249)

at org.apache.nifi.processors.standard.GenerateTableFetch.onTrigger(GenerateTableFetch.java:275)

at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147)

at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175)

at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)

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:748)

2019-02-19 10:11:48,596 INFO [NiFi Web Server-721] o.a.n.c.s.StandardProcessScheduler Stopping GenerateTableFetch[id=20211238-1dc7-1b0d-a51b-3936d2b035e2]

2019-02-19 10:11:48,596 INFO [NiFi Web Server-721] o.a.n.controller.StandardProcessorNode Stopping processor: class org.apache.nifi.processors.standard.GenerateTableFetch

2019-02-19 10:11:48,597 INFO [Timer-Driven Process Thread-2] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling GenerateTableFetch[id=20211238-1dc7-1b0d-a51b-3936d2b035e2] to run

2019-02-19 10:11:52,633 INFO [Flow Service Tasks Thread-2] o.a.n.p.FlowConfigurationArchiveManager Removing old archive file /opt/nifi/data/conf/archive/20190218T094523+0000_flow.xml.gz to reduce storage usage. currentSize=523338079

2019-02-19 10:11:52,635 INFO [Flow Service Tasks Thread-2] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@3eea70bb // Another save pending = false

@Shu can you please help me with these queries.