数据仓库【分层设计】

大数据松松

已于 2024-04-10 06:44:18 修改

阅读量2.7k

点赞数 66

文章标签：数据仓库大数据

于 2024-03-01 10:00:00 首次发布

本文链接：https://blog.csdn.net/xiayuhaisong/article/details/136338452

版权

一、为什么要进行数仓分层

清晰的数据结构：每一个分层都有它的作用域与职责，这样能够在我们使用的时候更方便的理解和定位；
统一输出口径：数仓分层加工数据共享层、指标层，统一了数据的输出口径；
数据血缘追踪：方便我们更准确快速的定位业务数据问题，并清除它对应的影响范围，及时反馈给业务，减少损失；
数据复用，减少重复开发：规范的数仓分层，开发一些通用的数仓中间层数据，能够极大的减少重复计算，实现结果的复用，减少烟囱式的开发模式，极大的降低存储和计算成本；
把复杂的问题简单化：将一个复杂的业务问题进行分步骤拆解，每一层只处理单一的步骤，比较简单也容易理解。如果数据出现问题，我们只需要从有问题的步骤开始维护，便于后期数据维护；
空间换取时间：规范的数仓分层，将数据进行了充分的数据处理，能够满足不同粒度的数据查询需求，节省业务查询数据的流程和时间；
屏蔽原始数据的影响，屏蔽业务的影响：业务系统发生变化时，不必每次都重新接入数据，通过DW层规范和屏蔽所有的业务复杂性，保证下游数据用户使用数据的便捷和规范，分层设计使得某一层的问题只在该层就得到解决，无需更改下一层的代码和逻辑，提高了数据的稳定性和连续性。

数据仓库，通过数仓建模更好的组织管理和存储数据，以便在性能、成本、效率和质量之间取得最好的平衡。

二、数据仓库（ETL）的四个操作

ETL（extraction Transformation Loading）负责将分散的、异构数据源中的数据抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或者数据集市中。ETL是实施数据仓库的核心和灵魂。

数据抽取（Extraction）：包括初始化数据装在和数据刷新。初始化数据装在主要是如何建立维表、事实表，并把响应的数据放到这些表中；以及如何做到从业务数据库到数仓的定期新增或者更新数据。
数据清洗：主要是针对源数据库中出现的二义性、重复的、不完整、违反业务或者逻辑规则等问题的数据进行统一的处理。清洗掉不符合业务或者没用的数据。
数据转换（Transformation）：主要是为了将清洗后的数据转换成数据仓库所需要的数据。来源于不同源系统的同一个字段的数据字典或者数据格式可能不一样，在数据仓库中需要给他们提供统一的数据字典和格式，对数据内容进行归一化。
数据加载（Loading）：是将最后上面处理完成的数据导入到对应的存储空间里，以便给数据集市提供，进而可视化。

三、数据仓库的架构体系

系统架构：基于Hadoop、Spark、Flink等组件为中心的架构体系
数据架构：顶层设计，主题域划分，分层设计，ODS-DWD-DWS-ADS
数据建模：维度建模，选择业务过程-声明粒度-确定维度-确定事实
数据管理：数据资产管理，元数据管理、数据质量管理、主数据管理、数据标准制定、数据安全管理
辅助系统：调度系统、ETL系统、监控系统
数据服务：数据门户、机器学习数据挖掘、数据查询、分析、报表系统、可视化系统

四、数仓分层

ODS层：Operation Data Store，元数据据层，存放原始数据，直接加载原始日志、数据，数据保持原貌不做处理；
DWD层：Datawarehouse Detail，基于维度建模、维度退化，复用关联计算，减少数据shuffle。对ODS层数据进行清洗（去除空值、脏数据、超过极限范围的数据）、脱敏等，保存业务事实明细，一行信息代表一次业务行为，例如一次下单。
DWS层：datawarehouse service，以DWD为基础，按天进行轻度汇总。构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表。一行信息代表一个主题对象一天的汇总行为，例如一个用户一天下单的次数。
DM层：以DWS层为基础，对数据进行累计汇总。一行信息代表一个主题对象的累计行为，例如用户从注册那天开始一共下来多少次单。
ADS层：Application data Store，以DWS为基础，按主题进行汇总。为各种统计报表提供数据、宽表集市、趋势指标。
DIM层：维度层，保存维度数据，主要对业务事实的描述信息，例如何时、何地、何人、渠道等。

