mysql 时间拉链表_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_datestringcomment '有效结束日期'

)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的实时变化