数仓学习之路一：数仓理论

数仓分层和数据集市

传统分层有好几个，比如：ODS>DWD>DWS>ADS；这里我举例的为传统五层数仓

数据仓库分层（传统）：

• ODS（Operation Data Store）：原始数据层，备份作用；数据保持不变

• DWD（Data Warehouse detail）：对ODS的数据进行轻度清洗（去空、脏数据、超过范围的数据）、维度退化、脱敏等

• DWS（Data Warehouse Service）：以DWD层为基础，按天进行轻度汇总

• DWT（Data warehouse Topic）：以DWS为基础，按照主题进行汇总

• ADS（Application Data Store）：ADS层，各种统计报表提供数据

为什么要对数据仓库进行分层？

1. 复杂问题简单化，将复杂的原始数据进行拆解多层，每一层的处理难度低，方便定位。

2. 减少了开发的重复性，规范了数仓建设，极大的减少了重复的计算 ；增加了计算的结果复用性。

3. 隔离原始数据，将原始数据用作备份。使真实数据与统计数据解耦开。

数据集市与数据仓库的区别

• 数据集市：微型的数据仓库；数据量、主题区域跨度等都是部门级别的。

• 数据仓库：企业级，内部包含许多数据集市。为整个企业提供数据支撑做决策支撑。

 

数仓命名规范

表

• ODS层命名为 ods_表名

• DWD层命名为 dwd_dim/fact_表名

• DWS层命名为 dws_表名

• DWT层命名为 dwt_购物车

• ADS层命名为 ads_表名

临时表命名为 xxx_tmp

用户行为表，以 log 为后缀

举例：

 -- 订单详情表
 -- 建表之前需要对表进行一个查询是否存在并存在即删除的操作
 drop table if exists ods_order_detail;
 -- 正式建表，含义：ods层订单详细表
 create external table ods_order_detail( 
     `id` string COMMENT '订单编号',
     `order_id` string  COMMENT '订单号', 
     `user_id` string COMMENT '用户id',
     `sku_id` string COMMENT '商品id',
     `sku_name` string COMMENT '商品名称',
     `order_price` decimal(10,2) COMMENT '商品价格',
     `sku_num` bigint COMMENT '商品数量',
     `create_time` string COMMENT '创建时间'
 ) COMMENT '订单详情表'
 -- 指明分区字段为时间日期
 PARTITIONED BY (`dt` string)
 -- 指定列之间的分隔符以及每行的分隔符
 row format delimited fields terminated by '	' 
 -- 指定输入输出格式
 STORED AS
   INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
 -- 指定装载数据的HDFS路径
 location '/warehouse/gmall/ods/ods_order_detail/';

 

脚本命名

• 数据源_to_目标_db/log.sh

• 用户行为脚本以log为后缀

• 业务数据脚本以db为后缀

举例：hdfs_to_ods_log.sh

 #!/bin/bash
 # 脚本名字为 hdfs_to_ods_log.sh
 ​
 # 定义变量方便修改
 APP=gmall  # Hive数据库的名称
 hive=/opt/module/hive/bin/hive  # Hive的绝对路径
 ​
 # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
 if [ -n "$1" ] ;then
    do_date=$1
 else 
    do_date=`date -d "-1 day" +%F`
 fi 
 ​
 echo "===日志日期为 $do_date==="
 ​
 # 装载数据
 sql="
 load data inpath '/origin_data/gmall/log/topic_start/$do_date' overwrite into table ${APP}.ods_start_log partition(dt='$do_date');
 ​
 load data inpath '/origin_data/gmall/log/topic_event/$do_date' overwrite into table ${APP}.ods_event_log partition(dt='$do_date');
 "
 ​
 # 执行HiveQL语句
 $hive -e "$sql"
 ​
 # 配置支持lzo压缩的环境
 hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_start_log/dt=$do_date
 ​
 hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_event_log/dt=$do_date

 

 

 

范式理论

范式概念

定义：

• 范式可以理解为设计一张数据表的表结构，符合规范和要求

优点：

• 降低数据冗余性

• 减少磁盘存储

缺点：

• 获取数据时需要通过Join拼接出最后的数据

分类：

• 第一范式

• 第二范式

• 第三范式等

 

函数依赖

1. 完全函数依赖：

   例如（学号、课程）推出分数；单独用学号推断不出分数；即分数完全依赖（学号、课程）

   AB-->C，AB,单独拆开推不出C，即C完全依赖AB。

2. 部分函数依赖：

   （学号、课程）推出姓名，可以直接用学号推出姓名；即姓名 部分依赖于（学号、课程）

   AB--> c; A --> C; B --> C; 即C部分依赖于AB

3. 传递函数依赖

   学号推出系名，系名推出系主任；但是系主任推不出学号，系主任主要依赖于系名；即系主任传递依赖于学号

   A --> B; B --> C; C不能推出A; 即传递依赖于A

 

三范式区分

1. 1NF: 属性不能切分

   商品: 5个苹果 ===> 商品: 苹果 数量: 5

2. 2NF: 不能存在部分函数依赖

   去除不能存在"部分函数依赖";

   学号 姓名 系名 系主任 课名 分数 ===> 学号 课名 分数 ; 学号 姓名 系名 系主任

3. 3NF: 不能存在传递函数依赖

   学号->系名->系主任, 但是系主任不能推出 学号; ===> 学号 姓名 系名 ; 系名 系主任

 

关系建模与维度建模

• 联机事务处理(OLTP)

  • 传统的关系型数据库的主要应用; 日常事务处理,例如银行交易

• 练级分析处理(OLAP)

  • 数据仓库系统主要应用,复杂分析操作, 侧重决策支持,提供直观移动的查询结果

