大數據

Hologres如何支持超高基數UV計算(基於roaringbitmap實現)

2021-05-26

RoaringBitmap是一種壓縮位圖索引，RoaringBitmap自身的數據壓縮和去重特性十分適合對於大數據下uv計算。其主要原理如下：

  • 對於32bit數, RoaringBitmap會構造2^16個桶，對應32位數的高16位；32位數的低16位則映射到對應桶的一個bit上。單個桶的容量由桶中的已有的最大數值決定
  • bitmap把32位數用1位表示，可以大大地壓縮數據大小。
  • bitmap位運算為去重提供了手段。

主體思想（T+1）：把上一天的所有數據根據最大的查詢維度聚合出的uid結果放入RoaringBitmap中，把RoaringBitmap和查詢維度存放在聚合結果表（每天百萬條）。之後查詢時，利用Hologres強大的列存計算直接按照查詢維度去查詢聚合結果表，對其中關鍵的RoaringBitmap字段做or運算進行去重後並統計基數，即可得出對應用戶數UV，count條數即可計算得出PV，達到亞秒級查詢。

只需進行一次最細粒度的預聚合計算，也只生成一份最細粒度的預聚合結果表。得益於Hologres的實時計算能力，該方案下預計算所需的次數和空間都達到較低的開銷。

Hologres計算UV、PV方案詳情

image

圖1 Hologres基於RoaringBitmap計算pv uv流程

1.創建相關基礎表

1）使用RoaringBitmap前需要創建RoaringBitmap extention，語法如下，同時該功能需要Hologres  0.10版本。

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2）創建表ods_app為明細源表，存放用戶每天大量的明細數據 （按天分區），其DDL如下：

