ClickHouse 在广告系统场景中的实践

目录

• 前言
• 需求分析
• 主要特点
• 适用场景
• I/O情况
• 测试结论

---

前言

随着数据量的增长，传统DB已经无法满足实时聚合分组排序分析需求，目前比较流行日OLAP系统有：Presto，Druid，Kylin，TiFlash，ClickHouse等，对现有流行OLAP进行调研和实验，ClickHouse能够解决业务的相似问题。

需求分析

广告业务数据分析维度较多，广告维度和时间维度组合查询条件较为复杂，查询时间跨度大，需要稳定，快速返回查询结果给客户。业务难点：当数据量和计算复杂度增加后，响应时间会逐渐变慢，从毫秒级上升到秒级，甚至到分钟级。

从需求上面我们可以看出，需要稳定的，基于SQL的，足够快的引擎，回溯数据方便快速，易扩容的，方能支持数据报表业务。

主要特点

• 绝大多数请求是以读为主
• 真正的面向列的DBMS
• 实时数据更新
• SQL语法支持
• 多核并行处理
• 数据高效压缩
• 分布式处理
• 数据复制完整性
• 丰富的索引
• 数据剪枝能力比较强
• 集群式管理
• 可直接读取MySQL数据
• 适合于在线实时查询
• 支持近似预估计算

适用场景

• 海量数据分析
• 数据报表
• 监控

I/O情况

1. 分析查询时，只需要读取少量的列。在面向列的数据库中，您只能读取所需的数据。例如，如果您需要20列中的5列，则I/O减少4倍+-。
2. 数据是以数据包形式读取，因此压缩比较容易，这进一步减少了I/O量。
3. 由于减少的I/O，更多的数据适合在系统缓存中。

测试结论

提前批量导入抽样的6亿+广告数据，连续一年日期用于用例测试。

ClickHouse批量导入（集群多副本，Distributed表引擎，表结构存储于Zookeeper，INSERT INTO tb FORMAT TSV方式）数据15～120s/天。天级别生产数据满足性能需要，另外、程序实现写入速度为：50000-230000/s。

Druid构建一天的数据（受HDFS集群负载影响）30m+/天，天级别生产数据勉强可以。如果数据需要回溯，简直是一种灾难！

ClickHouse测试表现：

测试数据总量：

SELECT count(*) FROM tb;

┌───count()─┐
│ 600009888 │
└───────────┘

1 rows in set. Elapsed: 0.003 sec. 

查询某个用户的下物料总数：

SELECT COUNT(0) FROM tb WHERE (uid=12) AND (d>='2019-01-01') AND (d<=2020-02-29'')

┌─COUNT(0)─┐
│  5517277 │
└──────────┘

1 rows in set. Elapsed: 0.635 sec. Processed 5.97 million rows, 53.72 MB (9.39 million rows/s., 84.53 MB/s.) 

查询某个用户下词列表：

SELECT uid, uname,  words, SUM(pv) pv, SUM(click) click, SUM(costs) costs FROM tb WHERE (a=12) AND (d>='2019-01-01') AND (d<=2020-02-29'') GROUP BY uid, uname, words ORDER BY costs DESC LIMIT 0, 30;

