声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章
2. 由于是个人总结, 所以用最精简的话语来写文章
3. 若有错误不当之处, 请指出
一、概述:
介绍:
数据仓库里存放着海量的数据, 并拥有分析计算程序, 计算输出的结果供企业制定决策使用
输入数据来源:
用户行为数据(前端埋点), 业务数据(MySQL数据库), 爬虫数据
用户行为数据: 用户的动作产生的数据, 比如浏览, 停留, 点击, 点赞, 评论, 收藏
业务数据: 业务流程中产生的数据, 比如登录, 订单, 支付
输出系统:
报表系统, 用户画像系统, 推荐系统, 供OLAP引擎提供数据进行分析
项目架构:
同比:
与历史同时期比较, 例如 本周三和上周三做比较
环比:
与上一个统计周期比较, 例如 本周三和本周二做比较
T+1模式:
今天凌晨开始去计算昨天的数据
SKU & SPU:
SKU: 一台黑色, 128G内存的iPhoneX手机
SPU: 一台iPhoneX手机
埋点日志:
页面, 曝光, 事件(动作, action), 启动, 错误
二、数仓分层:
分层的好处:
- 高层的计算 可以
复用低层计算的中间结果
当没有分层的时候:
比如要统计A, B, C指标, 那么A需要对初始表进行过滤空值 然后再进一步计算, B和C也要重复地去对初始表过滤空值
而分层后:
可以把过滤空值的计算交给dwd层去做一次, 然后ABC都从dwd层里去取过滤好的数据, 复用了dwd层计算的中间结果 - 业务解耦
- 层次分明, 便于开发和维护
分为哪些层:
- ods(数据接入层, 原始数据)
- 备份初始数据, 保持数据原貌不做任何修改
- 创建分区表, 防止后续的全表扫描
- 数据采用压缩, 减少占用的磁盘空间
- dwd(明细层, 处理初始数据, 如ETL等)
- 处理数据:
过滤掉脏数据(空值, 非法数据)- 对敏感数据进行
脱敏, 比如将手机号中的几位变成*- 对一些数据进行
加密
- 维度退化
用到维度表时要进行join, 而join操作非常耗时; 所以对于那些分的过细的维度进行退化删除, 减少后续的join
如: (商品表、spu表、品类表、一级分类、二级分类、三级分类) --> 商品表
(省份表、地区表) --> 地区表维度退化：将维度表和事实表 合成一张表
实时数仓里维度退化指的是: 主流要和A, B, C三条流进行维度关联, 干脆让A先关联B, 再去关联C, 最后主流只和A进行关联
- 解析用户行为数据, 将其分到不同的dwd层的表
- 列式存储
- 压缩
- dim(维度表)
建立维度表, 如 地区维度, 用户维度, 商品维度 - dws(汇总层, 聚合)
当天数据的汇总 - dwt(汇总层, 聚合)
字段:
最近一段时间数据的汇总
迄今为止的数据汇总
首次时间
末次时间 - ads(应用层, 面向需求指标(主题))
根据各种需求指标, 对dws/dwt层数据做进一步分析
三、数仓建模:
维度字段:
group by 的字段 是看待问题的角度: 如省份、时间、年龄段、职业
度量值:
可以用来聚合的值, 如下单金额、下单次数
维度表:
是对事实的描述信息, 是宽表(有很多列), 数据不经常变化
组成: 维度字段+维度信息(可能还有其他表的维度字段)
事实表:
每行数据代表一个事件(下单, 点赞), 是高表(有很多行), 数据经常变化
组成: 维度字段+度量值
表的分类:
- 周期型快照事实表(同步策略: 全量更新)
不会保留所有明细数据, 只会保留固定时间间隔内的聚合数据; 这样的话数据量较小, 故可以采用 全量更新
例如 每天或者每月的销售额 或 每月的账户余额, 不关心数据的变化过程, 只关心最终聚合值;
首日同步脚本:
Sqoop拉取MySQL时: where 1=1, 首日导入所有数据 到’2020-06-14’分区
DWD层: where dt=‘2020-06-14’; 静态分区partition(dt=‘2020-06-14’)
insert overwrite table dwd_cart_info partition(dt='2020-06-14') select xxx from ods_cart_info where dt='2020-06-14';
每日同步脚本: 代码逻辑和首日同步脚本相同
Sqoop拉取MySQL时: where 1=1, 每日都导入所有数据 到’2020-06-14’分区
DWD层: where dt=‘2020-06-15’; 静态分区partition(dt=‘2020-06-15’)
insert overwrite table dwd_cart_info partition(dt='2020-06-15') select xxx from ods_cart_info where dt='2020-06-15';
- 事务型事实表(同步策略: 增量更新)
数据不允许修改, 故可以采用 增量更新
首日同步脚本:
Sqoop拉取MySQL时: where 1=1, 首日导入所有数据 到’2020-06-14’分区
DWD层: where dt=‘2020-06-14’; 静态分区partition(dt=‘2020-06-14’)
insert overwrite table dwd_comment_info partition (dt='2020-06-14') select xxx from ods_comment_info where dt='2020-06-14';
每日同步脚本: 代码逻辑和首日同步脚本相同
Sqoop拉取MySQL时: where date_format(create_time,‘%Y-%m-%d’)=‘$do_date’, 每日导入当天数据(新增数据) 到当天分区
DWD层: where dt=‘2020-06-15’; 静态分区partition(dt=‘2020-06-15’)
insert overwrite table dwd_comment_info partition (dt='2020-06-15') select xxx from ods_comment_info where dt='2020-06-14';
- 累积型快照事实表(同步策略: 新增当天数据(放到9999-99-99分区) && 修改原分区的状态变化数据)
这里的数据, 是说一整行数据
用于跟踪业务事实的变化, 数据会不断修改
例如 订单表的商品记录在 生成订单, 运输 和 签收的各个业务阶段状态都会改变
这样的话数据量较大, 数据又允许修改, 故只能采用 新增及变化
首日同步脚本:
Sqoop拉取MySQL时: where 1=1, 首日导入所有数据 到’2020-06-14’分区
DWD层: where dt=‘2020-06-14’; 动态分区partition(dt)
insert overwrite table dwd_coupon_use partition(dt) select id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time, expire_time, -- 过期算作 过期那天使用了 coalesce(date_format(used_time,'yyyy-MM-dd'),date_format(expire_time,'yyyy-MM-dd'),'9999-99-99') -- 即动态分区字段dt from ods_coupon_use where dt='2020-06-14';
每日同步脚本:
Sqoop拉取MySQL时: 每日导入所有新增及变化数据 到当天分区
-- 新增及变化数据 where (date_format(get_time,'%Y-%m-%d')='$do_date' or date_format(using_time,'%Y-%m-%d')='$do_date' or date_format(used_time,'%Y-%m-%d')='$do_date' or date_format(expire_time,'%Y-%m-%d')='$do_date')"
DWD层: 类似于拉链表: 增加新数据(t2独有), 修改老数据(t1和t2共有 && 1.endDate=‘9999-99-99’)
前一天(首日): ‘2020-06-14’, 只有’9999-99-99’分区
当天(每日): ‘2020-06-15’, 有’9999-99-99’分区 和 '2020-06-14’分区
**站在维度的角度, 去看待度量值: **
select 维度字段, 聚合函数(度量值)
from dwd_order_info
group by 维度字段
同步策略:
维度表(用户维度表除外)一般数据量较小
- 全量更新
适合于 数据量小 的表: 一般是维度表(用户维度表除外, 因为用户太多了) - 增量更新
适合于 数据量大 & 不变化 的表 - 新增及变化
适合于 数据量大 & 变化 的表: 一般是订单表, 用户表 - 只首次同步:
适合常年数据不变化的表: 如地区表
关系建模:
严格遵循三范式,
优点: 建表规范有条理, 没有冗余字段
缺点: 表分的太细 join起来太麻烦, 数据分析不擅长大数据表join
维度建模:
面向业务, 将业务用事实表和维度表呈现出来
模型分类:
- 星型模型: 维度表关联只有一层
- 星座模型: 在星型模型基础上, 多张是事实表可以共享一张维度表
- 雪花模型: 维度表关联可以有多层(join起来麻烦, A的维度是B, B的维度是C)
一般采用星座模型, dwd层进行维度退化, 不会有维度表关联多层的情况
建模的流程:
- 选择业务过程
要选取MySQL里的哪些表进行导入 - 声明粒度
一行数据代表什么含义; 比如是一次的下单金额, 还是一天的下单金额, 还是一周的下单金额 - 确定维度字段
- 确定度量值
建模工具: EZDML, 展示表与表之间的依赖关系
业务总线矩阵表:
横轴: 维度字段+度量值
纵轴: 每个事实表
有哪些表:
用户, 商品, 订单, 活动, 优惠券, 购物车
有哪些指标:
留转G复活:
- 留存率
今天新增了100个用户, 1天后有80个用户活跃, 那么一日留存率就是80% - 转化率
商品详情页面 -> 下单页面 -> 支付页面 - GMV(销售总额)
- 复购率
- 日活
全部指标:
订单表 VS 订单详情表:
订单表的订单状态会发生变化, 而订单详情表是不变的,
订单表 指向 订单详情表
拉链表:
介绍:
拉链表记录每条信息的生命周期(开始日期, 结束日期), 一旦一条记录的生命周期结束, 就会新开启一个生命周期
如果当前信息至今仍在有效, 则结束日期记为9999-99-99
为什么要做拉链表?
以前是有多少数据就存多少行;
而做了拉链表后只要那个时间段内数据不变, 则只需要存一条数据, 更节省磁盘空间
什么样的表适合做成拉链表?
数据不经常发生变化的表
制作流程:
以上图的用户表为例:
结束日期为分区字段dt
前一天(首日): ‘2020-06-14’, 只有’9999-99-99’分区
当天(每日): ‘2020-06-15’, 有’9999-99-99’分区 和 '2020-06-14’分区
- 首日装载: 第一天的初始拉链表: 将每一条数据添加两个字段, 开始日期为当天, 结束日期为9999-99-99
- 每日装载: 以后的拉链表:
法一(full outer join):
1. select old的所有字段、new的所有字段 from old full outer join new as tmpold表来自于: where dt=‘9999-99-99’
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date
from dim_user_info
where dt='9999-99-99'
new表来自于: where dt=‘2020-06-15’, 且start_date记为’2020-06-15’, end_date记为’9999-99-99’
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,
'2020-06-15' start_date,
'9999-99-99' end_date
from ods_user_info
where dt='2020-06-15'
- 两部分进行union all
第一部分(公有部分的历史旧数据)：end_date改为 date_add(‘2020-06-15’,-1)
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('2020-06-15',-1) as string),
cast(date_add('2020-06-15',-1) as string) dt
from tmp
where new_id is not null and old_id is not null;第二部分：tmp(old表独有、公有部分的最新数据、new表独有)中所有字段优先使用new表中的数据
select
nvl(new_id,old_id),
nvl(new_login_name,old_login_name),
nvl(new_nick_name,old_nick_name),
nvl(new_name,old_name),
nvl(new_phone_num,old_phone_num),
nvl(new_email,old_email),
nvl(new_user_level,old_user_level),
nvl(new_birthday,old_birthday),
nvl(new_gender,old_gender),
nvl(new_create_time,old_create_time),
nvl(new_operate_time,old_operate_time),
nvl(new_start_date,old_start_date),
nvl(new_end_date,old_end_date),
nvl(new_end_date,old_end_date) dt
from tmp完整代码:
with
tmp as
(
select
old.id old_id,
old.login_name old_login_name,
old.nick_name old_nick_name,
old.name old_name,
old.phone_num old_phone_num,
old.email old_email,
old.user_level old_user_level,
old.birthday old_birthday,
old.gender old_gender,
old.create_time old_create_time,
old.operate_time old_operate_time,
old.start_date old_start_date,
old.end_date old_end_date,
new.id new_id,
new.login_name new_login_name,
new.nick_name new_nick_name,
new.name new_name,
new.phone_num new_phone_num,
new.email new_email,
new.user_level new_user_level,
new.birthday new_birthday,
new.gender new_gender,
new.create_time new_create_time,
new.operate_time new_operate_time,
new.start_date new_start_date,
new.end_date new_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
from dim_user_info
where dt='9999-99-99'
)old
full outer join
(
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,
'2020-06-15' start_date,
'9999-99-99' end_date
from ods_user_info
where dt='2020-06-15'
)new
on old.id=new.id
)法二(left join + union all):
1. 将原先未完成部分的拉链表记作t1 (where dt=‘9999-99-99’)
2. 获得订单表里当天产生(新增及变化)的数据, 记作临时表t2(from ods表 where dt='2020-16-15')
3. 用两个select 去union all:
**新增当天数据(放到9999-99-99分区) && 修改原分区的状态变化数据**
+ 第一个select是
```
select '2020-16-15' startDate '9999-99-99' endDate from t2
```
这部分数据涵盖 `当天的新增数据` 和 `变化后的新状态数据`
+ 第二个select是 用t1 去 left join t2
```
if(t2.id is not null and t1.endDate='9999-99-99', date_add('2020-06-15',-1), endDate) endDate
```
+ 如果是t1表独有的数据 就说明`此数据状态没发生变化`, **开始日期和结束日期不做修改**
+ 如果是t1表和t2表共有的数据, 且这部分数据在t1表里说明endDate为9999-99-99, 说明`此数据状态发生变化了`, **开始日期不做修改, 结束日期修改为(当天日期-1)**
用这两个select union all后的结果去**insert overwrite** table dim_user_info partition(dt)替换原始拉链表, 就得到了最新的拉链表
如何使用拉链表?
查询2020-06-14号及之前的历史数据:
select * from user where start_date<='2020-06-14' and end_date>='2020-06-14'四、ADS层指标:
最近3周内 连续3周都活跃的用户数
思路: 下面的分层 对用户每周的登录进行去重, 这样每个用户在每周的登录数据, 只会有1条或0条;
然后按uid分组, having count(*)==3即可
注意是T+1, 假设是周二才进行计算:
获取上周一的日期: date_add(next_day(‘2020-06-14’,‘MO’),-7*2)
获取上上周一的日期: date_add(next_day(‘2020-06-14’,‘MO’),-7*3)
获取上上上周一的日期: date_add(next_day(‘2020-06-14’,‘MO’),-7*4)
最近7天内 至少连续3天都活跃的用户数
数据表中 每个用户在每一天的登录数据, 只会有1条或0条
思路:
解法1: 等差数列(日期-rank排名即为 开始日期), 然后group by 开始日期
解法2: 开窗: order by日期后: lag==cur==lead, 再对select的结果集进行去重
五、数据治理:
数据质量:
数据的准确性和可信赖度
监控原则:
- 单表数据量监控
一张表的行数 在一个范围内 - 单表空值检测
某个字段为空的行数 在一个范围内 - 单表重复值检测
某个字段的重复值行数 在一个范围内 - 单表值范围检测
某个字段超出 合法数据范围 的行数 在一个范围内 - 跨表数据量对比
两张表的数据量相差行数 在一个范围内
权限管理:
Ranger 对大数据组件, 数据库, 表, 字段 进行鉴权拦截
元数据管理:
Atlas可以 生成 表与表 之间的血缘关系图(粒度能细化到字段级别)
用途: 便于梳理表与表之间的关系, 便于评估一张表执行失败后 能影响的范围
六、其他:
数据集市:
是一种微型的数据仓库, 是部门级别的, 小于企业级别
数据湖:
有Hudi, Iceberg, 可以处理任何类型的数据(结构化数据, 非结构化数据), 还具有数据挖掘能力
中台:
避免重复造轮子, 对于公共通用的 业务/技术/数据 进行封装
- 业务中台
- 技术中台
- 数据中台
- 算法中台
