03-数据仓库之拉链表

1、拉链表：

　　　　　　①记录每条信息的生命周期为单位
　　　　　　②一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期作为此记录的生效日期
　　　　　　③如果当前信息至今有效，在生效结束日期中填入一个极大值（如9999-12-31、9999-99-99）

　　　　　　用处：

　　　　　　　　①需要查看某些业务信息的某一个时间点当日的信息
　　　　　　　　②数据会发生变化，但是大部分是不变的。（无法做每日增量）
　　　　　　　　③数据量有一定的规模，无法按照每日全量的方法保存 。（无法做每日全量）

2、拉链表实例：

 

　　　　　　现在增量数据从mysql 已经导入到ods层中了：ods_order_info。

　　　　　　①在dwd层中，新建dwd _order_info表，结构和ods_order_info一样，多了'start_date'、'end_date'两个字段

　　　　　　　　drop table if exists dwd _order_info;
　　　　　　　　create table dwd _order_info(

　　　　　　　　　　　　..........
　　　　　　　　　　　　.........
　　　　　　　　　　'start_date' string comment '有效开始日期',
　　　　　　　　　　'end_date string comment '有效结束日期'
　　　　　　　　)comment '订单拉链表'
　　　　　　　　partioned by ('dt' string) 　　　　　　　　　　　　　　　　　　　　　　 //分区不是必要的
　　　　　　　　stored as parquet 　　　　　　　　　　　　　　　　　　　　　　　　 //存储格式
　　　　　　　　location '/warehouse/online_trade/dwd/dwd _order_info'
　　　　　　　　tblproperties("parquet.compression"="snappy") 　　　　　　　　　　 //压缩算法

　　　　　　　　拓展一下分区：
　　　　　　　　　　①减小查询范围
　　　　　　　　　　②索引
　　　　　　　　　　③数据量巨大

　　　　　　　　　　拉链表分区与不分区取决于数据量的多少，并且拉链表也不是每天做，可能是每周、每个月做也说不定！！！
　　　　　　　　　　也就是说，按天分区、按月分区、不分区都是可以的！！

　　　　　②将ods的增量数据导数据到dwd

　　　　　　　　insert overwrite table dwd_order_info
　　　　　　　　select
　　　　　　　　　　.....
　　　　　　　　　　'2019-01-10', 　　　　　　　　　　　　　　　　　　 //设置生效日期
　　　　　　　　　　'9999-99-99' 　　　　　　　　　　　　　　　　　　  //有效结束日期
　　　　　　　　from ods_order_info a where a.dt='2019-01-10' 　　　　   //将ods的数据导进去

 

　　　　 ③现在dwd_order_info是最新的增量数据，dwd_order_info_his：是HDFS上的以前的拉链数据（历史表），结构和dwd_order_info一样

 

　　　　　　　　新建一张dwd_order_info_tmp，结构和dwd_order_info一样：

　　　　　　　　目的是将今天的增量数据，和历史数据合并。

　　　　　　　　　　　　①如果今天增量中某些记录，以前已经在历史表存在，那么对历史表进行更新，历史数据有效期设为今天-1

　　　　　　　　　　　　②经过上一步，历史表 = 今天没更新的数据 + 今天更新的数据但是有效期设为昨天（已过期） ，那么历史表（dwd_order_info_his)    union all    最新的增量(dwd_order_info） =  最新的数据（dwd_order_info_tmp）

　　　　　　　　insert overwrite dwd_order_info_tmp
　　　　　　　　select
　　　　　　　　　　.......
　　　　　　　　　　.......
　　　　　　　　　　t1.start_date,
　　　　　　　　　　if(t2.id is null,t1.end_date,date_add('2019-01-10',-1) )
　　　　　　　　　　from dwd_order_info_his t1 left join dwd _order_info t2

　　　　　　　　　　on t1.id = t2.id and t1.end_date='9999-99-99' 　　　　　　　　　　　　　　//确保join连接的是还未过期的历史数据，对已过期的历史数据不做连接

　　　　　　　　where t2.dt = '2019-01-10' 　　　　　　　　　　　　　　　　　　　　　　　　 //确保增量数据是今天导入的。


　　　　　　　　//以历史表为基表：t2.id is null 表示已过期的历史数据，那么有效结束日期不变
　　　　　　　　// if is not null 表示历史数据中变化量，那么结束日期-1

　　　　　　　　union all

　　　　　　　　select * from dwd_order_info where dwd_order_info.dt = '2019-01-10'

　　　　　　　　//如果今天增量中某些记录以前没记录，那么进行合并到dwd_order_info_tmp表

 

　　　　　　④更新历史表

　　　　　　　　　　insert overwrite dwd_order_info_his
　　　　　　　　　　select ....... from dwd_order_info_tmp　　　　

　　

　　　　　　　　　　注：不要用select *,强烈不推荐使用

 

3、拉链表中获取增量问题：

　　　　如何获取mysql中的每日变动表？

　　　　　　①表中设计创建日期、变动日期字段，那么sqoop就能根据变动日期导数据！

　　　　　　②用canal监控mysql的实时变化