Trouble with Timestamp format '"dd-MM-yyyy HH:mm:ss"

omar_ben_salem · April 14, 2015, 9:19am

Hi,

I have trouble with the timestamp format required for elasticsearch.

I have data in my hadoop that I want to load into ES.
My date field was first as this : "EEE, dd MMM yyyy HH:mm:ss Z"

I've transformed that with talend like this :

https://lh3.googleusercontent.com/-rzoBkk29LMM/VSzY7w4kSFI/AAAAAAAAAHA/Uq3XtZEdsyI/s1600/Capture.PNG

Now if I try to load my data into ES, it keeps on mapping it as a string
and not as a timestamp as it should be !

This is what I've done :

1)CREATE TABLE xx ( subject STRING, author string, sendingTime string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA INPATH '/user/hive/out.csv' OVERWRITE INTO TABLE mails;

2)CREATE EXTERNAL TABLE es_xx (subject string,author string, sendingTime
string)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES('es.resource' = 'xx/xx',

'es.nodes'='xx',

          'es.mapping.names' = 'date:@timestamp');

INSERT OVERWRITE TABLE es_xx SELECT s.subject, s.author, s.sendingTime
from xx s;

And this how it was mapped :

{"xx":{"mappings":{"xx":{"properties":{"author":{"type":"string"},"sendingtime":{"type":"string"},"subject":{"type":"string"}}}}}}

In another example I've transformed my date format like this yyyy-MM-ddTHH:mm:ssZ and that went good !

Is that the only format that ES accept? If so, that have to change !

What should I do to make ES recognize my date field as a timestamp?

If I do a static mapping and transform the sendingtime field into a timestamp, would it work? or the current format has to change?

Hope for a quick response, thanks you all,

best regards,

Omar,

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/2bd9117a-38ba-4da1-a2e1-64462aa21486%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

omar_ben_salem · April 14, 2015, 9:26am

It's " 'es.mapping.names' = 'sendingTime:@timestamp');"
not 'es.mapping.names' = 'date:@timestamp');

On Tuesday, April 14, 2015 at 11:19:13 AM UTC+2, BEN SALEM Omar wrote:

Hi,

I have trouble with the timestamp format required for elasticsearch.

I have data in my hadoop that I want to load into ES.
My date field was first as this : "EEE, dd MMM yyyy HH:mm:ss Z"

I've transformed that with talend like this :

https://lh3.googleusercontent.com/-rzoBkk29LMM/VSzY7w4kSFI/AAAAAAAAAHA/Uq3XtZEdsyI/s1600/Capture.PNG

Now if I try to load my data into ES, it keeps on mapping it as a string
and not as a timestamp as it should be !

This is what I've done :

1)CREATE TABLE xx ( subject STRING, author string, sendingTime string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA INPATH '/user/hive/out.csv' OVERWRITE INTO TABLE mails;

2)CREATE EXTERNAL TABLE es_xx (subject string,author string, sendingTime
string)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES('es.resource' = 'xx/xx',

'es.nodes'='xx',
          'es.mapping.names' = 'date:@timestamp');
INSERT OVERWRITE TABLE es_xx SELECT s.subject, s.author, s.sendingTime
from xx s;

And this how it was mapped :

{"xx":{"mappings":{"xx":{"properties":{"author":{"type":"string"},"sendingtime":{"type":"string"},"subject":{"type":"string"}}}}}}

In another example I've transformed my date format like this yyyy-MM-ddTHH:mm:ssZ and that went good !

Is that the only format that ES accept? If so, that have to change !

What should I do to make ES recognize my date field as a timestamp?

If I do a static mapping and transform the sendingtime field into a timestamp, would it work? or the current format has to change?

Hope for a quick response, thanks you all,

best regards,

Omar,

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/a367c0fd-0426-49d8-b9db-058a345497dd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

jean_frege62 · April 14, 2015, 3:05pm

Hi,

I'm not sure if it will fix your problem but you could try the type
TIMESTAMP for your sendingTime field.
TIMESTAMP type is equivalent to the date elasticsearch type according to

Le mardi 14 avril 2015 11:26:49 UTC+2, BEN SALEM Omar a écrit :

It's " 'es.mapping.names' = 'sendingTime:@timestamp');"
not 'es.mapping.names' = 'date:@timestamp');

On Tuesday, April 14, 2015 at 11:19:13 AM UTC+2, BEN SALEM Omar wrote:
Hi,

I have trouble with the timestamp format required for elasticsearch.

