# Hive read operation fails when stored as external table pointing to Elastic search location
**URL:** https://discuss.elastic.co/t/hive-read-operation-fails-when-stored-as-external-table-pointing-to-elastic-search-location/130135
**Category:** Elasticsearch
**Tags:** es-hadoop
**Created:** [May 1, 2018, 4:14pm UTC](https://discuss.elastic.co/t/hive-read-operation-fails-when-stored-as-external-table-pointing-to-elastic-search-location/130135 "2018-05-01T16:14:13Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@bvk](https://discuss.elastic.co/u/bvk)
#### Post date: [May 1, 2018, 4:14pm UTC](https://discuss.elastic.co/t/hive-read-operation-fails-when-stored-as-external-table-pointing-to-elastic-search-location/130135/1 "2018-05-01T16:14:13Z")
Hi,
I have 5.10.2 CDH Kerberos enabled 6 node Hadoop cluster and I have installed Elasticsearch 6.1.4 on Edge-node (Single node ES).
I am trying to to load data from hive to Elasticsearch and read it in hive.
for ex: I have a table customer (a managed hive table). I am creating an external table like below:
CREATE EXTERNAL TABLE customer\_es(
cust\_id string,
polcy\_policy\_num string,
prod\_plan\_cd string,
comp\_cd int,
prod\_nm string,
polcy\_pol\_status int,
rider\_prm int,
gentype\_cd string,
prm\_pymt\_mde\_id int,
cust\_occupation string,
marstatus\_id string,
cust\_no\_of\_dpnds int,
polcycov\_cvrg\_amt double,
custaddrs\_add\_line1 string,
custaddrs\_add\_line2 int,
custaddrs\_add\_line3 string,
city\_cd string,
state\_cd string,
zip\_cd int,
tapestry\_segment string,
propensity\_score int,
rank1\_product string,
rank2\_product string,
rank3\_product string,
rank4\_product string,
rank5\_product string,
hh\_income int,
age int,
estimatedinforce\_anp double,
propensity\_threshold string)
ROW FORMAT SERDE 'org.elasticsearch.hadoop.hive.EsSerDe'
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES ('es.resource' = 'customer/custdata','es.nodes'='ab-edge-node1:9200','es.index.auto.create'='true','es.mapping.id'='cust\_id');
Once this table is created, I will load the data from hive to ES as below:
insert overwrite table customer\_es select \* from customer ;
logs:
Query ID = hduser\_20180501151212\_1e2da089-7411-48fb-beb8-2498c043ef21
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\_1524755329957\_0130, Tracking URL = [http://ab-master-node2:8088/proxy/application\_1524755329957\_0130/](http://ab-master-node2:8088/proxy/application_1524755329957_0130/)
Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job\_1524755329957\_0130
Hadoop job information for Stage-0: number of mappers: 1; number of reducers: 0
2018-05-01 15:12:33,113 Stage-0 map = 0%, reduce = 0%
2018-05-01 15:12:45,737 Stage-0 map = 100%, reduce = 0%, Cumulative CPU 1.88 sec
MapReduce Total cumulative CPU time: 1 seconds 880 msec
Ended Job = job\_1524755329957\_0130
MapReduce Jobs Launched:
Stage-Stage-0: Map: 1 Cumulative CPU: 1.88 sec HDFS Read: 12024 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 880 msec
OK
Time taken: 34.634 seconds
In the above log HDFS read i see some no. of bytes read, but write is zero. However when i curl to my elasticsearch node and see, a new index (customer) with data is created.
Now the issue is when i try to do : Select \* from customer\_es; I get below error
Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.io.IntWritable
Need some help to resolve this issue.
---
### Author:  [@james.baiera](https://discuss.elastic.co/u/james.baiera)
#### Post date: [May 1, 2018, 7:35pm UTC](https://discuss.elastic.co/t/hive-read-operation-fails-when-stored-as-external-table-pointing-to-elastic-search-location/130135/2 "2018-05-01T19:35:58Z")
Can you provide some mappings and test data to reproduce the error? My guess is that you have a field that is mapped as a Long in Elasticsearch, but is defined as an Int in your Hive DDL.
---
### Author:  [@bvk](https://discuss.elastic.co/u/bvk)
#### Post date: [May 2, 2018, 5:49am UTC](https://discuss.elastic.co/t/hive-read-operation-fails-when-stored-as-external-table-pointing-to-elastic-search-location/130135/3 "2018-05-02T05:49:47Z")
Hi James,
Thanks for ur reply.. PFB the sample data
01 CLAUDIA-MCRAE-11161960-531382437,ATR78038T,F,11,FLEX-PREM-ANNUITY,99,302,M,0,NA,NA,0,15077.87,4120 HELENS POUROFF AVE,0,,FREDERICK,MD,89085,?��,1,Product1,Product4,Product5,Product7," Product6",0,36,1200.52,High propensity
01 ILEEN-MCCALLISTER-11181970-762786023,ATR125570T,F,10,FLEX-PREM-ANNUITY,49,906,M,0,NA,Married,0,0,4421 OASIS HILL AVE,0,,ROSEDALE,MD,89085,?��,1,Product2,Product5,Product1,Product7," Product6",0,41,1204.52,High propensity
More interestingly i created one more table with just 2 rows and 3 columns. insert overwrite happens and data is available in ES. But when i query the table neither i get output nor error.
And I have not mapped explicitly any columns other than cust\_id in DDL of external table. Can you please help me with the mappings if you see that as a problem?
Thanks in advance !!!
---
### Author:  [@bvk](https://discuss.elastic.co/u/bvk)
#### Post date: [May 2, 2018, 6:07am UTC](https://discuss.elastic.co/t/hive-read-operation-fails-when-stored-as-external-table-pointing-to-elastic-search-location/130135/4 "2018-05-02T06:07:45Z")
Hi James,
PFB the mappings.
"properties": {
"age": {
"type": "long"
},
"city\_cd": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"comp\_cd": {
"type": "long"
},
"cust\_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"cust\_no\_of\_dpnds": {
"type": "long"
},
"cust\_occupation": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"custaddrs\_add\_line1": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"custaddrs\_add\_line2": {
"type": "long"
},
"custaddrs\_add\_line3": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"estimatedinforce\_anp": {
"type": "float"
},
"gentype\_cd": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"hh\_income": {
"type": "long"
},
"marstatus\_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"polcy\_pol\_status": {
"type": "long"
},
"polcy\_policy\_num": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"polcycov\_cvrg\_amt": {
"type": "float"
},
"prm\_pymt\_mde\_id": {
"type": "long"
},
"prod\_nm": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"prod\_plan\_cd": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"propensity\_score": {
"type": "long"
},
"propensity\_threshold": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"rank1\_product": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"rank2\_product": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"rank3\_product": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"rank4\_product": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"rank5\_product": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore\_above": 256
}
}
},
"rider\_prm": {
"type": "long"
},
```
Due to charters limitation.. removed few mappings..
And I see, as you rightly pointed out some of the columns are mapped to long in ES and they are int in hive.. It would be great if you can help me with hive DDL to map it correctly to ES.
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 30, 2018, 6:07am UTC](https://discuss.elastic.co/t/hive-read-operation-fails-when-stored-as-external-table-pointing-to-elastic-search-location/130135/5 "2018-05-30T06:07:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.