Custouch Blog

Appearance

On this page

大数据之模型架构 ​

数据分层架构 ​

不能为了分层而分层，没有最好的，只有适合的 一个好的分层架构，要有以下好处：

  • 清晰数据结构；
  • 数据血缘追踪；
  • 减少重复开发；
  • 数据关系条理化；
  • 屏蔽原始数据的影响。

数仓分层要结合公司业务进行，并且需要清晰明确各层职责

001

建模是在数据源层的下一层进行建设

  • 数据源层 ODS 层，是最接近数据源中数据的一层，为了考虑后续可能需要追溯数据问题，因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据即可，至于数据的去噪、去重、异常值处理等过程可以放在后面的 DWD 层来做

  • 数据仓库层：DW（Data Warehouse）数据仓库层是我们在做数据仓库时要核心设计的一层，在这里，从 ODS 层中获得的数据按照主题建立各种数据模型。DW 层又细分为 DWD（Data Warehouse Detail）层、DWM（Data WareHouse Middle）层和 DWS（Data WareHouse Servce） 层。

  • 数据明细层：DWD（Data Warehouse Detail) 该层一般保持和 ODS 层一样的数据粒度，并且提供一定的数据质量保证。DWD 层要做的就是将数据清理、整合、规范化、脏数据、垃圾数据、规范不一致的、状态定义不一致的、命名不规范的数据都会被处理。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化至事实表中，减少事实表和维表的关联。另外，在该层也会做一部分的数据聚合，将相同主题的数据汇集到一张表中，提高数据的可用性

  • 数据中间层：DWM（Data WareHouse Middle) 该层会在 DWD 层的数据基础上，数据做轻度的聚合操作，生成一系列的中间表，提升公共指标的复用性，减少重复加工。直观来讲，就是对通用的核心维度进行聚合操作，算出相应的统计指标。在实际计算中，如果直接从 DWD 或者 ODS 计算出宽表的统计指标，会存在计算量太大并且维度太少的问题，因此一般的做法是，在 DWM 层先计算出多个小的中间表，然后再拼接成一张 DWS 的宽表。由于宽和窄的界限不易界定，也可以去掉 DWM 这一层，只留 DWS 层，将所有的数据再放在 DWS 亦可

  • 数据服务层：DWS（Data WareHouse Servce) DWS 层为公共汇总层，会进行轻度汇总，粒度比明细数据稍粗，基于 DWD 层上的基础数据，整合汇总成分析某一个主题域的服务数据，一般是宽表。DWS 层应覆盖 80% 的应用场景。又称数据集市或宽表。按照业务划分，如主题域流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP 分析，数据分发等。一般来讲，该层的数据表会相对比较少，一张表会涵盖比较多的业务内容，由于其字段较多，因此一般也会称该层的表为宽表

  • 数据应用层：APP（Application) 在这里，主要是提供给数据产品和数据分析使用的数据，一般会存放在 ES、PostgreSql、Redis 等系统中供线上系统使用，也可能会存在 Hive 或者 Druid中供数据分析和数据挖掘使用。比如我们经常说的报表数据，一般就放在这里。

  • 维表层（Dimension) 如果维表过多，也可针对维表设计单独一层，维表层主要包含两部分数据：高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万

模型建设方法 ​

范式建模法 ​

范式建模法其实是我们在构建数据模型常用的一个方法,主要解决关系型数据库的数据存储

范式 是符合某一种级别的关系模式的集合,构造数据库必须遵循一定的规则，而在关系型数据库中这种规则就是范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、Boyce-Codd范式（BCNF）、第四范式（4NF）和第五范式（5NF）

数仓的范式建模： 一般采用第三范式。一个符合第三范式的关系必须具有以下三个条件

  1. 每个属性值唯一，不具有多义性
  2. 每个非主属性必须完全依赖于整个主键，而非主键的一部分
  3. 每个非主属性不能依赖于其他关系中的属性，因为这样的话，这种属性应该归到其他关系中去

002

维度建模法 ​

  1. 维度建模以分析决策的需求出发构建模型,构建的数据模型为分析需求服务，因此它重点解决用户如何更快速完成分析需求，同时还有较好的大规模复杂查询的响应性能

003

  1. 维度建模中比较重要的概念就是 事实表（Fact table）和维度表（Dimensiontable）。其最简单的描述就是，按照事实表、维度表来构建数据仓库、数据集市。

维度建模详解 ​

  • 维度建模是专门应用于分析型数据库、数据仓库、数据集市建模的方法。数据集市可以理解为是一种"小型数据仓库"

维度建模中表的类型

  • 事实表：必然存在的一些数据，像采集的日志文件，订单表，都可以作为事实表
    • 特征：是一堆主键的集合，每个主键对应维度表中的一条记录，客观存在的，根据主题确定出需要使用的数据
  • 维度表：维度就是所分析的数据的一个量，维度表就是以合适的角度来创建的表，分析问题的一个角度：时间、地域、终端、用户等角度

事实表 ​

  1. 发生在现实世界中的操作型事件，其所产生的可度量数值，存储在事实表中。从最低的粒度级别来看，事实表行对应一个度量事件
  2. 事实表表示对分析主题的度量, 比如一次购买行为我们就可以理解为是一个事实 004

