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

Elastic Stack Elasticsearch
1

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.

2

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.

3

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.

4

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.

5

Hi,

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

  1. 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.

  2. 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.

  3. 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.