五、数仓模型设计的基本原则（衡量数仓好坏）

高内聚低耦合：
1. 将业务相近或者相关的数据、粒度相同的数据设计为一个逻辑或者物理模型；
2. 将高概率同时访问的数据放一起，将低概率同时访问的数据分开存储；
核心模型与扩展模型分离：建立核心模型与扩展模型，核心模型包括的字段支持常用的核心的业务，扩展模型包括的字段支持个性化或是少量应用的需要，不能让扩展字段过度侵入核心模型，破坏了核心模型的架构间接性和可维护性。
公共处理逻辑下沉：越是底层共用的处理逻辑更应该在数据调度依赖的底层进行封装与实现，不要让公共的处理逻辑暴露给应用层实现，不要让公共逻辑在多出同时存在。
成本与性能平衡：适当的数据冗余换取查询和刷新的性能，不宜过度冗余与数据复制。
数据可回滚：处理逻辑不变，在不同多次运行数据结果确定不变。
指标一致性：相同的字段含义在不同的表中字段命名必须相同，必须使用规范定义中的名称。
命名清洗可理解：表命名需清晰、一致，表名需易于消费者理解和使用。
层次依赖合理：
1. dwd层应该严格遵守层次依赖，理论上只可引用ODS、DIM和部分的DWD数据，不可引用处于下游层次的ADS等数据，以避免出现“反向引用”的情况；
2. dws应该严格遵守层次依赖，理论上只可引用DIM、DWD的数据，不可引用处于下游层次的ADS等数据，以避免出现“反向引用”的情况。
ODS层查询率：ODS层查询次数越多、占比越高，说明数据仓模型设计的越不完善、不好用
DWD层数据的复用率：引用系数越高，说明数仓的复用性越好。模型引用系数：一个模型被读取，直接产出下游模型的平均数量。

六、每层如何设计

6.1 ODS设计

ODS层的处理比较简单，基本上是将业务系统数据原封不动的同步即可，一般采用增量、或者全量的方式。

数据引入层：将原始数据几乎无处理的存放在数据仓库系统中，结构上与源系统基本保持一致，是数据仓库的数据准备区。这一层的主要职责是将基础数据同步、存储。一般来说ODS层的数据和源系统的数据是同构的，主要目的是简化后续数据加工处理的工作。从数据粒度上来说ODS层的数据粒度是细的。ODS层的表通常包括两类，一个用于存储当前需要加载的数据，一个用于存储处理完后的历史数据。历史数据一般保存3-6个月后需要清楚，以节省空间。但是不同的项目要区别对待，如果源系统的数据量不大，可以保留更长的时间，甚至全量保存。

数据实时、离线

离线方面：每日定时任务型，跑批任务，业务库，比如我们典型的日计算任务，这里经常会使用Sqoop、DataX来抽取，比如我们每天定时抽取一次。每天凌晨计算T+1天的数据，早上起来看报表。这种任务经常使用Hive、Spark来计算，最终结果写入Hive、Hbase、Mysql、ES或者redis中。
实时数据：日志埋点数据后者业务数据库，这一部分主要是各种实时的系统使用，比如我们的实时推荐、实时用户画像，一般我们会用Spark Streaming、Flink来计算，最后会落入ES、Hbase或者Redis中。数据源是业务数据库，可以考虑用Canal监听Mysql的Binlog，实时接入即可，然后是收集到消息队列中，最终再由Camus拉取到HDFS。

数据主要来源：

