# Select es-hadoop table from hive failed

**URL:** <https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [March 24, 2016, 10:04am UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330 "2016-03-24T10:04:44Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joe\_Yang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_yang/32/20719_2.png) [@Joe\_Yang](https://discuss.elastic.co/u/Joe_Yang)\
**Post date:** [March 24, 2016, 10:04am UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/1 "2016-03-24T10:04:44Z")

</div>

Dear All:

We just creatd a external table from hive successfully:  
CREATE EXTERNAL TABLE user01(id BIGINT, name STRING) STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'  
TBLPROPERTIES('es.resource' = 'radio/artists','es.index.auto.create' = 'true','es.nodes'='192.168.112.172','es.port'='9200');

but when we query it , it failed with the following error, any advice would be appreciated,  
hive\> select \* from user01;  
OK  
Failed with exception java.io.IOException:org.elasticsearch.hadoop.rest.EsHadoopNoNodesLeftException: Connection error (check network and/or proxy settings)- all nodes failed; tried [[127.0.0.1:9200]]  
Time taken: 0.108 seconds  
hive\>

---

<div class="post-metadata">

**Author:** ![Joe\_Yang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_yang/32/20719_2.png) [@Joe\_Yang](https://discuss.elastic.co/u/Joe_Yang)\
**Post date:** [March 25, 2016, 2:52am UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/2 "2016-03-25T02:52:14Z")

</div>

any prompt comment would be appreciated...

---

<div class="post-metadata">

**Author:** ![Joe\_Yang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_yang/32/20719_2.png) [@Joe\_Yang](https://discuss.elastic.co/u/Joe_Yang)\
**Post date:** [March 29, 2016, 2:48am UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/3 "2016-03-29T02:48:09Z")

</div>

Dear Alll,

any advice would be welcome ...

if we insert an internal table into this external one, the error message is as follows:

hive\> insert overwrite table user01 select \* from user\_source;  
Query ID = csi\_20160329111717\_af94bd46-34e9-44c8-b29b-51892eeb3c25  
Total jobs = 1  
Launching Job 1 out of 1  
Number of reduce tasks is set to 0 since there's no reduce operator  
org.apache.hadoop.ipc.RemoteException(java.io.IOException): File /tmp/hadoop-yarn/staging/csi/.staging/job\_145872875 6355\_0018/libjars/accumulo-fate-1.6.0.jar could only be replicated to 0 nodes instead of minReplication (=1). There are 1 datanode(s) running and 1 node(s) are excluded in this operation.  
at org.apache.hadoop.hdfs.server.blockmanagement.BlockManager.chooseTarget4NewBlock(BlockManager.java:1550)  
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getNewBlockTargets(FSNamesystem.java:3110)  
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getAdditionalBlock(FSNamesystem.java:3034)  
at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.addBlock(NameNodeRpcServer.java:723)  
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.addBlock(ClientNamenodePro tocolServerSideTranslatorPB.java:492)  
at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingM ethod(ClientNamenodeProtocolProtos.java)  
at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:616)  
at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:969)  
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2049)  
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2045)  
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:1657)  
at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2043)

```
    at org.apache.hadoop.ipc.Client.call(Client.java:1476)
    at org.apache.hadoop.ipc.Client.call(Client.java:1407)
    at org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:229)
    at com.sun.proxy.$Proxy15.addBlock(Unknown Source)
    at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.addBlock(ClientNamenodeProtocolTrans latorPB.java:418)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:187)
    at org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:102)
    at com.sun.proxy.$Proxy16.addBlock(Unknown Source)
    at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.locateFollowingBlock(DFSOutputStream.java:1430)
    at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.nextBlockOutputStream(DFSOutputStream.java:1226)
    at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.run(DFSOutputStream.java:449)

```

