1、ClickHouse在自助分析场景中的探索及实践|王鹏哲 转转 大数据平台&实时计算架构师转转二手交易网2|CONTENTS转转自助分析场景下转转自助分析场景下OLAP选型选型01/高斯平台自助分析场景高斯平台自助分析场景02/ClickHouse优化实践优化实践03/ClickHouse未来在转转未来在转转的规划与展望的规划与展望04/让人们享受闲置交易的快乐，让世界因流转更可持续转转二手交易网3|转转自助分析场景下转转自助分析场景下OLAP选型选型转转二手交易网4|OLAP选型背景选型背景行为行为数据数据查询扫描数据量大，精确去重/近似去重/分组计算量大。即席查询即席查询传统离线数仓经过数

2、仓分层和汇总层通用指标预计算，但是无法满足用户个性化报表需求。稳定快速稳定快速保证高可用，支持任意指标、任意维度并秒级给出反馈。转转二手交易网5|性能性能数据量级：亿级/百亿级/千亿级数据时效性：毫秒级/秒级/分钟级灵活性灵活性查询场景：聚合结果/明细数据数据链路：离线/实时查询支撑：高并发、即席查询复杂性复杂性引擎架构：门槛低、运维简单、扩展性强OLAP选型考量选型考量转转二手交易网6|OLAP引擎选型引擎选型OLAP引擎引擎技术技术优点优点缺点缺点自身存储自身存储KylinMOLAP，完全预聚合立方体1.亚秒级的查询速度，同时支持高并发2.友好的web界面以管理，监控和使用立方体1.维度较

3、多时，交叉度太深会导致预计算结果爆炸式膨胀2.灵活性较弱，不支持ad-hoc查询否Druid位图索引查找、字符串编码，预聚合技术1.实时数据摄入2.高可用、高性能、高并发1.OLAP场景支持有限，JOIN不成熟2.无法支持精确去重是ImpalaMPP系统，SQL On Hadoop1.计算基于内存，支持使用磁盘进行连接和聚合2.支持窗口函数、UDF1.对于内存依赖较大；完全依赖于Hive2.每当新的文件被添加到HDFS，该表需要被刷新否PrestoMPP系统1.跨数据源的联邦查询2.支持多表 join，支持复杂查询1.多张大表关联操作容易OOM2.并发能力不足否转转二手交易网7|OLAP引擎选

4、型引擎选型OLAP引擎引擎技术技术优点优点缺点缺点自身存储自身存储ClickHouse明细动态聚合查询，物化视图1.单机性能彪悍2.列存储、向量化引擎3.可保留明细数据1.没有完整的事务支持2.分布式表join能力较弱是DorisMPP分布式架构1.运维简单，支持在线扩缩容2.支持事务和幂等性导数，物化视图自动聚合，查询自动路由1.版本迭代更新较快，成熟度不足2.大规模数据的复杂ETL容易内存不足是转转二手交易网8|ClickHouse是什么是什么0102030405完备的DBMS功能；较为完善的SQL支持。列式存储和数据压缩；支持索引。向量化引擎与SIMD提高了CPU利用率，多核多节点并行。

5、亚秒级查询响应。支持数据复制和数据完整性。多样化的表引擎。ClickHouse是一个面向实时联机分析处理(OLAP)的基于列存储的开源分析引擎。Yandex（俄罗斯最大的搜索引擎）于2016年6月15日开源；开发语言为C+；是一款PB级的交互式分析引擎。转转二手交易网9|01020304ClickHouse应用场景应用场景交互式报表交互式报表AB TEST用户画像系统用户画像系统监控系统监控系统转转二手交易网10|高斯平台自助分析场景转转二手交易网11|系统介绍系统介绍 埋点数据管理：埋点元数据纳管，埋点质量统一监控。自助分析：基于业务特点和多部门复合需求，提供多维度、多指标的交叉分析能力，全

6、面支撑日常数据分析需求。GAOS高斯平台高斯平台埋点埋点管理管理自助自助分析分析画像画像标签标签ABTEST转转二手交易网12|高斯平台高斯平台-系统架构系统架构数据接入数据接入 离线数据：SeaTunnel+调度平台 实时数据：Flink ClickHouseSink数据服务数据服务 对外：统一封装服务，外部调用 对内：提供客户端工具数据数据清洗清洗 离线数据：Hive ETL 实时数据：Flink+维表关联转转二手交易网13|高斯平台高斯平台-高可用高可用架构架构优势优势由ReplicatedMergeTree表引擎管理数据副本（依赖Zookeeper）。劣势劣势集群配置比较复杂，维护成本

7、比较大。转转二手交易网14|高斯平台高斯平台-集群现状集群现状集群规模集群规模服务器数量：20副本设置：双副本数据数据规模规模存量数据：40T+日增数据：20亿+TTL设置：186天平台统计：核心看板500+日活跃用户 200+业务支撑：在线报表查询+数据分析业务分析能力：报表型查询毫秒级响应；分析型查询大部分秒级响应支撑能力支撑能力转转二手交易网15|业务场景业务场景-行为分析行为分析业务背景业务背景近期上线一个活动专题，产品/运营想查看该活动页面各坑位的点击效果。技术实现技术实现MATERIALIZED VIEW（POPULATE）+SummingMergeTree+MergeTree转转

