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

hellosrc2023

已于 2022-09-12 12:49:21 修改

阅读量8.8k

点赞数 18

分类专栏：大数据文章标签：大数据

于 2021-12-09 23:26:46 首次发布

本文链接：https://blog.csdn.net/weixin_46141936/article/details/121846412

版权

大数据专栏收录该内容

39 篇文章 11 订阅

订阅专栏

    声明: 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(数据接入层, 原始数据)
1. 备份初始数据, 保持数据原貌不做任何修改
2. 创建分区表, 防止后续的全表扫描
3. 数据采用压缩, 减少占用的磁盘空间
dwd(明细层, 处理初始数据, 如ETL等)
1. 处理数据:
  1. 过滤掉脏数据(空值, 非法数据)
  2. 对敏感数据进行脱敏, 比如将手机号中的几位变成*
  3. 对一些数据进行加密
2. 维度退化
  
  用到维度表时要进行join, 而join操作非常耗时; 所以对于那些分的过细的维度进行退化删除, 减少后续的join
  
  如: (商品表、spu表、品类表、一级分类、二级分类、三级分类) --> 商品表
  
  (省份表、地区表) --> 地区表
  
  维度退化：将维度表和事实表合成一张表
  
  实时数仓里维度退化指的是: 主流要和A, B, C三条流进行维度关联, 干脆让A先关联B, 再去关联C, 最后主流只和A进行关联
3. 解析用户行为数据, 将其分到不同的dwd层的表
4. 列式存储
5. 压缩
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):

select old的所有字段、new的所有字段 from old full outer join new as tmp

old表来自于: 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, 可以处理任何类型的数据(结构化数据, 非结构化数据), 还具有数据挖掘能力

中台:

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

业务中台
技术中台
数据中台
算法中台

hellosrc2023

关注

18
点赞
踩
136

收藏

觉得还不错? 一键收藏
3
评论
离线数仓-基础知识(个人总结)

    声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章              2. 由于是个人总结, 所以用最精简的话语来写文章           
复制链接

扫一扫