全部产品
Search
文档中心

:离线UV计算

更新时间:Jul 11, 2024

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

背景信息

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

  1. 创建一张用户明细表,用于存放业务所有维度的明细数据。

  2. 创建一张历史用户映射表,存放历史每个访问过的用户ID(uid)和对应的int32数值,其中int32主要是Serial类型,便于与明细表做用户uid映射。

    说明

    RoaringBitmap类型要求用户ID必须是32位int类型且越稠密越好(用户ID最好连续),而常见的业务系统或者埋点中的用户ID很多是字符串类型,因此使用uid_mapping类型构建一张用户映射表。用户映射表利用Hologres的Serial类型(自增的32位int)来实现用户映射的自动管理和稳定映射。

  3. 把T+1(上一天)的明细表和历史用户映射表做Inner Join得到基础维度表。

  4. 根据业务逻辑,将基础维度表按照最细粒度基础维度group by,把上一天的所有数据根据最大的查询维度聚合出的uid结果放入RoaringBitmap中,并存放在聚合结果表(每天百万条)。

  5. 按照查询维度查询聚合结果表,对其中关键的RoaringBitmap字段做or运算进行去重后并统计基数,即可得出对应用户数UV,计数条数即可计算得出PV,达到亚秒级查询。

操作步骤

  1. 创建相关基础表

    1. 创建RoaringBitmap extention

      使用RoaringBitmap前需要创建RoaringBitmap extention,语法如下,同时Hologres实例需要 V0.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到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;
    4. 创建聚合结果表

      创建名称为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;
  2. 更新用户映射表和聚合结果表

    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
      ;
  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(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;
  4. 可视化展示

    计算出UV、PV后,大多数情况需要使用BI工具以更直观的方式可视化展示,由于需要使用RB_CARDINALITYRB_OR_AGG进行聚合计算,需要使用BI的自定义聚合函数的能力,常见的具备该能力的BI包括Apache Superset和Tableau。

    • Apache Superset

      1. Apache Superset连接Hologres,详情请参见Apache Superset

      2. 设置dws_app表作为数据集。添加Dataset

      3. 在数据集中创建一个名称为UV的单独Metrics,表达式如下。创建UV

        RB_CARDINALITY(RB_OR_AGG(uid32_bitmap))

        完成后您就可以开始探索数据了。

      4. (可选)创建Dashboard。

        创建仪表板请参见Create Dashboard

    • Tableau

      1. Tableau连接Hologres,详情请参见Tableau

        可以使用Tableau的直通函数直接实现自定义函数的能力,详细介绍请参见直通函数

      2. 创建一个计算字段,表达式如下。创建UV

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

        完成后您就可以开始探索数据了。

      3. (可选)创建Dashboard。

        创建仪表板请参见Create a Dashboard