當使用者Portrait analysis情境中存在大量的屬性標籤和行為標籤時,使用Roaring Bitmap演算法會有很大局限,Hologres為您提供BSI(Bit-sliced Index)演算法,在解決局限問題的基礎上儘可能保留Roaring Bitmap的多方優勢。本文為您介紹在Hologres中如何通過BSI實現標籤計算的最佳實務。
背景資訊
Roaring Bitmap在使用者Portrait analysis情境,可以通過對標籤表構建索引,將使用者ID進行編碼後以Bitmap格式儲存,將關係運算轉化為Bitmap的交並差運算,進而加速Realtime Compute效能。但Roaring Bitmap會有以下局限:
多標籤聯集查詢問題:Roaring Bitmap更多用於“屬性標籤”,且僅可用於固定標籤。對於大量“行為標籤”,如PV、訂單金額、觀看時間長度等量值類標籤,聯集查詢的解決方案只有回溯明細表,詳細資料下鑽成本增加。
高基數標籤的查詢問題:當某個標籤的去重值數量(基數)很大時,Roaring Bitmap的儲存會膨脹,導致查詢效能衰減。
針對上述局限,本文通過BSI(Bit-sliced Index)演算法與若干函數進行最佳化,詳情請參見BSI函數,解決以下問題:
對於多標籤聯集查詢情境,針對量值類“行為標籤”進行預計算,通過BSI進行壓縮儲存,在保障精度的同時還可以避免與明細表進行關聯查詢,即可實現“屬性標籤”和“行為標籤”的高效聯動分析。
對於高基數的行為標籤,通過位切片索引,最多產生32個位切片,即可儲存全部使用者在INT範圍內的行為標籤值,並且在查詢時可以通過二進位原理和Roaring Bitmap交並差運算進行快速計算,實現對高基數行為標籤的壓縮儲存和低延遲查詢。
Portrait analysis方案介紹
假設系統中存在兩張使用者標籤表,其中dws_userbase表示使用者基礎屬性(省份、性別等),usershop_behavior表示使用者行為標籤(GMV等)。
未經處理資料格式
Bitmap格式:通過rb_tag表描述使用者基礎屬性標籤和uid的Bitmap關係,使用tag_name區分省份、性別等不同的標籤。
BSI格式(位切片構建):通過bsi_gmv表描述使用者行為標籤值和uid的BSI關係,針對標籤值的二進位表示,記錄每一位切片對應的uid Bitmap,共計4個切片。
通過上述方案,將uid和對應的行為標籤數值壓縮儲存進BSI中,通過BSI和Roaring Bitmap與或非運算實現標籤的快速計算。比如:
通過BSI對圈選出的人群做行為標籤求和計算:將求和計算轉化為每一位BSI切片上的Bitmap交集計算。
通過BSI對圈選出的人群做行為標籤Top K計算:將全域排序計算轉化為自高位向低位的BSI切片上的Bitmap交集計算。
Portrait analysis基礎實踐
BSI表設計
表名 | 表欄位 | 備忘 |
dws_userbase | (uid int, province text, gender text) | 使用者原始屬性標籤表,同標籤寬表方案。 |
dws_uid_dict | (encode_uid serial, uid int) | 使用者uid字典編碼錶,同Roaring Bitmap方案。 |
usershop_behavior | (uid int, gmv int) | 使用者原始行為標籤表,記錄GMV等行為標籤資料。 |
rb_tag | (tag_name text, tag_val text, bitmap roaringbitmap) | 使用Roaring Bitmap表示的使用者屬性標籤表。 |
bsi_gmv | (gmv_bsi bsi) | 使用BSI表示的使用者GMV指標明細表。 |
表DDL語句如下:
使用者原始屬性標籤表
CREATE TABLE dws_userbase ( uid int NOT NULL PRIMARY KEY, province text, gender text ... -- 其他屬性列 ) WITH ( distribution_key = 'uid' );
使用者uid字典編碼錶
CREATE TABLE dws_uid_dict ( encode_uid serial, uid int PRIMARY KEY );
使用者原始行為標籤表
CREATE TABLE usershop_behavior ( uid int NOT NULL, gmv int ) WITH ( distribution_key = 'uid' );
Roaring Bitmap表示的使用者屬性標籤表
CREATE TABLE rb_tag ( tag_name text, tag_val text, bitmap roaringbitmap );
BSI表示的使用者行為標籤表(GMV)
CREATE TABLE bsi_gmv (
gmv_bsi bsi
CREATE TABLE bsi_gmv ( gmv_bsi bsi );
資料匯入
通過原始屬性標籤表和uid字典編碼錶產生屬性標籤Roaring Bitmap資料並分桶
INSERT INTO rb_tag SELECT 'province', province, rb_build_agg (b.encode_uid) AS bitmap FROM dws_userbase a JOIN dws_uid_dict b ON a.uid = b.uid GROUP BY province; INSERT INTO rb_tag SELECT 'gender', gender, rb_build_agg (b.encode_uid) AS bitmap FROM dws_userbase a JOIN dws_uid_dict b ON a.uid = b.uid GROUP BY gender;
通過原始行為標籤表和uid字典編碼錶產生行為標籤BSI資料並分桶
INSERT INTO bsi_gmv SELECT bsi_build(array_agg(b.encode_uid),array_agg(a.gmv)) AS bitmap FROM usershop_behavior a JOIN dws_uid_dict b ON a.uid = b.uid ;
Portrait analysis
通過BSI,可以非常便捷的將人群的屬性標籤和行為標籤關聯分析,包括人群圈選後的行為標籤洞察、基於行為標籤過濾的人群圈選等多個情境,樣本如下:
人群圈選+行為標籤分析
查詢“廣東”“男”使用者的GMV總值和人均GMV:
通過BSI和Roaring Bitmap查詢。
SELECT sum(kv[1]) AS total_gmv, -- 總GMV sum(kv[1])/sum(kv[2]) AS avg_gmv -- 人均GMV FROM ( SELECT bsi_sum(t1.gmv_bsi,t2.crowd) AS kv FROM bsi_gmv t1, (SELECT rb_and(a.bitmap,b.bitmap) AS crowd FROM (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a, -- 男性使用者 (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = '廣東') b -- 廣東使用者 ) t2 ) t;
通過原始屬性標籤與行為標籤表查詢。
SELECT sum(b.gmv) AS total_gmv, avg(b.gmv) AS avg_gmv FROM dws_userbase a JOIN usershop_behavior b ON a.uid = b.uid WHERE a.province = '廣東' AND a.gender = 'Male';
查詢“廣東”“男”使用者的消費金額分布:
通過BSI和Roaring Bitmap查詢:通過bsi_stat函數,定義邊界值數組,即可高效完成多個區間內查詢。
SELECT bsi_stat('{100,300,500}', filter_bsi) FROM ( SELECT bsi_filter(t1.gmv_bsi,t2.crowd) AS filter_bsi FROM bsi_gmv t1, (SELECT rb_and(a.bitmap,b.bitmap) AS crowd FROM (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a, -- 男性使用者 (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = '廣東') b -- 廣東使用者 ) t2 ) t;
通過原始屬性標籤和行為標籤表查詢:只能通過CASE WHEN文法表達。
SELECT CASE WHEN gmv >= 0 AND gmv <= 100 THEN '0-100' WHEN gmv > 100 AND gmv <= 300 THEN '100-300' WHEN gmv > 300 AND gmv <= 500 THEN '300-500' WHEN gmv > 500 THEN '>500' END AS gmv_range, COUNT(*) AS user_count FROM dws_userbase a JOIN usershop_behavior b ON a.uid = b.uid WHERE a.province = '廣東' AND a.gender = 'Male' GROUP BY gmv_range ORDER BY gmv_range;
昨日“廣東”“男”使用者消費金額Top K查詢:
通過BSI和Roaring Bitmap查詢。
SELECT rb_to_array(bsi_topk(filter_bsi,10)) FROM ( SELECT bsi_filter(t1.gmv_bsi,t2.crowd) AS filter_bsi FROM bsi_gmv t1, (SELECT rb_and(a.bitmap,b.bitmap) AS crowd FROM (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a, -- 男性使用者 (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = '廣東') b -- 廣東使用者 ) t2 ) t;
通過原始屬性標籤與行為標籤表查詢:
SELECT b.uid, b.gmv FROM dws_userbase a JOIN usershop_behavior b ON a.uid = b.uid WHERE a.province = '廣東' AND a.gender = 'Male' ORDER BY gmv DESC LIMIT 10;
基於行為標籤的人群圈選
圈選消費金額大於1000的使用者:
通過BSI和Roaring Bitmap查詢。
SELECT rb_to_array(bsi_gt(gmv_bsi, 1000)) AS crowd FROM bsi_gmv;
通過原始屬性標籤與行為標籤表查詢。
SELECT array_agg(uid) FROM usershop_behavior WHERE gmv > 800;
Portrait analysis進階實踐-分桶計算
依據上文的方案,將dws_userbase表按照省份、性別的列Bitmap壓縮為一個Roaring Bitmap表,將usershop_behavior壓縮成一個BSI表,壓縮出來的Bitmap和BSI只能分布與叢集中的少數節點,計算儲存都不均勻,叢集的資源並不能充分利用。因此,有必要將Bitmap和BSI拆分成多段,並將它們打散到叢集中來提升並發執行的能力,假設將Bitmap和BSI都打散成65536段,實踐方案如下:
BSI表設計
表名 | 表欄位 | 備忘 |
dws_userbase | (uid int, province text, gender text) | 使用者原始屬性標籤表,同上文基礎實踐。 |
dws_uid_dict | (encode_uid serial, uid int) | 使用者uid字典編碼錶,同上文基礎實踐。 |
usershop_behavior | (uid int, category text, gmv int, ds date) | 使用者原始行為標籤表。 相較於基礎實踐,增加了類目(category)、日期(ds)欄位,用於對資料進行分類,分時計算。 |
rb_tag | (tag_name text, tag_val text, bucket int, bitmap roaringbitmap) | 使用Roaring Bitmap表示的使用者屬性標籤表。 相較於基礎實踐,增加分桶(bucket)欄位。 |
bsi_gmv | (ds text, category text, bucket int, gmv_bsi bsi) | 使用BSI表示的使用者GMV指標明細表。 相較於基礎實踐,增加了類目(catagory)、日期(ds)、分桶(bucket)欄位,用於對BSI資料進行分類、分時、分桶計算。 |
表DDL語句如下:
Roaring Bitmap表示的使用者屬性標籤表並分桶
CREATE TABLE rb_tag ( tag_name text, tag_val text, bucket int, bitmap roaringbitmap ) WITH ( distribution_key = 'bucket' -- 將分桶編號作為distribution_key );
BSI表示的使用者行為標籤表(GMV)並分桶
CREATE TABLE bsi_gmv ( category text, bucket int, gmv_bsi bsi, ds date ) WITH ( distribution_key = 'bucket' -- 將分桶編號作為distribution_key );
資料匯入
通過原始屬性標籤表和uid字典編碼錶產生屬性標籤Roaring Bitmap資料並分桶
INSERT INTO rb_tag SELECT 'province', province, encode_uid / 65536 AS "bucket", rb_build_agg (b.encode_uid) AS bitmap FROM dws_userbase a JOIN dws_uid_dict b ON a.uid = b.uid GROUP BY province, "bucket"; INSERT INTO rb_tag SELECT 'gender', gender, encode_uid / 65536 AS "bucket", rb_build_agg (b.encode_uid) AS bitmap FROM dws_userbase a JOIN dws_uid_dict b ON a.uid = b.uid GROUP BY gender, "bucket";
通過原始行為標籤表和uid字典編碼錶產生行為標籤BSI資料並分桶
INSERT INTO bsi_gmv SELECT a.category, b.encode_uid / 65536 AS "bucket", bsi_build(array_agg(b.encode_uid),array_agg(a.gmv)) AS bitmap, a.ds FROM usershop_behavior a JOIN dws_uid_dict b ON a.uid = b.uid WHERE ds = CURRENT_DATE - interval '1 day' GROUP BY category, "bucket", ds;
Portrait analysis
通過BSI,可以非常便捷的將人群的屬性標籤和行為標籤關聯分析,包括人群圈選後的行為標籤洞察、基於行為標籤過濾的人群圈選等多個情境,同時還可以通過將人群資料打散到不同分桶進行加速計算,具體如下:
人群圈選+行為標籤分析
查詢“廣東”“男”使用者“昨天”在“3C”類目下的GMV總值和人均GMV:
SELECT sum(kv[1]) AS total_gmv, -- 總GMV sum(kv[1])/sum(kv[2]) AS avg_gmv -- 人均GMV FROM ( SELECT bsi_sum(t1.gmv_bsi,t2.crowd) AS kv, t1.bucket FROM (SELECT gmv_bsi, bucket FROM bsi_gmv WHERE category = '3C' AND ds = CURRENT_DATE - interval '1 day') t1 JOIN (SELECT rb_and(a.bitmap,b.bitmap) AS crowd, a.bucket FROM (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a -- 男性使用者 JOIN (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = '廣東') b -- 廣東使用者 ON a.bucket = b.bucket ) t2 ON t1.bucket = t2.bucket ) t;
查詢“廣東”“男”使用者“昨天”在“3C”類目下的消費金額分布:
SELECT bsi_stat('{100,300,500}', bsi_add_agg(filter_bsi)) FROM ( SELECT bsi_filter(t1.gmv_bsi,t2.crowd) AS filter_bsi, t1.bucket FROM (SELECT gmv_bsi, bucket FROM bsi_gmv WHERE category = '3C' AND ds = CURRENT_DATE - interval '1 day') t1 JOIN (SELECT rb_and(a.bitmap,b.bitmap) AS crowd, a.bucket FROM (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a -- 男性使用者 JOIN (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = '廣東') b -- 廣東使用者 ON a.bucket = b.bucket ) t2 ON t1.bucket = t2.bucket ) t;
“廣東”“男”使用者“昨日”消費金額Top K查詢:
SELECT bsi_topk(bsi_add_agg(filter_bsi),10) FROM ( SELECT bsi_filter(t1.gmv_bsi,t2.crowd) AS filter_bsi, t1.bucket FROM (SELECT bsi_add_agg(gmv_bsi) AS gmv_bsi, bucket FROM bsi_gmv WHERE ds = CURRENT_DATE - interval '1 day' GROUP BY bucket) t1 JOIN (SELECT rb_and(a.bitmap,b.bitmap) AS crowd, a.bucket FROM (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male') a -- 男性使用者 JOIN (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = '廣東') b -- 廣東使用者 ON a.bucket = b.bucket ) t2 ON t1.bucket = t2.bucket ) t;
基於行為標籤的人群圈選
圈選“近一個月”在“3C”類目下消費金額大於1000的使用者:
SELECT rb_to_array(bsi_gt(bsi_add_agg(gmv_bsi), 1000)) AS crowd FROM bsi_gmv WHERE category = '3C' AND ds BETWEEN CURRENT_DATE - interval '30 day' AND CURRENT_DATE - interval '1 day';