我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

華為雲開發者社群發表於2021-09-15
摘要：在移動網際網路時代，使用者數量龐大，標籤數量眾多，使用者標籤的資料量巨大。使用者畫像系統中，對於標籤的儲存和查詢，不同的企業有不同的實現方案。當前主流的實現方案採用ElasticSearch方案。但基於ElasticSearch構建使用者畫像平臺，往往面臨靈活性不足、資源開銷大、無SQL介面開發不便等問題。為此，本文提供了一種基於華為MRS ClickHouse構建使用者畫像系統的方法。

本文分享自華為雲社群《基於MRS-ClickHouse構建使用者畫像系統方案介紹》，作者：hourongqi 。

1. 業務場景

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

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

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

2.1 MRS-ClickHouse簡介

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

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

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.2 點陣圖索引介紹

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

舉例：

ID集合：[0,1,4,5,6,7,9,10,13,14]
通過點陣圖可以表示為：11001111 01100110

如下圖所示：

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

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

舉例：

假設有兩個標籤，一個是標籤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]。

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

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

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

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

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

ClickHouse點陣圖建構函式：

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

ClickHouse位置操作函式：

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

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

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

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

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

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

在ClickHouse中建立一張原始標籤表，將原始標籤資料匯入其中。然後基於標籤原始表構建標籤點陣圖表，並建立對應的分散式表。上層標籤查詢應用基於標籤點陣圖表(分散式表)進行標籤查詢。

流程如下圖所示：

我用MRS-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());

資料預覽如下：

我用MRS-ClickHouse構建的使用者畫像系統，讓老闆拍手稱讚

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

4.總結

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

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

 

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章