BEGIN;
CREATE TABLE IF NOT EXISTS public.ods_app (
     uid text,
     country text,
     prov text,
     city text,
     channel text,
     operator text,
     brand text,
     ip text,
     click_time text,
     year text,
     month text,
     day text,
     ymd text NOT NULL
);
CALL set_table_property('public.ods_app', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
--distribution_key根據需求設置，根據該表的實時查詢需求，從什麼維度做分片能夠取得較好效果即可
CALL set_table_property('public.ods_app', 'distribution_key', 'uid');
--用於做where過濾條件，包含完整年月日時間字段推薦設為clustering_key和event_time_column
CALL set_table_property('public.ods_app', 'clustering_key', 'ymd');
CALL set_table_property('public.ods_app', 'event_time_column', 'ymd');
CALL set_table_property('public.ods_app', 'orientation', 'column');
COMMIT;

3）創建表uid_mapping為uid映射表，uid映射表用於映射uid到32位int類型。

RoaringBitmap類型要求用戶ID必須是32位int類型且越稠密越好（用戶ID最好連續），而常見的業務系統或者埋點中的用戶ID很多是字符串類型，因此使用uid_mapping類型構建一張映射表。映射表利用Hologres的SERIAL類型（自增的32位int）來實現用戶映射的自動管理和穩定映射。

注: 該表在本例每天批量寫入場景，可為行存表也可為列存表，沒有太大區別。如需要做實時數據（例如和Flink聯用），需要是行存表，以提高Flink維表實時JOIN的QPS。

BEGIN;
 CREATE TABLE public.uid_mapping (
     uid text NOT NULL,
     uid_int32 serial,
            PRIMARY KEY (uid) 
 );
 --將uid設為clustering_key和distribution_key便於快速查找其對應的int32值
CALL set_table_property('public.uid_mapping', 'clustering_key', 'uid');
CALL set_table_property('public.uid_mapping', 'distribution_key', 'uid');
CALL set_table_property('public.uid_mapping', 'orientation', 'row');
COMMIT;

3）創建表dws_app基礎聚合表，用於存放在基礎維度上聚合後的結果

基礎維度為之後進行查詢計算pv和uv的最細維度，這裡以country， prov， city為例構建聚合表

begin;
create table dws_app(
    country text,
  prov text,
  city text, 
  ymd text NOT NULL,  --日期字段
  uid32_bitmap roaringbitmap, -- UV計算
  pv integer, -- PV計算
  primary key(country, prov, city, ymd)--查詢維度和時間作為主鍵，防止重複插入數據
);
CALL set_table_property('public.dws_app', 'orientation', 'column');
--clustering_key和event_time_column設為日期字段，便於過濾
CALL set_table_property('public.dws_app', 'clustering_key', 'ymd');
CALL set_table_property('public.dws_app', 'event_time_column', 'ymd');
--distribution_key設為group by字段
CALL set_table_property('public.dws_app', 'distribution_key', 'country,prov,city');
end;

2.更新dws表及id_mapping表

每天從上一天的uid中找出新客戶（uid映射表uid_mapping中沒有的uid）插入到uid映射表中

WITH
-- 其中ymd = '20210329'表示上一天的數據
    user_ids AS ( SELECT uid FROM ods_app WHERE ymd = '20210329' GROUP BY uid )
    ,new_ids AS ( SELECT user_ids.uid FROM user_ids LEFT JOIN uid_mapping ON (user_ids.uid = uid_mapping.uid) WHERE uid_mapping.uid IS NULL )
INSERT INTO uid_mapping SELECT  new_ids.uid
FROM    new_ids
;

更新完uid映射表後，將數據做聚合運算後插入聚合結果表，主要步驟如下：

  • 首先通過源表inner join uid映射表，得到上一天的聚合條件和對應的uid_int32；
  • 然後按照聚合條件做聚合運算後插入RoaringBitmap聚合結果表，作為上一天的聚合結果；
  • 每天只需進行一次聚合，存放一份數據，數據條數最壞等於UV的量。以案例說明，明細表每天幾億的增量，在聚合結果表每天只需存放百萬級數據。
WITH
    aggregation_src AS( SELECT country, prov, city, uid_int32 FROM ods_app INNER JOIN uid_mapping ON ods_app.uid = uid_mapping.uid WHERE ods_app.ymd = '20210329' )
INSERT INTO dws_app SELECT  country
        ,prov
        ,city
        ,'20210329'
        ,RB_BUILD_AGG(uid_int32)
        ,COUNT(1)
FROM    aggregation_src
GROUP BY country
         ,prov
         ,city
;

3.UV、PV查詢

查詢時，從彙總表dws_app 中按照查詢維度做聚合計算，查詢bitmap基數，得出Group by條件下的用戶數

--運行下面RB_AGG運算查詢，可先關閉三階段聚合開關性能更佳（默認關閉）
set hg_experimental_enable_force_three_stage_agg=off    

--可以查詢基礎維度任意組合，任意時間段的uv pv
SELECT  country
        ,prov
        ,city
        ,RB_CARDINALITY(RB_OR_AGG(uid32_bitmap)) AS uv
        ,sum(1) AS pv
FROM    dws_app
WHERE   ymd = '20210329'
GROUP BY country
         ,prov
         ,city;
         
--查一個月         
SELECT  country
        ,prov
        ,RB_CARDINALITY(RB_OR_AGG(uid32_bitmap)) AS uv
        ,sum(1) AS pv
FROM    dws_app
WHERE   ymd >= '20210301' and ymd <= '20210331'
GROUP BY country
         ,prov;
該查詢等價於
SELECT  country
        ,prov
        ,city
        ,COUNT(DISTINCT uid) AS uv
        ,COUNT(1) AS pv
FROM    ods_app
WHERE   ymd = '20210329'
GROUP BY country
         ,prov
         ,city;
         
SELECT  country
        ,prov
        ,COUNT(DISTINCT uid) AS uv
        ,COUNT(1) AS pv
FROM    ods_app
WHERE   ymd >= '20210301' and ymd <= '20210331'
GROUP BY country
         ,prov;

4.可視化展示

計算出UV、PV和，大多數情況需要用BI工具以更直觀的方式可視化展示，由於需要使用RB_CARDINALITY 和 RB_OR_AGG 進行聚合計算，需要使用BI的自定義聚合函數的能力，常見的具備該能力的BI包括Apache Superset和Tableau，下面將會講述這兩個BI工具的最佳實踐。

4.1 使用 Apache Superset

Apache Superset 對接 Hologres 的方式，請參考產品手冊。在Superset中可以直接使用dws_app表作為Dataset使用

image

並且在數據集中，創建一個單獨Metrics，名為UV，表達式如下：

RB_CARDINALITY(RB_OR_AGG(uid32_bitmap))

image

然後您就可以開始探索數據了

image

image

當然也可以創建Dashborad：

image

4.2 使用 Tableau

Tableau 對接 Hologres 的方式，請參考產品手冊。可以使用Tableau的直通函數直接實現自定義函數的能力，詳細介紹請參照Tableau的手冊。在Tableau對接Hologres後，可以創建一個計算字段，表達式如下

RAWSQLAGG_INT("RB_CARDINALITY(RB_OR_AGG(%1))", [Uid32 Bitmap])

image

然後您就可以開始探索數據了

image

當然也可以創建Dashborad

image.png

Leave a Reply

Your email address will not be published. Required fields are marked *