数据源是业务数据库，公司所有系统产生的数据
是通过在客户端埋点上报，收集用户的行为日志，以及一些后端日志的日志类型数据源。对于埋点行为日志来说，一般会经过一个这样的流程，首先数据会上报到Nginx然后经过Flume收集，然后存储到Kafka这样的消息队列，然后再由实时或者离线的一些拉取任务，拉取到我们的离线数据仓库HDFS。
外部数据：包括合作数据以及爬虫获得的数据，将所采集的数据汇总到一起。

数据存储策略（增量、全量）

增量存储：为了满足历史数据分析需求，可以在ODS层表中添加时间维度作为分区字段，以天为单位的增量存储，以业务日期作为分区，每个分区存放日增量的业务数据。交易、日志等事务性较强的ODS表适合增量存储方式，这类表数据量较大，采用全量存储的方式存储成本压力大，此外，这里表的下游应用对于历史全量数据访问的需求较小。
全量存储：以天为单位的全量存储，以业务日期作为分区，每个分区存放截止到当前业务日期位置的全量业务数据。对于小数据量的缓慢变化维度数据，例如商品类目，可直接使用全量存储。
拉链存储：拉链存储通过新增两个时间戳字段（start_dt和end_dt），将所有以天为粒度的变更数据都记录下来，通过分区字段也是这两个时间戳字段

6.2公共层设计（DWD\DWS）

公共层主要通过抽象、复用、沉淀物理或逻辑的模型，以提供整体架构的数据效率并确保口径一致性。整个公共层设计要点在于需求上识别公共性逻辑，设计上抽象复用模型，现实上平衡易用性和稳定性。一般要遵循以下几点原则：

是否具有共性：是公共层要考虑的核心问题
1. DWD的原则：一般情况下，DWD的模型相对好设计一些，核心是基于维度建模，冗余维度属性，降低频率关联，提升基础数据模型的易用性；
2. DWS的原则：DWS的核心诉求是通过空间换取时间，在解决成本、提升效率的同时，实现数据口径的一致性。既然是这样，那就不能为了加工DWS而加工DWS数据，要基于是否是业务核心指标判断是否要沉淀公共层，另外，如果是事后沉淀公共层，那要看下需要沉淀的指标的应用场景有多少，假如只有一个地方使用，那就没有必要沉淀DWS的必要了。
复用性、易用性、稳定性：公共层模型不是为了某一应用场景单独设计的，而是面向大部分的应用场景进行设计，因此需要进行一定的抽象以提升通用性，从而尽可能覆盖更多的应用场景；
1. 复用性：指标复用性抽象，转变不可累加的指标为可累加的指标，如比例、比率建议保留分子分母；粒度复用性抽象，以最大公约数的逻辑抽象复用，比如上游表是子公司的粒度，表二是一级类目粒度，那就设计出sku粒度的dws表。
2. 易用性：在不影响模型产出时效性的情况下，需尽量考虑模型易用性，提升应用研发的使用效率。易用性的设计主要指的是宽表的设计和水平切分，用于降低下游理解和多表关联。DWS模型易用性上，通过冗余维度属性、采用大宽表的方式构建，以提升下游易用性；DWS冗余相对不易变的维度属性，减少下游频繁关联；DWD模型易用性上，采用星型模型、维度冗余和信息完善度进行设计，以提升下游易用性，模型设计应以星型模型为主。
3. 稳定性：通过打款表的建设方式，公共层极大提升了模型的易用性，但因应用场景差异化，时效性也对应有不同的要求。公共层需进行必要的稳定性设计，满足下游重要应用高时效性产出的要求
成本和效率要有一个权衡
1. 一般情况下，对于数据量比较小的场景，可以优先构建DWD，后构建DWS，在构建DWS的过程中，可以优先构建细粒度的DWS表（为了扩展性），最后沉淀粗粒度的DWS表
2. 对于数据体量比较大的情况，可以优先构建粗粒度的DWS，对于DWD的构建，可以采用水平拆解的方式，比如不在冗余半结构化的字段，从而提升产出的时效，日升下游的使用效率。
迭代升级
1. 数据域的划分是建设公共层的基础，但是数据域不是一成不变的，由于业务不同，对应的数据域划分也自然各不相同，有时候需要灵活处理，并且要根据业务的发展而调整相关数据域的划分；
2. 数据域目的是为了给数据分类，所以尽量可以更好的以业务分析视角去组织公共数据，从而更好的保持数据的独立性，切不可大一统的简单划分粗略的一个领域。

