关于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

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