数仓（十八）数仓建模以及分层总结(ODS、DIM、DWD、DWS、DWT、ADS层) - 墨天轮

一、维度模型分类:星型模型,雪花模型,星座模型

1、星型模型

星型模型中只有一张事实表，以及0张或多张维度表，事实与纬度表通过主键外键相关联，维度之间不存在关联关系，当所有纬度都关联到事实表时，整个图形非常像一种星型的结构，所以称之为“星型模型”。

注：事实表中只存外键和度量值。

2、雪花模型

当一个或多个纬度表没有直接连接到事实表，而是通过其他维度表连接到事实表时，其图解就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展，它对星型模型的维度进一步层次化。

优点是避免了数据冗余。

缺点是增加了join，导致效率低。

3、星座模型

星座模型也是星型模型的扩展，区别是星座模型中存在多张事实表，不同的事实表之间共享维度表信息。日常开发用的就是星座模型。

二、范式

范式：在进行关系建模时，需要遵循的规则。

范式的作用：降低数据的冗余性，减少存储空间，保持数据一致性。

1、函数依赖：

完全函数依赖，部分函数依赖，传递函数依赖。

1）完全函数依赖

z=f(x,y)有了x,y才能计算出z，所以z完全函数依赖于x,y。比如通过(学号,课程)推出分数，但是单纯用学号推断不出来分数，那么就可以说分数全完依赖于(学号,课程)。

2）部分函数依赖

z=f(x,y)当给定x,y则能计算出z,当给x,y,n时，也能计算出z，此时z部分函数依赖于z,y,n。比如通过（学号，课程）推出姓名，因为可以直接通过学号退出姓名，所以：姓名部分依赖于（学号，课程）。

3）传递函数依赖

y=f(x)，z=g(y)，依赖x可以得到y，从而得到z，z传递依赖于x。比如：学号推出系名，系名退出系主任，系主任传递依赖于学号。

2、第一范式

字段不可分割。

商品字段中"5台电脑"可以切割成"5台"+"电脑"，改为

3、第二范式

满足第一范式，且不能存在非主键字段部分函数依赖于主键字段。

主键为:"学号"+"课名"。"分数”完全依赖于（学号，课名），但是姓名并不完全依赖于（学号，课名），姓名只依赖于学号。

4、第三范式

满足第一二范式，且不能存在非主键字段传递函数依赖于主键字段。

主键：学号。学号->系名->系主任

上面表需要再次拆解：

三、数仓分层

1、ODS层：原始数据层

ODS(O=original D=data S=store)

1)设计要点

存储来自多个业务系统、前端埋点、爬虫获取的一系列数据源的数据。

我们要做三件事：

【1】保持数据原貌不做任何修改，保留历史数据，起到数据备份的作用。

【2】使用lzo压缩。100G的数据压缩之后大概为20G。

【3】创建分区表，防止后续的全表扫描，一般按天存储。

2）ODS层数据组成

【1】前端埋点日志：由kafka或者sqoop采集到HDFS上

【2】由前端业务数据库采集到HDFS上

3）前端埋点日志的处理

前端埋点日志以JSON格式形式存在

建表语句

create external table ods_log(line string)partitioned by (dt string)Stored as inputformat 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

将kafka落盘的数据建立lzo索引，否则无法分片

hadoop jar /opt/module/hadoop-3.1.4/share/hadoop/common/hadoop-lzo-0.4.20.jar \pression.lzo.DistributedLzoIndexer \-Dmapreduce.job.queuename=hive \/warehouse/gmail/ods/ods_log/dt=-05-01

加载建立好所以的数据

load data inpath '/origin_data/gmall/log/topic_log/-05-01'into table ods_log partition (dt='-05-01')；

4）MySQL数据库的处理

mysql数据库的表通过sqoop采集到HDFS，用的是\t作为分割，那数仓里面ODS层也需要\t作为分割；

5）同步策略

【1】增量同步：订单表

【2】全量同步：商品表

【3】特殊：一次性拉取，不建分区表（DIM层的父数据）

2、DIM

dim=dimension

存储为Parquet格式

1）同步策略

全量同步：商品维度，优惠券维度。首日和每日都是全量先导入到ODS再导入到DIM层

特殊：日期，地区（自己处理）

拉链表：用户维度表（见下面）

3、DWD层

dwd=data warehouse detail

1）说明

dwd层是对事实表的处理，代表的是业务的最小粒度层，任何数据的记录都可以从这一层获取，为后续的dws和dwt层做准备。

dwd层是站在选择好事实表的基础上，对维度建模的视角。

2）对埋点数据进行处理

将ods.ods_log表唯一的字段line这个json串进行解析，并将其中的内容拆分为两个表：

启动日志、事件日志

3）对业务数据进行处理

【1】周期快照事实表

每天导入全量数据。

如：购物车这个表，咱们只在乎购物车里有多少东西，不关注加减购物车内商品的操作

【2】累计快照事实表(没看完)

103-尚硅谷-数仓搭建-DWD层优惠券领用事实表_哔哩哔哩_bilibili

如：优惠券领用，这种发生周期变化的场景

领取->用券下单->用券支付->过期

create table dwd_coupun_use(coupun_id string, --优惠券iduser_id string, --用户idcoupun_status string, --优惠券状态using_time string, --使用时间(下单)used_time string, --使用时间(支付)expire_time string --过期时间)partitioned by(dt string);

4、dim和dwd

DIM和DWD采用维度建模，一般采用星型模型，呈现状态一般为星座模型。详情见维度建模的步骤。

5、dws、dwt、ads

