人群创建的基础：画像宽表

​本节主要介绍人群创建所依赖的画像宽表的生成方式。为什么要创建画像宽表？基于原始的标签数据表进行人群圈选有什么问题？如何生成画像宽表？针对这些问题本节会给出详细解答。

画像宽表

本小节将首先介绍画像宽表的表结构以及在人群创建中的主要优势，然后通过一个示例介绍画像宽表的生成方式及优化手段，最后介绍画像宽表数据写入ClickHouse的实现方案。

画像宽表概念

假设用户的两个画像标签性别和常驻省分别存储在两张Hive表中，其表结构如图5-2所示。

图5-2 性别和常住省标签数据表示例

如果需求从上述两张表中圈选出河北省男性用户，最传统的实现方案是直接通过inner join语句找出两张表中满足条件的用户，其SQL语句如下所示。

SELECTt1.user_idFROM(SELECTuser_idFROMuserprofile_demo.gender_labelWHEREgender = '男') t1INNER JOIN (SELECTuser_idFROMuserprofile_demo.province_labelWHEREprovince = '河北省') t2 ON (t1.user_id = t2.user_id)

如果在河北省男性用户的基础上再增加一个筛选条件，比如河北省中年男性用户，按照SQL编写逻辑需要再次inner join用户年龄段标签表。随着筛选条件的增加，这个SQL语句的长度和执行时间会逐渐增长，代码可维护性会逐渐降低。假设按图5-3所示将所有的标签拼接到一张数据表中并构建出一张宽表，上述圈选SQL语句可以简化成如下语句。该语句更加简洁且容易理解，其复杂度也不会随着筛选条件的增多而提高。与传统实现方式相比，基于宽表进行工程开发的难度和维护成本都将降低很多。

SELECTuser_idFROMuserprofile_demo.userprofile_wide_tableWHEREgender = '男'AND province = '河北省'

图5-3 性别和常住省标签在一张数据表中

上面的SQL语句就是人群创建的雏形，如果创建过程直接关联到每个标签的源数据表，那么任何源数据表的改动或者异常都将影响后续的人群创建功能。画像宽表将散落在不同表中的标签数据进行汇总，是对数据的一种封装方式，其不仅降低了人群圈选语句的复杂度，而且还可以解决如表5-1所示的所有问题。

                         表5-1 画像宽表相对分散表可以解决的主要问题

常见的画像宽表表结构设计如图5-4所示，其中包含的关键元素主要是日期分区p_date，画像数据主键user_id以及各画像标签列。日期分区用于区分不同时间下的标签取值，每个分区中都包含全量用户数据。图中画像宽表的创建语句如下代码所示。

