簡(jiǎn)介：本文將會(huì)介紹Hologres基于roaringbitmap實(shí)現(xiàn)超高基數(shù)的UV計(jì)算

RoaringBitmap是一種壓縮位圖索引，RoaringBitmap自身的數(shù)據(jù)壓縮和去重特性十分適合對(duì)于大數(shù)據(jù)下uv計(jì)算。其主要原理如下：

• 對(duì)于32bit數(shù), RoaringBitmap會(huì)構(gòu)造2^16個(gè)桶，對(duì)應(yīng)32位數(shù)的高16位；32位數(shù)的低16位則映射到對(duì)應(yīng)桶的一個(gè)bit上。單個(gè)桶的容量由桶中的已有的最大數(shù)值決定
• bitmap把32位數(shù)用1位表示，可以大大地壓縮數(shù)據(jù)大小。
• bitmap位運(yùn)算為去重提供了手段。

主體思想（T+1）：把上一天的所有數(shù)據(jù)根據(jù)最大的查詢維度聚合出的uid結(jié)果放入RoaringBitmap中，把RoaringBitmap和查詢維度存放在聚合結(jié)果表（每天百萬(wàn)條）。之后查詢時(shí)，利用Hologres強(qiáng)大的列存計(jì)算直接按照查詢維度去查詢聚合結(jié)果表，對(duì)其中關(guān)鍵的RoaringBitmap字段做or運(yùn)算進(jìn)行去重后并統(tǒng)計(jì)基數(shù)，即可得出對(duì)應(yīng)用戶數(shù)UV，count條數(shù)即可計(jì)算得出PV，達(dá)到亞秒級(jí)查詢。

只需進(jìn)行一次最細(xì)粒度的預(yù)聚合計(jì)算，也只生成一份最細(xì)粒度的預(yù)聚合結(jié)果表。得益于Hologres的實(shí)時(shí)計(jì)算能力，該方案下預(yù)計(jì)算所需的次數(shù)和空間都達(dá)到較低的開(kāi)銷。

Hologres計(jì)算UV、PV方案詳情

圖1 Hologres基于RoaringBitmap計(jì)算pv uv流程

1.創(chuàng)建相關(guān)基礎(chǔ)表

1）使用RoaringBitmap前需要?jiǎng)?chuàng)建RoaringBitmap extention，語(yǔ)法如下，同時(shí)該功能需要Hologres ?0.10版本。

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2）創(chuàng)建表ods_app為明細(xì)源表，存放用戶每天大量的明細(xì)數(shù)據(jù) （按天分區(qū)），其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根據(jù)需求設(shè)置，根據(jù)該表的實(shí)時(shí)查詢需求，從什么維度做分片能夠取得較好效果即可 CALL set_table_property('public.ods_app', 'distribution_key', 'uid'); --用于做where過(guò)濾條件，包含完整年月日時(shí)間字段推薦設(shè)為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）創(chuàng)建表uid_mapping為uid映射表，uid映射表用于映射uid到32位int類型。

RoaringBitmap類型要求用戶ID必須是32位int類型且越稠密越好（用戶ID最好連續(xù)），而常見(jiàn)的業(yè)務(wù)系統(tǒng)或者埋點(diǎn)中的用戶ID很多是字符串類型，因此使用uid_mapping類型構(gòu)建一張映射表。映射表利用Hologres的SERIAL類型（自增的32位int）來(lái)實(shí)現(xiàn)用戶映射的自動(dòng)管理和穩(wěn)定映射。

注: 該表在本例每天批量寫(xiě)入場(chǎng)景，可為行存表也可為列存表，沒(méi)有太大區(qū)別。如需要做實(shí)時(shí)數(shù)據(jù)（例如和Flink聯(lián)用），需要是行存表，以提高Flink維表實(shí)時(shí)JOIN的QPS。

BEGIN; CREATE TABLE public.uid_mapping ( uid text NOT NULL, uid_int32 serial, PRIMARY KEY (uid) ); --將uid設(shè)為clustering_key和distribution_key便于快速查找其對(duì)應(yīng)的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）創(chuàng)建表dws_app基礎(chǔ)聚合表，用于存放在基礎(chǔ)維度上聚合后的結(jié)果

基礎(chǔ)維度為之后進(jìn)行查詢計(jì)算pv和uv的最細(xì)維度，這里以country， prov， city為例構(gòu)建聚合表

