# Hive cannot read @timestamp from ES which format is unix time
**URL:** https://discuss.elastic.co/t/hive-cannot-read-timestamp-from-es-which-format-is-unix-time/806
**Category:** Elasticsearch
**Tags:** es-hadoop
**Created:** [May 17, 2015, 11:35am UTC](https://discuss.elastic.co/t/hive-cannot-read-timestamp-from-es-which-format-is-unix-time/806 "2015-05-17T11:35:13Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@stoneling](https://discuss.elastic.co/u/stoneling)
#### Post date: [May 17, 2015, 11:35am UTC](https://discuss.elastic.co/t/hive-cannot-read-timestamp-from-es-which-format-is-unix-time/806/1 "2015-05-17T11:35:13Z")
Hive cannot read @timestamp from ES which format is unix time
hive require timestamp format must be yyyy-mm-dd hh:mm:ss,how to read unix time .
I have an apache Hadoop instance 2.5.2 and Hive 0.13.1
I have installed Elasticsearch 1.4.4 from the tar file and elasticsearch starts fine.
I have added elasticsearch-hadoop-2.1.0.BUILD-20150515.023716-401.jar in hadoop and Im refering to it from hive-site.xml.
I also added this jar fil in the hive/lib folder.
I have created a table in Hive for ES:
CREATE EXTERNAL TABLE bicon.artists1 (
test timestamp)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES('es.resource' = 'radio/artists1','es.index.auto.create'='true','es.mapping.names'='test:@timestamp');
the error: timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
I have created a table in Hive for ES:
CREATE EXTERNAL TABLE bicon.artists1 (
test string)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES('es.resource' = 'radio/artists1','es.index.auto.create'='true','es.mapping.names'='test:@timestamp');
the error:org.apache.hadoop.hive.serde2.io.TimestampWritable cannot to org.apache.hadoop.io.Text
other type all have the same question;
What am I missing? Thanks in advance!
---
### Author:  [@costin](https://discuss.elastic.co/u/costin)
#### Post date: [May 20, 2015, 8:57am UTC](https://discuss.elastic.co/t/hive-cannot-read-timestamp-from-es-which-format-is-unix-time/806/2 "2015-05-20T08:57:22Z")
> [@stoneling](#):
>
> the error:org.apache.hadoop.hive.serde2.io.TimestampWritable cannot to org.apache.hadoop.io.Text
>
> other type all have the same question;
I'm afraid I don't understand what you are trying to say.
The error (which seems incomplete) indicates there's a type mismatch between the type read from Elasticsearch and the one in Hive. This typically occurs if the timestamp inside Elasticsearch is not properly created - this can occur with automatic detection as explained here [1].
Double check that the mapping in Elasticsearch properly indicates the date field as timestamp; likely it is mapped as String only.
[1] [https://www.elastic.co/guide/en/elasticsearch/hadoop/2.1.Beta/mapping.html#mapping](https://www.elastic.co/guide/en/elasticsearch/hadoop/2.1.Beta/mapping.html#mapping)
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 1:28pm UTC](https://discuss.elastic.co/t/hive-cannot-read-timestamp-from-es-which-format-is-unix-time/806/3 "2017-07-06T13:28:22Z")