elasticsearch range aggs timezone 实践，elasticsearchaggs

和通数据库htsjk.Com2019-06-08 01:25 来源:未知阅读:19848 评论 32 热度5

标签：

elasticsearch range aggs timezone 实践，elasticsearchaggs

在使用elasticsearch做数据存储和搜索引擎时，不可避免的会涉及到一些时间类的数据，比如：数据生成时间。本文主要针对elasticsearch使用过程中时间类型数据的mapping、写入、查询以及聚合计算等，谈谈自己的理解和总结出的最佳实践。

Index/Type mapping

官方支持的时间类型，大致上分为两类：时间戳（支持毫秒）和日期字符串类型。考虑到可阅读性，这里我选择日期字符串类型（当然，不考虑成本的情况下，完全可以两者都使用）

数据写入时的时区选择

elasticsearch中相关时区的操作往往以 UTC 时间（即：0时区时间）为准，为了和 elasticsearch 保持一致，在插入数据时，我们使用 UTC 时间戳。例如：2017-12-25T16:00:00Z

数据筛选和聚合时的时区问题

基于上述和 elasticsearch 保持一致的 UTC 时间数据，做 range 筛选和 aggs 聚合时，考虑到数据分析脚本或者服务的本地化，我们往往使用 +08:00 的时间参数作为输入，如此在传递给elasticsearch时，由于时区不同，需要指定传入的实践参数为 +08:00 时区。

实例
构建 Index/Type Mapping

PUT http://127.0.0.1:9200/timezone
{
    "mappings": {
        "timezone-26.27": {
            "properties": {
                "@timestamp": {
                    "type": "date"
                },
                "timestring": {
                    "type": "keyword"
                }
            }
        }
    }
}

插入模拟数据

