Elasticsearch index mapping of datetime fields

Elastic Stack Elasticsearch
1

Hi,

I am inserting some messages into elasticsearch via fluendd (using fuentd REST method).
message object has two datetime fields (START_DATETIME and END_DATETIME) which when inserted are mapped as string.

My index changes on date basis.

In order to map datetime fields of object:

How can I create index mapping ?

Or

Can I specify value type within json object that is posted to fluentd REST ?

My message object is:

{
  "_index": "nh-2019.04.19",
  "_type": "fluentd",
  "_id": "2fLEM2oBGpY3nRioB0hP",
  "_version": 1,
  "_score": null,
  "_source": {
    "SRC_SYS": "xyz",
    "COUNTRY_CODE": "IN",
    "SRV_PROFILE": "PROF5",
    "TXN_ID": "xxxxxxx",
    "NH_API": "SOME_API",
    "REQ_MSG": "MSG5",
    "OCN": "XXXXXXXX",
    "START_DATETIME": "19/04/2019 09:28:59",
    "END_DATETIME": "19/04/2019 09:34:01",
    "@timestamp": "2019-04-19T09:34:01.895080212+05:30",
    "tag": "my.tag"
  },
  "fields": {
    "@timestamp": [
      "2019-04-19T04:04:01.895Z"
    ]
  }

Regards,
-Manish

2

Hi,

I have tried to create a template, but I now I can see 400 code from elasticsearch in fluentd logs.

curl -X PUT "localhost:9200/_template/nh_template" -H 'Content-Type: application/json' -d'
{
  "index_patterns": ["nh-*"],
  "settings": {
    "number_of_shards": 1
  },
  "mappings": {
    "fluentd": {
      "_source": {
        "enabled": true
      },
      "properties": {
        "START_DATETIME": {
          "type": "date",
	  "format": "dd-MM-yyyy HH:mm:ss"
        },
        "END_DATETIME": {
          "type": "date",
	  "format": "dd-MM-yyyy HH:mm:ss"
        },
               "API_TASK":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "COUNTRY_CODE":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "DST_SYS":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "ERR_CODE":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "ERR_DATETIME":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "ERR_DETAILS":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "ERR_ID":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "NH_API":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "OCN":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "RELATIVE_ERR_ID":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "REQ_MSG":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "SRC_SYS":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "SRV_PROFILE":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "TXN_ID":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "tag":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               }
      }
    }
  }
}'

In Fluentd logs I can see following message

2019-04-19 11:51:06 +0530 [warn]: #0 dump an error event: error_class=Fluent::Plugin::ElasticsearchErrorHandler::ElasticsearchError error="400 - Rejected by Elasticsearch" location=nil tag="nh.wmtest" time=2019-04-19 11:51:02.021301738 +0530 record={"SRC_SYS"=>"wm", "COUNTRY_CODE"=>"JP", "SRV_PROFILE"=>"PROF3", "TXN_ID"=>"47118951", "NH_API"=>"NH_API6", "REQ_MSG"=>"MSG4", "OCN"=>"WKV5760727", "START_DATETIME"=>"19/04/2019 11:42:40", "END_DATETIME"=>"19/04/2019 11:51:02"}

Not sure whats wrong.

Regards,
-Manish

3

The date format in your mapping is not the same as the date format in your documents. Try the following instead:

format": "dd/MM/yyyy HH:mm:ss"
4

Thanks @abdon for pointing it out. I am trying it with updated template.
But I will prefer to set data type in json payload. Is it possible to set "datetime" type on on START_TIMESTAMP and END_TIMESTAMP fields during insertion ?

This way my database will remain schema free, and data type can be set by client on need basis.

Regards,
-Manish

5

If you index your dates in the ISO 8601 format (for example 2019-04-19T00:57:51.548Z), then Elasticsearch will automatically recognize those as dates, and you won't have to define any mappings.

1 Like
6

Hi @abdon,

index your dates in the ISO 8601 format (for example 2019-04-19T00:57:51.548Z ), then Elasticsearch will automatically recognize those as dates

This is interesting to know. Thanks, let me try it out.

Regards,
-Manish

7

Wow, this worked perfect @abdon I have verified it with a date based query. It works well.

curl -XGET "http://localhost:9200/nh-*/_search" -H 'Content-Type: application/json' -d'
{
  "size": 100, 
    "query": {
        "range" : {
            "START_DATETIME": {
                "gte": "now-1d",
                "lte": "now"
            }
        }
    }
    
}'

And I get

{
  "took" : 18,
  "timed_out" : false,
  "_shards" : {
    "total" : 190,
    "successful" : 190,
    "skipped" : 175,
    "failed" : 0
  },
  "hits" : {
    "total" : 525,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "nh-2019.04.19",
        "_type" : "fluentd",
        "_id" : "9vsUNWoBGpY3nRioTnLK",
        "_score" : 1.0,
        "_source" : {
          "SRC_SYS" : "wm",
          "COUNTRY_CODE" : "DE",
          "SRV_PROFILE" : "PROF3",
          "TXN_ID" : "11425680",
          "NH_API" : "NH_API1",
          "REQ_MSG" : "MSG1",
          "OCN" : "JEP5715063",
          "START_DATETIME" : "2019-04-19T09:59:23.949800+00:00",
          "END_DATETIME" : "2019-04-19T10:10:18.949848+00:00",
          "@timestamp" : "2019-04-19T15:40:18.953785103+05:30",
          "tag" : "nh.wmtest"
        }
      },
1 Like
8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.