ElasticSearch使我们能够在任何给定字段上通过regex过滤一组文档,还可以使用“桶聚合”将结果文档按给定(相同或不同字段)中的术语分组。例如,在包含"Url“字段和" UserAgent”字段(某种web服务器日志)的索引上,下面将返回在UserAgent字段中找到的术语的顶部文档计数。
{
query: { filtered: { filter: { regexp: { Url : ".*interestingpage.*" } } } },
size: 0,
aggs: { myaggregation: { terms: { field: "UserAgent" } } }
}我想要做的是使用regexp过滤器(它在整个字段上运行,而不仅仅是字段中的术语)来手动定义我的聚合桶,这样我就可以相对可靠地通过“用户代理类型”数据来分割文档/计数/命中,而不是在字段中通过弹性搜索解析的任意术语。
基本上,我在寻找与组BY中的CASE语句等价的SQL语句。表示我的意图的SQL查询应该如下所示:
SELECT Bucket, Count(*)
FROM (
SELECT CASE
WHEN UserAgent LIKE '%android%' OR UserAgent LIKE '%ipad%' OR UserAgent LIKE '%iphone%' OR UserAgent LIKE '%mobile%' THEN 'Mobile'
WHEN UserAgent LIKE '%msie 7.0%' then 'IE7'
WHEN UserAgent LIKE '%msie 8.0%' then 'IE8'
WHEN UserAgent LIKE '%firefox%' then 'FireFox'
ELSE 'OTHER'
END Bucket
FROM pagedata
WHERE Url LIKE '%interestingpage%'
) Buckets
GROUP BY Bucket可以在ElasticSearch查询中完成吗?
发布于 2015-06-18 09:08:07
您可以使用带有脚本字段的术语聚合:
{
query: { filtered: { filter: { regexp: { Url : ".*interestingpage.*" } } } },
size: 0,
aggs: {
myaggregation: {
terms: {
script: "doc['UserAgent'] =~ /.*android.*/ || doc['UserAgent'] =~ /.*ipad.*/ || doc['UserAgent'] =~ /.*iphone.*/ || doc['UserAgent'] =~ /.*mobile.*/ ? 'Mobile' : doc['UserAgent'] =~ /.*msie 7.0.*/ ? 'IE7' : '...you got the idea by now...'"
}
}
}
}但要小心表演的成功!
发布于 2015-06-18 09:26:19
这是一个有趣的用例。
这是一个更灵活的搜索方法。其想法是在索引时执行所有这些正则表达式匹配,并且搜索时间要快(搜索期间的脚本,如果有很多文档,执行起来不太好,而且需要时间)。让我解释一下:
FireFox、IE8、IE7、Mobile。每个文档可以有多个这样的字段。这意味着像msie 7.0 sucks and ipad rules这样的文本只会产生两个术语:IE7和Mobile。所有这一切都是由keep 令牌过滤器实现的。
pattern_replace 令牌过滤器来实现。msie 7.0),所以您需要一种方法来捕获这两个单词(msie和7.0),其中一个是另一个。这是可以使用shingle 令牌过滤器的。让我把所有这些放在一起,并提供完整的解决办法:
PUT /test
{
"settings": {
"analysis": {
"analyzer": {
"my_pattern_replace_analyzer": {
"tokenizer": "whitespace",
"filter": [
"filter_shingle",
"my_pattern_replace1",
"my_pattern_replace2",
"my_pattern_replace3",
"my_pattern_replace4",
"words_to_be_kept"
]
}
},
"filter": {
"filter_shingle": {
"type": "shingle",
"max_shingle_size": 10,
"min_shingle_size": 2,
"output_unigrams": true
},
"my_pattern_replace1": {
"type": "pattern_replace",
"pattern": "android|ipad|iphone|mobile",
"replacement": "Mobile"
},
"my_pattern_replace2": {
"type": "pattern_replace",
"pattern": "msie 7.0",
"replacement": "IE7"
},
"my_pattern_replace3": {
"type": "pattern_replace",
"pattern": "msie 8.0",
"replacement": "IE8"
},
"my_pattern_replace4": {
"type": "pattern_replace",
"pattern": "firefox",
"replacement": "FireFox"
},
"words_to_be_kept": {
"type": "keep",
"keep_words": [
"FireFox", "IE8", "IE7", "Mobile"
]
}
}
}
},
"mappings": {
"test": {
"properties": {
"UserAgent": {
"type": "string",
"fields": {
"custom": {
"analyzer": "my_pattern_replace_analyzer",
"type": "string"
}
}
}
}
}
}
}测试数据:
POST /test/test/_bulk
{"index":{"_id":1}}
{"UserAgent": "android OS is the best firefox"}
{"index":{"_id":2}}
{"UserAgent": "firefox is my favourite browser"}
{"index":{"_id":3}}
{"UserAgent": "msie 7.0 sucks and ipad rules"}查询:
GET /test/test/_search?search_type=count
{
"aggs": {
"myaggregation": {
"terms": {
"field": "UserAgent.custom",
"size": 10
}
}
}
}结果:
"hits": {
"total": 3,
"max_score": 0,
"hits": []
},
"aggregations": {
"myaggregation": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "FireFox",
"doc_count": 2
},
{
"key": "Mobile",
"doc_count": 2
},
{
"key": "IE7",
"doc_count": 1
}
]
}
}https://stackoverflow.com/questions/30909927
复制相似问题