事实与维度

  1. 事实表的特征：表里没有存放实际的内容，他是一堆主键的集合,这些 ID 分别能对应到维度表中的一条记录。事实表包含了与各维度表相关联的外键，可与维度表关联
  2. 明细表宽表: 为了分析方便，可以事实表中的一个字段切割提取多个属性出来构成新的字段，因为字段变多了，所以称为宽表，原来的成为窄表 a) eg: 以 2021-03-18 06:31:42 时间为例 , 需要拆分为各种时间维度 如 年-月-日-时-分, 还可以获取属于第几季度 以便后续进行相应维度的统计汇总
  3. 事实表种类
    1. 事务事实表 : 表中的一行对应空间或时间上某点的度量事件。就是一行数据中必须有度量字段(指标) ,比如说销售金额，销售数量 , 事务事实表都包含一个与维度表关联的外键
    2. 周期快照事实表: 每行都带有时间值字段，代表周期，通常时间值都是标准周期, 粒度是周期, 一个周期快照事实表中数据可以是多个事实，但是它们都属于某单个周期内
    3. 累积快照事实表: 由多个周期数据组成, 每行汇总了过程开始到结束之间的度量 , 如订单数据，累计快照事实表的一行就是一个订单 , 当订单状态发生变化时 这行数据就会被修改
    4. 无事实的事实表: 以上讨论的事实表度量都是数字化的，实际应用中大多数都是数字化的度量，但是也会有少量的没有数字化的值但是还很有价值的字段，无事实的事实表就是为这种数据准备，利用这种事实表可以分析发生了什么
    5. 聚集事实表: 对原子粒度的数据进行简单的聚合操作，目的就是为了提高查询性能
    6. 合并事实表: 遵循相同粒度原则,. 只要它们属于相同粒度，就可以合并为一个事实表

维度表 ​

维度表都包含单一的主键列, 维度表的主键可以作为与之关联的任何事实表的外键 , 维度表通常比较宽，是扁平型非规范表，包含大量的低粒度的文本属性

常见的分析场景维度为: 时间维度(年月日,周,季度) 地域, 商家, 用户维度 ,

  • 退化维度: 退化维度就是将维度退回到事实表中。因为有时维度除了主键没有其他内容，虽然也是合法维度键，但是一般都会退回到事实表中，减少关联次数，提高查询性能
  • 多层次维度: 包含不止一个层次 , 日期维度可以从天的层次到周到月到年的层次
  • 日历日期维度 : 日期维度表中，主键的设置不要使用顺序生成的 id 来表示，可以使用更有意义的数据表示，比如将年月日合并起来表示

维度建模三种模式 ​

星型模型 ​

  • 星型模式是以事实表为中心，所有的维度表直接连接在事实表上，像星星一样
  • 数据有一定的冗余
  • 星形模式的维度建模由一个事实表和一组维表组成, 如下图 005

雪花模式 ​

  • 雪花模式的维度表可以拥有其他维度表的
  • 维护成本比较高, 性能方面需要关联多层维表，性能也比星型模型要低 006

星座模式 ​

  • 星座模式是星型模式延伸而来 , 星型模式是基于一张事实表的，而星座模式是基于多张事实表的，而且共享维度信息
  • 前面的两种维度建模方法都是多维表对应单事实表 , 但很多时候维度空间内的事实表不止一个，而一个维表也可能被多个事实表用到 所以会使用到此模型 007

扩展之拉链表: ​

  • 一般应用于 缓慢变化维 场景下
  • 用于存储变化，但变化的频率较慢的数据。这样的数据用全量存储，会存储大量重复数据，因此用拉链表 制造拉链表
sql
-- 建表sql
create table ods_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,hour string ,min string )
stored as parquet
table properties("parquet.compression"="lzo")
;
  • 分区

008

  • 首日装载

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

sql
insert overwrite table dim.dim_user_info partition(dt='9999-99-99')
select
id,
user_name,
name,
phone_num,
gerder,
email,
create_time,
operate_time,
'2022-19-01' start_date,
'9999-99-99' end_date
from 
ods.ods_user_info
where dt='2022-10-01'
  • 每日装载

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

  • 如果new为null（没有变化），则取old
  • 如果new不为null（今日发生了新增及变化），则取new
sql
select
if(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,
nvl(new.start_date,old.start_date) start_date,
if(new.end_date is not null,new.end_date,old.end_date) end_date
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-19-01' start_date,
    '9999-99-99' end_date
    from 
    ods.ods_user_info
    where dt='9999-99-99'
)ods
full join
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-10-01' start_date,
    '9999-99-99' end_date --新增及变化的数据都是最新数据
    from 
    ods.ods_user_info --ods_user_info表是每日增量导入的
    where dt='2022-10-01' --新增及变化的数据
    )new
on ods.id=new.id

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

  • new和old都有的数据取old的
sql
select
old.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
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-19-01' start_date,
    '9999-99-99' end_date
    from 
    dim.dim_user_info
    where dt='9999-99-99'
)ods
full join
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-10-01' start_date,
    '9999-99-99' end_date --新增及变化的数据都是最新数据
    from 
    ods.ods_user_info --ods_user_info表是每日增量导入的
    where dt='2022-10-01' --新增及变化的数据
    )new
on ods.id=new.id
where new.id is not null and old.id is not null
;

对拉链表进行查询 获取在某天有效的的所有用户的数据

获取2019-01-01有效的所有历史数据

sql
select * from user_info where start_date<='2019-01-01' and end_date>='2019-01-01';

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

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

Date: 2023/1/13

Authors: 朱明烨

Tags: BigData