数据仓库层

数据仓库层（DW）：数据仓库层是我们在做数据仓库时要核心设计的一层，本层将从ODS层中获得的数据按照主题建立各种数据模型，每一个主题对应一个宏观的分析领域，数据仓库层排除对决策无用的数据，提供特定主题的简明视图。DW层会保存BI系统中所有的历史数据。

DW存放明细事实数据、维度数据以及公共指标汇总数据。其中，明细事实数据、维表数据一般根据ODS层数据加工生成。公共指标汇总数据一般根据维表数据和明细事实数据加工生成。

DW层有细分为维度层（DIM）、明细数据层（DWD）和汇总数据层（DWS），采用维度模型方法作为理论基础，可以定义维度模型主键与事实模型中外键关系，减少数据冗余，也提高明细数据表的易用性。在汇总数据层同样可以关联复用统计粒度中的维度，采取更多的宽表化手段构建公共指标数据层，提升公共指标的复用性，减少重复加工。

维度层（DIM，Dimension）：以维度作为建模驱动，基于每个维度的业务含义，通过添加维度属性、关联维度等定义计算逻辑，完成属性定义的过程并建立一致的数据分析维表。为了避免在维度模型中冗余关联维度的属性，基于雪花模型构建维度表。
- 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别；
- 低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表，数据量可能是个位或者几千几万。
- 命名：dim_{业务板块名称/pub}_{维度定义}[自定义命名标签]。pub是与具体业务板块无关或者各个业务板块都可共用的维度，如时间维度。举例：dim_pub_area；商品维表：dim_asale_itm
明细数据层（DWD，Data Warehouse Detail）：以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细事实表。可以将某些重要属性字段做适当的冗余，也即宽表化处理。
汇总数据层（DWS，Data Warehouse Summary）：以分析的驻日对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标，以宽表化手段物理化模型，构建命名统一、口径一致的统计指标，为上层提供公共指标，建立汇总宽表、明细事实表。

数据域和主题域

主题域：面向业务过程，将业务活动时间进行抽象的集合，如下单、支付、退款等都是业务过程，针对公共明细层（DWD）进行主题划分。主题域通常是联系较为紧密的数据主题的集合，可以根据业务的关注点，将这些数据主题划分到不同的主题域（也就是对某个主题进行分析后确定的主题的边界）
数据域：面向业务分析，将业务过程或者维度进行抽象的集合，针对公共汇总层进行数据域划分。

维度建模的步骤

选择业务过程：在业务系统中，挑选感兴趣的业务线，比如下单业务、支付业务、退款业务、物流业务，一条业务线对应一张事实表，如果是小公司，尽量把所有的业务过程都选择。
声明粒度：数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽量选择最小粒度，一次来应对各种各样的需求。典型的粒度声明如下：订单当中的每个商品作为下单事实表中的一行，粒度为每次。每周的订单次数作为一行，粒度为每周。每月的订单为一行，粒度为每月。
确定维度：维度的主要作用是描述业务时事实，主要表示的是“谁、何时、何处”等信息。确定维度的原则是：后续需求中是否要分析相关维度的指标。例如，需要统计，什么时间下单的订单多，那个地区的订单多，那个用户下的订单多。需要确定的维度就包括：时间维度、地区维度、用户维度。维度表：需要根据维度建模中的星型模型原则进行维度退化。
确定事实：此处的事实一词，指的是业务中的度量值（次数、个数、件数、金额，可以进行累加），例如订单金额、下单次数等，dwd层，以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表，事实表可以做适当的宽表化处理。

