Support Questions
Find answers, ask questions, and share your expertise

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;
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'

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
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"

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.