Hive cannot read @timestamp from ES which format is unix time


#1

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!


(Costin Leau) #2

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


(system) #3