基於MRS-ClickHouse構建用戶畫像系統方案介紹

  • 2022 年 3 月 28 日
  • 筆記

業務場景

用戶畫像是對用戶資訊的標籤化。用戶畫像系統通過對收集的各維度數據，進行深度的分析和挖掘，給不同的用戶打上不同的標籤，從而刻畫出客戶的全貌。通過用戶畫像系統，可以對各個用戶進行精準定位，從而將其應用於個性化推薦、精準營銷等業務場景中。用戶畫像系統已經被各個企業廣泛採用，是大數據落地的重要方式之一。

在移動互聯網時代，用戶數量龐大，標籤數量眾多，用戶標籤的數據量巨大。用戶畫像系統中，對於標籤的存儲和查詢，不同的企業有不同的實現方案。當前主流的實現方案採用ElasticSearch方案。但基於ElasticSearch構建用戶畫像平台，往往面臨靈活性不足、資源開銷大、無SQL介面開發不便等問題。為此，本文提供了一種基於華為MRS ClickHouse構建用戶畫像系統的方法。

為什麼基於MRS-ClickHouse構建標籤查詢系統

1.MRS-ClickHouse簡介

MRS-ClickHouse是一款面向聯機分析處理的列式資料庫。其最核心的特點是極致壓縮率和極速查詢性能。MRS-ClickHouse支援SQL查詢，且查詢性能好，特別是基於大寬表的聚合分析查詢性能非常優異，比其他分析型資料庫速度快一個數量級。

ClickHouse有如下特點：

完備的DBMS功能

ClickHouse擁有完備的資料庫管理功能，具備一個DBMS基本的功能，包括DDL、DML、許可權控制、數據備份與恢復、分散式管理。

列式存儲與數據壓縮

ClickHouse是一款使用列式存儲的資料庫，數據按列進行組織，屬於同一列的數據會被保存在一起，列與列之間也會由不同的文件分別保存。在執行數據查詢時，列式存儲可以減少數據掃描範圍和數據傳輸時的大小，提高了數據查詢的效率。

向量化執行引擎

ClickHouse利用CPU的SIMD指令實現了向量化執行。SIMD的全稱是Single Instruction Multiple Data，即用單條指令操作多條數據，通過數據並行以提高性能的一種實現方式，它的原理是在CPU暫存器層面實現數據的並行操作。

關係模型與SQL查詢

ClickHouse完全使用SQL作為查詢語言，提供了標準協議的SQL查詢介面，使得現有的第三方分析可視化系統可以輕鬆與它集成對接。

同時ClickHouse使用了關係模型，所以將構建在傳統關係型資料庫或數據倉庫之上的系統遷移到ClickHouse的成本會變得更低。

數據分片與分散式查詢

ClickHouse集群由1到多個分片組成，而每個分片則對應了ClickHouse的1個服務節點。分片的數量上限取決於節點數量（1個分片只能對應1個服務節點）。

ClickHouse提供了本地表 （Local Table）與分散式表 （Distributed Table）的概念。一張本地表等同於一份數據的分片。而分散式表本身不存儲任何數據，它是本地表的訪問代理，其作用類似分庫中間件。藉助分散式表，能夠代理訪問多個數據分片，從而實現分散式查詢。

2.點陣圖索引介紹

點陣圖是一種通過數組下標與某些特定的值進行關聯的數據結構。在點陣圖中，每一個元素佔用1個比特位。比特位為1時，表示對應的元素有該特定的值。反之則表示沒有。

舉例：

ID集合：[0,1,4,5,6,7,9,10,13,14]

通過點陣圖可以表示為：11001111 01100110

如下圖所示：

點陣圖索引是一種使用點陣圖的特殊索引，主要針對大量相同值的列而創建。點陣圖中位置編碼中的每一位表示對應的數據行的有無。點陣圖索引適合固定值的列，如性別、婚姻狀況、行政區等等。而不適合像身份證號、消費金額這種離散值的列。用戶畫像場景中，每一個標籤，對應大量的人群。標籤的數量是有限的枚舉值，這一特點非常適合點陣圖索引。

舉例：

假設有兩個標籤，一個是標籤1-持有貴金屬，另一個是標籤2-持有保險。各個持卡人擁有的標籤情況如下表所示。

從中，我們可以看到，有標籤1-持有貴金屬的持卡人ID集合是：[0，1，4，5，6，7，9，10，13，14]。有標籤2-持有保險的持卡人ID集群是：[2，3，5，7，8，11，12，13，15]。

當我們需要查詢同時有這個標籤的用戶時，基於點陣圖索引，只需要將兩個標籤相應的點陣圖進行位運算，即可得到最終結果。這樣，標籤數據的存儲空間佔用非常小，標籤計算的速度非常快。

3.MRS-ClickHouse原生支援點陣圖索引

在ClickHouse出現之前，如果要將點陣圖索引應用於用戶畫像場景，需要自己構建點陣圖數據結構、管理點陣圖索引，使用門檻較高。好消息是，MRS-ClickHouse原生提供了對點陣圖數據結構和位置索引的支援，將點陣圖的構建及維護封裝在ClickHouse內部。使用者基於ClickHouse構建點陣圖索引變成非常的簡單。

ClickHouse點陣圖構造函數：

序號 函數原型 用途
1 bitmapBuild(array) 將無符號整數數組構建點陣圖對象
2 groupBitmapState() 通過ID列表聚合構建點陣圖對象

