# Hive to elastic :- EsHadoopNoNodesLeftException **URL:** \ **Category:** Elasticsearch\ **Tags:** es-hadoop\ **Created:** [August 14, 2018, 5:04pm UTC](https://discuss.elastic.co/t/hive-to-elastic-eshadoopnonodesleftexception/144375 "2018-08-14T17:04:14Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Amandeep\_Singh1](https://avatars.discourse-cdn.com/v4/letter/a/6f9a4e/32.png) [@Amandeep\_Singh1](https://discuss.elastic.co/u/Amandeep_Singh1)\ **Post date:** [August 14, 2018, 5:04pm UTC](https://discuss.elastic.co/t/hive-to-elastic-eshadoopnonodesleftexception/144375/1 "2018-08-14T17:04:14Z")
Trying to connect HIve to elastic added `elasticsearch-hadoop-6.3.2.jar elasticsearch-hadoop-6.3.2-javadoc.jar elasticsearch-hadoop-6.3.2-sources.jar` to`/usr/lib/hive/auxlib` The query for creating the table. ``` CREATE EXTERNAL TABLE prediction ( opid bigint, egmcogsspread double, egmnrpred double, egmshippred double, modelnum int, bclgid int, eventdate string) STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' TBLPROPERTIES('es.resource' = 'prediction/document', 'es.nodes' = '10.228.164.125', 'es.mapping.id' = ‘opid’, ’es.index.auto.create' = 'true','es.nodes.discovery' = 'true','es.nodes.client.only' = 'false’, ); ``` `desc tblegm` opid bigin egmcogspred double egmnrpred double egmshippred double modelnum int bclgid int eventdate string query to pass data: `INSERT OVERWRITE table prediction select * from tblegm where eventdate="2018-07-05" limit 100;` Error: Error: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row (tag=0) {"key":{},"value":{"\_col0":2662420715,"\_col1":25.42,"\_col2":34.05,"\_col3":7.9464,"\_col4":null,"\_col5":1}} at org.apache.hadoop.hive.ql.exec.mr.ExecReducer.reduce(ExecReducer.java:265) ``` at org.apache.hadoop.mapred.ReduceTask.runOldReducer(ReduceTask.java:444) at org.apache.hadoop.mapred.ReduceTask.run(ReduceTask.java:392) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1920) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158) Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row (tag=0) {"key":{},"value":{"_col0":2662420715,"_col1":25.42,"_col2":34.05,"_col3":7.9464,"_col4":null,"_col5":1}} at org.apache.hadoop.hive.ql.exec.mr.ExecReducer.reduce(ExecReducer.java:253) ... 7 more Caused by: org.elasticsearch.hadoop.rest.EsHadoopNoNodesLeftException: Connection error (check network and/or proxy settings)- all nodes failed; tried [[10.228.164.125:9200]] at org.elasticsearch.hadoop.rest.NetworkClient.execute(NetworkClient.java:149) at org.elasticsearch.hadoop.rest.RestClient.execute(RestClient.java:380) at org.elasticsearch.hadoop.rest.RestClient.execute(RestClient.java:344) at org.elasticsearch.hadoop.rest.RestClient.execute(RestClient.java:348) at org.elasticsearch.hadoop.rest.RestClient.get(RestClient.java:158) at org.elasticsearch.hadoop.rest.RestClient.getHttpNodes(RestClient.java:115) at org.elasticsearch.hadoop.rest.InitializationUtils.discoverNodesIfNeeded(InitializationUtils.java:92) at org.elasticsearch.hadoop.rest.RestService.createWriter(RestService.java:580) at org.elasticsearch.hadoop.mr.EsOutputFormat$EsRecordWriter.init(EsOutputFormat.java:173) at org.elasticsearch.hadoop.hive.EsHiveOutputFormat$EsHiveRecordWriter.write(EsHiveOutputFormat.java:58) at org.apache.hadoop.hive.ql.exec.FileSinkOperator.processOp(FileSinkOperator.java:717) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.LimitOperator.processOp(LimitOperator.java:51) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84) at org.apache.hadoop.hive.ql.exec.mr.ExecReducer.reduce(ExecReducer.java:244) ... 7 more ``` Just to add: 1. there are 5 mapping jobs and 1 reducing job. 2. curl [http://10.228.164.125:9200/](http://10.228.164.125:9200/) { "name" : "[elasticclientc2n1.dev.bo1.csnzoo.com](http://elasticclientc2n1.dev.bo1.csnzoo.com)", "cluster\_name" : "logslabbo1", "cluster\_uuid" : "i741QymvTjSLghDqhjQ1rQ", "version" : { "number" : "6.2.3", "build\_hash" : "c59ff00", "build\_date" : "2018-03-13T10:06:29.741383Z", "build\_snapshot" : false, "lucene\_version" : "7.2.1", "minimum\_wire\_compatibility\_version" : "5.6.0", "minimum\_index\_compatibility\_version" : "5.0.0" }, "tagline" : "You Know, for Search" } alternative table properties tried 1. setting node to [elasticclientc2n1.dev.bo1.csnzoo.com](http://elasticclientc2n1.dev.bo1.csnzoo.com) 2. `TBLPROPERTIES('es.resource'='prediction/document', 'es.nodes'= 'elasticclientc2n1.dev.bo1.csnzoo.com', 'es.mapping.id'= 'opid', 'es.index.auto.create'='true', 'es.nodes.wan.only'='true');` ---
**Author:** ![james.baiera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/james.baiera/32/10209_2.png) [@james.baiera](https://discuss.elastic.co/u/james.baiera)\ **Post date:** [August 17, 2018, 8:14pm UTC](https://discuss.elastic.co/t/hive-to-elastic-eshadoopnonodesleftexception/144375/2 "2018-08-17T20:14:31Z")
> [@Amandeep\_Singh1](#): > > added > `elasticsearch-hadoop-6.3.2.jar elasticsearch-hadoop-6.3.2-javadoc.jar elasticsearch-hadoop-6.3.2-sources.jar` > to `/usr/lib/hive/auxlib` You shouldn't have to add the javadoc or the sources jars at all. Is there anything else that is logged in the HiveServer logs? When ES-Hadoop cannot establish a connection, it should log at error level the problem with the underlying http connection. Additionally, you can try enabling `TRACE` level logging on the `org.elasticsearch.hadoop.rest.commonshttp` package to see the wire contents being sent to Elasticsearch. This is very useful for debugging as well. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 14, 2018, 8:14pm UTC](https://discuss.elastic.co/t/hive-to-elastic-eshadoopnonodesleftexception/144375/3 "2018-09-14T20:14:42Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.