POST http://127.0.0.1:9200/timezone/timezone-26.27/_bulk?pretty&refresh
{"index":{"_id":1}}
{"@timestamp":"2017-12-25T16:00:00Z","timestring":"2017-12-26 00:00:00"}
{"index":{"_id":2}}
{"@timestamp":"2017-12-25T16:59:59Z","timestring":"2017-12-26 00:59:59"}
{"index":{"_id":3}}
{"@timestamp":"2017-12-25T17:00:00Z","timestring":"2017-12-26 01:00:00"}
{"index":{"_id":4}}
{"@timestamp":"2017-12-25T17:59:59Z","timestring":"2017-12-26 01:59:59"}
{"index":{"_id":5}}
{"@timestamp":"2017-12-25T18:00:00Z","timestring":"2017-12-26 02:00:00"}
{"index":{"_id":6}}
{"@timestamp":"2017-12-25T18:59:59Z","timestring":"2017-12-26 02:59:59"}
{"index":{"_id":7}}
{"@timestamp":"2017-12-25T19:00:00Z","timestring":"2017-12-26 03:00:00"}
{"index":{"_id":8}}
{"@timestamp":"2017-12-25T19:59:59Z","timestring":"2017-12-26 03:59:59"}
{"index":{"_id":9}}
{"@timestamp":"2017-12-25T20:00:00Z","timestring":"2017-12-26 04:00:00"}
{"index":{"_id":10}}
{"@timestamp":"2017-12-25T20:59:59Z","timestring":"2017-12-26 04:59:59"}
{"index":{"_id":11}}
{"@timestamp":"2017-12-25T21:00:00Z","timestring":"2017-12-26 05:00:00"}
{"index":{"_id":12}}
{"@timestamp":"2017-12-25T21:59:59Z","timestring":"2017-12-26 05:59:59"}
{"index":{"_id":13}}
{"@timestamp":"2017-12-25T22:00:00Z","timestring":"2017-12-26 06:00:00"}
{"index":{"_id":14}}
{"@timestamp":"2017-12-25T22:59:59Z","timestring":"2017-12-26 06:59:59"}
{"index":{"_id":15}}
{"@timestamp":"2017-12-25T23:00:00Z","timestring":"2017-12-26 07:00:00"}
{"index":{"_id":16}}
{"@timestamp":"2017-12-25T23:59:59Z","timestring":"2017-12-26 07:59:59"}
{"index":{"_id":17}}
{"@timestamp":"2017-12-26T00:00:00Z","timestring":"2017-12-26 08:00:00"}
{"index":{"_id":18}}
{"@timestamp":"2017-12-26T00:59:59Z","timestring":"2017-12-26 08:59:59"}
{"index":{"_id":19}}
{"@timestamp":"2017-12-26T01:00:00Z","timestring":"2017-12-26 09:00:00"}
{"index":{"_id":20}}
{"@timestamp":"2017-12-26T01:59:59Z","timestring":"2017-12-26 09:59:59"}
{"index":{"_id":21}}
{"@timestamp":"2017-12-26T02:00:00Z","timestring":"2017-12-26 10:00:00"}
{"index":{"_id":22}}
{"@timestamp":"2017-12-26T02:59:59Z","timestring":"2017-12-26 10:59:59"}
{"index":{"_id":23}}
{"@timestamp":"2017-12-26T03:00:00Z","timestring":"2017-12-26 11:00:00"}
{"index":{"_id":24}}
{"@timestamp":"2017-12-26T03:59:59Z","timestring":"2017-12-26 11:59:59"}
{"index":{"_id":25}}
{"@timestamp":"2017-12-26T04:00:00Z","timestring":"2017-12-26 12:00:00"}
{"index":{"_id":26}}
{"@timestamp":"2017-12-26T04:59:59Z","timestring":"2017-12-26 12:59:59"}
{"index":{"_id":27}}
{"@timestamp":"2017-12-26T05:00:00Z","timestring":"2017-12-26 13:00:00"}
{"index":{"_id":28}}
{"@timestamp":"2017-12-26T05:59:59Z","timestring":"2017-12-26 13:59:59"}
{"index":{"_id":29}}
{"@timestamp":"2017-12-26T06:00:00Z","timestring":"2017-12-26 14:00:00"}
{"index":{"_id":30}}
{"@timestamp":"2017-12-26T06:59:59Z","timestring":"2017-12-26 14:59:59"}
{"index":{"_id":31}}
{"@timestamp":"2017-12-26T07:00:00Z","timestring":"2017-12-26 15:00:00"}
{"index":{"_id":32}}
{"@timestamp":"2017-12-26T07:59:59Z","timestring":"2017-12-26 15:59:59"}
{"index":{"_id":33}}
{"@timestamp":"2017-12-26T08:00:00Z","timestring":"2017-12-26 16:00:00"}
{"index":{"_id":34}}
{"@timestamp":"2017-12-26T08:59:59Z","timestring":"2017-12-26 16:59:59"}
{"index":{"_id":35}}
{"@timestamp":"2017-12-26T09:00:00Z","timestring":"2017-12-26 17:00:00"}
{"index":{"_id":36}}
{"@timestamp":"2017-12-26T09:59:59Z","timestring":"2017-12-26 17:59:59"}
{"index":{"_id":37}}
{"@timestamp":"2017-12-26T10:00:00Z","timestring":"2017-12-26 18:00:00"}
{"index":{"_id":38}}
{"@timestamp":"2017-12-26T10:59:59Z","timestring":"2017-12-26 18:59:59"}
{"index":{"_id":39}}
{"@timestamp":"2017-12-26T11:00:00Z","timestring":"2017-12-26 19:00:00"}
{"index":{"_id":40}}
{"@timestamp":"2017-12-26T11:59:59Z","timestring":"2017-12-26 19:59:59"}
{"index":{"_id":41}}
{"@timestamp":"2017-12-26T12:00:00Z","timestring":"2017-12-26 20:00:00"}
{"index":{"_id":42}}
{"@timestamp":"2017-12-26T12:59:59Z","timestring":"2017-12-26 20:59:59"}
{"index":{"_id":43}}
{"@timestamp":"2017-12-26T13:00:00Z","timestring":"2017-12-26 21:00:00"}
{"index":{"_id":44}}
{"@timestamp":"2017-12-26T13:59:59Z","timestring":"2017-12-26 21:59:59"}
{"index":{"_id":45}}
{"@timestamp":"2017-12-26T14:00:00Z","timestring":"2017-12-26 22:00:00"}
{"index":{"_id":46}}
{"@timestamp":"2017-12-26T14:59:59Z","timestring":"2017-12-26 22:59:59"}
{"index":{"_id":47}}
{"@timestamp":"2017-12-26T15:00:00Z","timestring":"2017-12-26 23:00:00"}
{"index":{"_id":48}}
{"@timestamp":"2017-12-26T15:59:59Z","timestring":"2017-12-26 23:59:59"}
{"index":{"_id":49}}
{"@timestamp":"2017-12-26T16:00:00Z","timestring":"2017-12-27 00:00:00"}
{"index":{"_id":50}}
{"@timestamp":"2017-12-26T16:59:59Z","timestring":"2017-12-27 00:59:59"}
{"index":{"_id":51}}
{"@timestamp":"2017-12-26T17:00:00Z","timestring":"2017-12-27 01:00:00"}
{"index":{"_id":52}}
{"@timestamp":"2017-12-26T17:59:59Z","timestring":"2017-12-27 01:59:59"}
{"index":{"_id":53}}
{"@timestamp":"2017-12-26T18:00:00Z","timestring":"2017-12-27 02:00:00"}
{"index":{"_id":54}}
{"@timestamp":"2017-12-26T18:59:59Z","timestring":"2017-12-27 02:59:59"}
{"index":{"_id":55}}
{"@timestamp":"2017-12-26T19:00:00Z","timestring":"2017-12-27 03:00:00"}
{"index":{"_id":56}}
{"@timestamp":"2017-12-26T19:59:59Z","timestring":"2017-12-27 03:59:59"}
{"index":{"_id":57}}
{"@timestamp":"2017-12-26T20:00:00Z","timestring":"2017-12-27 04:00:00"}
{"index":{"_id":58}}
{"@timestamp":"2017-12-26T20:59:59Z","timestring":"2017-12-27 04:59:59"}
{"index":{"_id":59}}
{"@timestamp":"2017-12-26T21:00:00Z","timestring":"2017-12-27 05:00:00"}
{"index":{"_id":60}}
{"@timestamp":"2017-12-26T21:59:59Z","timestring":"2017-12-27 05:59:59"}
{"index":{"_id":61}}
{"@timestamp":"2017-12-26T22:00:00Z","timestring":"2017-12-27 06:00:00"}
{"index":{"_id":62}}
{"@timestamp":"2017-12-26T22:59:59Z","timestring":"2017-12-27 06:59:59"}
{"index":{"_id":63}}
{"@timestamp":"2017-12-26T23:00:00Z","timestring":"2017-12-27 07:00:00"}
{"index":{"_id":64}}
{"@timestamp":"2017-12-26T23:59:59Z","timestring":"2017-12-27 07:59:59"}
{"index":{"_id":65}}
{"@timestamp":"2017-12-27T00:00:00Z","timestring":"2017-12-27 08:00:00"}
{"index":{"_id":66}}
{"@timestamp":"2017-12-27T00:59:59Z","timestring":"2017-12-27 08:59:59"}
{"index":{"_id":67}}
{"@timestamp":"2017-12-27T01:00:00Z","timestring":"2017-12-27 09:00:00"}
{"index":{"_id":68}}
{"@timestamp":"2017-12-27T01:59:59Z","timestring":"2017-12-27 09:59:59"}
{"index":{"_id":69}}
{"@timestamp":"2017-12-27T02:00:00Z","timestring":"2017-12-27 10:00:00"}
{"index":{"_id":70}}
{"@timestamp":"2017-12-27T02:59:59Z","timestring":"2017-12-27 10:59:59"}
{"index":{"_id":71}}
{"@timestamp":"2017-12-27T03:00:00Z","timestring":"2017-12-27 11:00:00"}
{"index":{"_id":72}}
{"@timestamp":"2017-12-27T03:59:59Z","timestring":"2017-12-27 11:59:59"}
{"index":{"_id":73}}
{"@timestamp":"2017-12-27T04:00:00Z","timestring":"2017-12-27 12:00:00"}
{"index":{"_id":74}}
{"@timestamp":"2017-12-27T04:59:59Z","timestring":"2017-12-27 12:59:59"}
{"index":{"_id":75}}
{"@timestamp":"2017-12-27T05:00:00Z","timestring":"2017-12-27 13:00:00"}
{"index":{"_id":76}}
{"@timestamp":"2017-12-27T05:59:59Z","timestring":"2017-12-27 13:59:59"}
{"index":{"_id":77}}
{"@timestamp":"2017-12-27T06:00:00Z","timestring":"2017-12-27 14:00:00"}
{"index":{"_id":78}}
{"@timestamp":"2017-12-27T06:59:59Z","timestring":"2017-12-27 14:59:59"}
{"index":{"_id":79}}
{"@timestamp":"2017-12-27T07:00:00Z","timestring":"2017-12-27 15:00:00"}
{"index":{"_id":80}}
{"@timestamp":"2017-12-27T07:59:59Z","timestring":"2017-12-27 15:59:59"}
{"index":{"_id":81}}
{"@timestamp":"2017-12-27T08:00:00Z","timestring":"2017-12-27 16:00:00"}
{"index":{"_id":82}}
{"@timestamp":"2017-12-27T08:59:59Z","timestring":"2017-12-27 16:59:59"}
{"index":{"_id":83}}
{"@timestamp":"2017-12-27T09:00:00Z","timestring":"2017-12-27 17:00:00"}
{"index":{"_id":84}}
{"@timestamp":"2017-12-27T09:59:59Z","timestring":"2017-12-27 17:59:59"}
{"index":{"_id":85}}
{"@timestamp":"2017-12-27T10:00:00Z","timestring":"2017-12-27 18:00:00"}
{"index":{"_id":86}}
{"@timestamp":"2017-12-27T10:59:59Z","timestring":"2017-12-27 18:59:59"}
{"index":{"_id":87}}
{"@timestamp":"2017-12-27T11:00:00Z","timestring":"2017-12-27 19:00:00"}
{"index":{"_id":88}}
{"@timestamp":"2017-12-27T11:59:59Z","timestring":"2017-12-27 19:59:59"}
{"index":{"_id":89}}
{"@timestamp":"2017-12-27T12:00:00Z","timestring":"2017-12-27 20:00:00"}
{"index":{"_id":90}}
{"@timestamp":"2017-12-27T12:59:59Z","timestring":"2017-12-27 20:59:59"}
{"index":{"_id":91}}
{"@timestamp":"2017-12-27T13:00:00Z","timestring":"2017-12-27 21:00:00"}
{"index":{"_id":92}}
{"@timestamp":"2017-12-27T13:59:59Z","timestring":"2017-12-27 21:59:59"}
{"index":{"_id":93}}
{"@timestamp":"2017-12-27T14:00:00Z","timestring":"2017-12-27 22:00:00"}
{"index":{"_id":94}}
{"@timestamp":"2017-12-27T14:59:59Z","timestring":"2017-12-27 22:59:59"}
{"index":{"_id":95}}
{"@timestamp":"2017-12-27T15:00:00Z","timestring":"2017-12-27 23:00:00"}
{"index":{"_id":96}}
{"@timestamp":"2017-12-27T15:59:59Z","timestring":"2017-12-27 23:59:59"}

基于模拟数据的筛选聚合

POST http://127.0.0.1:9200/timezone/timezone-26.27/_search?pretty
{
	"size" : 0,
	"query": {
		"range": {
			"@timestamp": {
				"gte": "2017-12-26 00:00:00",
				"lte": "2017-12-27 23:59:59",
				"format":"yyyy-MM-dd HH:mm:ss",
				"time_zone":"+08:00"
			}
		}
	},
	"aggs": {
		"per_day": {
			"date_histogram": {"interval": "day","field": "@timestamp","time_zone": "+08:00"}
		}
	}
}

聚合结果及分析

{
    "took": 3,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 96,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "per_day": {
            "buckets": [
                {
                    "key_as_string": "2017-12-26T00:00:00.000+08:00",
                    "key": 1514217600000,
                    "doc_count": 48
                },
                {
                    "key_as_string": "2017-12-27T00:00:00.000+08:00",
                    "key": 1514304000000,
                    "doc_count": 48
                }
            ]
        }
    }
}

相关链接： Range Query
Date Histogram Aggregation Mapping Date Type