全部產品
Search
文件中心

Hologres:Portrait analysis - BSI最佳化方案(Beta)

更新時間:Jun 30, 2024

當使用者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等)。

  • 未經處理資料格式image.png

  • Bitmap格式:通過rb_tag表描述使用者基礎屬性標籤和uid的Bitmap關係,使用tag_name區分省份、性別等不同的標籤。

    image.png

  • BSI格式(位切片構建):通過bsi_gmv表描述使用者行為標籤值和uid的BSI關係,針對標籤值的二進位表示,記錄每一位切片對應的uid Bitmap,共計4個切片。image.png

通過上述方案,將uid和對應的行為標籤數值壓縮儲存進BSI中,通過BSI和Roaring Bitmap與或非運算實現標籤的快速計算。比如:

  • 通過BSI對圈選出的人群做行為標籤求和計算:將求和計算轉化為每一位BSI切片上的Bitmap交集計算。image.png

  • 通過BSI對圈選出的人群做行為標籤Top K計算:將全域排序計算轉化為自高位向低位的BSI切片上的Bitmap交集計算。image.png

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';