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.

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

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.