ClickHouse 在十亿级用户画像平台的应用实践

MCtalk 技术文章/2021.09.26 文｜周磊 网易云商资深应用开发工程师

---

导读：

ClickHouse 以其卓越的分析性能、极好的线性扩容能力和丰富的功能，成为近年来开源 OLAP 数据库领域的一匹黑马。本文将与大家分享网易定位产品线基于 ClickHouse 构建用户画像平台的实践经验。

业务背景

网易定位产品线的用户画像平台目前主要应用在问卷自动化投放、数字罗盘等业务场景。通过提供标签分类、人群数预估、人群画像、人群包创建等功能，赋能业务方实现用户精细化运营和精准投放。典型业务流程如下：

​

标签圈选(或基于地理位置圈选)、人群数预估

以可视化的勾选和拖拽形式，自由组合标签来圈选目标人群，并实时返回目标人群数量。

​

基于地理坐标来圈选目标人群。

​

人群画像

形成多维立体的用户画像，勾勒群体特征。根据特征，查看人群是否符合投放要求。

​

生成人群包投放

实时生成人群包，并提供人群内用户详情信息的查询能力。 业务方根据详情信息(例如: 手机号、微信等)进行投放。

面临的挑战

数据量大

目前平台拥有12亿+的用户数据、3万+的标签及标签枚举，单个人群包内的用户数量可达亿级，人群包的条数也可达亿级。

计算复杂

标签圈选的条件可达上百个，符合条件的人群需要进行大量的交并补计算。

查询时长要求短

如果人群数预估、人群创建的耗时较长，对业务方的影响较大。所以通常需要在5s 以内返回。

业务使用场景复杂

多个亿级表之间需要进行 Join 操作，同时对结果再进行大量的聚合运算，以满足业务需要。

为什么选择 ClickHouse？

ClickHouse 的核心特性

完备的 DBMS 功能。 完备的数据库管理功能，包括 DML（数据操作语言）、DDL（数据定义语言）、权限控制、数据备份与恢复、分布式管理。

列式存储与数据压缩。 数据按列存储，在按列聚合的场景下，可有效减少查询时所需扫描的数据量。 同时，按列存储数据对数据压缩有天然的友好性（列数据的同类性），降低网络传输和磁盘 IO 的压力。

关系模型与 SQL。 ClickHouse 使用关系模型描述数据并提供了传统数据库的概念（数据库、表、视图和函数等）。与此同时，使用 SQL 作为查询语言（例如: GROUP BY、ORDER BY、JOIN、IN 等大部分标准 SQL） ，使得它更容易理解和学习，并轻松与第三方系统集成。

多样化的表引擎。 ClickHouse 共拥有合并树、内存、文件、接口和其他6大类20多种表引擎。其中每一种表引擎都有着各自的特点（例如适合聚合型、明细查询型等），用户可以根据实际业务场景的要求，选择合适的表引擎使用。

数据分片与分布式查询。 将数据横向切分，分布到集群内各个服务器节点进行存储。与此同时，可将数据的查询计算下推至各个节点并行执行，提高查询速度。

丰富的函数集。 提供上千种函数满足各类聚合、计算以及数据处理的需要。

实时查询。 相比 SparkSQL、Vertica、Hive、ElasticSearch 等其他分析型数据库，ClickHouse 真正阐释了“实时”二字的含义。即便是在复杂聚合和查询的场景下，它也能够做到极快响应，且无须对数据进行任何预处理加工。

选择 ClickHouse 的理由

除了以上总结的列式存储、完备的 DBMS、分布式架构、数据分片、计算下推等基础特性，从满足业务需求的角度来看，我们最终选择 ClickHouse 的原因如下：

分析性能翻倍。 原有系统中，6亿用户的宽表数据在 ElasticSearch 中完成一种规则的画像计算耗时大约40秒左右。而在基于 ClickHouse 的新系统中，12亿用户的宽表数据完成相同规则的画像计算，耗时只需8秒左右，性能提升数倍。

简化数据处理流程。 由于 ClickHouse 直接基于宽表（明细表）进行聚合计算的卓越性能，使得系统省去了大量的数据预加工、预处理操作。大部分的统计分析需求，可以直接基于宽表进行计算和查询。

支持关系模型和 SQL。 关系型数据库和 SQL 对于开发人员有天然的亲和度，相比较 ElasticSearch、Spark 等降低了学习成本和应用难度。

