单张表超过30个字段_拉链表

为什么要做拉链表

拉链表适合于：数据会发生新增和变化，但是大部分是不变的，且是缓慢变化的（如电商中用户信息表中的手机号不可能每天都变化），如果是快速变化的（如每天一变），则每天做全量更新（事务型事实表）。主要目的是节省存储空间。

拉链表的使用场景

在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：

• 1 有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些。
• 2 表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。
• 3 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。
• 4 表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小

什么是拉链表

记录每条信息的生命周期，一旦一条信息的生命周期结束，就重新开始一条新纪录，并把当前日期放入生效日期。 如果当前日期至今有效，在结束日期放入一个最大值，例如（9999-99-99）

- 优势：订单1经历了四个生命周期，如果每天增量同步数据，则一年有365条数据，而拉链表只会产生4条数据

制作拉链表

首先初始化拉链表

通常要在原表的基础上加入start_date,end_date,并从原表导入数据到拉链表

drop table if exists dwd_order_info_his;
create external table dwd_order_info_his(
    `id` string COMMENT '订单编号',
    `order_status` string COMMENT '订单状态',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链表'

创建临时表整个中间数据

临时表和拉链表表结构一致，只是为了存储中间过程。

drop table if exists dwd_order_info_his_tmp;
create external table dwd_order_info_his_tmp(
    `id` string COMMENT '订单编号',
    `order_status` string COMMENT '订单状态',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链临时表'

向临时表中插入数据

插入前，拉链表数据

订单新增表数据

插入数据后

订单1和订单2，订单状态都发生了改变，产生了新数据

具体sql

insert overwrite table dwd_order_info_his_tmp
select * from 
(
select 
id,
    total_amount,
    order_status,
    user_id,
    payment_way,
    out_trade_no,
    create_time,
    operate_time,
    '2019-02-14' start_date,
    '9999-99-99' end_date
from dwd_order_info where dt='2019-02-14'

union all 
select oh.id,
    oh.total_amount,
    oh.order_status,
    oh.user_id,
    oh.payment_way,
    oh.out_trade_no,
    oh.create_time,
    oh.operate_time,
    oh.start_date,
    if(oi.id is null, oh.end_date, date_add(oi.dt,-1)) end_date
from dwd_order_info_his oh left join 
     (
select
*
from dwd_order_info
where dt='2019-02-14'
) oi
     on oh.id=oi.id   
)his 
order by his.id, start_date;

分为两个部分数据 第一部分把新增修改表直接插入，这部分数据都是新鲜的，end_date是9999-99-99

第二部分是原拉链表和新增变化表做左连接，原表存在id且终止时间为无限的数据会保留，说明这部分数据需要变更。（如果右表id为空，说明这部分数据没有任何改变，end_date保持不变，如果右表数据存在，说明经过了改变，则，end_date需要减1），所以这部分完成了历史数据的更改

将拉链表数据用临时表覆盖

因为hive修改字段值不方便，直接全变覆盖

insert overwrite table dwd_order_info_his 
select * from dwd_order_info_his_tmp;