ES学习笔记 - 实现列分组统计

Elasticsearch是Elastic Stack核心的分布式搜索和分析引擎。

1.背景

因为业务调整采用了ES作为数据库，所以需要了解ES对于这一块的设计如何实现类似Mysql中Group By的查询的效果。

2.实现方式

ES实现Group By有两种方式：TermsAgg、CompositeAgg，它们也具有不同程度的优缺点。

TermsAgg的使用方式非常粗暴，直接进行桶嵌套即可，如下:

TermsAgg请求DSL语句

{
	"aggregations": {
		"group_app": {
			"terms": {
				"field": "app",
				"size": 1000000
			},
			"aggregations": {
				"group_campaign_id": {
					"terms": {
						"field": "campaign_id",
						"size": 1000000
					}
				}
			}
		}
	},
	"size": 0
}

TermsAgg执行结果

{
    "took": 142,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 175033,
        "max_score": 0.0,
        "hits": []
    },
    "aggregations": {
        "group_app": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "com.aa.bb.cc",
                    "doc_count": 59929,
                    "group_campaign_id": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "Campaign_118",
                                "doc_count": 56466
                            },
                            {
                                "key": "Campaign_119",
                                "doc_count": 1937
                            }
                        ]
                    }
                },
                {
                    "key": "com.dd.ee.ff",
                    "doc_count": 23231,
                    "group_campaign_id": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "Campaign_120",
                                "doc_count": 16692
                            },
                            {
                                "key": "Campaign_121",
                                "doc_count": 5336
                            }
                        ]
                    }
                }
            ]
        }
    }
}

CompositeAgg的使用方式略微有一些不同，下面：

CompositeAgg请求DSL语句

{
	"aggregations": {
		"group_by": {
			"composite": {
				"sources": [
					{
					    "app": {
					        "terms": {
					            "field": "app"
					        }
					    }
					},
					{
					    "campaign_id": {
						    "terms": {
						        "field": "campaign_id"
						    }
					    }
					}
				],
                "size": 1000
			}
		}
	},
	"size": 0
}

CompositeAgg执行结果

{
    "took": 14,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 175033,
        "max_score": 0.0,
        "hits": []
    },
    "aggregations": {
        "group_by": {
            "after_key": {
                "app": "com.dd.ee.ff",
                "campaign_id": "Campaign_120"
            },
            "buckets": [
                {
                    "key": {
                        "app": "com.aa.bb.cc",
                        "campaign_id": "Campaign_119"
                    },
                    "doc_count": 182
                },
                {
                    "key": {
                        "app": "com.dd.ee.ff",
                        "campaign_id": "Campaign_120"
                    },
                    "doc_count": 40
                }
            ]
        }
    }
}

3.优缺点

TermsAgg

优点：使用简单、没有数据量限制。

缺点：数据结构层次深、不支持分页。

CompositeAgg

优点：使用简单、数据结构清晰可读，支持分页。

缺点：有数据量限制。

最后

至于ES这两种分组方式的性能方面还没有研究，后续学习笔记会更新性能上面的差异。

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html // 附上ES官方文档