基于Clickhouse 的用戶圈選實踐

1. 背景

在伴魚，我們努力了解我們的用戶，旨在為用戶提供更好的服務(wù)。APP 內(nèi)容推薦，需要根據(jù)用戶特征來決定推送內(nèi)容；促銷活動，需要針對不同的用戶群體設(shè)計不同的活動方案；線上產(chǎn)品售賣，也需要了解用戶喜好，才能更好地把產(chǎn)品賣給用戶。

為此，我們搭建了用戶畫像平臺。本文將首先探討平臺的功能需求、標簽體系定位，隨后介紹平臺的架構(gòu)和具體功能實現(xiàn)。

2. 功能

用戶畫像平臺把重點放在了分析場景，使用方主要是公司各業(yè)務(wù)線的運營和數(shù)據(jù)分析同學。平臺在一期主要支持以下幾個功能。

1. 定義標簽：標簽是用于描述用戶的一個維度，例如「注冊設(shè)備類型」、「常駐城市」、「年齡段」等。

2. 人群圈選：指定一組用戶標簽和其對應(yīng)的標簽值，得到符合條件的用戶人群。例如，找出「城市為北京，且設(shè)備類型為蘋果」的用戶。

3. 用戶畫像：對于人群圈選結(jié)果，查看該人群的標簽分布。例如，查看「城市為北京，且設(shè)備類型為蘋果」的用戶的年齡段分布。

3. 標簽體系

確定完用戶畫像平臺的使用場景和主要功能，我們再來倒推看用戶標簽體系。用戶標簽可以從兩個維度進行分類：標簽的實時性，和標簽的值類型。

首先看標簽的實時性?？紤]到用戶畫像平臺的主要功能是「人群圈選」和「用戶畫像查看」，而這兩個功能都不需要非常高的實時性，那么實時標簽的收益就不大，T+1 的非實時標簽完全能滿足數(shù)據(jù)分析和運營同學的需求。

再來看標簽的值類型，即標簽是枚舉和還是非枚舉的。枚舉標簽，顧名思義，就是指標簽值可枚舉的標簽，例如 device_type, network_type, country, city 等，這類標簽往往在人群圈選方面有較大作用。而非枚舉標簽，就是標簽值可無限遞增的標簽，比如 active_days，register_date 等，這類標簽大多會用來做用戶信息展示?？紤]到「人群圈選」是各業(yè)務(wù)線最迫切的需求，我們在一期舍棄了非枚舉標簽這個功能。

綜上，我們就確定了用戶畫像平臺的一期標簽體系為非實時的枚舉標簽，主要滿足「人群圈選」和「人群畫像」這兩個查詢功能。

4. 架構(gòu)與實現(xiàn)

在架構(gòu)上，用戶畫像平臺分為兩個模塊：數(shù)據(jù)寫入，分析查詢。

4.1 數(shù)據(jù)寫入

數(shù)據(jù)寫入模塊為人群圈選和用戶畫像功能提供數(shù)據(jù)支持。具體流程分為兩步。

第一步，大數(shù)據(jù)團隊完成每日標簽計算后，得到一張 Hive 大寬表，如下表所示。表的每一行代表一個用戶，每一列代表一個標簽。

第二步，大數(shù)據(jù)團隊將大寬表的數(shù)據(jù)「轉(zhuǎn)置」后批量寫入 ClickHouse，如下表所示。表中的每一行代表一個標簽實例（即標簽和標簽值的組合），例如「city = Beijing」。此外，這一行同時存儲了具有該標簽值的所有用戶的集合，服務(wù)于分析查詢模塊。

4.2 分析查詢

分析查詢模塊則實現(xiàn)了人群圈選和用戶畫像的查詢。用戶通過前端頁面，進行標簽、標簽值、組合方式的勾選，后端將它們拼接為 SQL 語句，從 ClickHouse 中查詢數(shù)據(jù)，展示給前端頁面。例如，在下圖中，我們?nèi)x了屬于北京、深圳、上海的蘋果用戶，并且按照年齡、網(wǎng)絡(luò)運營商、網(wǎng)絡(luò)類型、性別查看人群的分布情況。

不難看出，ClickHouse 在用戶畫像平臺的數(shù)據(jù)存儲和計算中起到了最關(guān)鍵的作用。下面，讓我們一起來回答幾個問題：

• 如何設(shè)計 ClickHouse 的表結(jié)構(gòu)?

• 如何使用 ClickHouse 進行人群圈選？

• 如何使用 ClickHouse 查看人群畫像？

4.2.1 設(shè)計 ClickHouse 表結(jié)構(gòu)

根據(jù)使用場景，我們設(shè)計 ClickHouse 表結(jié)構(gòu)如下：

CREATE TABLE analytics.user_tag_bitmap_local(    `tag` String,    `tag_item` String,    `p_day` Date,    `origin_user` UInt64,    `users` AggregateFunction(groupBitmap, UInt64) MATERIALIZED bitmapBuild([origin_user]))ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/analytics/user_tag_bitmap_local', '{replica}')PARTITION BY toYYYYMMDD(p_day)ORDER BY (tag, tag_item)SETTINGS index_granularity = 8192;