┌────────uid─┬─uname─────────┬─────────bid─┬─words──────────────┬──────pvs─┬─clicks─┬───────costs─┐
│ 3145298302 │ PC超亿12_精确 │ 26080049777 │ 首页官网           │ 37248919 │ 234492 │ 70347.60000 │
│ 3145298302 │ PC超亿12_精确 │ 26186326337 │ 网站大全           │  6206134 │ 104139 │ 31241.70000 │
│ 3145298302 │ PC超亿12_精确 │ 24816548095 │ 板                 │  1457180 │  93951 │ 28185.30000 │
│ 3145298302 │ PC超亿12_精确 │ 26107100801 │ 图                 │  1896121 │  84312 │ 25293.60000 │
│ 3145298302 │ PC超亿12_精确 │ 26834629020 │ 官网               │   487345 │  61395 │ 21226.10000 │
│ 3145298302 │ PC超亿12_精确 │ 26196959633 │ 漫画               │  1480118 │  25444 │ 15834.42000 │
│ 3145298302 │ PC超亿12_精确 │ 26186329636 │ 联盟               │   456358 │  46051 │ 13815.30000 │
│ 3145298302 │ PC超亿12_精确 │ 24857366663 │ 查询电话           │  5278596 │  25568 │ 12142.75000 │
│ 3145298302 │ PC超亿12_精确 │ 24857357703 │ 模板               │   574470 │  34251 │ 10275.30000 │
│ 3145298302 │ PC超亿12_精确 │ 25975600742 │ 百度               │ 28774114 │  34157 │ 10247.10000 │
│ 3145298302 │ PC超亿12_精确 │ 23537517700 │ 儿童               │   387200 │  30973 │  9291.90000 │
│ 3145298302 │ PC超亿12_精确 │ 26742512966 │ 阅读网             │  2535672 │  28294 │  8488.20000 │
│ 3145298302 │ PC超亿12_精确 │ 26712075029 │ 查询网             │  3070835 │  21588 │  7929.20000 │
│ 3145298302 │ PC超亿12_精确 │ 26742550022 │ 直播               │   221523 │  25977 │  7793.10000 │
│ 3145298302 │ PC超亿12_精确 │ 26186205650 │ 全集观看           │  2188534 │  20392 │  6736.84000 │
│ 3145298302 │ PC超亿12_精确 │ 26107100833 │ 地图               │   143585 │  19935 │  5980.50000 │
│ 3145298302 │ PC超亿12_精确 │ 27040647713 │ 客户端             │   570540 │  16346 │  4903.80000 │
│ 3145298302 │ PC超亿12_精确 │ 26800482794 │ 单号查询           │  2003334 │  16252 │  4875.60000 │
│ 3145298302 │ PC超亿12_精确 │ 26776266759 │ 房屋租赁           │   380903 │  16219 │  4865.70000 │
│ 3145298302 │ PC超亿12_精确 │ 26776381177 │ 快递单号           │  1851953 │  14473 │  4341.90000 │
│ 3145298302 │ PC超亿12_精确 │ 26186219167 │ 加减法             │   148837 │  14460 │  4338.00000 │
│ 3145298302 │ PC超亿12_精确 │ 26954252248 │ 精品               │  1280662 │  14390 │  4317.00000 │
│ 3145298302 │ PC超亿12_精确 │ 26924100555 │ 播放器             │   855142 │  14163 │  4248.90000 │
│ 3145298302 │ PC超亿12_精确 │ 26479917828 │ 图片               │   179839 │  12593 │  3976.10000 │
│ 3145298302 │ PC超亿12_精确 │ 22671940297 │ 人体               │   220719 │  13204 │  3961.20000 │
│ 3145298302 │ PC超亿12_精确 │ 26001297994 │ 思维               │   205330 │  12639 │  3791.70000 │
│ 3145298302 │ PC超亿12_精确 │ 24816632460 │ 植物               │   448441 │  12420 │  3726.00000 │
│ 3145298302 │ PC超亿12_精确 │ 26712060443 │ 手游               │   201656 │  12360 │  3708.00000 │
│ 3145298302 │ PC超亿12_精确 │ 26742132975 │ 六年               │   297673 │  11971 │  3591.30000 │
│ 3145298302 │ PC超亿12_精确 │ 26800365256 │ 全国               │   778811 │  11878 │  3563.40000 │
└────────────┴───────────────┴─────────────┴────────────────────┴──────────┴────────┴─────────────┘

30 rows in set. Elapsed: 0.657 sec. Processed 5.97 million rows, 556.60 MB (9.09 million rows/s., 847.34 MB/s.) 

从造用场景上ClickHouse更贴近于目前的数据业务，性能表现上更有优势，批量生产数据时速度最快。