如何实现ClickHouse与Elasticsearch聚合性能对比测试
本篇文章为大家展示了如何实现ClickHouse与 Elasticsearch聚合性能对比测试,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
为金坛等地区用户提供了全套网页设计制作服务,及金坛网站建设行业解决方案。主营业务为做网站、成都网站设计、金坛网站设计,以传统方式定制建设网站,并提供域名空间备案等一条龙服务,秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求,就会得到认可,从而选择与我们长期合作。这样,我们也可以走得更远!
Elasticsearch以其优秀的分布式架构与全文搜索引擎等特点在机器数据的存储、分析领域广为使用,但随着数据量的增长,其聚合分析性能已无法满足业务需求。而ClickHouse作为一个高性能的OLAP列式数据库管理系统有望解决这一痛点。
本文是对ClickHouse与Elasticsearch聚合性能的简单对比测试。主要关注查询语句的响应时间,暂不考虑资源占用情况。
组件 | 版本 | CPU | 内存 |
---|---|---|---|
ClickHouse | 7.9.0 | 4C | 8G |
Elasticsearch | 20.11.4.13 | 4C | 8G |
使用ClickHouse官方提供的测试数据集,共67G,约6亿行。
其中,ClickHouse使用LO_ORDERDATE字段作为分区键,使用LO_ORDERDATE, LO_ORDERKEY作为排序键。
某字段出现次数TOP 10
# ClickHouse
SELECT LO_SHIPMODE,COUNT() FROM lineorder GROUP BY LO_SHIPMODE ORDER BY COUNT() DESC LIMIT 10
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"1": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 10
}
}
},
"size": 0
}
某字段按年进行计数
# ClickHouse
SELECT toYear(LO_ORDERDATE),COUNT() FROM lineorder GROUP BY toYear(LO_ORDERDATE) FORMAT PrettyCompactMonoBlock
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"2": {
"date_histogram": {
"field": "LO_ORDERDATE",
"calendar_interval":"1y",
"format":"yyyy-MM-dd"
}
}
},
"size": 0
}
多个字段按年进行统计
# ClickHouse
SELECT LO_ORDERDATE,LO_ORDERKEY,LO_SHIPMODE,LO_ORDERPRIORITY,LO_COMMITDATE FROM lineorder WHERE LO_ORDERDATE >= '1992-01-01' AND LO_ORDERDATE < '1993-01-01' ORDER BY LO_ORDERDATE LIMIT 500
# Elasticsearch
GET lineorder/_search
{
"size": 500,
"sort": [
{
"timestamp": {
"order": "desc",
"unmapped_type": "boolean"
}
}
],
"query": {
"bool": {
"must": [],
"filter": [
{
"match_all": {}
},
{
"match_all": {}
},
{
"range": {
"LO_ORDERDATE": {
"gte": "1992-01-01",
"lte": "1993-01-01",
"format": "strict_date_optional_time"
}
}
}
],
"should": [],
"must_not": []
}
}
}
基于时间的多字段聚合
# ClickHouse
SELECT toYear(LO_ORDERDATE),LO_SHIPMODE,COUNT() FROM lineorder GROUP BY toYear(LO_ORDERDATE),LO_SHIPMODE ORDER BY toYear(LO_ORDERDATE) FORMAT PrettyCompactMonoBlock
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"3": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 10
},
"aggs": {
"2": {
"date_histogram": {
"field": "LO_ORDERDATE",
"calendar_interval": "1y",
"time_zone": "Asia/Shanghai",
"min_doc_count": 1
}
}
}
}
},
"size": 0
}
基于时间的多字段聚合
# ClickHouse
SELECT toYear(LO_ORDERDATE),LO_SHIPMODE,COUNT() FROM lineorder GROUP BY toYear(LO_ORDERDATE),LO_SHIPMODE ORDER BY toYear(LO_ORDERDATE) FORMAT PrettyCompactMonoBlock
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"3": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 10
},
"aggs": {
"2": {
"date_histogram": {
"field": "LO_ORDERDATE",
"calendar_interval": "1y",
"time_zone": "Asia/Shanghai",
"min_doc_count": 1
}
}
}
}
},
"size": 0
}
聚合嵌套(非时间字段)
# ClickHouse
SELECT LO_SHIPMODE,COUNT(LO_SHIPMODE),LO_ORDERPRIORITY,COUNT(LO_ORDERPRIORITY) FROM lineorder GROUP BY LO_SHIPMODE,LO_ORDERPRIORITY ORDER BY COUNT(LO_SHIPMODE),COUNT(LO_ORDERPRIORITY) LIMIT 5 BY LO_SHIPMODE,LO_ORDERPRIORITY
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"2": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 5
},
"aggs": {
"3": {
"terms": {
"field": "LO_ORDERPRIORITY.keyword",
"order": {
"_count": "desc"
},
"size": 5
}
}
}
}
},
"size": 0
}
聚合场景 | ck(ms) | es(ms) | 性能对比 |
---|---|---|---|
基于时间的多字段聚合 | 5506 | 15599 | 近3倍 |
多个字段按年进行计数(数据表) | 381 | 6267 | 16倍多 |
某字段出现次数 TOP 10(饼图) | 4048 | 7317 | 近2倍 |
某字段按年进行计数(时间趋势图) | 901 | 23257 | 25倍多 |
聚合嵌套(非时间字段) | 6937 | 15767 | 2倍多 |
相同数据量下,ClickHouse的聚合性能都要优于Elasticsearch,且如果基于排序键进行聚合,性能更好,是ES的数倍。
此外,ClickHouse的SummaryMergeTree、AggregatingMergeTree表引擎支持后台自动聚合数据,所以在某些场景下其聚合分析性能会更优。
上述内容就是如何实现ClickHouse与 Elasticsearch聚合性能对比测试,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注创新互联行业资讯频道。
文章题目:如何实现ClickHouse与Elasticsearch聚合性能对比测试
链接URL:http://myzitong.com/article/gddico.html