I have data in my hadoop that I want to load into ES.
My date field was first as this : "EEE, dd MMM yyyy HH:mm:ss Z"

I've transformed that with talend like this :

https://lh3.googleusercontent.com/-rzoBkk29LMM/VSzY7w4kSFI/AAAAAAAAAHA/Uq3XtZEdsyI/s1600/Capture.PNG

Now if I try to load my data into ES, it keeps on mapping it as a string
and not as a timestamp as it should be !

This is what I've done :

1)CREATE TABLE xx ( subject STRING, author string, sendingTime string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA INPATH '/user/hive/out.csv' OVERWRITE INTO TABLE mails;

2)CREATE EXTERNAL TABLE es_xx (subject string,author string, sendingTime
string)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES('es.resource' = 'xx/xx',

'es.nodes'='xx',
          'es.mapping.names' = 'date:@timestamp');
INSERT OVERWRITE TABLE es_xx SELECT s.subject, s.author, s.sendingTime
from xx s;

And this how it was mapped :

{"xx":{"mappings":{"xx":{"properties":{"author":{"type":"string"},"sendingtime":{"type":"string"},"subject":{"type":"string"}}}}}}

In another example I've transformed my date format like this yyyy-MM-ddTHH:mm:ssZ and that went good !

Is that the only format that ES accept? If so, that have to change !

What should I do to make ES recognize my date field as a timestamp?

If I do a static mapping and transform the sendingtime field into a timestamp, would it work? or the current format has to change?

Hope for a quick response, thanks you all,

best regards,

Omar,

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/938af0c2-3a03-4f48-94be-21bb2db744a4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

ElasticSearch_Users_ · April 14, 2015, 3:24pm

I have transformed my field with talend to be like this
"yyyy-MM-ddTHH:mm:ssZ" and that went good !
Doesn't ES accept another format? other than the one above?

On Tue, Apr 14, 2015 at 5:05 PM, jean.frege62@gmail.com wrote:

Hi,

I'm not sure if it will fix your problem but you could try the type
TIMESTAMP for your sendingTime field.
TIMESTAMP type is equivalent to the date elasticsearch type according to
Apache Hive integration | Elasticsearch for Apache Hadoop [8.11] | Elastic

Le mardi 14 avril 2015 11:26:49 UTC+2, BEN SALEM Omar a écrit :
It's " 'es.mapping.names' = 'sendingTime:@timestamp');"
not 'es.mapping.names' = 'date:@timestamp');

On Tuesday, April 14, 2015 at 11:19:13 AM UTC+2, BEN SALEM Omar wrote:
Hi,

I have trouble with the timestamp format required for elasticsearch.

I have data in my hadoop that I want to load into ES.
My date field was first as this : "EEE, dd MMM yyyy HH:mm:ss Z"

I've transformed that with talend like this :

https://lh3.googleusercontent.com/-rzoBkk29LMM/VSzY7w4kSFI/AAAAAAAAAHA/Uq3XtZEdsyI/s1600/Capture.PNG

Now if I try to load my data into ES, it keeps on mapping it as a string
and not as a timestamp as it should be !

This is what I've done :

1)CREATE TABLE xx ( subject STRING, author string, sendingTime string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA INPATH '/user/hive/out.csv' OVERWRITE INTO TABLE mails;

2)CREATE EXTERNAL TABLE es_xx (subject string,author string, sendingTime
string)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES('es.resource' = 'xx/xx',

'es.nodes'='xx',
          'es.mapping.names' = 'date:@timestamp');
INSERT OVERWRITE TABLE es_xx SELECT s.subject, s.author, s.sendingTime
from xx s;

And this how it was mapped :

{"xx":{"mappings":{"xx":{"properties":{"author":{"type":"string"},"sendingtime":{"type":"string"},"subject":{"type":"string"}}}}}}

In another example I've transformed my date format like this yyyy-MM-ddTHH:mm:ssZ and that went good !

Is that the only format that ES accept? If so, that have to change !

What should I do to make ES recognize my date field as a timestamp?

If I do a static mapping and transform the sendingtime field into a timestamp, would it work? or the current format has to change?

Hope for a quick response, thanks you all,

best regards,

Omar,

--
You received this message because you are subscribed to a topic in the
Google Groups "elasticsearch" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/elasticsearch/HAarVd-hPhA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/938af0c2-3a03-4f48-94be-21bb2db744a4%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/938af0c2-3a03-4f48-94be-21bb2db744a4%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAGgBrpzd5daDj6thAx0LfXX5V21kBqpc55VrX0ObCcO3B%2BazTw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