开源、社区活跃度高。 版本迭代非常快，几乎几天到十几天更新一个小版本，发展趋势迅猛。

ClickHouse 在用户画像平台的实践

总体架构

​

架构图主要以和 ClickHouse 相关的模块为主， 与系统相关的 api 接口、限流、熔断、负载均衡、服务发现、权限体系以及监控等模块不在本文的讨论范畴。我们依据数据的流向将 ClickHouse 的应用架构划分为3个层级，下面将针对各层级内的模块如何应用 ClickHouse 实现业务功能以及实现过程中遇到的问题及解决方案进行详述。在此之前，先列出核心表结构设计及关键参数设置，因为各模块与 ClickHouse 的交互都是围绕这几张表展开。

核心表结构设计以及关键参数设置

用户画像平台主要涉及4张核心表：

1. upp_user:

​

upp_user 表用于存储10亿+用户及其对应的标签信息，也称为明细表。 由于篇幅限制，这里只列出了其中一部分标签字段（真实字段大约2000+左右）。

ENGINE (表引擎) 选择 ReplicatedMergeTree，该引擎是在 MergeTree 能力的基础之上增加了分布式协同的能力，借助 ZooKeeper 的消息日志广播功能，实现了副本实例之间的数据同步功能，从而保证数据的可靠性。

ODER BY 参数可以为设置的字段添加索引。index_granularity 表示索引粒度，默认值8192对于大部分的应用场景是最佳的选择。

2. upp_label_bitmap：

​

upp_label_bitmap 表用于存储标签枚举值和对应人群的 id 列表。 这里用到了 ClickHouse 非常重要的功能函数集 Bitmap。Bitmap 字面意思解释为位图，准确翻译为基于位的映射， 就是用一个 bit 位来标记某个元素对应的 value。

Bitmap 算法在处理大量数据的排序、查询、去重以及在用户群做交集和并集运算的时候有极大的便利性和极高的性能。由于采用了 bit 为单位来存储数据，因此存储空间也大大节省。

表中 tag_key 字段为 String 类型，存储标签+枚举值字符串。 例如：age#5，表示年龄在40岁-44岁的人群标签；dwids 字段用 AggregateFunction（groupBitmap，UInt64）关键字声明为 bitmap 类型，用来存储符合该标签枚举的人群 id 列表。

3. upp_customer_group_bitmap：

​

upp_customer_group_bitmap 存储客群 id 和对应人群的 id 列表。 dwids 字段也声明为 bitmap 类型，用来存储该客群 id 下的人群 id 列表。

4. upp_user_geo：

​

upp_user_geo 存储具有地理位置信息用户的经度、纬度数据。 用于基于地理位置圈选人群的场景。

数据接入层

数据接入层负责离线数据的导入工作。通过编写 Spark 应用，将该应用部署在网易猛犸数据平台，从 Hive 同步宽表、标签表数据至 MySQL 和 ClickHouse（建议使用官方的 ClickHouse-jdbc）。该应用具体同步方法如下：

• 将标签分类、标签以及标签枚举数据写入 MySQL。
• 从 Hive 读取12亿宽表数据，直接通过 insert 语句写入 ClickHouse 的upp_user 表。ClickHouse-jdbc 支持批量写入方式，可提高写入速度。

示例代码如下：

​

upp_label_bitmap 表数据的生成。通过遍历每个标签枚举并执行以下 sql 完成 bitmap 数据的生成。

其中 age#5 表示标签枚举，groupBitmapState() 函数会将查询出的 dwid 列转为 bitmap 值，splitByChar() 可以用来分割 age 列中的多值数据。sql 示例如下：

insert into upp_label_bitmap (tag_key, dwids) select 'age#5', groupBitmapState(dwid) from upp_user where has(splitByChar('^', age), '5');

数据存储层

ClickHouse 采用代理（Chproxy）+多节点的集群方式部署，实现了请求的负载均衡和数据存储的高可靠。 具体的部署和配置方法，以及 ClickHouse 的运维和管理不在本文的讨论范畴。

数据服务层

标签管理

标签管理模块主要负责标签的层级管理和特殊标签的处理，并提供接口给外部进行标签列表的查询。 标签分类、标签、标签枚举都存在 Mysql 中，总数据量大约3万+。

客群管理

