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:
- there are 5 mapping jobs and 1 reducing job.
- curl http://10.228.164.125:9200/
{
"name" : "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
- setting node to elasticclientc2n1.dev.bo1.csnzoo.com
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');