一、功能场景
在mysql数据库中查询数据时，我们可以采用dinstinct关键字去重。那么，在ES中如何实现查询结果去重呢？
二、原理分析
DISTINCT关键字去重的sql语句等价于对需要去重的字段进行GROUP BY。
以下2个sql都能实现对name,age字段查询结果的去重。
SELECT DISTINCT name,age FROM distinct_index
SELECT name,age FROM distinct_index GROUP BY name,age
三、方案汇总
根据上面的分析，总结出以下2种实现方案：
1、使用GROUP BY的SQL查询
2、使用Aggregation(聚合)查询
说明：
ES6.3之后的版本以及支持SQL查询
四、数据准备
## 删除索引
## DELETE distinct_index
## 新建索引
PUT distinct_index
{
"mappings": {
"properties": {
"name": {
"type": "keyword"
},
"age": {
"type": "integer"
}
}
}
}
## 添加数据
POST distinct_index/_bulk?refresh
{ "create": { } }
{ "name": "小天", "age": 25}
{ "create": { } }
{ "name": "小天", "age": 25}
{ "create": { } }
{ "name": "老万", "age": 35}
{ "create": { } }
{ "name": "老王", "age": 45}
{ "create": { } }
{ "name": "小明", "age": 15}
{ "create": { } }
{ "name": "小明", "age": 15}
{ "create": { } }
{ "name": "小红", "age": 12}
{ "create": { } }
{ "name": "乐乐", "age": 18}
五、方案实战
1、SQL查询方案
##可以通过format参数控制返回结果的格式，txt表示文本格式，看起来更直观点，默认为json格式。
POST _sql?format=txt
{
"query": "SELECT name,age FROM distinct_index group by name,age"
}
查询结果：
和预期相符，查询结果达到去重的效果。
SQL语句转DSL：
POST /_sql/translate
{
"query": "SELECT name,age FROM distinct_index group by name,age"
}
结果：
{
"size" : 0,
"_source" : false,
"stored_fields" : "_none_",
"aggregations" : {
"groupby" : {
"composite" : {
"size" : 1000,
"sources" : [
{
"f5b401c4" : {
"terms" : {
"field" : "name",
"missing_bucket" : true,
"order" : "asc"
}
}
},
{
"f557e07b" : {
"terms" : {
"field" : "age",
"missing_bucket" : true,
"order" : "asc"
}
}
}
]
}
}
}
}说明：
通过将sql语句转为dsl语句可以发现，sql语句中的group by查询底层原理是转化成了dsl中的aggregations聚合查询。
2、aggregations聚合查询
POST distinct_index/_search
{
"from": 0,
"size": 0,
"aggregations": {
"name": {
"terms": {
"field": "name",
"size": 2147483647
},
"aggregations": {
"age": {
"terms": {
"field": "age",
"size": 2147483647
}
}
}
}
}
}
查询结果：
{
"took" : 3,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 8,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"name" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "小天",
"doc_count" : 2,
"age" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 25,
"doc_count" : 2
}
]
}
},
{
"key" : "小明",
"doc_count" : 2,
"age" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 15,
"doc_count" : 2
}
]
}
},
{
"key" : "乐乐",
"doc_count" : 1,
"age" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 18,
"doc_count" : 1
}
]
}
},
{
"key" : "小红",
"doc_count" : 1,
"age" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 12,
"doc_count" : 1
}
]
}
},
{
"key" : "老万",
"doc_count" : 1,
"age" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 35,
"doc_count" : 1
}
]
}
},
{
"key" : "老王",
"doc_count" : 1,
"age" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 45,
"doc_count" : 1
}
]
}
}
]
}
}
}六、异常记录
1、ES中的sql查询不支持DISTINCT关键字
POST _sql?format=txt
{
"query": "SELECT DISTINCT name,age FROM distinct_index"
}
报错：
{
"error" : {
"root_cause" : [
{
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 1:8: SELECT DISTINCT is not yet supported"
}
],
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 1:8: SELECT DISTINCT is not yet supported"
},
"status" : 400
}2、ES的sql查询中表名中不能有中划线，比如my-index-000001
PUT my-index-000001
{
"mappings": {
"properties": {
"name": {
"type": "keyword"
},
"age": {
"type": "integer"
}
}
}
}
POST _sql?format=txt
{
"query": "SELECT name FROM my-index-000001"
}
报错：
{
"error" : {
"root_cause" : [
{
"type" : "parsing_exception",
"reason" : "line 1:20: extraneous input '-' expecting {<EOF>, ',', 'ANALYZE', 'ANALYZED', 'AS', 'CATALOGS',
……遇到这种情况，最简单方法是给索引添加别名。
POST /_aliases
{
"actions" : [
{ "add" : { "index" : "my-index-000001", "alias" : "my_index" } }
]
}
POST _sql?format=txt
{
"query": "SELECT name FROM my_index"
}
总结
本文主要介绍了ES中如何实现类似dinstinct的数据去重功能。
1、首先通过通过dinstinct和group by的等价sql语句，说明可以通过分组函数实现数据去重。
2、分别介绍了ES中通过sql语句查询和aggregations聚合查询实现对查询结果的去重。
3、ES中的sql查询不支持DISTINCT关键字
4、ES中的sql查询中表名不能包含中划线，比如my-index-000001