离线数仓-基础知识(个人总结)

    声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章
              2. 由于是个人总结, 所以用最精简的话语来写文章
              3. 若有错误不当之处, 请指出

一、概述: 介绍:

数据仓库里存放着海量的数据, 并拥有分析计算程序, 计算输出的结果供企业制定决策使用

输入数据来源:

用户行为数据(前端埋点), 业务数据(MySQL数据库), 爬虫数据

用户行为数据: 用户的动作产生的数据, 比如浏览, 停留, 点击, 点赞, 评论, 收藏

业务数据: 业务流程中产生的数据, 比如登录, 订单, 支付

输出系统:

报表系统, 用户画像系统, 推荐系统, 供OLAP引擎提供数据进行分析

项目架构:

同比:
与历史同时期比较, 例如 本周三和上周三做比较

环比:
与上一个统计周期比较, 例如 本周三和本周二做比较

T+1模式:

今天凌晨开始去计算昨天的数据

SKU & SPU:

SKU: 一台黑色, 128G内存的iPhoneX手机

SPU: 一台iPhoneX手机

埋点日志:

页面, 曝光, 事件(动作, action), 启动, 错误

二、数仓分层: 分层的好处:

1. 高层的计算 可以复用 低层计算的中间结果
   当没有分层的时候:

   ​ 比如要统计A, B, C指标, 那么A需要对初始表进行过滤空值 然后再进一步计算, B和C也要重复地去对初始表过滤空值
   而分层后:

   ​ 可以把过滤空值的计算交给dwd层去做一次, 然后ABC都从dwd层里去取过滤好的数据, 复用了dwd层计算的中间结果

2. 业务解耦

3. 层次分明, 便于开发和维护

分为哪些层:

1. ods(数据接入层, 原始数据)

   1. 备份初始数据, 保持数据原貌不做任何修改
   2. 创建分区表, 防止后续的全表扫描
   3. 数据采用压缩, 减少占用的磁盘空间

2. dwd(明细层, 处理初始数据, 如ETL等)

   1. 处理数据:

      1. 过滤掉脏数据(空值, 非法数据)
      2. 对敏感数据进行脱敏, 比如将手机号中的几位变成*
      3. 对一些数据进行加密

   2. 维度退化

      用到维度表时要进行join, 而join操作非常耗时; 所以对于那些分的过细的维度进行退化删除, 减少后续的join

      如: (商品表、spu表、品类表、一级分类、二级分类、三级分类) --> 商品表

      ​ (省份表、地区表) --> 地区表

      维度退化：将维度表和事实表 合成一张表

      实时数仓里维度退化指的是: 主流要和A, B, C三条流进行维度关联, 干脆让A先关联B, 再去关联C, 最后主流只和A进行关联

   3. 解析用户行为数据, 将其分到不同的dwd层的表

   4. 列式存储

   5. 压缩

3. dim(维度表)

   建立维度表, 如 地区维度, 用户维度, 商品维度

4. dws(汇总层, 聚合)

   当天数据的汇总

5. dwt(汇总层, 聚合)

   字段:

   ​ 最近一段时间数据的汇总

   ​ 迄今为止的数据汇总

   ​ 首次时间

   ​ 末次时间

6. ads(应用层, 面向需求指标(主题))

   根据各种需求指标, 对dws/dwt层数据做进一步分析

三、数仓建模: 维度字段:

group by 的字段 是看待问题的角度: 如省份、时间、年龄段、职业

度量值:

可以用来聚合的值, 如下单金额、下单次数

维度表:

是对事实的描述信息, 是宽表(有很多列), 数据不经常变化

组成: 维度字段+维度信息(可能还有其他表的维度字段)

事实表:

每行数据代表一个事件(下单, 点赞), 是高表(有很多行), 数据经常变化

组成: 维度字段+度量值

表的分类:

1. 周期型快照事实表(同步策略: 全量更新)

   不会保留所有明细数据, 只会保留固定时间间隔内的聚合数据; 这样的话数据量较小, 故可以采用 全量更新

   例如 每天或者每月的销售额 或 每月的账户余额, 不关心数据的变化过程, 只关心最终聚合值;

   首日同步脚本:

   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';
   

2. 事务型事实表(同步策略: 增量更新)

   数据不允许修改, 故可以采用 增量更新

   首日同步脚本:

   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';
   

