简介：本文将会介绍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_columnCALL 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.uidFROM 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_srcGROUP BY country ,prov ,city;3.UV、PV查问
查问时，从汇总表dws\_app 中依照查问维度做聚合计算，查问bitmap基数，得出Group by条件下的用户数
--运行上面RB_AGG运算查问，可先敞开三阶段聚合开关性能更佳（默认敞开）set hg_experimental_enable_force_three_stage_agg=off --能够查问根底维度任意组合，任意时间段的uv pvSELECT country ,prov ,city ,RB_CARDINALITY(RB_OR_AGG(uid32_bitmap)) AS uv ,sum(1) AS pvFROM dws_appWHERE ymd = '20210329'GROUP BY country ,prov ,city;--查一个月 SELECT country ,prov ,RB_CARDINALITY(RB_OR_AGG(uid32_bitmap)) AS uv ,sum(1) AS pvFROM dws_appWHERE ymd >= '20210301' and ymd <= '20210331'GROUP BY country ,prov;该查问等价于SELECT country ,prov ,city ,COUNT(DISTINCT uid) AS uv ,COUNT(1) AS pvFROM ods_appWHERE ymd = '20210329'GROUP BY country ,prov ,city;SELECT country ,prov ,COUNT(DISTINCT uid) AS uv ,COUNT(1) AS pvFROM ods_appWHERE 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
版权申明：本文内容由阿里云实名注册用户自发奉献，版权归原作者所有，阿里云开发者社区不领有其著作权，亦不承当相应法律责任。具体规定请查看《阿里云开发者社区用户服务协定》和《阿里云开发者社区知识产权爱护指引》。如果您发现本社区中有涉嫌剽窃的内容，填写侵权投诉表单进行举报，一经查实，本社区将立即删除涉嫌侵权内容。