基于特定数据Oracle、ClickHouse、ES存储比较

笔者在工作中遇到一种情况，有一批数据需要和其他表进行各种复杂计算、并表操作，输出统计值。一般情况，类似场景都会使用Oracle视图进行处理。但本次场景发现做关联和计算后，使用视图查询效率非常低，甚至达到了无法忍受的地步（有时1min以上）。于此同时，现在OLAP数据库比较火，所以就特定场景从效率和存储上选取Oracle、ClickHouse、ElasticSearch做了一次简单的对比。

01

测试集说明与准备

由于本身业务场景是一个并表操作，使用的测试数据集有一个特点，即首先有近30列，其次每行数据基本上只有个别字段有差异，多数列重复度比较高。数据集是1000条原始数据，通过视图构建初350w条数据。

数据样例(类似只有个别几列数据不相同)

1aaa......11.52aaa......133aaa......124bbb......1305bbb......112

测试主机配置

均是虚拟机 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

结果比较

整体对比数据存放量与查询速度：

OracleClickHouseElasticSearch数据存放量1.5g45m1.1g查询速度12.219s0.527s0.298s

04

结论与总结

本次测试只是针对特定数据集进行测试，不具备普遍性，且也没有对这oracle、clickhouse、es做特定优化，所以只能大致反应出一些结论。整体面对大宽表，clickhouse的数据压缩能力还是很强大的。同时查询速度也可以接受。在测试过程中发现，clickhouse占用机器内存比较多。

clickhouse号称分析能力杠杠的，后期会将原oracle上的分析、并表操作迁移至clickhouse并进一步对比分析。