|NO.Z.00052|——————————|^^ 案例 ^^|——|Hadoop&PB级数仓.V10|——|PB数仓.v10|周期性事实表|实现小结|
一、周期性事实表：有如下订单表，6月20号有3条记录(001/002/003)：
| 订单创建日期 | 订单编号 | 订单状态 |
| 2020-06-20 | 001 | 创建订单 |
| 2020-06-20 | 002 | 创建订单 |
| 2020-06-20 | 003 | 支付完成 |
6月21日，表中有5条记录。其中新增2条记录（004/005），修改1条记录（001）：
| 订单创建日期 | 订单编号 | 订单状态 |
| 2020-06-20 | 001 | 支付完成(从创建到支付) |
| 2020-06-20 | 002 | 创建订单 |
| 2020-06-20 | 003 | 支付完成 |
| 2020-06-21 | 004 | 创建订单 |
| 2020-06-21 | 005 | 创建订单 |
6月22日，表中有6条记录。其中新增1条记录（006），修改2条记录（003/005）：
| 订单创建日期 | 订单编号 | 订单状态 |
| 2020-06-20 | 001 | 支付完成 |
| 2020-06-20 | 002 | 创建订单 |
| 2020-06-20 | 003 | 已发货（从支付到发货） |
| 2020-06-21 | 004 | 创建订单 |
| 2020-06-21 | 005 | 支付完成（从创建到支付） |
| 2020-06-21 | 006 | 创建订单 |
二、订单表处理
### --- 订单事实表的处理方法：
### --- 历史拉链表数据
~~~ 只保留一份全量。数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；
~~~ 每天都保留一份全量。在数据仓库中可以在找到所有的历史信息，
~~~ 但数据量大了，而且很多信息都是重复的，会造成较大的存储浪费；
~~~ 使用拉链表保存历史信息，会有下面这张表。
~~~ 历史拉链表，既能满足保存历史数据的需求，也能节省存储资源。| 订单创建日期 | 订单编号 | 订单状态 | begin_date | end_date |
| 2020-06-20 | 001 | 创建订单 | 2020-06-20 | 2020-06-20 |
| 2020-06-20 | 001 | 支付完成 | 2020-06-21 | 9999-12-31 |
| 2020-06-20 | 002 | 创建订单 | 2020-06-20 | 9999-12-31 |
| 2020-06-20 | 003 | 支付完成 | 2020-06-20 | 2020-06-21 |
| 2020-06-20 | 003 | 已发货 | 2020-06-22 | 9999-12-31 |
| 2020-06-21 | 004 | 创建订单 | 2020-06-21 | 9999-12-31 |
| 2020-06-21 | 005 | 创建订单 | 2020-06-21 | 2020-06-21 |
| 2020-06-21 | 005 | 支付完成 | 2020-06-22 | 9999-12-31 |
| 2020-06-22 | 006 | 创建订单 | 2020-06-22 | 9999-12-31 |
三、周期性事实表：拉链表实现
### --- 前提条件
~~~ 订单表的刷新频率为一天，当天获取前一天的增量数据；
~~~ 如果一个订单在一天内有多次状态变化，只记录最后一个状态的信息；
~~~ 订单状态包括三个：创建、支付、完成；
~~~ 创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，
~~~ 那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；### --- 数仓ODS层有订单表，数据按日分区，存放每天的增量数据：
DROP TABLE test.ods_orders;
CREATE TABLE test.ods_orders(
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (dt STRING)
row format delimited fields terminated by ',';### --- 数仓DWD层有订单拉链表，存放订单的历史状态数据：
DROP TABLE test.dwd_orders;
CREATE TABLE test.dwd_orders(
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING,
start_date STRING,
end_date STRING
)
row format delimited fields terminated by ',';四、周期性事实表拉链表的实现
### --- 全量初始化
~~~ # 数据文件order1.dat
[root@hadoop02 ~]# vim /data/yanqidw/data/order1.dat
001,2020-06-20,2020-06-20,创建
002,2020-06-20,2020-06-20,创建
003,2020-06-20,2020-06-20,支付~~~ # 加载数据
hive (default)> load data local inpath '/data/yanqidw/data/order1.dat'
into table test.ods_orders partition(dt='2020-06-20');
hive (default)> INSERT overwrite TABLE test.dwd_orders
SELECT orderid, createtime, modifiedtime, status,
createtime AS start_date,
'9999-12-31' AS end_date FROM test.ods_orders
WHERE dt='2020-06-20';~~~ # 查看加载的数据
hive (default)> select * from test.ods_orders;
~~~输出参数
ods_orders.orderid ods_orders.createtime ods_orders.modifiedtime ods_orders.status ods_orders.dt
1 2020-06-20 2020-06-20 创建 2020-06-20
2 2020-06-20 2020-06-20 创建 2020-06-20
3 2020-06-20 2020-06-20 支付 2020-06-20
hive (default)> select * from test.dwd_orders;
~~~输出参数
dwd_orders.orderid dwd_orders.createtime dwd_orders.modifiedtime dwd_orders.status dwd_orders.start_date dwd_orders.end_date
1 2020-06-20 2020-06-20 创建 2020-06-20 9999-12-31
2 2020-06-20 2020-06-20 创建 2020-06-20 9999-12-31
3 2020-06-20 2020-06-20 支付 2020-06-20 9999-12-31### --- 增量抽取
~~~ # 数据文件order2.dat
[root@hadoop02 ~]# vim /data/yanqidw/data/order2.dat
001,2020-06-20,2020-06-21,支付
004,2020-06-21,2020-06-21,创建
005,2020-06-21,2020-06-21,创建~~~ # 加载数据
hive (default)> load data local inpath '/data/yanqidw/data/order2.dat'
into table test.ods_orders partition(dt='2020-06-21');~~~ # 查看加载的数据
hive (default)> select * from test.ods_orders;
~~~输出参数
ods_orders.orderid ods_orders.createtime ods_orders.modifiedtime ods_orders.status ods_orders.dt
1 2020-06-20 2020-06-20 创建 2020-06-20
2 2020-06-20 2020-06-20 创建 2020-06-20
3 2020-06-20 2020-06-20 支付 2020-06-20
1 2020-06-20 2020-06-21 支付 2020-06-21
4 2020-06-21 2020-06-21 创建 2020-06-21
5 2020-06-21 2020-06-21 创建 2020-06-21### --- 增量刷新历史数据
~~~ # 拉链表中的数据分两部实现：新增数据(ods_orders)、历史数据(dwd_orders)
~~~ # 处理新增数据
hive (default)> SELECT orderid,
createtime,
modifiedtime,
status,
modifiedtime AS start_date,
'9999-12-31' AS end_date
FROM test.ods_orders
where dt='2020-06-21';
~~~输出参数
orderid createtime modifiedtime status start_date end_date
1 2020-06-20 2020-06-21 支付 2020-06-21 9999-12-31
4 2020-06-21 2020-06-21 创建 2020-06-21 9999-12-31
5 2020-06-21 2020-06-21 创建 2020-06-21 9999-12-31~~~ # 处理历史数据。历史数据包括：有修改、无修改的数据
~~~ ods_orders 与 dwd_orders 进行表连接
~~~ 连接上，说明数据被修改
~~~ 未连接上，说明数据未被修改
hive (default)> select A.orderid,
A.createtime,
A.modifiedtime,
A.status,
A.start_date,
case when B.orderid is not null and A.end_date>'2020-06-21'
then '2020-06-20'
else A.end_date
end end_date
from test.dwd_orders A
left join (select * from test.ods_orders where dt='2020-06-21')
B on A.orderid=B.orderid;
~~~输出参数
a.orderid a.createtime a.modifiedtime a.status a.start_date end_date
1 2020-06-20 2020-06-20 创建 2020-06-20 2020-06-20
2 2020-06-20 2020-06-20 创建 2020-06-20 9999-12-31
3 2020-06-20 2020-06-20 支付 2020-06-20 9999-12-31~~~ # 用以上信息覆写拉链表
hive (default)> insert overwrite table test.dwd_orders
SELECT orderid,
createtime,
modifiedtime,
status,
modifiedtime AS start_date,
'9999-12-31' AS end_date
FROM test.ods_orders
where dt='2020-06-21'
union all
select A.orderid,
A.createtime,
A.modifiedtime,
A.status,
A.start_date,
case when B.orderid is not null and A.end_date>'2020-06-21'
then '2020-06-20'
else A.end_date
end end_date
from test.dwd_orders
A left join (select * from test.ods_orders where dt='2020-06-21')
B on A.orderid=B.orderid;~~~ # 查看
hive (default)> select * from test.dwd_orders;
~~~输出参数
dwd_orders.orderid dwd_orders.createtime dwd_orders.modifiedtime dwd_orders.status dwd_orders.start_date dwd_orders.end_date
1 2020-06-20 2020-06-20 创建 2020-06-20 2020-06-20
2 2020-06-20 2020-06-20 创建 2020-06-20 9999-12-31
3 2020-06-20 2020-06-20 支付 2020-06-20 9999-12-31
1 2020-06-20 2020-06-21 支付 2020-06-21 9999-12-31
4 2020-06-21 2020-06-21 创建 2020-06-21 9999-12-31
5 2020-06-21 2020-06-21 创建 2020-06-21 9999-12-31附录一：拉链表小结
Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
——W.S.Landor
分类:
bdv014-PB离线数仓
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通