对比属性	OLTP	OLAP
读	每次查询只返回少量记录	对大量记录进行汇总
写	随机 低延迟写入用户的输入	批量导入
使用场景	用户 JavaEE项目	内部分析师,决策提供支持
数据表征	最新数据状态	随时间变化的历史状态
数据规模	GB	TB到PB

关系建模

• 关系模型严格遵循第三范式

• 数据冗余程度低, 解耦合, 功能性强

• 应用OLTP系统, 保证数据的一致性以及避免冗余

• 关系模型虽然冗余少,但是在大规模数据, 跨表分析查询过程中, 会造成多表关联, 大大降低执行效率

  • 事实表

  • 维度表

维度建模

维度建模的基础上分为:

• 星型模型

  • 标准星型模型维度只有一层

  • 减少Join 减少 Shuffle

• 雪花模型

  • 可能涉及多层

  • 靠近3NF,无法完全遵守(成本)

• 星座模型

  • 事实表的数量,基于多个事实表

  • 只反映是否有多个事实表, 他们之间是否共享一些维度

 

维度表和事实表(重点)

维度表

• 维度表: 一般是对事实的描述信息; 一张维度表对应现实世界中的一个对象或者概念(用户 商品 日期 地区)

  • 范围宽(具有多个属性 列比较多)

  • 与事实表相比, 行数相对较小: 通常 < 10万条

  • 内容相对固定: 编码表

  时间维度表:

  • 周

  • 年

  • 季度

  • 节假日等等

事实表

• 每行数据代表一个业务事件(下单 支付 退款 评价)

• "事实": 表示的是业务事件的 度量值(可统计次数 个数 件数 金额) 订单事件中的下单金额

• 事实表的行包括: 具有可加性的数值型的度量值, 与维表相连接的外键, 通常具有两个及以上的外键,外键之间表示维表之间的多对多关系

  特征:

  • 大数据量

  • 内容相对窄, 列数较少

  • 经常变化, 每天会新增加很多

1. 事务性事实表

   每个事务或事件为单位: 一个销售订单记录, 一笔支付记录等

   一旦事务被提交, 事实表数据被插入, 数据不再进行更改, 其更新方式为 增量更新

2. 周期性快照事实表

   不会保留所有的数据, 只保留固定时间间隔的数据, (每天或者每月的销售额, 每月的账户余额)

3. 累积型快照事实表

   累计快照事实表用于跟踪业务事实的变化(数仓中可能需要累计或者存储订单从下单开始追踪数据, 事实表记录不断的更新)

    

 

数据仓库建模

ODS层

1. HDFS 用户行为数据

2. HDFS 业务数据

3. 针对HDFS 上的用户行为数据和业务数据, 规划 处理?

   • 保持数据原貌, 不做任何任何修改, 起到备份数据的作用

   • 数据采用压缩, 减少磁盘存储空间(例如: 原始数据 100G, 可以压缩到10G)

   • 创建分区表, 防止后续的全表扫描

DWD层

一般采用星型模型, 呈现的状态一般为 星座模型

步骤:

1. 选择业务过程

   如下单业务, 支付业务, 退款业务, 物流业务等; 一条业务线对应一张事实表

   一般情况下, 尽量所有的业务过程都选择; 数量大情况, 选择和需求相关的业务线

2. 声明粒度

   数仓的数据保存数据的细化程度, 或者综合程度级别

   即 精确定义事实表中的一行数据表示什么? 应该尽可能的选择最小粒度

   如:

   • 订单中的每个商品项作为下单事实表中的一行, 粒度为每次

   • 每周的订单次数作为一行, 粒度为每周等等

3. 确认维度

   描述业务是事实, 表示是"who where when" 等信息

   原则: 后续需求中是否要分析相关维度的指标; (需要统计 什么时间下的订单多 那个地区下单多, 那个用户下单多, 需要确定的维度: 时间 地区 用户)

   维度表: 需要根据维度建模中的星型模型原则进行维度退化

4. 确认事实

   业务中的度量值(次数 个数 件数 金额 , 可以进行累加) 如 订单金额 下单次数

   DWD层, 以业务过程为建模驱动, 基于每个具体业务过程的特点, 构建最细粒度的明细事实表(做适当的宽表化处理)

   事实表和维度表 关联灵活;

DWD层是以业务过程为驱动

• DWS层 DWT层和ADS层都是以需求为驱动;

• DWS层 DWT层 都是建立宽表; 按照主题建表(主题即观察问题的角度, 对应着维度表)

DWS层

DWS层统计各个主体对象的 当天行为; 服务于DWT层的主题宽表

1. 问题引出: 统计每个省份的订单个数; 统计每个省份的订单总金额

2. 处理: 将省份表 和 订单表 join, group by 省份, 然后进行计算 (计算重复)

3. 优化: 地区跨表字段: 下单次数 下单金额 支付次数 支付金额等; 只需要和每个事实表一次join

4. 总结:

   • 需要建哪些表: 以维度为基准, 关联对应多个事实表

   • 宽表里面的字段: 站在不同的维度看事实表, 重点关注事实表聚合后的度量值

   DWS层宽表包括: 每日设备行为, 每日会员行为, 每日商品行为, 每日活动统计, 每日地区统计

DWT层

DWT层统计各个主题对象的累计行为

1. 建表: 以维度为基准, 去关联对应多个事实表

2. 宽表字段: 维度表的角度看事实表, 重点关注事实表度量值的累计值, 事实表行为的首次和末次时间

3. 栗子: 订单事实表的度量值: 下单次数 下单金额;

   订单事实表 的 行为是 下单; 关注: 累计下单次数, 累计下单金额, 某时间段内的累计次数, 累计金额;

   下单行为的首次时间和末次时间

ADS层

对各大主题指标分别进行分析...