在Hologres中如何进行离线UV计算 -

本文为您介绍在Hologres中如何进行离线UV计算。

背景信息

离线UV计算方案流程如下图所示。流程图

创建一张用户明细表，用于存放业务所有维度的明细数据。
创建一张历史用户映射表，存放历史每个访问过的用户ID（uid）和对应的int32数值，其中int32主要是Serial类型，便于与明细表做用户uid映射。
说明
RoaringBitmap类型要求用户ID必须是32位int类型且越稠密越好（用户ID最好连续），而常见的业务系统或者埋点中的用户ID很多是字符串类型，因此使用uid_mapping类型构建一张用户映射表。用户映射表利用Hologres的Serial类型（自增的32位int）来实现用户映射的自动管理和稳定映射。
把T+1（上一天）的明细表和历史用户映射表做Inner Join得到基础维度表。
根据业务逻辑，将基础维度表按照最细粒度基础维度group by，把上一天的所有数据根据最大的查询维度聚合出的uid结果放入RoaringBitmap中，并存放在聚合结果表（每天百万条）。
按照查询维度查询聚合结果表，对其中关键的RoaringBitmap字段做or运算进行去重后并统计基数，即可得出对应用户数UV，计数条数即可计算得出PV，达到亚秒级查询。

操作步骤

创建相关基础表

创建RoaringBitmap extention
使用RoaringBitmap前需要创建RoaringBitmap extention，语法如下，同时Hologres实例需要 V0.10及以上版本。
```
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
```

创建用户明细表

创建名称为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;

创建用户映射表
创建名称为uid_mapping的用户映射表，用于映射uid到32位INT类型，其DDL如下所示。
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;
```

创建聚合结果表

创建名称为dws_app的聚合结果表，用于存放RoaringBitmap聚合后的结果，其DDL如下所示。

基础维度为之后进行查询计算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;

更新用户映射表和聚合结果表
1. 更新用户映射表
  每天从上一天的uid中找出新客户（用户映射表uid_mapping中没有的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
;
```
2. 更新聚合结果表
  更新完用户映射表后，将数据做聚合运算后插入聚合结果表，主要步骤如下。
  1. 通过明细表Inner Join用户映射表，得到上一天的聚合条件和对应的uid_int32。
  2. 按照聚合条件做聚合运算后插入RoaringBitmap聚合结果表，作为上一天的聚合结果。
  3. 每天只需进行一次聚合，存放一份数据，数据条数等于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
;
```

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(pv) 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(pv) 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(pv) AS pv
FROM    ods_app
WHERE   ymd = '20210329'
GROUP BY country
         ,prov
         ,city;

SELECT  country
        ,prov
        ,COUNT(DISTINCT uid) AS uv
        ,COUNT(pv) AS pv
FROM    ods_app
WHERE   ymd >= '20210301' and ymd <= '20210331'
GROUP BY country
         ,prov;

可视化展示
计算出UV、PV后，大多数情况需要使用BI工具以更直观的方式可视化展示，由于需要使用RB_CARDINALITY和RB_OR_AGG进行聚合计算，需要使用BI的自定义聚合函数的能力，常见的具备该能力的BI包括Apache Superset和Tableau。
- Apache Superset
  1. Apache Superset连接Hologres，详情请参见Apache Superset。
  2. 设置dws_app表作为数据集。
  3. 在数据集中创建一个名称为UV的单独Metrics，表达式如下。
```
RB_CARDINALITY(RB_OR_AGG(uid32_bitmap))
```
    完成后您就可以开始探索数据了。
  4. （可选）创建Dashboard。
    创建仪表板请参见Create Dashboard。
- Tableau
  1. Tableau连接Hologres，详情请参见Tableau。
    可以使用Tableau的直通函数直接实现自定义函数的能力，详细介绍请参见直通函数。
  2. 创建一个计算字段，表达式如下。
```
RAWSQLAGG_INT("RB_CARDINALITY(RB_OR_AGG(%1))", [Uid32 Bitmap])
```
    完成后您就可以开始探索数据了。
  3. （可选）创建Dashboard。
    创建仪表板请参见Create a Dashboard。