8、二手交易网16|业务场景业务场景-AB TEST分析分析业务背景业务背景转转内部AB实验应用非常广泛，特别用来验证推荐算法和功能优化的效果。传统T+1的离线AB实验指标时效性无法满足业务需求，需要实时实验指标观察功能或者算法上线后的效果。技术技术方案方案转转二手交易网17|实时写入实时写入-问题与挑战问题与挑战问题：问题：Too many parts(311).Merges are processing significantly slower than inserts解决：解决：1、Client端调整：降低并发度，自定义Sink灵活性增加，可配置时间、批次大小、写入本地表2、Server端参

9、数调整：parts_to_throw_insert原因：原因：频繁小批量插入，merge速度跟不上插入的速度转转二手交易网18|业务场景业务场景-亿级数据亿级数据JOIN业务背景业务背景需要用户曝光/点击情况，来JOIN用户画像表，用以匹配圈选人群和用户特征行为的关系。技术技术方案方案联接字段分桶JOIN 本地表写入：数据导入按照关联字段（用户ID）进行分桶写入shard分片 分布式表写入：Distributed(cluster_name,database_name,table_name,sharding_key)转转二手交易网19|亿级数据亿级数据JOIN-底层原理底层原理技术原理技术原理由

10、于数据已经预分区了，相同的JOIN KEY对应的数据一定在一起，不会跨节点存在，所以无需对右表做分布式查询，也能获得正确结果。转转二手交易网20|高斯平台高斯平台当前痛点当前痛点02不支持事务性的 DDL 与 DML 操作，且多副本模式的元数据管理强依赖于 Zookeeper。01部分业务场景需要高并发查询，ClickHouse高并发能力弱，性能下降。03缺少完整的UPDATE、DELETE操作04集群需要扩容时，ClickHouse 缺少自动的 rebalance 机制，横向扩容时需要借助第三方工具或者手动 rebalance,扩容缩容运维复杂。转转二手交易网21|ClickHouse优化实

11、践转转二手交易网22|问题实践问题实践-内存相关内存相关问题报错问题报错Memory limit(total)exceeded:would use 169.14 GiB(attempt to allocate chunk of 4312306 bytes),maximum:169.14 GiB:While executingAggregatingTransform.分析及解决分析及解决配置参数(max_memory_usage)限制SQL的查询内存使用的上限，当内存使用量大于该值的时候，查询被强制KILL。配置max_bytes_before_external_group_by参数，当使用内存

12、到达该阈值，进行磁盘group by；配置max_bytes_before_external_sort参数，当使用内存到达该阈值，进行磁盘order by；count distinct内存不够，推荐使用一些预估函数，这样不仅可以减少内存的使用同时还会提升查询速度；转转二手交易网23|问题实践问题实践-Zookeeper相关相关问题报错问题报错Code:999.DB:Exception:Received from:9000.DB:Exception:Cannot allocate block number inZooKeeper:Coordination:Exception:Connection

13、 loss.Code:225,DB:Exception:Received from:9000,DB:Exception:ZooKeeper session has been expired.分析及解决分析及解决与Zookeeper的连接丢失导致不能分配块号等问题；Zookeeper会话超时。调整MaxSessionTimeout参数，加大Zookeeper会话最大超时时间 在Zookeeper中将dataLogDir、dataDir目录分离，可使用SSD存储 ClickHouse建表的时候添加use_minimalistic_part_header_in_zookeeper参数，对元数据进行压

14、缩存储转转二手交易网24|性能调优性能调优-基础参数基础参数配置参数配置参数参数含义参数含义建议值建议值max_concurrent_queries最大并发处理的请求数默认值100，建议150-300max_memory_usage单个查询的最大RAM使用量建议总内存的80%max_memory_usage_for_all_queries单服务器所有查询使用的最大内存建议总内存的80-90%max_memory_usage_for_user用户查询的最大RAM量max_bytes_before_external_group_bygroup by使用内存超出阈值后会刷新到磁盘进行max_memo

15、ry_usage/2max_bytes_before_external_sortorder by使用内存超出阈值后会溢出磁盘进行排序background_pool_size后台线程池的大小默认值16，建议32转转二手交易网25|性能调优性能调优-建建表表/查询规范查询规范数据类型数据类型 建表时能用数值型或日期时间型表示的字段就不用String；数值类型group by最快 不建议使用Nullable，Nullable列无法被索引查询查询调优调优 列裁剪和分区裁剪;尽量避免在大数据集上使用虚拟列;数据字典：将一些经常需要关联分析的业务创建成字典表进行join操作，前提是字典表不宜太大，因为字典

16、表会常驻内存 物化视图：对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免数据查询时重复计算的过程 使用Global：在分布表in或者join时使用，避免出现查询指数级放大转转二手交易网26|ClickHouse未来在转转的规划与展望转转二手交易网27|未来规划及展望未来规划及展望业务方易用性、多租户隔离、限流熔断、监控报警，业务治理。服务平台化、故障规范化服务平台化、故障规范化存算分离；集群横向扩展、数据均衡。ClickHouse容器化部署容器化部署根据业务场景，自适应选择ClickHouse、DorisDB引擎。服务架构智能化服务架构智能化实时写入一致性保证；分布式事务支持；移除Zookeeper服务依赖。ClickHouse内核级优化内核级优化THANK YOU|