begin; create table dws_app( country text, prov text, city text, ymd text NOT NULL, --日期字段 uid32_bitmap roaringbitmap, -- UV計(jì)算 pv integer, -- PV計(jì)算 primary key(country, prov, city, ymd)--查詢維度和時(shí)間作為主鍵，防止重復(fù)插入數(shù)據(jù) ); CALL set_table_property('public.dws_app', 'orientation', 'column'); --clustering_key和event_time_column設(shè)為日期字段，便于過(guò)濾 CALL set_table_property('public.dws_app', 'clustering_key', 'ymd'); CALL set_table_property('public.dws_app', 'event_time_column', 'ymd'); --distribution_key設(shè)為group by字段 CALL set_table_property('public.dws_app', 'distribution_key', 'country,prov,city'); end;

2.更新dws表及id_mapping表

每天從上一天的uid中找出新客戶（uid映射表uid_mapping中沒(méi)有的uid）插入到uid映射表中

WITH -- 其中ymd = '20210329'表示上一天的數(shù)據(jù) 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映射表后，將數(shù)據(jù)做聚合運(yùn)算后插入聚合結(jié)果表，主要步驟如下：

• 首先通過(guò)源表inner join uid映射表，得到上一天的聚合條件和對(duì)應(yīng)的uid_int32；
• 然后按照聚合條件做聚合運(yùn)算后插入RoaringBitmap聚合結(jié)果表，作為上一天的聚合結(jié)果；
• 每天只需進(jìn)行一次聚合，存放一份數(shù)據(jù)，數(shù)據(jù)條數(shù)最壞等于UV的量。以案例說(shuō)明，明細(xì)表每天幾億的增量，在聚合結(jié)果表每天只需存放百萬(wàn)級(jí)數(shù)據(jù)。

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查詢

查詢時(shí)，從匯總表dws_app 中按照查詢維度做聚合計(jì)算，查詢bitmap基數(shù)，得出Group by條件下的用戶數(shù)

--運(yùn)行下面RB_AGG運(yùn)算查詢，可先關(guān)閉三階段聚合開(kāi)關(guān)性能更佳（默認(rèn)關(guān)閉） set hg_experimental_enable_force_three_stage_agg=off --可以查詢基礎(chǔ)維度任意組合，任意時(shí)間段的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; --查一個(gè)月 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; 該查詢等價(jià)于 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.可視化展示

計(jì)算出UV、PV和，大多數(shù)情況需要用BI工具以更直觀的方式可視化展示，由于需要使用RB_CARDINALITY 和 RB_OR_AGG 進(jìn)行聚合計(jì)算，需要使用BI的自定義聚合函數(shù)的能力，常見(jiàn)的具備該能力的BI包括Apache Superset和Tableau，下面將會(huì)講述這兩個(gè)BI工具的最佳實(shí)踐。

4.1 使用 Apache Superset

Apache Superset 對(duì)接 Hologres 的方式，請(qǐng)參考產(chǎn)品手冊(cè)。在Superset中可以直接使用dws_app表作為Dataset使用

并且在數(shù)據(jù)集中，創(chuàng)建一個(gè)單獨(dú)Metrics，名為UV，表達(dá)式如下：

RB_CARDINALITY(RB_OR_AGG(uid32_bitmap))

然后您就可以開(kāi)始探索數(shù)據(jù)了

當(dāng)然也可以創(chuàng)建Dashborad：

4.2 使用 Tableau

Tableau 對(duì)接 Hologres 的方式，請(qǐng)參考產(chǎn)品手冊(cè)。可以使用Tableau的直通函數(shù)直接實(shí)現(xiàn)自定義函數(shù)的能力，詳細(xì)介紹請(qǐng)參照Tableau的手冊(cè)。在Tableau對(duì)接Hologres后，可以創(chuàng)建一個(gè)計(jì)算字段，表達(dá)式如下

RAWSQLAGG_INT("RB_CARDINALITY(RB_OR_AGG(%1))", [Uid32 Bitmap])然后您就可以開(kāi)始探索數(shù)據(jù)了

當(dāng)然也可以創(chuàng)建Dashborad

原文鏈接：https://developer.aliyun.com/article/784334?

版權(quán)聲明：本文內(nèi)容由阿里云實(shí)名注冊(cè)用戶自發(fā)貢獻(xiàn)，版權(quán)歸原作者所有，阿里云開(kāi)發(fā)者社區(qū)不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。具體規(guī)則請(qǐng)查看《阿里云開(kāi)發(fā)者社區(qū)用戶服務(wù)協(xié)議》和《阿里云開(kāi)發(fā)者社區(qū)知識(shí)產(chǎn)權(quán)保護(hù)指引》。如果您發(fā)現(xiàn)本社區(qū)中有涉嫌抄襲的內(nèi)容，填寫(xiě)侵權(quán)投訴表單進(jìn)行舉報(bào)，一經(jīng)查實(shí)，本社區(qū)將立刻刪除涉嫌侵權(quán)內(nèi)容。

總結(jié)

以上是生活随笔為你收集整理的Hologres如何基于roaringbitmap实现超高基数UV计算？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。