超大数据量UV类和PV类问题解方案

超大数据量UV类PV类问题解决方案

背景

1. 技术使用：基于大数据平台的 spark-sql,有基础能力去处理百亿级数据表
2. 问题背景：随着大数据平台和数仓的建设,以及业务极大增加和日常积累,会让单表达到百亿级别

这时候就会出现两个典型的场景,就是标题所说的UV场景和PV场景

名词解释

1. UV类问题

UV 英文就是Unique visitor 即这个网站有多少个访问者,这里就有个用户去重的问题,多次访问需要去重

拓展一下就是针对数据表维度进行 count(distinct ) 计算的一类场景,不只是限制在用户

2. PV类型问题

PV 英文就是Page View 即这个网站被访问了多少次,这里只要去计数就可以了,不需要考虑重复的问题

拓展一下就是针对数据表进行 count() 计算的一类场景, 往往这种表都是日志表,每天去count(), 再去sum()

实际案例

往往名词解释都是概述的,并不能具体的指向遇到什么样的问题,该怎么做,接下来分别举个例子来说明

• 大表准备

t_big_log 是一个以part(日期字段)为分区的增量日志表

最早日期为 2016-01-01

数据量为 200亿

并且以每天千万的增量增长

create table t_big_log
(   uuid                   String        COMMENT  '唯一id',
    user_id                String        COMMENT  '用户id',
    active_time            String        COMMENT  '活动时间,精确到具体时分秒',
    appkey                 String        COMMENT  '应用系统key'
) PARTITIONED BY (part string)
STORED AS PARQUET

• 需求说明

  • 需要计算各个应用系统每天有多少个用户量,需要每天更新,不是一次性需求
  • 计算每个系统每天被访问了多少次,需要每天更新,不是一次性需求

一. UV实际案例及处理方式

计算每天有多少用户量

如果是小数据量的话,很容易计算直接每天执行

select 
to_date(active_time),
appkey,
count(distinct user_id)
from t_big_log
group by 
to_date(active_time),
appkey

但是当数据量达到 200亿的时候,这个计算量是很恐怖的,实际生产的应用中非常的耗时同时也耗资源

这个计算每天都在执行,这是不可接受的

这时就采用以下方法：

1. 首先新建一个中间表

create table t_uv_result_end
(   user_id                String        COMMENT  '用户id',
    active_date            String        COMMENT  '活动日期',
    appkey                 String        COMMENT  '应用系统key',
    statis_date            String        COMMENT  '统计日期'
) PARTITIONED BY (hisornow string)
STORED AS PARQUET

2. 数据初始化

备注: current_date() 在实际应用中一般会采用参数传入的方式,保证不会因为跨日而导致运行周期不一致
这里就简化了

取截止到昨天为止的去重数据放入his分区

insert overwrite table t_uv_result_end PARTITION(hisornow)
select
distinct
user_id,
to_date(active_time) as active_date,
appkey,
date_sub(current_date(),1) as statis_date,
'his' as hisornow
from t_big_log
where 1=1
and part <= date_sub(current_date(),1)
;

这一步非常耗时,不过这个是一次性的执行,之后一般不需要再执行

这一步做了哪些事

• 首先由于日志是active_time 明细到具体时间的, 现在转为日期,大大的缩减了数据量,200亿 -> 5亿
• 预先做了用户的去重, 去除了历史的重复用户, 达到我们计算目的的同时, 也大大缩减了数据量 5亿 -> 1亿
• 记录 statis_date 这个字段是为了之后数据执行的时候出现错误,知道最数据从哪天开始

接下来是叠加截止到昨天为止的数据和今天新增的数据放入now分区

insert overwrite table t_uv_result_end PARTITION(hisornow)
select
distinct
user_id,
active_date,
appkey,
current_date() as statis_date,
'now' as hisornow
from
(
	select
		user_id,
        active_date,
        appkey
	from t_uv_result_end
	where hisornow = 'his'

    union all

	select
	    distinct
		user_id,
        to_date(active_time) as active_date,
        appkey,
	from t_big_log
	where 1=1
    and part = current_date()
)
;

这一步由于his分区的数据量被大大减少,同时t_big_log表只有一天的分区,且总体的计算只有个简单union all

所以这一步是相对比较快速的

这样就完成了数据初始化

3. 日常脚本

insert overwrite table t_uv_result_end PARTITION(hisornow)
select
user_id,
active_time,
appkey,
date_sub(current_date(),1),
'his'
from t_uv_result_end
where hisornow = 'now'
;

insert overwrite table t_uv_result_end PARTITION(hisornow)
select
distinct
user_id,
active_date,
appkey,
current_date(),
'now'
from
(
	select
		user_id,
        active_date,
        appkey
	from t_uv_result_end
	where hisornow = 'his'

    union all

	select
	    distinct
		user_id,
        to_date(active_time) as active_date
	from t_big_log
	where 1=1
	and pdate = current_date()
)
;

日常脚本和初始化脚本类似,只是调换个顺序,不多赘述

然后

select 
active_date,
count(distinct user_id)
from t_uv_result_end
where hisornow = 'now'
group by active_date

这样由于t_uv_result_end 这个表每天更新变的很快

同时数据量本身也被降到了一个可接受的范围

就完成了UV类问题的解决

总结：

• UV类问题 总是能够通过去重的方式,大大减少数据量
• 由于是可去重数据,所以可以采用先计算一个历史截止数据,再和新的增量去计算
• 为什么要设置his和now分区, 因为即使已经是经过去重的方式仍然还有5千万的数据,每天保存仍然是个不划算的方式

二. PV实际案例及处理方式

计算每天页面访问多少次

如果是小数据量的话,很容易计算直接每天执行

select 
to_date(active_time),
appkey,
count(uuid)
from t_big_log
group by 
to_date(active_time),
appkey

但是当数据量达到 200亿的时候,这个计算量是很恐怖的,实际生产的应用中非常的耗时同时也耗资源

这个计算每天都在执行,这是不可接受的

这时就采用以下方法：

1. 首先新建一个中间表

create table t_pv_result_part
(   active_date            String        COMMENT  '活动日期',
    appkey                 String        COMMENT  '应用系统key',
    pv                     String        COMMENT  '应用系统key'
) PARTITIONED BY (part string)
STORED AS PARQUET

2. 追历史并计算每天的汇总

备注: current_date() 在实际应用中一般会采用参数传入的方式,保证不会因为跨日而导致运行周期不一致
这里就简化了

insert overwrite table t_pv_result_part PARTITION(part)
select
to_date(active_time) as active_date,
appkey,
count(uuid) as pv,
part
from t_big_log
where 1=1
and part <= current_date()
group by 
to_date(active_time),
appkey,
part
;

这一步非常耗时,不过这个是一次性的执行,之后一般不需要再执行

这一步做了哪些事

• 把计算pv这件事,分到每个分区去计算
• 这个也是利用了pv数量的累加性

3. 日常脚本

insert overwrite table t_pv_result_part PARTITION(part)
select
to_date(active_time) as active_date,
appkey,
count(uuid) as pv,
current_date()
from t_big_log
where 1=1
and part = current_date()
group by 
to_date(active_time),
appkey
;

然后

select 
active_date,
appkey
sum(pv)
from t_uv_result_part
where 1=1
group by 
active_date,
appkey

这样由于 t_uv_result_part 这个表每天更新变的很快

同时数据量本身也被降到了一个可接受的范围

就完成了PV类问题的解决

总结：

• PV类问题 总是能够通过预先每天计算再分区汇总的方式,大大减少数据量