文章目录
数仓项目
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分区