首先，看表的名稱。表名包含 _local 后綴，即這是一個本地表，也存在一個對應(yīng)的分布式表。我們使用「寫本地表，讀分布式表」的讀寫分離模式，具體原因可以參考《伴魚事件分析平臺：設(shè)計篇》的「如何高效寫入 ClickHouse」一節(jié)。

然后，看表包含的字段。

• tag?代表標簽， tag_item 代表標簽值。因為在標簽的圈選查詢中，經(jīng)常有?tag = "city" AND tag_item = "beijing"?的語句，我們將?(tag, tag_item)?作為主鍵，以提高查詢效率。

• p_day?代表數(shù)據(jù)寫入的日期，也作為 ClickHouse 的分片鍵。因為每天的標簽數(shù)據(jù)都是全量導(dǎo)入，p_day 不僅可以用來區(qū)分標簽版本，也方便我們批量刪除歷史數(shù)據(jù)。

• origin_user?是單個用戶 ID。然而，相比單個用戶的標簽情況，我們更關(guān)心具有特定標簽的用戶人群。因此，我們使用?users?字段來表達根據(jù)?origin_user?聚合得到用戶人群。為此，我們使用了 AggregatingMergeTree，它在原始數(shù)據(jù)插入后自動觸發(fā)聚合，將具有相同 (tag, tag_item, p_day) 的數(shù)據(jù)聚合為一行。

最后，看表的存儲引擎，我們使用了 ReplicatedAggregatingMergeTree 引擎。前文中我們提到 Aggregating 是用來聚合數(shù)據(jù)，而 Replicated 則是用來創(chuàng)建數(shù)據(jù)副本，對應(yīng)雙副本存儲模式。

4.2.2 使用 ClickHouse 進行人群圈選

組合不同標簽，圈選出最適合某個活動的用戶人群里，是運營同學們較為關(guān)心的步驟。例如，我們想找出城市為北京、性別為女的用戶。

圖注：用戶人群查詢

我們只需首先找到城市為北京的用戶人群（用 bitmap 表示），然后找到性別為女的用戶人群，然后對它們進行 AND 操作即可。具體查詢?nèi)缦拢?/span>

WITH(    SELECT groupBitmapMergeState(users)    FROM user_tag_bitmap_all    WHERE p_day = '2021_06_01' AND tag = 'city' AND tag_item = 'beijing') AS user_group_1,(    SELECT groupBitmapMergeState(users)    FROM user_tag_bitmap_all    WHERE p_day = '2021_06_01' AND tag = 'gender' AND tag_item = 'female') AS user_group_2SELECT bitmapToArray(bitmapAnd(user_group_1, user_group_2))

其中，groupBitmapMergeState 函數(shù)對通過 WHERE 篩除得到的任意個數(shù)的 bitmap (users) 進行 AND 操作，而 bitmapAnd 只能對兩個 bitmap 進行 AND 操作。

4.2.3 使用 ClickHouse 查看用戶畫像

再回到剛剛的例子，圈選得到「北京的女性用戶」這一人群后，我們想知道，人群中有多少人在用蘋果設(shè)備，而有多少人在用安卓。這類標簽分布信息，就是我們所說的用戶畫像。

圖注：用戶畫像查詢

這個查詢的實現(xiàn)同樣是直觀的。

1. 我們采用和上一節(jié)一樣的步驟，得到「北京的女性用戶」這一 bitmap。

2. 對人群進行分組，分別得到「設(shè)備為蘋果的用戶」和「設(shè)備為安卓的用戶」的 bitmap。如果存在除了蘋果和安卓之外的設(shè)備，我們這一步會得到更多的 bitmap。

3. 將步驟 2 中的每一個 bitmap 與步驟 1 中的 bitmap 進行 AND 操作，就能得到「北京的女性用戶」基于「設(shè)備類型」的分布情況。

具體的實現(xiàn)見下面的查詢。

WITH(    SELECT groupBitmapMergeState(users)    FROM user_tag_bitmap_all    WHERE p_day = '2021_06_01' AND tag = 'city' AND tag_item = 'beijing') AS user_group_1,(    SELECT groupBitmapMergeState(users)    FROM user_tag_bitmap_all    WHERE p_day = '2021_06_01' AND tag = 'gender' AND tag_item = 'female') AS user_group_2,(    SELECT bitmapAnd(user_group_1, user_group_2)) AS filter_usersSELECT    bitmapCardinality(bitmapAnd(filter_users, group_by_users)) AS count,    tag,    tag_itemFROM(SELECT    groupBitmapMergeState(users) AS group_by_users,    tag,    tag_itemFROM user_tag_bitmap_allWHERE tag = "device_type"GROUP BY (tag, tag_item));

上述查詢用到了一個沒介紹到的函數(shù) bitmapCardinality 。它的作用可以理解為計算 bitmap 中 1 的個數(shù)。