DWD层做了哪些事

命名：dwd_{业务板块/pub}_{数据域缩写}_{业务过程缩写}{_{自定义表命名标签缩写}}_{单分区增量全量标识}。pub表示数据包括多个业务板块的数据。单分区增量全量标识：i表示增量，f表示全量。例如交易商品信息事实表：dwd_asale_trd_itm_di;交易会员信息事实表：ods_asale_trd_mbr_di；交易订单信息事实表：dwd_asale_trd_ord_di。

CREATE TABLE IF NOT EXISTS dwd_asale_trd_itm_di
(
    item_id              BIGINT COMMENT '商品ID',
    item_title           STRING COMMENT '商品名称',
    item_price           DOUBLE COMMENT '商品价格',
    item_stuff_status    BIGINT COMMENT '商品新旧程度_0全新1闲置2二手',
    item_prov            STRING COMMENT '商品省份',
    item_city            STRING COMMENT '商品城市',
    cate_id              BIGINT COMMENT '商品类目ID',
    cate_name            STRING COMMENT '商品类目名称',
    commodity_id         BIGINT COMMENT '品类ID',
    commodity_name       STRING COMMENT '品类名称',
    buyer_id             BIGINT COMMENT '买家ID',
)
COMMENT '交易商品信息事实表'
PARTITIONED BY (ds     STRING COMMENT '日期')
LIFECYCLE 400;

数据清洗过滤
1. 去除废弃字段、去除格式错误的信息
2. 去除丢失关键字段的信息
3. 过滤核心字段无意义的数据，比如订单id为null的，支付表支付id为空的
4. 对手机号、身份证号等敏感信息数据脱敏
5. 去除不含时间信息的数据
数据映射、转换
1. 将GPS经纬度转换成省市县详细地址。业界常用gps快速查询一般将地理位置知识库使用genhash映射，然后将需要比对的gps转换为geohash后跟知识库中的geohash对比，查找出地理位置信息；
2. 将IP地址也转换为省市县详细地址，这个有很多快速查找库，不过基本原理都是二分查找，因为IP地址可以转换为长整数，典型的如ip2region库
3. 将时间转换成年、月、日甚至周、季度维度信息
数据规范化：因为大数据处理的数据可能来自不同的部门，不同的项目、不同的客户端，这个时候可能相同的业务数据字段、数据类型、空值等都不一样，需要在DWD层做抹平，否则后续处理使用的时候，会造成很大的困扰。
1. 如boolean，有使用0、1标识的，也有使用true、false标识的
2. 如字符串空值，有使用’‘的，也有使用null的，统一为null即可
3. 如日期格式，这种差异性更大，需要根据实际业务数据决定，不过一般都格式化为YYYY-MM-dd HH:mm:ss这类标准格式
维度退化：对业务数据传过来的表进行维度退化和降维。订单id冗余在事实表。

DWS数据服务层，汇总层宽表

DWS层（数据汇总）宽表，面向主题的汇总，维度相对来说比较少，DWS是根据DWD层基础数据各个维度ID进行粗粒度汇总聚合，如按交易来源，交易类型进行汇总。整个汇总成分析某一个主题域的服务数据，一般是宽表，用于提供后续的业务查询，OLAP分析、数据分发等。

主题建模：围绕某一个业务主题进行数据建模，将相关数据抽离提取出来，如流量会话按照天、月进行聚合，每日用户进行聚合。
维度建模：根据业务需要，提前将后续数据查询处理需要的维度数据抽离处理出来，方便后续查询使用；