CREATE TABLE IF NOT EXISTS `userprofile_demo.userprofile_wide_table`(`user_id` bigint COMMENT '用户ID',`gender` string COMMENT '性别',`province` string COMMENT '省份',`other_labels` string COMMENT '其他标签',COMMENT '画像宽表'PARTITIONED BY (`p_date` string COMMENT 'yyyy-MM-dd');

图5-4 画像宽表表结构设计

画像宽表中为什么要设置日期分区，仅保留一份最新的标签数据可以吗？当然可以，本书中采用带有日期的宽表设计主要有如下三点考虑。

• 支持灵活的行为圈选。部分标签是行为统计类标签，比如当日是否送礼、在线时长、观看文章数、点赞次数等，如果圈选条件涉及时间范围时需要保留历史一段时间内的画像标签数据。比如圈选出7月1日到7月6日范围内平均在线时长超过20分钟的用户、圈选7月9日到7月15日期间累计点赞次数超过20次的用户，以上圈选条件都需要查询过往7天的标签数据。上述圈选需求也可以转换成“近一周平均在线时长”和“近一周累计点赞次数”标签来解决，但是这种通过增加标签来满足日期范围下用户圈选的方式不够灵活。当用户圈选需求涉及任意N天的用户行为时，只能通过存储历史标签数据来解决。
• 支持跨时间的人群分析。有了标签历史数据便可以实现跨时间的人群分析，比如分析北京市男性用户在过去半个月的平均在线时长变化，基于画像宽表可以快速计算出分析结果。
• 兼容单日期分区。仅保留最新标签数据是多日期数据下的一种特殊情况。本书技术方案支持多日期画像数据下的人群圈选等功能，自然兼容单日期下的各类功能。

画像宽表生成

画像宽表的表结构已经明确，那如何生成宽表数据？最简单直接的方式是通过SQL语句来拼接各类标签源数据表，图5-5展示了将多个标签汇总到画像宽表的主要流程。其中userprofile_base_table表包含了全量的用户信息，通过left join其他标签表来补齐合并标签数据；在合并不同标签数据的过程中可以添加数据处理逻辑，比如将其中的性别标签值进行数字编码、补齐性别和省份缺失值等。

图5-5 多个标签表生成画像宽表主要流程

随着业务发展，生产画像宽表所涉及的标签数量逐渐增加，仅通过一条SQL语句生成宽表的缺陷逐渐暴露出来。首先SQL语句随着标签的增多会变冗长且结构复杂，在SQL中增删改标签的难度增大，提高了维护成本。其次每个标签Hive表的就绪时间不同，单条SQL语句执行模式会等待所有标签就绪，这就造成宽表的产出时间受最晚就绪的标签影响，而且在SQL执行时涉及所有上游标签数据，其需要大量的计算资源集中进行计算，这无疑会造成宽表的产出时间延长，时效性较低。最后，当单个标签数据异常时，需要重跑整个SQL语句来纠正数据问题，这无疑造成了资源的浪费。为了解决以上问题，可以通过如图5-6所示的分组方案生成画像宽表。

图5-6 通过分组的方式生成画像宽表

图5-6中采用了分治的思路逐层生成画像宽表。所有标签被划分成多个分组，每个分组下的标签自行产出中间宽表，最后将所有的中间宽表合并成最终的画像宽表。标签可以采取随机分组策略，即所有标签随机分配到某个分组下，每个中间宽表所包含的标签量和计算所需的资源量基本一致；也可以按标签的就绪时间段进行分组，比如早上8点到10点就绪的标签可以分为一组，这样可以把中间宽表的生产时间打散，避免集中计算造成系统压力过大。宽表生成SQL语句可以使用Spark引擎执行，通过Spark引擎参数调优、Join语句数据表顺序调整、使用Bucket Join等方式都可以提升宽表的生产效率，更多宽表生产优化细节可参见后续文章。

画像宽表存储

画像宽表数据存储在Hive表中，可以通过Hive SQL执行人群圈选操作，由于其依赖Hadoop生态下的数据引擎执行，其执行时间通常在几分钟到几十分钟不等。如果画像平台用户对于人群圈选的速度没有要求，直接基于Hive表进行计算是可行的。但是有些业务对人群圈选速度有比较高的要求，比如热点运营团队，当热点事件出现之后，需要能够以最快的速度找到目标用户并推送Push消息，此时直接从Hive表中圈选用户便不再满足业务需求。

ClickHouse是最近几年比较流行的大数据分析工具，面对百亿数据量级的分析需求可以实现秒级响应。ClickHouse也比较擅长做宽表分析，基于这一特点可以把其作为Hive表的“缓存”使用，从而满足人群圈选和人群分析的提速。实践证明，基于ClickHouse表进行人群圈选可以实现秒级响应，相比Hive表实现方式的分钟级响应有显著提升。选择ClickHouse另外一个原因是其对SQL语法支持非常全面，其表结构设计与Hive表非常相似，这极大地降低了工程开发难度。

要将图5-4所示的Hive表写入ClickHouse中，首先要创建ClickHouse数据表，其创建表语句如下所示。

-- 创建Local表，数据表按照日期进行分区，以user_id和gender作为排序键 --CREATE TABLE userprofile_demo.userprofile_wide_table_ch_local ON CLUSTER default (p_date Date,user_id Int64,gender Int8,province String) ENGINE = MergeTree()PARTITION BY toYYYYMM(p_date)PRIMARY KEY (user_id)ORDER BY(user_id, gender) SETTINGS index_granularity = 8192;-- 创建分布式表 关联到Local表--CREATE TABLE userprofile_demo.userprofile_wide_table_ch ON CLUSTER default AS userprofile_demo.userprofile_wide_table_local ENGINE = Distributed(default,userprofile_demo,userprofile_wide_table_local,intHash32(user_id));

画像宽表数据写入ClickHouse首要解决读取Hive数据的问题，可以采用与第4章中标签数据灌入缓存相同的技术方案：通过Spark、Flink或者自研代码方式读取Hive数据。将数据写入ClickHouse的方式主要有两种：通过insert语句直接写入以及通过文件的方式批量导入。和其他常见数据库一样，通过insert语句可以直接将数据写入ClickHouse表中；也可以将数据存储在CSV临时文件后再批量导入到ClickHouse中。图5-7展示了Hive宽表数据写入ClickHouse的主要实现逻辑。

图5-7 Hive宽表数据写入ClickHouse实现逻辑

图中写数据到ClickHouse的关键语句如下所示，第一条是完整的insert语句，可以通过ClickHouse客户端或者JDBC来执行；第二条是基于CSV文件导入ClickHouse的语句，其依赖ClickHouse客户端来执行。

-- 通过INSERT语句批量写入数据 --INSERT INTO userprofile_demo.userprofile_wide_table_ch (p_date, user_id, gender, province)VALUES('2022-07-28', 100, 1, '山东省'),('2022-07-28', 101, 2, '陕西省'),('2022-07-28', 102, 1, '河南省');-- 通过文件批量导入如何实现 --clickhouse-client --query="INSERT INTO userprofile_demo.userprofile_wide_table_ch FORMAT CSVWithNames" < /path/to/csvfilename.csv

画像宽表这种数据组织形式也存在一些缺点，最主要的问题是数据的冗余存储。属性类标签取值与时间无关，比如性别、教育程度、出生地等不受时间影响，当宽表按日期分区存储一段时间属性类标签数据时会造成存储资源的浪费。为了解决这个问题也可以将标签拆分到两个小宽表中，与日期无关的标签单独放一张宽表且仅保留最新日期的数据；与日期有关的标签放到另外一张宽表中，且按日期保存一段时间的数据。保障画像宽表生产具有较高的维护成本，随着宽表标签列的增加，其生产、修改、补数据等情况会比较频繁，任何一个标签的改动都会影响整张宽表的使用。

本文节选自《用户画像：平台构建与业务实践》，转载请注明出处。