3. 累积型快照事实表(同步策略: 新增当天数据(放到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 维度字段

同步策略:

维度表(用户维度表除外)一般数据量较小

1. 全量更新

   适合于 数据量小 的表: 一般是维度表(用户维度表除外, 因为用户太多了)

2. 增量更新

   适合于 数据量大 & 不变化 的表

3. 新增及变化

   适合于 数据量大 & 变化 的表: 一般是订单表, 用户表

4. 只首次同步:

   适合常年数据不变化的表: 如地区表

关系建模:

严格遵循三范式,

优点: 建表规范有条理, 没有冗余字段

缺点: 表分的太细 join起来太麻烦, 数据分析不擅长大数据表join

维度建模:

面向业务, 将业务用事实表和维度表呈现出来

模型分类:

1. 星型模型: 维度表关联只有一层
2. 星座模型: 在星型模型基础上, 多张是事实表可以共享一张维度表
3. 雪花模型: 维度表关联可以有多层(join起来麻烦, A的维度是B, B的维度是C)

一般采用星座模型, dwd层进行维度退化, 不会有维度表关联多层的情况

建模的流程:

1. 选择业务过程

   要选取MySQL里的哪些表进行导入

2. 声明粒度

   一行数据代表什么含义; 比如是一次的下单金额, 还是一天的下单金额, 还是一周的下单金额

3. 确定维度字段

4. 确定度量值

建模工具: EZDML, 展示表与表之间的依赖关系

业务总线矩阵表:

横轴: 维度字段+度量值

纵轴: 每个事实表

有哪些表:

用户, 商品, 订单, 活动, 优惠券, 购物车

有哪些指标:

留转G复活:

1. 留存率

   今天新增了100个用户, 1天后有80个用户活跃, 那么一日留存率就是80%

2. 转化率

   商品详情页面 -> 下单页面 -> 支付页面

3. GMV(销售总额)

4. 复购率

5. 日活

全部指标:

订单表 VS 订单详情表:

订单表的订单状态会发生变化, 而订单详情表是不变的,

订单表 指向 订单详情表

拉链表:

介绍:

拉链表记录每条信息的生命周期(开始日期, 结束日期), 一旦一条记录的生命周期结束, 就会新开启一个生命周期

如果当前信息至今仍在有效, 则结束日期记为9999-99-99

为什么要做拉链表?

以前是有多少数据就存多少行;

而做了拉链表后只要那个时间段内数据不变, 则只需要存一条数据, 更节省磁盘空间

什么样的表适合做成拉链表?

数据不经常发生变化的表

制作流程:

以上图的用户表为例:

结束日期为分区字段dt

前一天(首日): ‘2020-06-14’, 只有’9999-99-99’分区

当天(每日): ‘2020-06-15’, 有’9999-99-99’分区 和 '2020-06-14’分区

1. 首日装载: 第一天的初始拉链表: 将每一条数据添加两个字段, 开始日期为当天, 结束日期为9999-99-99

2. 每日装载: 以后的拉链表:

   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的结果集进行去重

五、数据治理: 数据质量:

数据的准确性和可信赖度

监控原则:

1. 单表数据量监控

   一张表的行数 在一个范围内

2. 单表空值检测

   某个字段为空的行数 在一个范围内

3. 单表重复值检测

   某个字段的重复值行数 在一个范围内

4. 单表值范围检测

   某个字段超出 合法数据范围 的行数 在一个范围内

5. 跨表数据量对比

   两张表的数据量相差行数 在一个范围内

权限管理:

Ranger 对大数据组件, 数据库, 表, 字段 进行鉴权拦截

元数据管理:

Atlas可以 生成 表与表 之间的血缘关系图(粒度能细化到字段级别)

用途: 便于梳理表与表之间的关系, 便于评估一张表执行失败后 能影响的范围

六、其他: 数据集市:

是一种微型的数据仓库, 是部门级别的, 小于企业级别

数据湖:

有Hudi, Iceberg, 可以处理任何类型的数据(结构化数据, 非结构化数据), 还具有数据挖掘能力

中台:

避免重复造轮子, 对于公共通用的 业务/技术/数据 进行封装

1. 业务中台

2. 技术中台

3. 数据中台

4. 算法中台