文章目录

数仓项目1. 项目背景 电商1.1 遇到的问题/思路1.2 数仓架构设计1.3 范式1.4 数据库与数仓各自的侧重点 2. 维度2.1 维度概念2.2 维度如何获得？2.3 怎么设计维度表2.4 维度的层次2.5 规范化和反规范化2.6 一致性维度2.7 维度的整合和拆分2.8 历史数据归档2.9 缓慢变化维2.10 快照维表 3. 增量抽取 拉链表3.1 增量抽取3.2 拉链表

数仓项目

1. 项目背景 电商

1.1 遇到的问题/思路

随着发展， 数据需要做决策 将不同系统的表全部导入hive里

第一阶段 问题: 1.烟囱式开发/重复开发 取数 需求：建立数仓 --> 数仓架构设计/表名规范/分层设计 搭建各个主体域的中间层 mysql --> hive --> excel给业务方

第二阶段 问题：1.数据量上来，数据抽取消费时间 --> 如何选择最优的数据抽取/存储策略 2.ETL任务 脚本跑在服务器上 每天凌晨 定时服务 pull push 3.调度问题 依赖 使用 azkaban

第三阶段 问题：1.随着业务发展 业务系统需要进行一定程度的改造 批次 有批次/无批次 有批次 一品多商 无批次 一品一商 通过数据血缘 来改造 2.业务方质疑你的数据不准 数据口径不统一 取错数据 针对数据口径不同意， 做一个指标中心 也可能是数据收取挂了 64张抽了60涨 ==> 简历数据质量中心DQC 监控每天数据条数变化 对应指标 3.任务跑的慢 sql写的不规范 分区

需求：多维分析 ==< kylin+ BI系统 数据血缘 + 指标中心 + 数仓表信息（数据字典） ==> 元数据中心

第四阶段 问题: 集群稳定性 cdh 采集各个系统（数据团队）的关键数据 == > 图表 离线任务运行时长 枚举中心 监控上游数据的变更

1.2 数仓架构设计

考虑 ： 数仓分层 ODS 尽量保持和源端的数据格式一样

DWD 数据清洗（过滤一些数据，日期格式转换 时间戳–>yyyy-MM-dd） DWM 数据中间层 提升数仓公共指标的复用性，减少重复加工 DWS 生成字段比较多的宽表 提供给后续的业务查询

APP 数据应用层 report 严禁读取ods dwd层 ，说明中间层建设不完善

DIM 维表 数仓通用的维表 /时间/地区/类目/商品 数据回流 sync_xxxx脚本 mysql提供给业务

数仓表名、字段名、命名规范 例子：ods_t_item_d 脚本 ods_t_item_d.sh

后缀 天更新 _d结尾 月更新 _m结尾

_i 增量表 数据是增加 _s拉链表

ODS层 层级_业务库_mysql表名_d ods_trade_pay_xxx_d/i

中间层 层级_主体域_业务过程_[/d/h/m…] dws层 _1d 统计日当天的汇总数据 _nd 统计近n天的汇总数据 _mtd 统计当月累计到统计日的汇总数据 _td 统计的就是历史全量数据 _dth

维表 dim_维度名称_d 每日 dim_维度名称 dim_维度名称_ds 拉链表 极限存储表

1.3 范式

第零范式 没有重复数据 第一范式 满足属性不可分 有冗余 第二范式 第一范式的基础上更进一步，确保数据库的表当中每一个字段都和主键相关 第三范式 确保数据表中的每一列数据都和主键直接相关，而不能间接相关 在第二范式的计出上 属性只直接以来主键

星形模型 范式符合第二范式 雪花模型 和星形模型区别 维度是否和事实表直接相连

1.4 数据库与数仓各自的侧重点

数据库设计 1.联机事务处理 on-line transaction processing OLTP 增删改查 2.事务的设置 3.业务系统 4.order_id 存储实时数据 5.避免冗余

数仓 1.联机分析处理 OLAP 面向日常数据分析 数据插入和查询，不会设计数据删除和修改(映射) 2.主题的设置 3.分析系统 4.存储历史数据 5.为了设计主体域 刻意引入冗余操作

2. 维度

2.1 维度概念

度量 指标 == > 事实 对于一些环境的描述 ⇒ 维度

select item, -->商品 维度 sum(gmv) -->全年下单金额 聚合指标 指标 from tbl group by item

分析交易过程 维度–> 商品 时间 买家 卖家 --> 用于分析指标所需要的一个多样的环境

2.2 维度如何获得？

1.业务决定 2.数据中获取

2.3 怎么设计维度表

成熟的方法论 步骤

List itemv选择一个维度 (商品 时间 地区)确认主维表确定相关 需要关联的维表 sku 件 盒 托盘 spu iphone4j 一个产品单位确定维度属性 1.尽可能的丰富 下游在使用维度表更方便 统计分析类的操作 ID name 2.字段 作为维度 具体的事实 通常情况下用于查询的约束条件where 或者 用于分组统计group by 通常参与实际的度量计算 事实

2.4 维度的层次

上卷 最大的范围统计 （全国）下钻 较小的范围统计 (地区)

2.5 规范化和反规范化

规范化 大多数OLTP 底层数据模型设计 采用规范化 一些维度属性 移到他们自身所属的表当中 删除冗余数据！！！ 类似于第二范式反规范化 退化维度 冗余一部分的数据 到维表当中 --> 类似于一张平表 浪费一些存储 但是对下游的应用型会高点 存储成本高

2.6 一致性维度

存在数据探查（交叉探查）概念

2.7 维度的整合和拆分

数据仓库的定义：面向主题的、集成的数据集合 表现形式&注意点