Job Submission failed with exception 'org.apache.hadoop.ipc.RemoteException(File /tmp/hadoop-yarn/staging/csi/.stagi ng/job\_1458728756355\_0018/libjars/accumulo-fate-1.6.0.jar could only be replicated to 0 nodes instead of minReplicat ion (=1). There are 1 datanode(s) running and 1 node(s) are excluded in this operation.  
at org.apache.hadoop.hdfs.server.blockmanagement.BlockManager.chooseTarget4NewBlock(BlockManager.java:1550)  
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getNewBlockTargets(FSNamesystem.java:3110)  
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getAdditionalBlock(FSNamesystem.java:3034)  
at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.addBlock(NameNodeRpcServer.java:723)  
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.addBlock(ClientNamenodePro tocolServerSideTranslatorPB.java:492)  
at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingM ethod(ClientNamenodeProtocolProtos.java)  
at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:616)

---

<div class="post-metadata">

**Author:** ![Joe\_Yang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_yang/32/20719_2.png) [@Joe\_Yang](https://discuss.elastic.co/u/Joe_Yang)\
**Post date:** [March 29, 2016, 7:22am UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/4 "2016-03-29T07:22:26Z")

</div>

hadoop:2.7.1  
hive: 1.2.1  
es-hadoop: 2.1.1

---

<div class="post-metadata">

**Author:** ![Joe\_Yang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_yang/32/20719_2.png) [@Joe\_Yang](https://discuss.elastic.co/u/Joe_Yang)\
**Post date:** [March 29, 2016, 7:24am UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/5 "2016-03-29T07:24:23Z")

</div>

hive\> CREATE EXTERNAL TABLE user01 (id bigint, name STRING) STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'  
\> TBLPROPERTIES('es.resource' = 'radio/artists', 'es.index.auto.create' = 'true');  
OK  
Time taken: 0.178 seconds  
hive\> INSERT OVERWRITE TABLE user01 SELECT id, name FROM user\_source;  
Query ID = csi\_20160329152402\_516b7db1-38d8-4921-bcae-708b23d73909  
Total jobs = 1  
Launching Job 1 out of 1  
Number of reduce tasks is set to 0 since there's no reduce operator  
Starting Job = job\_1458728756355\_0020, Tracking URL = [http://server1:8088/proxy/application\_1458728756355\_0020/](http://server1:8088/proxy/application_1458728756355_0020/)  
Kill Command = /home/csi/hadoop/bin/hadoop job -kill job\_1458728756355\_0020

it hangs on kill commend

---

<div class="post-metadata">

**Author:** ![Joe\_Yang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_yang/32/20719_2.png) [@Joe\_Yang](https://discuss.elastic.co/u/Joe_Yang)\
**Post date:** [April 1, 2016, 5:05am UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/6 "2016-04-01T05:05:45Z")

</div>

Hello:

Any prompt update would be appreciated, please..

---

<div class="post-metadata">

**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\
**Post date:** [April 5, 2016, 3:07pm UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/7 "2016-04-05T15:07:24Z")

</div>

You are showing different configurations and mixing the errors with the so-called code snippets (By the way, formatting helps).

1. if you don't specify any nodes, es-hadoop will use localhost instead. 99% of the time, this is not what you want.
2. use the latest es-hadoop, namely 2.2.0.
3. check out the troubleshooting page  
[https://www.elastic.co/guide/en/elasticsearch/hadoop/master/troubleshooting.html](https://www.elastic.co/guide/en/elasticsearch/hadoop/master/troubleshooting.html)

---

<div class="post-metadata">

**Author:** ![Joe\_Yang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_yang/32/20719_2.png) [@Joe\_Yang](https://discuss.elastic.co/u/Joe_Yang)\
**Post date:** [April 5, 2016, 3:27pm UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/8 "2016-04-05T15:27:08Z")

</div>

Thanks Costin, will look into it and update it soon...

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 1:25pm UTC](https://discuss.elastic.co/t/select-es-hadoop-table-from-hive-failed/45330/9 "2017-07-06T13:25:23Z")

</div>