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.

cant create external tables in hive

cant create external tables in hive

Master Collaborator

this used to work but after the elasticsearch upgrade I am unable to create the external table.

hive> list jars;
/usr/hdp/current/hive-client/lib/commons-httpclient-3.0.1.jar
/usr/hdp/2.6.3.0-235/hive/lib/elasticsearch-hadoop-6.1.2.jar
hive> CREATE EXTERNAL TABLE pa_lane_txn_es (
    >     txn_id             BIGINT,
    >     txn_process_date   TIMESTAMP,
    >     transp_id          STRING,
    >     ext_plaza_id       STRING,
    >     ext_lane_id        STRING,
    >     ext_date_time      TIMESTAMP,
    >     toll_amt_charged   FLOAT,
    >     toll_amt_collected FLOAT,
    >     toll_amt_full      FLOAT,
    >     ent_plaza_id STRING,ent_date_time TIMESTAMP,ent_lane_id STRING)
    > STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
    > TBLPROPERTIES('es.resource' = 'lanetxn/txn-details','es.nodes' = 'hadoop1:9200');
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. org.elasticsearch.hadoop.EsHadoopIllegalArgumentException: Cannot detect ES version - typically this happens if the network/Elasticsearch cluster is not accessible or when targeting a WAN/Cloud instance without the proper setting 'es.nodes.wan.only'
hive>

elasticsearch cluster is up

[root@hadoop1 ~]# su - elasticsearch
[elasticsearch@hadoop1 ~]$ curl 'hadoop1:9200/_cat/indices?v'
health status index       uuid                   pri rep docs.count docs.deleted store.size pri.store.size
green  open   shakespeare AQMcHSaoQbWg4jfKbbM7qQ   5   1     111396            0     56.6mb         28.3mb
green  open   .kibana     _LRZY1BQQfSiuLcDCFfkvg   1   1          3            1     32.7kb         16.3kb
[elasticsearch@hadoop1 ~]$ curl 'hadoop1:9200'
{
  "name" : "hadoop1",
  "cluster_name" : "ftes",
  "cluster_uuid" : "aLazB94yTIez9iO1-7eoAg",
  "version" : {
    "number" : "6.1.2",
    "build_hash" : "5b1fea5",
    "build_date" : "2018-01-10T02:35:59.208Z",
    "build_snapshot" : false,
    "lucene_version" : "7.1.0",
    "minimum_wire_compatibility_version" : "5.6.0",
    "minimum_index_compatibility_version" : "5.0.0"
  },
  "tagline" : "You Know, for Search"
}
1 REPLY 1
Highlighted

Re: cant create external tables in hive

Master Collaborator

found out the issue, hive only expects one version of elasticsearch-hadoop jar in the hive/lib folder . I deleted the older version and it works fine now

Don't have an account?
Coming from Hortonworks? Activate your account here