# Select query from External elasticsearch table throwing classcastexception

**URL:** <https://discuss.elastic.co/t/select-query-from-external-elasticsearch-table-throwing-classcastexception/122088>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [March 1, 2018, 2:05pm UTC](https://discuss.elastic.co/t/select-query-from-external-elasticsearch-table-throwing-classcastexception/122088 "2018-03-01T14:05:03Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![adarsh\_ks](https://avatars.discourse-cdn.com/v4/letter/a/9e8a1a/32.png) [@adarsh\_ks](https://discuss.elastic.co/u/adarsh_ks)\
**Post date:** [March 1, 2018, 2:05pm UTC](https://discuss.elastic.co/t/select-query-from-external-elasticsearch-table-throwing-classcastexception/122088/1 "2018-03-01T14:05:03Z")

</div>

Hi,

I am using Elasticsearch in Hadoop version 6.2.0 to connect Hive and elasticsearch.

My queries is as below.

**Hive Tables**

```
CREATE TABLE IF NOT EXISTS User_Details(
Id bigint,
username string, 
name string,
address1 string,
address2 string,
city string,
state string
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

CREATE TABLE IF NOT EXISTS temp_User_Orders(
Id bigint,
ItemId bigint,
orderdate String
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

CREATE TABLE IF NOT EXISTS User_Orders(
Id bigint,
ItemId bigint,
orderdate timestamp
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

insert into table User_Orders select Id,ItemId,from_unixtime(unix_timestamp(orderdate, 'yyyy-MM-dd')) from temp_User_Orders;

```

**Elasticsearch index mapping overridden as below**

```
curl -XPUT 'localhost:9200/user?pretty' -H 'Content-Type: application/json' -d'
{
  
    "mappings" : {
      "details" : {
        "properties" : {
          "id" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "user1" : {
            "properties" : {
              "name" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "orders" : {
				"type":"nested",
                "properties" : {
                  "itemid" : {
                    "type" : "long"
                  },
                  "orderdate" : {
                    "type" : "date",
					"format": "date_time_no_millis"
                  }
                }
              },
              "shippingaddress" : {
                "properties" : {
                  "address1" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "address2" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "city" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "state" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  }
                }
              },
              "username" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          }
        }
      }
    }

}
'

```

**Elasticsearch external table query**

```
CREATE EXTERNAL TABLE IF NOT EXISTS User_Nested_Table_poc(
    Id STRING,
    user1 STRUCT<username: STRING, name:STRING,shippingaddress:struct<address1:STRING,address2:STRING,city:STRING,state:STRING>, orders:array<STRUCT<ItemId:bigint,orderdate:timestamp>>>
) STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' 
TBLPROPERTIES('es.nodes' = 'localhost',
'es.resource' = 'user/details',
'es.mapping.id' = 'Id');

insert overwrite table User_Nested_Table_poc 
select ud.Id,
named_struct('username',ud.username,
'name',ud.name,
'shippingaddress',named_struct('address1',ud.address1,
'address2',ud.address2,
'city',ud.city,
'state',ud.state),
'orders',array(named_struct('ItemId',uo.ItemId,'orderdate',cast(uo.orderdate as timestamp)))
)
from User_Details ud JOIN User_Orders uo on ud.Id=uo.Id;

```

After insertion, I am unable to execute the select query for external table from Hive and is throwing the below error.

```
select * from user_nested_table_poc;

Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.Text cannot be cast to org.apache.hadoop.hive.serde2.io.TimestampWritable
Time taken: 0.042 seconds

```

I am stuck in this for the past 2 days, please help me resolve this issue.

Regards,  
Adarsh K S

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 29, 2018, 2:05pm UTC](https://discuss.elastic.co/t/select-query-from-external-elasticsearch-table-throwing-classcastexception/122088/2 "2018-03-29T14:05:20Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.