表名 字段名 的命名规范统一 字段类型的统一业务含义相同的表进行统一 源端 数据形式 差异较小的表进行整合 源端 数据形式 差异很大的表进行拆分 主从表的设计 主表常用 从表不常用

2.8 历史数据归档

归档策略 归档到oss去

归档策略1 前台商品展策略： 商品状态 商品是否被删除 更新策略 ==> 对应下单信息不会有这批商品 归档策略跟着 前台商品的归档策略 走 问题：实现后 维护成本很高

归档策略2 binlog

归档策略3 自定义 通过设置数仓表的生命周期 ods 3天 dwd 3个月 dw 个月 report 历史全保留 增量表 历史全保留

2.9 缓慢变化维

维度变化

新增维度列新增维度行重写维度值

2.10 快照维表

快照 ：某个时间点的状态存储 快照周期：多久去获取一次数据 天 h

问题1：按天获取快照 如何获取 item 分区表 按天进行分区 抽取是全量

问题2：按天分区 数据量太大怎么办

性能问题1 ： 存储 1张表 365个分区 n张表 n*365

性能问题2 ：抽取 sqoop 抽取慢 -m 往上加 一定程度上加快整体的抽取速度 临城 对多个实例 进行抽取 1个实力 会有多张mysql库 1个库 会有多个mysql表

3. 增量抽取 拉链表

3.1 增量抽取

实际操作

全量表 create table dwd_order_d( pay_day string, trade_no string, phone string, pay_amount decimal(18,2), pay_status int, update_time string ) row format delimited fields terminated by ',' stored as textfile; 增量表 create table ods_order_di( pay_day string, trade_no string, phone string, pay_amount decimal(18,2), pay_status int, update_time string ) row format delimited fields terminated by ',' stored as textfile;

数据i路转 初始化 mysql order ==> hive osd_order_d ⇒ dwd_order_d 增量： ==》 hive ods_order_di

数据导入

全量表 order_his.txt 2020-06-01,001,111111,1,0,2020-06-01 2020-06-01,002,222222,2,0,2020-06-01 2020-06-01,003,333333,3,0,2020-06-01 2020-06-01,004,444444,4,0,2020-06-01 增量表数据 order_incremenet.txt 2020-06-01,002,222222,2,1,2020-06-02 2020-06-01,004,444444,4,1,2020-06-02 2020-06-02,005,444444,4,0,2020-06-02

数据导入

load data local inpath '/home/hadoop/data/order_his.txt' into table dwd_order_d; load data local inpath '/home/hadoop/data/order_increment.txt' into table ods_order_di;

sql合并

insert overwrite table dwd_order_d select a.pay_day, a.trade_no, a.phone, a.pay_amount, a.pay_status, a.update_time from ( select pay_day, trade_no, phone, pay_amount, pay_status, update_time from dwd_order_d ) as a left join ( select pay_day, trade_no, phone, pay_amount, pay_status, update_time from ods_order_di ) as b on a.trade_no = b.trade_no where b.trade_no is null union all select pay_day, trade_no, phone, pay_amount, pay_status, update_time from ods_order_di

3.2 拉链表

拉链表的意义： 方式一：全量抽取 按天分区 天天全量 方式二：增量抽取+merge 到全量 dwd层开始 按天分区 天天全量 方式三：拉链表 最大作用 降低了非常多的存储

建表 拉链表 create table dwd_order_ds( pay_day string, trade_no string, phone string, pay_amount decimal(18,2), pay_status int, start_date string, end_date string ) row format delimited fields terminated by ',' stored as textfile; --ods初始化的全量表 create table ods_order_d( pay_day string, trade_no string, phone string, pay_amount decimal(18,2), pay_status int, update_time string ) row format delimited fields terminated by ',' stored as textfile; 数据导入 load data local inpath '/home/hadoop/data/order_his.txt' into table ods_order_d; --ods层的订单更新表 ods_order_di

第一步：初始化拉链表

insert overwrite table dwd_order_ds select pay_day, trade_no, phone, pay_amount, pay_status, '2020-06-01' as start_date, '9999-12-31' as end_date from ods_order_d;

到了2020-06-02 第二步

对于历史上已经被更新的数据 end_date 要从原始的改为失效的那一天的日期将新增的记录或者是被更新的记录给insert进去，同时设置start end 赋值 insert overwrite table dwd_order_ds select t.* from ( select a.pay_day, a.trade_no, a.phone, a.pay_amount, a.pay_status, a.start_date, case when a.end_date='9999-12-31' and b.trade_no is not null then '2020-06-01' else a.end_date end as end_date from dwd_order_ds as a left join ods_order_di as b on a.trade_no = b.trade_no union all select c.pay_day, c.trade_no, c.phone, c.pay_amount, c.pay_status, '2020-06-02' as start_date, '9999-12-31' as end_date from ods_order_di as c ) t

拉链表的缺点

加字段怎么办 历史的状态数据会丢？ mysql a字段在2020-06-01加上了 而且a字段很重要 业务统计要用的 hive a字段 2020-06-07才知道 1-7号 关于a字段的状态丢了？ 1月后 业务统计需要历史状态 拉链表该不该加字段 做拉链表要考虑周全 需要什么字段 同步加会怎么样？ ⇒ 对历史数据他有默认值 hive端 对于拉链表来说 历史的数据也需要有默认值 重新初始化？ 历史状态数据没了

上游出现脏数据 以订单数据为例子 变化周期是固定 确定业务周期 这些数据过了这个周期就不会再变了 数据结转 dp 过期 历史 活跃 过期 所有生命周期已经无效了的数据 历史 不会再做任何变更的数据 活跃 在固定业务周期内 还会做变化的数据

拉链表的优化：

存储格式 列式存储指定分区字段之后 查询能做分区裁剪 dp 业务上的时间 pay_day分区