elasticsearch pre_offset/post_offset設定

 

概要

kibana3から一週間刻みでグラフを表示する際、特定曜日を基準で一週間ずつ切るのが可能か調べてみた

デフォルトだと木曜日から水曜日までの一週間刻みで表示する

 

先だって結論

kibana3上では不可能

ElasticSearchのクエリでは「pre_offset/post_offset」を利用して可能

kibana4ではオプションクエリを入れる欄ができてそこにpre_offsetを入れれば可能

 

interval:7d設定のみの場合

interval:7d設定のみの場合、クエリの結果をみてみよう

"interval": "7d",

結果:

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 31,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "facets" : {
    "0" : {
      "_type" : "date_histogram",
      "entries" : [ {
        "time" : 1411603200000,
        "count" : 2,
        "min" : 54.0,
        "max" : 61.0,
        "total" : 115.0,
        "total_count" : 2,
        "mean" : 57.5
      }, {
        "time" : 1412208000000,
        "count" : 7,
        "min" : 24.0,
        "max" : 89.0,
        "total" : 365.0,
        "total_count" : 7,
        "mean" : 52.142857142857146
      }, {
        "time" : 1412812800000,
        "count" : 7,
        "min" : 12.0,
        "max" : 74.0,
        "total" : 368.0,
        "total_count" : 7,
        "mean" : 52.57142857142857
      }, {
        "time" : 1413417600000,
        "count" : 7,
        "min" : 12.0,
        "max" : 86.0,
        "total" : 360.0,
        "total_count" : 7,
        "mean" : 51.42857142857143
      }, {
        "time" : 1414022400000,
        "count" : 7,
        "min" : 23.0,
        "max" : 87.0,
        "total" : 379.0,
        "total_count" : 7,
        "mean" : 54.142857142857146
      }, {
        "time" : 1414627200000,
        "count" : 1,
        "min" : 57.0,
        "max" : 57.0,
        "total" : 57.0,
        "total_count" : 1,
        "mean" : 57.0
      } ]
    }
  }
}

 

pre_offset:1d設定を追記した場合

 

"interval": "7d",

"pre_offset": "1d"

結果:

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 31,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "facets" : {
    "0" : {
      "_type" : "date_histogram",
      "entries" : [ {
        "time" : 1411603200000,
        "count" : 1,
        "min" : 61.0,
        "max" : 61.0,
        "total" : 61.0,
        "total_count" : 1,
        "mean" : 61.0
      }, {
        "time" : 1412208000000,
        "count" : 7,
        "min" : 24.0,
        "max" : 58.0,
        "total" : 330.0,
        "total_count" : 7,
        "mean" : 47.142857142857146
      }, {
        "time" : 1412812800000,
        "count" : 7,
        "min" : 41.0,
        "max" : 89.0,
        "total" : 445.0,
        "total_count" : 7,
        "mean" : 63.57142857142857
      }, {
        "time" : 1413417600000,
        "count" : 7,
        "min" : 12.0,
        "max" : 86.0,
        "total" : 308.0,
        "total_count" : 7,
        "mean" : 44.0
      }, {
        "time" : 1414022400000,
        "count" : 7,
        "min" : 23.0,
        "max" : 87.0,
        "total" : 391.0,
        "total_count" : 7,
        "mean" : 55.857142857142854
      }, {
        "time" : 1414627200000,
        "count" : 2,
        "min" : 52.0,
        "max" : 57.0,
        "total" : 109.0,
        "total_count" : 2,
        "mean" : 54.5
      } ]
    }
  }
}

 

10/02(木) – 10/08(水) の結果を取得した

 

pre_offset:2d設定を追記した場合

 

"interval": "7d",

"pre_offset": "1d"

結果:

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 31,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "facets" : {
    "0" : {
      "_type" : "date_histogram",
      "entries" : [ {
        "time" : 1412208000000,
        "count" : 7,
        "min" : 24.0,
        "max" : 61.0,
        "total" : 346.0,
        "total_count" : 7,
        "mean" : 49.42857142857143
      }, {
        "time" : 1412812800000,
        "count" : 7,
        "min" : 41.0,
        "max" : 89.0,
        "total" : 416.0,
        "total_count" : 7,
        "mean" : 59.42857142857143
      }, {
        "time" : 1413417600000,
        "count" : 7,
        "min" : 12.0,
        "max" : 86.0,
        "total" : 359.0,
        "total_count" : 7,
        "mean" : 51.285714285714285
      }, {
        "time" : 1414022400000,
        "count" : 7,
        "min" : 23.0,
        "max" : 87.0,
        "total" : 349.0,
        "total_count" : 7,
        "mean" : 49.857142857142854
      }, {
        "time" : 1414627200000,
        "count" : 3,
        "min" : 52.0,
        "max" : 65.0,
        "total" : 174.0,
        "total_count" : 3,
        "mean" : 58.0
      } ]
    }
  }
}

すると結果は「10/01(水) – 10/07(火)」のデータを取得することができた

 

このようにpre_offsetを利用してinterval 7dの曜日基準をずらすことが可能だった

 

以下のリンクの文言から見るとpre_offset,post_offsetはもうすぐでなくなるオプションのようだ。。

pre_offset and post_offset are deprecated and replaced by offset

http://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です