基于特定数据Oracle、ClickHouse、ES存储比较
笔者在工作中遇到一种情况,有一批数据需要和其他表进行各种复杂计算、并表操作,输出统计值。一般情况,类似场景都会使用Oracle视图进行处理。但本次场景发现做关联和计算后,使用视图查询效率非常低,甚至达到了无法忍受的地步(有时1min以上)。于此同时,现在OLAP数据库比较火,所以就特定场景从效率和存储上选取Oracle、ClickHouse、ElasticSearch做了一次简单的对比。
01
测试集说明与准备
由于本身业务场景是一个并表操作,使用的测试数据集有一个特点,即首先有近30列,其次每行数据基本上只有个别字段有差异,多数列重复度比较高。数据集是1000条原始数据,通过视图构建初350w条数据。
数据样例(类似只有个别几列数据不相同)
1 | aaa | ...... | 1 | 1.5 |
2 | aaa | ...... | 1 | 3 |
3 | aaa | ...... | 1 | 2 |
4 | bbb | ...... | 1 | 30 |
5 | bbb | ...... | 1 | 12 |
测试主机配置
均是虚拟机 4c 8g 500G
02
测试步骤
一、Oracle 测试
1、数据准备
从vw_test_list视图中导入数据到实体表test_detail中。
Insert into test_detail select * from vw_test_list t ;
2、查看存储大小
select * from
(select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type) t
where t.segment_name='test_detail'
3、测试查询速度
select * from test_detail t where t.test_title like '%测试数据集AAA%'
二、ClickHouse测试
1、构建数据表
注意点:
1、clickhouse客户端对回车敏感,所以如果在文本中编辑完成后,需要替换掉’\n’
2、Clickhouse使用mergetree引擎时需要指定一个date类型的字段定义主键。
2、数据加载
从oracle中导出数据,使用click-client进行数据导入
cat data.csv | clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO test_detail FORMAT CSV";
3、查看占用空间
select table as "表名",sum(rows) as "总行数",formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) "压缩率" from system.parts where table in('test_detail') group by table;
4、测试查询效率
SQL与Oracle一致
三、ElasticSearch测试
1、数据导入
使用Logstash进行数据导入
2、查看占用空间
使用 Get 请求
http://localhost:9200/_cat/indices?v
3、测试查询效率
使用Get请求
http://localhost:9200/dwzq/_search?pretty=true&size=2000 |
param: { "query": { "match": { "WARNING_TITLE": "%测试数据集AAA%" } } } |
03
结果比较
整体对比数据存放量与查询速度:
Oracle | ClickHouse | ElasticSearch | |
数据存放量 | 1.5g | 45m | 1.1g |
查询速度 | 12.219s | 0.527s | 0.298s |
04
结论与总结
本次测试只是针对特定数据集进行测试,不具备普遍性,且也没有对这oracle、clickhouse、es做特定优化,所以只能大致反应出一些结论。整体面对大宽表,clickhouse的数据压缩能力还是很强大的。同时查询速度也可以接受。在测试过程中发现,clickhouse占用机器内存比较多。
clickhouse号称分析能力杠杠的,后期会将原oracle上的分析、并表操作迁移至clickhouse并进一步对比分析。