数据仓库本质
对数据的统计分析,存储的数据为中间计算结果
数据仓库建模方法论
ER模型(业务数据库设计要点)
遵循范式
第一范式(1NF) 属性不可分割
第二范式 (2NF) 不能存在“函数部分依赖”
第三范式 (3NF) 不能存在传递函数依赖
巴斯-科德范式 (BCNF)
第四范式 (4NF)
第五范式 (5NF)
b.维度模型(数据仓库设计要点)
维度建模理论
维度表设计要点
确认维度(表)
如果某些维度表的维度属性很少,例如只有一个**名称,则可不创建该维度表,而把该表的维度属性直接增加到与之相关的事实表中,这个操作称为维度退化。
确认主维度表和相关维度表
确认维度属性
尽可能生成丰富的维度属性
尽量不要使用编码,而使用明确的文字说明,一般可以编码和文字共存
尽量沉淀出通用的维度属性
星型模型、雪花模型
b.维度变化
全量快照表
离线数据仓库的计算周期通常为每天一次,所以可以每天保存一份全量的维度数据。这种方式的优点和缺点都很明显。
优点是简单而有效,开发和维护成本低,且方便理解和使用。
缺点是浪费存储空间,尤其是当数据的变化比例比较低时。
拉链表:记录每条消息的生命周期,一旦一条记录的生命周期结束,就重新开始一条新的记录,并把当前日期放入生效开始日期(生效开始日期->生效结束日期)
拉链表适用于,数据会发生变化,但是变化频率并不高的维度
数据仓库设计
ODS Operation Data Store 原始数据层
DWD Data Warehouse Detail 明细数据层
DIM Dimension 公共维度层
DWS Data Warehouse Summary 汇总数据层
ADS Application Data Service 数据应用层
ODS层(异构数据的融合)
设计要点:
ODS层不会进行统计分析,数据格式不会发生改变,数据格式尽量贴合原始数据的格式(行式存储)
ODS层的数据是非常多的,因为不断同步业务数据库和日志服务器的数据(资源不够),而且不会删除和备份,所以压缩格式选择gzip(压缩比 高)
压缩格式:
gzip : 压缩比非常高,hadoop默认支持,压缩和解压缩的效率不高。
lzo : 压缩比还行,hadoop默认不支持,压缩和解压缩的效率还行。
snappy : 压缩比不高,hadoop默认不支持,压缩和解压缩的效率非常高。
ODS层命名的规则:ods_表名_单分区增量全量标识(inc/full)
b.建表
分区 partition
主要用于过滤文件目录,数据仓库必须使用分区表
分区表的分区策略 : 以天为单位
列的类型
特殊类型
数组(集合)
Map(k-v集合)
value的类型要统一
key的个数不确定,属性值的类型确定
结构体(对象)
属性个数固定,属性值的类型不确定
DIM层(共通维度层)
设计要点
DIM层中保存的表都是维度表,遵循维度建模理论(尽可能的丰富)
分析维度的数据来源
主维表 : 主要用于分析维度的业务表
相关维表: 分析维度的业务表关联的表
DIM层的数据存储格式为orc列式存储+snappy压缩
DIM层表名的命名规范为dim_表名_全量表或者拉链表标识(full/zip)
DWD层(Data Warehouse Detail)
设计要点
DWD层的设计根据是维度建模理论,该层存储维度模式的事实表
DWD层的数据存储格式为orc列式存储+snappy压缩
DWD层表名的命令规范为dwd_数据域_表名_单分区增量全量标识(inc/full)
事实表:用于保存业务行为的表
统计数据的本质就是统计事情发生后的结果
设计表:
站在不同的角度,将事情描述清楚
必要的维度数据 + 可选的维度数据 + 度量值(结果)
表的分类
事务型事实表(95%)(inc)
原子性的业务行为(登录成功,注册成功,下单,退单,取消订单)
周期型快照事实表(full)
存量型数据指标(库存,余额)
累积型快照事实表
累积的就是业务行为
业务流程表:一张表中包含了一个业务流程中多个业务行为
DWS层(Data Warehouse Summary(汇总))
设计要点
DWS层的设计参考指标体系
DWS层的数据存储格式为orc列式存储+snappy压缩
DWS层表名的命名规范为dws_数据域_统计粒度_业务过程_统计周期(1d/nd/td)
注意:1d表示最近1日,nd表示最近n日,td表示历史至今
ADS层(Application Data Service)
设计要点
统计结果的数据量不会很多,没有分区
主要目的是为对外提供服务,所以不需要再统计分析,所以存储方式为行式存储,不用压缩
业务总结
1.拉链表(如何实现)
方法一:union all
1.先筛选出ods中昨天更新的最后一条数据(打标记筛选)
row_nuber() over(partition by data ,id order by ts desc)
2.dim中的表(dim_user_zip)与ods层中的表(ads_user_info_inc) union all
3.打标记
row_nuber()over(partition by id order by start desc)
4.进行判断
如果是2分在昨天-1的分区
如果是1分到‘9999-12-31’的分区
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_user_zippartition (dt)
select
`id` ,
`login_name` ,
`nick_name` ,
`name` ,
`phone_num` ,
`email` ,
`user_level` ,
`birthday` ,
`gender` ,
`create_time` ,
`operate_time` ,
`start_date` ,
if ( rn=2, date_sub('2022-06-09', 1), '9999-12-31' ) `end_date`,
if ( rn=2, date_sub('2022-06-09', 1), '9999-12-31' )
from (
select
*,
row_number() over ( partitionbyidorderbystart_datedesc) rn
from (
select
`id` ,
`login_name` ,
`nick_name` ,
`name` ,
`phone_num` ,
`email` ,
`user_level` ,
`birthday` ,
`gender` ,
`create_time` ,
`operate_time` ,
`start_date` ,
`end_date`
fromdim_user_zip
wheredt='9999-12-31'
unionall
select
*
from (
select
data.`id` ,
data.`login_name` ,
data.`nick_name` ,
data.`name` ,
data.`phone_num` ,
data.`email` ,
data.`user_level` ,
data.`birthday` ,
data.`gender` ,
data.`create_time` ,
data.`operate_time` ,
'2022-06-09'start_date,
'9999-12-31'end_date,
row_number() over (partitionbydata.idorderbytsdesc) rn1
fromods_user_info_inc
wheredt='2022-06-09'
) t0wherern1=1
) t
) t1
方法二:full join
1.先筛选出ods中昨天更新的最后一条数据(打标记筛选)
row_nuber() over (partition by data ,id order by ts desc)
2.full join 得到emp表
3.判断 emp表(new_id is not null,new _id,old_id) 得到t1表(放入9999-12-31分区)
4.过滤 old_id not null and new_id is not null 得到t2表(放入到昨天-1天分区)
5.t1 union all t2 然后分区
sethive.exec.dynamic.partition.mode=nonstrict;
with
tmpas
(
select
old.idold_id,
old.login_nameold_login_name,
old.nick_nameold_nick_name,
old.nameold_name,
old.phone_numold_phone_num,
old.emailold_email,
old.user_levelold_user_level,
old.birthdayold_birthday,
old.genderold_gender,
old.create_timeold_create_time,
old.operate_timeold_operate_time,
old.start_dateold_start_date,
old.end_dateold_end_date,
new.idnew_id,
new.login_namenew_login_name,
new.nick_namenew_nick_name,
new.namenew_name,
new.phone_numnew_phone_num,
new.emailnew_email,
new.user_levelnew_user_level,
new.birthdaynew_birthday,
new.gendernew_gender,
new.create_timenew_create_time,
new.operate_timenew_operate_time,
new.start_datenew_start_date,
new.end_datenew_end_date
from
(
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date
fromdim_user_zip
wheredt='9999-12-31'
)old
fullouterjoin
(
select
id,
login_name,
nick_name,
md5(name) name,
md5(phone_num) phone_num,
md5(email) email,
user_level,
birthday,
gender,
create_time,
operate_time,
'2022-06-09'start_date,
'9999-12-31'end_date
from
(
select
data.id,
data.login_name,
data.nick_name,
data.name,
data.phone_num,
data.email,
data.user_level,
data.birthday,
data.gender,
data.create_time,
data.operate_time,
row_number() over (partitionbydata.idorderbytsdesc) rn
fromods_user_info_inc
wheredt='2022-06-09'
)t1
wherern=1
)new
onold.id=new.id
)
insertoverwritetabledim_user_zippartition(dt)
select
if(new_idisnotnull,new_id,old_id),
if(new_idisnotnull,new_login_name,old_login_name),
if(new_idisnotnull,new_nick_name,old_nick_name),
if(new_idisnotnull,new_name,old_name),
if(new_idisnotnull,new_phone_num,old_phone_num),
if(new_idisnotnull,new_email,old_email),
if(new_idisnotnull,new_user_level,old_user_level),
if(new_idisnotnull,new_birthday,old_birthday),
if(new_idisnotnull,new_gender,old_gender),
if(new_idisnotnull,new_create_time,old_create_time),
if(new_idisnotnull,new_operate_time,old_operate_time),
if(new_idisnotnull,new_start_date,old_start_date),
if(new_idisnotnull,new_end_date,old_end_date),
if(new_idisnotnull,new_end_date,old_end_date) dt
fromtmp
unionall
select
old_id,
old_login_name,
old_nick_name,
old_name,
old_phone_num,
old_email,
old_user_level,
old_birthday,
old_gender,
old_create_time,
old_operate_time,
old_start_date,
cast(date_add('2022-06-09',-1) asstring) old_end_date,
cast(date_add('2022-06-09',-1) asstring) dt
fromtmp
whereold_idisnotnull
andnew_idisnotnull;
2.最近7天连续三天下单(登录)用户数
指标拆建
建模
在ods层需要下单(登录)的数据
在dwd中创建下单(登录)事务事实表
在dim层没有维度
在dws(轻度聚合)创建1d下单表
在ads写逻辑:
打标记:获取后两行下单时间
作差:后两行的数据-当前行的数据
筛选:过滤出减的值等于二
insertoverwritetableads_order_continuously_user_count
select*fromads_order_continuously_user_count
union
select
'2022-06-08',
7,
count(distinct(user_id))
from
(
select
user_id,
datediff(lead(dt,2,'9999-12-31') over(partitionbyuser_idorderbydt),dt) diff
fromdws_trade_user_order_1d
wheredt>=date_add('2022-06-08',-6)
)t1
wherediff=2;
总结:连续问题解决方案:
方法一:获取后行日期相减,在过滤出连续的天数
方法二:先进行排序,日期减排序的序号,在分组
select
t3.id,
t3.days+1aslianxu_day
from (
select
t2.id,
datediff(max(t2.dt),min(t2.last_dt)) as days
from (
select
t1.id,
t1.dt,
t1.last_dt,
datediff( t1.dt,t1.last_dt) chazhi
from (
select
id,
dt,
lag(dt,1,'1970-01-01') over(partitionbyidorderbydt ) last_dt
fromgame_user
) t1
) t2
wheret2.chazhi<=2
groupbyt2.id
) t3;
方法三:使用二进制的方式
3.各个省份的交易额
为什么会选择这个指标呢:因为这个会产生数据倾斜,需要解释怎么避免数据倾斜
指标拆建:
建模
ods层需要下单和省份相关的数据
dwd层需要下单事务实时表
dim需要省份维度的数据
dws需要下单和省份join出1/7/30d轻度聚合
ads union dws中的表
insertoverwritetableads_order_by_province
select*fromads_order_by_province
union
select
'2022-06-08'dt,
1recent_days,
province_id,
province_name,
area_code,
iso_code,
iso_3166_2,
order_count_1d,
order_total_amount_1d
fromdws_trade_province_order_1d
wheredt='2022-06-08'
union
select
'2022-06-08'dt,
recent_days,
province_id,
province_name,
area_code,
iso_code,
iso_3166_2,
caserecent_days
when7thenorder_count_7d
when30thenorder_count_30d
endorder_count,
caserecent_days
when7thenorder_total_amount_7d
when30thenorder_total_amount_30d
endorder_total_amount
fromdws_trade_province_order_ndlateralviewexplode(array(7,30)) tmpasrecent_days
wheredt='2022-06-08';
4.加购到支付的一个平均使用时长
ods层需要下单和支付相关的数据
dwd层需要下单事务实时表和支付事务事实表
dim没有
dws需要下单1d表和支付1d表
ads 下单和支付join
5.路径问题(桑基图)
ods层需要日志行为数据
dwd层需要流量域页面浏览事务事实表
dim没有
ads 先打标记(后一个页面以及排序)(进行group by 一页页面+后一页页面)然后在count
insertoverwritetableads_page_path
select*fromads_page_path
union
select
'2022-06-08'dt,
source,
nvl(target,'null'),
count(*) path_count
from
(
select
concat('step-',rn,':',page_id) source,
concat('step-',rn+1,':',next_page_id) target
from
(
select
page_id,
lead(page_id,1,null) over(partitionbysession_idorderbyview_time) next_page_id,
row_number() over (partitionbysession_idorderbyview_time) rn
fromdwd_traffic_page_view_inc
wheredt='2022-06-08'
)t1
)t2
groupbysource,target;
6.用户留存率
留存分析一般包含新增留存和活跃留存分析。
新增留存分析是分析某天的新增用户中,有多少人有后续的活跃行为。活跃留存分析是分析某天的活跃用户中,有多少人有后续的活跃行为。
留存分析是衡量产品对用户价值高低的重要指标。
此处要求统计新增留存率,新增留存率具体是指留存用户数与新增用户数的比值,例如2022-06-08新增100个用户,1日之后(2022-06-09)这100人中有80个人活跃了,那2022-06-08的1日留存数则为80,2022-06-08的1日留存率则为80%。
用户域用户注册事务事实表
用户域用户粒度登录历史至今汇总表
insertoverwritetableads_user_retention
select*fromads_user_retention
union
select
'2022-06-08'dt,
login_date_firstcreate_date,
datediff('2022-06-08',login_date_first) retention_day,
sum(if(login_date_last='2022-06-08',1,0)) retention_count,
count(*) new_user_count,
cast(sum(if(login_date_last='2022-06-08',1,0))/count(*)*100asdecimal(16,2)) retention_rate
from
(
select
user_id,
date_idlogin_date_first
fromdwd_user_register_inc
wheredt>=date_add('2022-06-08',-7)
anddt<'2022-06-08'
)t1
join
(
select
user_id,
login_date_last
fromdws_user_user_login_td
wheredt='2022-06-08'
)t2
ont1.user_id=t2.user_id
groupbylogin_date_first;
7.离线指标:
沉默用户占比:只在当天启动过,且启动时间在一周前
流量主题:
各渠道流量统计
用户主题:
用户行为漏斗分析
最近7/30日品牌复购率
新增下单用户统计
商品主题:
最近30日各品牌复购率
各品牌商品下单统计
各品类商品下单统计
交易主题:
下单到支付时间间隔平均值
各省份交易统计
优惠券主题:
优惠券使用统计
8.dws有哪些表
最近1日汇总表
交易域用户商品粒度订单最近1日汇总表
工具域用户优惠券粒度优惠券使用(支付)最近1日汇总表
互动域商品粒度收藏商品最近1日汇总表
流量域会话粒度页面浏览最近1日汇总表
最近n日汇总表
交易域用户商品粒度订单最近n日汇总表
历史至今汇总表
交易域用户粒度订单历史至今汇总表
9.dwd有哪些表
交易域加购事务事实表
交易域下单事务事实表
交易域支付成功事务事实表
工具域优惠券使用(支付)事务事实表
互动域收藏商品事务事实表
10.dim有哪些表
商品维度表
优惠券维度表
活动维度表
地区维度表
用户维度表