我用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+政企客戶。

 

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

相關文章