dws= data warehouse service

dwt=data warehosue topic

ads=application data store

dws、dwt、ads都是以需求为驱动的，和纬度建模已经没有关系了。

dws、dwt：统称为宽表层，这两层的设计思想大致相同，通过以下案例进行说明：

1）问题引出

两个需求，统计每个省份订单的个数、统计内个省份订单的总金额

2）处理办法

都是将省份和订单表进行join,group by省份，然后计算，同样的数据被计算了两次，实际上类似的场景还有很多，那怎么设计才能避免重复计算呢？针对上述场景可以设计一张地区表，其主键为地区ID，字段为：下单次数，下单金额，支付次数，支付金额等，上述所有指标统一进行计算，并将结果保存在该宽表中，这样就能有效避免数据的重复计算。

3）需要那些宽表（主题表）：以维度为基准

4）DWS和DWT层的区别：

DWS层存放所有当天的汇总行为，例如每个地区当天下单次数，下单金额等，dwt层存放的是所有主题对象的累计行为，例如每个地区最近7天（15天、30天、60天）的下单次数，下单金额等。

5）ads层：存储各个报表需要的结果。

四、DIM层用户维度拉链表

1、什么是拉链表

用于存储变化，但变化的频率较慢的数据。这样的数据用全量存存储大量重复数据，因此用拉链表。

2、每条数据的意义

该条数据的有效时间

3、制造拉链表

1）建表语句

create table dim_user_info(id string,user_name string --用户名称,name string --真实姓名,phone_num string,gerder string --性别,email string,create_time string --创建时间,operate_time string --操作时间,start_date string --开始日期(拉链表特有),end_date string --结束日期(拉链表特有))partitioned by(dt string)stored as parquettable properties("pression"="lzo");

2） 分区规划：

3）首日装载

要进行初始化，ods层该表第一天从MySQL拉取的所有数据放到9999-99-99分区

insert overwrite table dim.dim_user_info partition(dt='9999-99-99')selectid,user_name,name,phone_num,gerder,email,create_time,operate_time,'-19-01' start_date,'9999-99-99' end_datefrom ods.ods_user_infowhere dt='-10-01'

4）每日装载

【1】将最新的数据装载到9999-99-99分区

如果new为null（没有变化），则取old,

如果new不为null（今日发生了新增及变化），则取new

selectif(new.id is not null,new.id,old.id) id,if(new.user_name is not null,new.user_name,old.user_name) user_name,if(new.name is not null,new.name,old.name) name,if(new.phone_num is not null,new.phone_num,old.phone_num) num,if(new.gerder is not null,new.gerder,old.gerder) gerder,if(new.email is not null,new.email,old.email) nemail,if(new.create_time is not null,new.create_time,old.create_time) create_time ,if(new.operate_time is not null,new.operate_time,old.operate_time) operate_time,if(new.start_date is not null,new.start_date,old.start_date) start_date,if(new.end_date is not null,new.end_date,old.end_date) end_date(selectid,user_name,name,phone_num,gerder,email,create_time,operate_time,'-19-01' start_date,'9999-99-99' end_datefrom dim.dim_user_infowhere dt='9999-99-99')odsfull join(selectid,user_name,name,phone_num,gerder,email,create_time,operate_time,'-10-01' start_date,'9999-99-99' end_date --新增及变化的数据都是最新数据from ods.ods_user_info --ods_user_info表是每日增量导入的where dt='-10-01' --新增及变化的数据)newon ods.id=new.id

【2】将过期数据装载到前一天的分区(注意日期之间没有重合)

new和old都有的数据取old的

selectold.id id,old.user_name user_name,old.name name,old.phone_num num,old.gerder gerder,old.email nemail,old.create_time create_time ,old.operate_time operate_time,old.start_date start_date,old.end_date end_date(selectid,user_name,name,phone_num,gerder,email,create_time,operate_time,'-19-01' start_date,'9999-99-99' end_datefrom dim.dim_user_infowhere dt='9999-99-99')odsfull join(selectid,user_name,name,phone_num,gerder,email,create_time,operate_time,'-10-01' start_date,'9999-99-99' end_date --新增及变化的数据都是最新数据from ods.ods_user_info --ods_user_info表是每日增量导入的where dt='-10-01' --新增及变化的数据)newon ods.id=new.idwhere new.id is not null and old.id is not null;

4.对拉链表进行查询

1）获取在某天有效的的所有用户的数据

--获取-01-01有效的所有历史数据select * from user_info where start_date<='-01-01' and end_date>='-01-01';

2）获取目前所有用户的最新的数据

select * from user_info where end_date>='9999-99-99';

五、纬度建模

纬度建模步骤：选择业务过程声明粒度、确认纬度、确认事实

1、选择业务过程

整个业务流程中选取我们需要建模的业务，根据公司业务提供的需求及日后的易扩展性等进行选择业务。

这里我们选择了几个业务过程是：支付、订单、加购物车、优惠券领用、收藏、评论、退款等。

2、声明粒度

总体采用最小粒度规则，不做任何聚合操作。

声明力度意味着定义事实表中的一行数据表示什么，应尽可能选择最小粒度，以此来应对各种各样的需求。

3、确认纬度

确定维度对象，维度的主要作用是描述业务事实，主要表示的是：“谁、何处、何时”等信息。

确定维度的原则是：后续需求中是否要分析相关维度的指标。例如：需要统计哪个用户下的订单多，哪个地区下的订单多，什么地区下单多，需要确定的维度就包括：用户维度，地区维度，时间维度。

4、确认事实

事实表只有纬度id和度量值。