Hologres如何支援超高基數UV計算(基于roaringbitmap實作)

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方案詳情

圖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使用

并且在資料集中，建立一個單獨Metrics，名為UV，表達式如下：

RB_CARDINALITY(RB_OR_AGG(uid32_bitmap))      

然後您就可以開始探索資料了

當然也可以建立Dashborad：

4.2 使用 Tableau

Tableau 對接 Hologres 的方式，請參考

。可以使用Tableau的直通函數直接實作自定義函數的能力，詳細介紹請參照

Tableau的手冊

。在Tableau對接Hologres後，可以建立一個計算字段，表達式如下

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

當然也可以建立Dashborad