ClickHouse位置操作函數：

ClickHouse點陣圖運算結果獲取函數：

綜上，為什麼是選擇基於ClickHouse構建標籤查詢系統？

  1. ClickHouse查詢速度快，最快可達亞秒級響應；
  2. ClickHouse內置點陣圖數據結構，方便構建點陣圖索引，提升標籤查詢性能；
  3. 基於JDBC/SQL介面，開發更簡單；
  4. 基於MPP架構，可橫向擴展。

如何基於MRS-ClickHouse構建標籤查詢系統

在ClickHouse中創建一張原始標籤表，將原始標籤數據導入其中。然後基於標籤原始表構建標籤點陣圖表，並創建對應的分散式表。上層標籤查詢應用基於標籤點陣圖表(分散式表)進行標籤查詢。
流程如下圖所示：

詳細過程如下文所述。

Step 1：創建標籤原始表，導入標籤原始數據

首先，創建一張標籤原始表，保存標籤原始數據。上游系統計算出的標籤結果數據，寫入本表中。本表為本地表。其建表語句如下：

CREATE TABLE IF NOT EXISTS tbl_tag_src ON CLUSTER default_cluster(
    tagname String,   --標籤名稱
    tagvalue String,  --標籤值
    userid UInt64 
)ENGINE = ReplicatedMergeTree('/clickhouse/default/tables/{shard}/tbl_tag_src ','{replica}')
PARTITION BY tagname
ORDER BY tagvalue;

然後創建分散式表：

CREATE TABLE IF NOT EXISTS default.tbl_tag_src_all ON CLUSTER default_cluster 
AS tbl_tag_src 
ENGINE = Distributed(default_cluster, default, tbl_tag_src, rand());

數據預覽如下：

Step 2：創建標籤點陣圖表，構建標籤點陣圖

創建一張標籤點陣圖表，先創建本地表。本地表用於保存標籤點陣圖數據。其創建語句如下：

-- 創建點陣圖表，先創建本地表
CREATE TABLE IF NOT EXISTS tbl_tag_bitmap ON CLUSTER default_cluster
(
    tagname String,   --標籤名稱
    tagvalue String,  --標籤值
    tagbitmap AggregateFunction(groupBitmap, UInt64 )  --userid集合
)
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/default/tables/{shard}/ tbl_tag_bitmap ','{replica}')
PARTITION BY tagname
ORDER BY (tagname, tagvalue)
SETTINGS index_granularity = 128;

然後再創建對應的分散式表。分散式表用於上層應用查詢標籤。其建表語句如下：

CREATE TABLE IF NOT EXISTS default.tbl_tag_bitmap_all ON CLUSTER default_cluster
(
    tagname String,   --標籤名稱
    tagvalue String,  --標籤值
    tagbitmap AggregateFunction(groupBitmap, UInt64 )  --userid集合
)
ENGINE = Distributed(default_cluster, default, tbl_tag_bitmap, rand());

將標籤原始表的數據導入標籤點陣圖表中。並在導入過程中，使用groupBitmapState()函數構建點陣圖。SQL語句如下：

-- 導入數據, 將同一個標籤的所有userid使用groupBitmapState函數合併成一個bitmap
INSERT INTO tbl_tag_bitmap_all
SELECT tagname,tagvalue,groupBitmapState(userid)
FROM tbl_tag_src_all
GROUP BY tagname,tagvalue;

Step 3：基於分散式錶快速檢索標籤

查詢持有貴金屬產品，並且性別是男的userid列表：

WITH
    (
        SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '持有產品' AND tagvalue = '貴金屬' LIMIT 1
    ) AS bitmap1,
    (
        SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '性別' AND tagvalue = '男' LIMIT 1
    ) AS bitmap2
SELECT bitmapToArray(bitmapAnd(bitmap1, bitmap2)) AS res

分別統計持有保險的客戶中，男性和女性的總人數：

---- 查詢持有保險的客戶中，男性人數：
WITH
    (
        SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '持有產品' AND tagvalue = '保險' LIMIT 1
    ) AS bitmap1,
    (
        SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '性別' AND tagvalue = '男' LIMIT 1
    ) AS bitmap2
SELECT bitmapCardinality(bitmapAnd(bitmap1, bitmap2)) AS res

---- 查詢持有保險的客戶中，女性人數：
WITH
    (
        SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '持有產品' AND tagvalue = '保險' LIMIT 1
    ) AS bitmap1,
    (
        SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '性別' AND tagvalue = '女' LIMIT 1
    ) AS bitmap2
SELECT bitmapCardinality(bitmapAnd(bitmap1, bitmap2)) AS res

總結

針對用戶畫像場景中的海量標籤查詢，傳統的方案存在靈活性不足、資源消耗大、缺少SQL介面開發難度大等問題。基於華為MRS-ClickHouse，可以非常方便的構建點陣圖索引，實現海量標籤數據的實時檢索。MRS-ClickHouse讓開發成本大幅降低，標籤查詢更快響應，讓精準營銷更便捷。

華為雲FusionInsight MRS雲原生數據湖已廣泛應用於政府、金融、運營商、大企業、互聯網等行業，攜手800+合作夥伴，服務於全球60+國家和地區3000+政企客戶。

VirMach 便宜 VPS

QNews

QNews