关于sql:Hologres如何基于roaringbitmap实现超高基数UV计算

简介：本文将会介绍Hologres基于roaringbitmap实现超高基数的UV计算

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

版权申明：本文内容由阿里云实名注册用户自发奉献，版权归原作者所有，阿里云开发者社区不领有其著作权，亦不承当相应法律责任。具体规定请查看《阿里云开发者社区用户服务协定》和《阿里云开发者社区知识产权爱护指引》。如果您发现本社区中有涉嫌剽窃的内容，填写侵权投诉表单进行举报，一经查实，本社区将立即删除涉嫌侵权内容。