drop table
if exists dws_sale_detail_daycount;
create external table dws_sale_detail_daycount(
user_id                 string comment '用户 id',
--用户信息 
user_gender             string comment '用户性别',
user_age                string comment '用户年龄',
user_level              string comment '用户等级',
buyer_nick              string comment '买家昵称',
mord_prov               string comment '地址',  
--下单数、 商品数量， 金额汇总  
login_count             bigint comment '当日登录次数',
cart_count              bigint comment '加入购物车次数',
order_count             bigint comment '当日下单次数',
order_amount            decimal(16,2) comment '当日下单金额',
payment_count           bigint comment '当日支付次数',
payment_amount          decimal(16,2) comment '当日支付金额',
confirm_paid_amt_sum_1d double comment '最近一天订单已经确认收货的金额总和'
order_detail_stats   array<struct<sku_id:string,sku_num:bigint,order_count:bigint,order_amount:decimal(20,2)>> comment '下单明细统计'
 
) comment '每日购买行为'
partitioned by(`dt`
        string)
stored as parquet
location '/warehouse/gmall/dws/dws_sale_detail_daycount/'
tblproperties("parquet.compression" = "lzo");

用户粒度汇总表样例

首次、末次登录时间
首次、末次下单时间
首次、末次支付时间
最近30天下单的次数、金额
最近30天支付次数、金额
累计登录天数、最近30天的登录天数
累计下单次数、金额
累计支付次数、金额

6.3如何设计ADS层

应用层的定位是根据特定业务需求，按照业务角度组织数据以快速满足业务需求。应用层研发核心关注研发效率、口径一致性，以及核心应用的稳定性。设计原则以下：

需求驱动：需求驱动构建集市，按照最小原则设计，除非有明确的业务延续，否则不做过度的扩展设计。应用层的设计需要考虑业务定制的需求，提供面向业务低估值的应用数据，比如报表数据、大宽表等，供线上系统使用。
划分集市域、公共抽象下沉
1. 与公共层类似，以高内聚低耦合的原则对集市进行划分，让单集市数据研发聚焦在某一个领域需求实现；集市间应该避免互相依赖，避免复杂度的提升；
2. ADS也可以抽象公共部分，通过依赖ADS数据，提升开发效率和产出效率
减少ods的依赖：减少直接引用ODS表，降低源系统变更带来的改造成本，架构合理上考虑，公共层针对复用性的场景进行模型沉淀，当源系统变更时，通过公共层适应性改造屏蔽下游变更。

CREATE TABLE app_usr_interact( user_id string COMMENT '用户id',  
        nickname string COMMENT '用户昵称',  
        register_date string COMMENT '注册日期',  
        register_from string COMMENT '注册来源',  
        remark string COMMENT '细分渠道',  
        province string COMMENT '注册省份',  
        pl_cnt bigint COMMENT '评论次数',  
        ds_cnt bigint COMMENT '打赏次数',  
        sc_add bigint COMMENT '添加收藏',  
        sc_cancel bigint COMMENT '取消收藏',  
        gzg_add bigint COMMENT '关注商品',  
        gzg_cancel bigint COMMENT '取消关注商品',  
        gzp_add bigint COMMENT '关注人',  
        gzp_cancel bigint COMMENT '取消关注人',  
        buzhi_cnt bigint COMMENT '点不值次数',  
        zhi_cnt bigint COMMENT '点值次数',  
        zan_cnt bigint COMMENT '点赞次数',  
        share_cnts bigint COMMENT '分享次数',  
        bl_cnt bigint COMMENT '爆料数',  
        fb_cnt bigint COMMENT '好价发布数',  
        online_cnt bigint COMMENT '活跃次数',  
        checkin_cnt bigint COMMENT '签到次数',  
        fix_checkin bigint COMMENT '补签次数',  
        house_point bigint COMMENT '幸运屋金币抽奖次数',  
        house_gold bigint COMMENT '幸运屋积分抽奖次数',  
        pack_cnt bigint COMMENT '礼品兑换次数',  
        gold_add bigint COMMENT '获取金币',  
        gold_cancel bigint COMMENT '支出金币',  
        surplus_gold bigint COMMENT '剩余金币',  
        event bigint COMMENT '电商点击次数',  
        gmv_amount bigint COMMENT 'gmv',  
        gmv_sales bigint COMMENT '订单数'
)
PARTITIONED BY( dt string)
        --stat_dt
date COMMENT '互动日期',