ElasticSearch sort by date in order


(sirkubax) #1

Hi

I have a problem with sorting in order elasticsearch queries.
I have read that it is imposible to sort string multi-filed (is this true?)

So I have created testdate7 field with custom mapping:

curl -XPUT localhost:9200/*/_mapping/loglog -d '
{
"loglog" : {
"properties" : {
"testdate7" : {"type" : "date", "format" : "yyyy-MM-dd
HH:mm:ss.SSSSSS", "store" : true }
}
}
}
'

The mappping are:
{
"logstash-2014.06.09" : {
"mappings" : {
"loglog" : {
"dynamic_templates" : [ {
"string_fields" : {
"mapping" : {
"type" : "multi_field",
"fields" : {
"raw" : {
"index" : "not_analyzed",
"ignore_above" : 256,
"type" : "string"
},
"{name}" : {
"index" : "analyzed",
"omit_norms" : true,
"type" : "string"
}
}
},
"match" : "*",
"match_mapping_type" : "string"
}
} ],
"properties" : {
"@timestamp" : {
"type" : "date",
"format" : "dateOptionalTime"
},
"@version" : {
"type" : "string",
"index" : "not_analyzed"
},

      "testdate6" : {
        "type" : "string",
        "norms" : {
          "enabled" : false
        },
        "fields" : {
          "raw" : {
            "type" : "string",
            "index" : "not_analyzed",
            "ignore_above" : 256
          }
        }
      },
      "testdate7" : {
        "type" : "string",
        "norms" : {
          "enabled" : false
        },
        "fields" : {
          "raw" : {
            "type" : "string",
            "index" : "not_analyzed",
            "ignore_above" : 256
          }
        }
      },
      "testhour6" : {
        "type" : "string",
        "norms" : {
          "enabled" : false
        },
        "fields" : {
          "raw" : {
            "type" : "string",
            "index" : "not_analyzed",
            "ignore_above" : 256
          }
        }
      },
    }
  }
}

}
}

My logstash filter section looks like:

filter {
if [type] == "loglog" {

       grok {                
            pattern => '(?<process_name>[a-z0-9_.]+):[0-9]+: 

(?[A-Z_]+)((?<trace_lvl>[0-9]+))
(?%{YEAR}-%{MONTHNUM}-%{MONTHDAY})
(?%{HOUR}:?%{MINUTE}:(?(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?))'

            add_field => ["testdate6", "%{datep2}_%{hourp2}"]
            add_field => ["testdate7", "%{datep2} %{hourp2}"]
            add_field => ["testhour6", "%{hourp2}"]
       }

}

The message that I parse:
"ANYTHING (pid: 23291, thread: 4131280592) *******] [ aa.xx:555: MSG(3)
2014-06-09 10:50:08.255111 ... "

And the result:

testdate7 testhour6 testdate6 @timestamp2014-06-09 10:50:08.255111
10:50:08.2551112014-06-09_10:50:08.2551112014-06-09T08:50:12.158Z2014-06-09
10:50:08.39198810:50:08.3919882014-06-09_10:50:08.391988
2014-06-09T08:50:12.159Z2014-06-09 10:50:08.39230310:50:08.392303
2014-06-09_10:50:08.3923032014-06-09T08:50:12.159Z2014-06-09 10:50:08.423341
10:50:08.4233412014-06-09_10:50:08.4233412014-06-09T08:50:12.176Z2014-06-09
10:50:08.42392710:50:08.4239272014-06-09_10:50:08.423927
2014-06-09T08:50:12.177Z2014-06-09 11:14:08.69731911:14:08.697319
2014-06-09_11:14:08.6973192014-06-09T09:14:13.788Z2014-06-09 11:14:08.699317
11:14:08.6993172014-06-09_11:14:08.6993172014-06-09T09:14:13.821Z2014-06-09
11:14:08.92984211:14:08.9298422014-06-09_11:14:08.929842
2014-06-09T09:14:13.853Z2014-06-09 11:14:08.93043911:14:08.930439
2014-06-09_11:14:08.9304392014-06-09T09:14:13.855Z2014-06-09 11:14:08.944728
11:14:08.9447282014-06-09_11:14:08.9447282014-06-09T09:14:13.856Z2014-06-09
11:14:08.94492011:14:08.9449202014-06-09_11:14:08.944920
2014-06-09T09:14:13.856Z2014-06-09 11:14:09.65145811:14:09.651458
2014-06-09_11:14:09.6514582014-06-09T09:14:13.875Z2014-06-09 11:14:09.653228
11:14:09.6532282014-06-09_11:14:09.6532282014-06-09T09:14:13.876Z2014-06-09
10:49:10.45611310:49:10.4561132014-06-09_10:49:10.456113
2014-06-09T08:49:31.969Z2014-06-09 10:49:38.47383810:49:38.473838
2014-06-09_10:49:38.4738382014-06-09T08:49:41.065Z2014-06-09 10:49:33.127422
10:49:33.1274222014-06-09_10:49:33.1274222014-06-09T08:49:41.078Z2014-06-09
10:50:43.36797510:50:43.3679752014-06-09_10:50:43.367975
2014-06-09T08:51:02.423Z2014-06-09 10:50:13.77646710:50:13.776467
2014-06-09_10:50:13.7764672014-06-09T08:50:31.271Z2014-06-09 10:50:14.033604
10:50:14.0336042014-06-09_10:50:14.0336042014-06-09T08:50:31.287Z2014-06-09
10:50:14.21700910:50:14.2170092014-06-09_10:50:14.217009
2014-06-09T08:50:31.308Z2014-06-09 10:49:54.41016210:49:54.410162
2014-06-09_10:49:54.4101622014-06-09T08:50:02.114Z2014-06-09 10:37:31.897583
10:37:31.8975832014-06-09_10:37:31.8975832014-06-09T08:37:40.171Z2014-06-09
10:49:33.11399110:49:33.1139912014-06-09_10:49:33.113991
2014-06-09T08:49:41.076Z

I do wonder why the testdate7 changes from "type" : "date", to "type" : "
string", as presented below, it is set properly on index that exist, but is
empty because I did not write any data to it.

{
"logstash-2014.06.05" : {
"mappings" : {
"loglog" : {
"dynamic_templates" : [ {
"string_fields" : {
"mapping" : {
"type" : "multi_field",
"fields" : {
"raw" : {
"index" : "not_analyzed",
"ignore_above" : 256,
"type" : "string"
},
"{name}" : {
"index" : "analyzed",
"omit_norms" : true,
"type" : "string"
}
}
},
"match" : "*",
"match_mapping_type" : "string"
}
} ],
"properties" : {
"@timestamp" : {
"type" : "date",
"format" : "dateOptionalTime"
},
"@version" : {
"type" : "string",
"index" : "not_analyzed"
},
"testdate7" : {
"type" : "date",
"store" : true,
"format" : "yyyy-MM-dd HH:mm:ss.SSSSSS"
},

Is that a problem with
"match" : "*",
"match_mapping_type" : "string"
or should I add my date format to dynamic_date_formats

--
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/8d26b1dd-67b0-4e57-b544-8ff1e93238a5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(sirkubax) #2

When I asked a question, i think I'm close to anwser

curl -XPUT localhost:9200//_mapping/loglog -d '
{
"dynamic_templates" : [ {
"string_fields" : {
"mapping" : {
"type" : "multi_field",
"fields" : {
"raw" : {
"index" : "not_analyzed",
"ignore_above" : 256,
"type" : "string"
},
"testdate8" : {
"match": "
",
"match_mapping_type": "date",
"mapping" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss.SSSSSS"
}
},
"{name}" : {
"index" : "analyzed",
"omit_norms" : true,
"type" : "string"
}
}
},
"match" : "*",
"match_mapping_type" : "string"
}
} ]
}
'

"logstash-2014.06.09" : {
"mappings" : {
"loglog" : {
"dynamic_templates" : [ {
"string_fields" : {
"mapping" : {
"type" : "multi_field",
"fields" : {
"raw" : {
"index" : "not_analyzed",
"ignore_above" : 256,
"type" : "string"
},
"testdate8" : {
"match" : "",
"match_mapping_type" : "date",
"mapping" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss.SSSSSS"
}
},
"{name}" : {
"index" : "analyzed",
"omit_norms" : true,
"type" : "string"
}
}
},
"match" : "
",
"match_mapping_type" : "string"
}
} ],

but It did not work, when I fillled it with data:

      "testdate8" : {
        "type" : "string",
        "norms" : {
          "enabled" : false
        },
        "fields" : {
          "raw" : {
            "type" : "string",
            "index" : "not_analyzed",
            "ignore_above" : 256
          }
        }
      },

--
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/4283b805-4e1e-4687-b6a6-3a481da696f4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(sirkubax) #3

I have tried to add refault mapping:

curl -XPUT localhost:9200//_mapping/default -d '
{
"default" : {
"dynamic_date_formats" : ["date_optional_time", "yyyy-MM-dd
HH:mm:ss.SSSSSS"],
"dynamic_templates" : [ {
"string_fields" : {
"mapping" : {
"type" : "multi_field",
"fields" : {
"raw" : {
"index" : "not_analyzed",
"ignore_above" : 256,
"type" : "string"
},
"{name}" : {
"index" : "analyzed",
"omit_norms" : true,
"type" : "string"
}
}
},
"match" : "
",
"match_mapping_type" : "string"
}
} ],
"properties" : {
"@version" : {
"type" : "string",
"index" : "not_analyzed"
},
"geoip" : {
"dynamic" : "true",
"properties" : {
"location" : {
"type" : "geo_point"
}
}
}
}
}
}
'

--
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/69554449-a090-4b19-8567-928fbe95b3fa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(sirkubax) #4

*I guess I nailed it with dynamic_date_formats and extra *
dynamic_templates http://www.elasticsearch.org/guide/en/elasticsearch/reference/current//mapping-root-object-type.html#_dynamic_date_formats

like that

curl -XPUT localhost:9200/_mapping/default -d @mappings2

cat mappings2
{
"default" : {
"dynamic_date_formats" : [ "date_optional_time", "yyyy-MM-dd
HH:mm:ss.SSSSSS" ],
"dynamic_templates" : [
{ "testdate": {
"match": "testdate*",
"mapping": {
"type": "date",
"format" : "yyyy-MM-dd HH:mm:ss.SSSSSS"
}
}},
{
"string_fields" : {
"mapping" : {
"type" : "multi_field",
"fields" : {
"raw" : {
"index" : "not_analyzed",
"ignore_above" : 256,
"type" : "string"
},
"{name}" : {
"index" : "analyzed",
"omit_norms" : true,
"type" : "string"
}
}
},
"unmatch" : "testdate*",
"match" : "*",
"match_mapping_type" : "string"
}
} ],
"properties" : {
"@version" : {
"type" : "string",
"index" : "not_analyzed"
},
"geoip" : {
"dynamic" : "true",
"properties" : {
"location" : {
"type" : "geo_point"
}
}
}
}
}
}

It should be working well after few minutes - new dynamic template was not
present for about 4-5 minutes

curl -XGET localhost:9200/_mapping/ismigolog?pretty |less

--
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/0ec00ba9-f808-40bc-99ee-168297744ad5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(system) #5