Search code examples
elasticsearchhiveexternal-tables

Unable to query date field in Hive external table

Totally stuck with fetch data from hive external table. I have done below till now.

  1. I had a Managed table with date field whose value is 2014-10-23.

  2. I created external table to store data in elastic search like below

    create external table ext3 ( run_date date) ROW FORMAT SERDE 'org.elasticsearch.hadoop.hive.EsSerDe' STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' TBLPROPERTIES('es.resource' = 'dfs/ext3', 'es.field.read.empty.as.null' = 'true','es.nodes'=);

  3. inserting one row in external table to create the Elastic Search Index and mapping.

Problem 1: My Elastic search field is created as string.

  1. Later I changed the mapping in elastic search to date.

    "run_date":{"type":"date", "format": "yyyy-MM-ddZ", "index": "not_analyzed"}

  2. re inserted the data in external table. when I query Elastic search its very fine. value is displayed as '2014-10-23+08:00'

Problem 2 When I query data for external table like select count(*) from ext3 I am getting below error.

2015-04-17 18:45:34,254 FATAL [main] org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row [Error getting row data with exception java.lang.ClassCastException: org.apache.hadoop.hive.serde2.io.TimestampWritable cannot be cast to org.apache.hadoop.hive.serde2.io.DateWritable
    at org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableDateObjectInspector.getPrimitiveWritableObject(WritableDateObjectInspector.java:38)
    at org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:259)
    at org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:349)
    at org.apache.hadoop.hive.serde2.SerDeUtils.getJSONString(SerDeUtils.java:193)
    at org.apache.hadoop.hive.serde2.SerDeUtils.getJSONString(SerDeUtils.java:179)
    at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:545)

Guys please help me on this, whole day is wasted. I have another external table with more data, I need to join these two tables and create a view to have my consolidated data ready for analysis.

Solution

  • I think the error gives a clue to your problem:

    Error getting row data with exception java.lang.ClassCastException:
  org.apache.hadoop.hive.serde2.io.TimestampWritable cannot be cast to 
  org.apache.hadoop.hive.serde2.io.DateWritable

    You have a date field in your hive table but the data you have inserted is of the type timestamp.

    Re-create your table (or a new one if you don't want to replace it)

    CREATE EXTERNAL TABLE ext3 ( run_date timestamp )
ROW FORMAT SERDE 'org.elasticsearch.hadoop.hive.EsSerDe' 
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES('es.resource' = 'dfs/ext3', 'es.field.read.empty.as.null' = 'true','es.nodes'=);