Hi All,
I am currently working with Elasticsearch - Hive integration . I have created the index and loaded the data successfully, with the below mapping...
curl -XPOST localhost:9200/sales_v3 -d '{
"settings" : {
"number_of_shards" : 3,
"number_of_replicas" : 1
},
"mappings" : {
"fact" : {
"_source" : { "enabled" : false },
"properties" : {
"PROD_ID" : { "type" : "integer" },
"CUST_ID" : { "type" : "integer" },
"TIME_ID" : { "type" : "date", "format" : "yyyy-MM-dd" },
"CHANNEL_ID" : { "type" : "text" },
"PROMO_ID" : { "type" : "integer" },
"QUANTITY_SOLD" : { "type" : "text" },
"AMOUNT_SOLD" : { "type" : "double" },
"UNIT_COST" : { "type" : "double" },
"UNIT_PRICE" : { "type" : "double" }
}
}
}
}'
my data in json file is as below -
head -6 /tmp/salesv3_newe.json
{ "index" : { "_index" : "sales_v3", "_type" : "fact", "_id" : "1"} }
{ "prod_id": 13, "cust_id": 987, "time_id": "1998-01-10", "channel_id": 3, "promo_id": 999, "quantity_sold": 1, "amount_sold": 1232.16, "unit_cost": 783.03, "unit_price": "1232.16" }
{ "index" : { "_index" : "sales_v3", "_type" : "fact", "_id" : "2"} }
{ "prod_id": 13, "cust_id": 1660, "time_id": "1998-01-10", "channel_id": 3, "promo_id": 999, "quantity_sold": 1, "amount_sold": 1232.16, "unit_cost": 783.03, "unit_price": "1232.16" }
{ "index" : { "_index" : "sales_v3", "_type" : "fact", "_id" : "3"} }
{ "prod_id": 13, "cust_id": 1762, "time_id": "1998-01-10", "channel_id": 3, "promo_id": 999, "quantity_sold": 1, "amount_sold": 1232.16, "unit_cost": 783.03, "unit_price": "1232.16" }
Later I have created my Hive external table as below -
CREATE EXTERNAL TABLE sales (
prod_id INT,
cust_id INT,
time_id TIMESTAMP,
channel_id STRING,
promo_id INT,
quantity_sold STRING,
amount_sold DOUBLE,
unit_cost DOUBLE,
unit_price DOUBLE)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES('es.nodes' = 'localhost:9200', 'es.resource' = 'sales_v3/fact', 'es.query' = '?q=*');
I am using the Elasticsearch 6.2.4 and the same elasticsearch-hadoop jar
ls -l /usr/lib/hive/lib/elastic*
-rwxr-xr-x 1 root root 890273 Feb 25 12:34 /usr/lib/hive/lib/elasticsearch-hadoop-6.2.4.jar
When I query the data - this is error message I see. How would I resolve this. Please help and Thanks in advance.
> select * from sales;
OK
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.Text
Time taken: 0.473 seconds
Thanks
ksivaus