costin · April 15, 2015, 9:12am

Hi,

There are several components at play here and it's worth understanding
which one does what.

Elasticsearch offers quite a number of options for dealing with Date
objects as explained in the docs [1]. Note this options need to be defined
before-hand on your index otherwise (or through an index template)
otherwise the index will be created using the default options which might
not be what you want. Note that all these formats are variations of ISO
8601 standard.
Es-Hadoop tries to be smart and convert Date objects from the Hadoop
library used to Elasticsearch and back. It does that based on the mapping
used (where available). In Hive's case, if a column is of type Timestamp it
will serialize/deserialize the data as a Date. If it's not (as in your
case) it will simply pass the data as is.
In other words, if you want the Date to be stored as a String and mapping
it as a String, it will be returned as a String. This is handy since it
allows the user to handle the conversion himself.
Use Longs
Where possible, instead of using Strings for dates, it's better to use
longs. Elasticsearch can use them instead - not only they are more
efficient but also easier to handle and convert on the client side than
String formats.

Hope this helps,

[1]

On Tue, Apr 14, 2015 at 6:24 PM, 'BEN SALEM Omar' via elasticsearch <
elasticsearch@googlegroups.com> wrote:

I have transformed my field with talend to be like this
"yyyy-MM-ddTHH:mm:ssZ" and that went good !
Doesn't ES accept another format? other than the one above?

On Tue, Apr 14, 2015 at 5:05 PM, jean.frege62@gmail.com wrote:
Hi,

I'm not sure if it will fix your problem but you could try the type
TIMESTAMP for your sendingTime field.
TIMESTAMP type is equivalent to the date elasticsearch type according to
Apache Hive integration | Elasticsearch for Apache Hadoop [8.11] | Elastic

Le mardi 14 avril 2015 11:26:49 UTC+2, BEN SALEM Omar a écrit :
It's " 'es.mapping.names' = 'sendingTime:@timestamp');"
not 'es.mapping.names' = 'date:@timestamp');

On Tuesday, April 14, 2015 at 11:19:13 AM UTC+2, BEN SALEM Omar wrote:
Hi,

I have trouble with the timestamp format required for elasticsearch.

I have data in my hadoop that I want to load into ES.
My date field was first as this : "EEE, dd MMM yyyy HH:mm:ss Z"

I've transformed that with talend like this :

https://lh3.googleusercontent.com/-rzoBkk29LMM/VSzY7w4kSFI/AAAAAAAAAHA/Uq3XtZEdsyI/s1600/Capture.PNG

Now if I try to load my data into ES, it keeps on mapping it as a
string and not as a timestamp as it should be !

This is what I've done :

1)CREATE TABLE xx ( subject STRING, author string, sendingTime string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA INPATH '/user/hive/out.csv' OVERWRITE INTO TABLE mails;

2)CREATE EXTERNAL TABLE es_xx (subject string,author string,
sendingTime string)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES('es.resource' = 'xx/xx',

'es.nodes'='xx',
          'es.mapping.names' = 'date:@timestamp');
INSERT OVERWRITE TABLE es_xx SELECT s.subject, s.author, s.sendingTime
from xx s;

And this how it was mapped :

{"xx":{"mappings":{"xx":{"properties":{"author":{"type":"string"},"sendingtime":{"type":"string"},"subject":{"type":"string"}}}}}}

In another example I've transformed my date format like this yyyy-MM-ddTHH:mm:ssZ and that went good !

Is that the only format that ES accept? If so, that have to change !

What should I do to make ES recognize my date field as a timestamp?

If I do a static mapping and transform the sendingtime field into a timestamp, would it work? or the current format has to change?

Hope for a quick response, thanks you all,

best regards,

Omar,

--
You received this message because you are subscribed to a topic in the
Google Groups "elasticsearch" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/elasticsearch/HAarVd-hPhA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/938af0c2-3a03-4f48-94be-21bb2db744a4%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/938af0c2-3a03-4f48-94be-21bb2db744a4%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CAGgBrpzd5daDj6thAx0LfXX5V21kBqpc55VrX0ObCcO3B%2BazTw%40mail.gmail.com
https://groups.google.com/d/msgid/elasticsearch/CAGgBrpzd5daDj6thAx0LfXX5V21kBqpc55VrX0ObCcO3B%2BazTw%40mail.gmail.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAJogdme%2BX24BhB9v6VG3HLFp3MQhp4y0rQ5GFyTrnUifsLah%3Dg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.