本质上，标签圈选或基于地理位置的圈选行为，最终都会生成一个客群。客群管理模块主要负责客群的创建、更新和删除，以及客群人数的计算。 采用了 bitmap 之后，不同标签的与或非组合就变成了 bitmap 的 and、or、xor 操作，极大的提升了客群生成的速度。

例如计算年龄40-44岁、学历本科、男性的人群数量，用以下 sql 可快速得到结果：

​

sql 中 bitmap 函数集说明：

bitmapCardinality() 函数： 计算 bitmap 对象的数量；

bitmapAnd() 函数： bitmap对象的 and 操作；

bitmapOr() 函数： bitmap 对象的 or 操作；

groupBitmapOrState() 函数： 把列转为 bitmap 对象。

基于地理位置的圈选，可以通过 greatCircleDistance() 函数来实现。greatCircleDistance() 函数用来计算地球表面两点之间的距离。 因此要获取某个地理位置为圆心、一定半径内的人群列表，可以通过以下 sql 实现，其中120.212574, 30.290867是杭州东站的经纬度、25000表示半径为25公里：

select dwid from upp_user_geo where（greatCircleDistance（120.212574, 30.290867, lng, lat） < 25000）;

人群画像

在人群画像场景中，例如男性、女性、职业等标签的占比这类统计需求，都可以利用 bitmap 函数集快速的计算出结果。 例如选定的人群中男性的占比，可通过如下4步实现：

1. 将 upp_label_bitmap 中标签枚举值为 gender#male（男性标签）的 bitmap对象和圈选人群的 bitmap 对象进行 bitmapAnd() 函数操作，从而获取该圈选人群中男性的 bitmap 对象。
2. 通过 bitmapCardinality() 函数计算出圈选人群中男性 bitmap 对象的人数数量。
3. 通过 bitmapCardinality() 函数计算出圈选人群 bitmap 对象的人数数量，即圈选人数的总数量。
4. 第二步与第三步的值进行除法运算，即可计算出百分比。

以上步骤也可通过一条复杂 sql 拼接实现，这里不在详述。

遇到的问题及解决方法

1. upp_label_bitmap 表查询 bitmap 对象的速度较慢，查询单行 bitmap 对象的耗时大约在3秒左右。

问题原因： bitmap 对象内的数值列表如果无序，会导致 bitmap 对象的存储空间占用较大，引发读放大问题。

解决方法： 将 upp_user 表中的 dwid 字段改为有序自增值，单行 bitmap 对象的查询速度从3秒提升为20毫秒返回。

2. 标签圈选场景下，upp_label_bitmap 表中的多个标签值进行 bitmap 对象交并补操作时执行速度较慢，通常超过30秒返回。

问题原因： 由于 bitmap 对象占用的存储空间比较大，index_granularity 如果设置的较大，会引发稀疏索引带来的读放大问题。

解决方法： 将 upp_label_bitmap 表的 index_granularity 改为1或者50以下的值后，执行相同的 sql 语句300毫秒完成返回。

未来规划

目前在 upp_label_bitmap 表内，每个标签枚举的 bitmap 对象是通过在 Spark 应用中执行 ClickHouse 的 sql 生成，sql 中包含字段值的切分等复杂处理，因此执行速度较慢。

由于 ClickHouse 底层 bitmap 对象的存储结构是 Roaring Bitmap，因此可以在 Hive 端编写 udf，根据字段原始值直接调用 Roaring Bitmap 库生成 bitmap 对象， Spark 应用只需要将数据从 Hive 写入 ClickHouse 即可，从而提升数据导入效率。

ClickHouse 对高并发查询的支持能力较弱，因此系统需要增加缓存层的设计。

针对不同的业务场景需求，将用户标签数据使用不同的表引擎存储。例如 Join 较多的表使用 Join 引擎、聚合较多的表使用 SummingMergeTree 引擎。

总结

当前时代，数据分析早已不再满足于传统的 T+1式报表或需要提前设置好维度与指标的 OLAP 查询，而是更希望使用可以支持任意指标、任意维度并秒级给出反馈的大数据实时查询系统。

ClickHouse 的出现，满足了绝大数场景下对大数据的实时分析和查询需求。本文主要介绍了 ClickHouse 的基本概念、核心特性，以及基于 ClickHouse 构建用户画像平台的设计方案、实践经验和未来规划。 如果你正在使用 ClickHouse，希望本文能够帮助到你。

作者介绍

周磊，网易云商资深应用开发工程师。目前从事网易定位产品线的研发工作，致力于网易云商数据通用平台的建设