1.背景
拉链表是什么，在数仓建设时候，一种重要的表数据处理形式，能够将数据结构于算法，类比于拉链表于数仓，旨在解决数仓建设外面的SCD需要，那么什么是SCD，就是迟缓变动维，随着工夫流逝，数据绝对事实表产生迟缓变动。
SCD的罕用解决形式有以下几种：
- 保留原值
- 间接笼罩
- 减少新属性列
- 快照表
- 拉链表
本文次要解说拉链表来解决SCD的问题，其特点演绎以下，有以下几种场景时候，能够应用拉链表。
1.表数据量较大，用全量表会占用很多存储
2.表数据会有批改，用增量表，难以解决反复且批改数据
3.有回溯的需要，须要晓得历史某个工夫点的全量数据
4.数据有批改，然而频率和量不是很大比方只有百万分之一有批改
2.拉链表处理实践
首先拉链表是一个全量表且不是分区表，为了达到后面形容的各种成果，必然须要一个两头表来做两头跳板，这个两头跳板表是一个分区表，数据是增量数据，增量内容包含批改和减少，即经常是create_time or update_time 落在以后天，对于拉链表须要减少两个与原始数据没有关系的两个字段来标识数据开始工夫和无效截至工夫，在示例中，这两个日期别离为start_date 和 end_date ，拉链表其解决形式次要有以下三种：初始化，每天更新数据，回滚数据。
2.1 初始化和新增数据
其每天的滚动形式如下图：
初始化局部，是拉链全量表的开始工夫，也奠定了回滚时候可能回滚的最早工夫，每天更新逻辑如上图，新增数据会分为两局部，一部分是每天新增的数据，对于当天分区外面有雷同变动或者未变动的数据时候，别离批改对应的start_date 和 end_date 即可达到更新数据。
2.1 数据回滚
对于下面的更新逻辑，咱们来思考如何回滚数据，即回到历史的某个工夫点，对于拉链表来说是全量表，所以只有一个回滚即可。回滚策略能够依据回滚工夫点和数据生成的start_date 和 end_date，具体怎么回滚，咱们来看上面的示意图：
在end_date < rollback_date 的数据要保留，对于解决end_date ≥ rollback_date ≥ start_date 设置end_date 为9999-12-31 ，对于回滚的后果，个别为了保持数据的完整性，能够将回滚的数据放在一个新的拉链长期表中。
3.拉链表处理案例
对于数仓的罕用分层DIM即维度层是拉链表的罕用场景，上面有个例子拉看看拉链表怎么做新增和回滚。
用拉链表实现外围交易剖析中DIM层商家维表，并实现该拉链表的回滚。
3.1 创立表并导入数据
其中商家维表构造如下：
--创立商家信息表(增量表 分区表)drop table if exists ods.ods_trade_shops;create table ods.ods_trade_shops( `shopid` int COMMENT '商铺ID', `userid` int COMMENT '商铺负责人', `areaid` int COMMENT '区域ID', `shopname` string COMMENT '商铺名称', `shoplevel` int COMMENT '商铺等级', `status` int COMMENT '商铺状态', `createtime` string COMMENT '创立日期', `modifytime` string COMMENT '批改日期') COMMENT '商家信息表'PARTITIONED BY (`dt` string)row format delimited fields terminated by ',';-- 创立商家信息维表drop table if exists dim.dim_trade_shops;create table dim.dim_trade_shops( `shopid` int COMMENT '商铺ID', `userid` int COMMENT '商铺负责人', `areaid` int COMMENT '区域ID', `shopname` string COMMENT '商铺名称', `shoplevel` int COMMENT '商铺等级', `status` int COMMENT '商铺状态', `createtime` string COMMENT '创立日期', `modifytime` string COMMENT '批改日期', `startdate` string COMMENT '失效起始日期', `enddate` string COMMENT '生效完结日期') COMMENT '商家信息表';导入以下测试数据：
/root/data/shop-2020-11-20.dat100050,1,100225,WSxxx营超市,1,1,2020-06-28,2020-11-20 13:22:22100052,2,100236,陈腐xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22100053,3,100011,华为xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22100054,4,100159,小米xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22100055,5,100211,苹果xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22 /root/data/shop-2020-11-21.dat100057,7,100311,三只xxx鼠零食,1,1,2020-06-28,2020-11-21 13:22:22100058,8,100329,良子xxx铺美食,1,1,2020-06-28,2020-11-21 13:22:22100054,4,100159,小米xxx旗舰店,2,1,2020-06-28,2020-11-21 13:22:22100055,5,100211,苹果xxx旗舰店,2,1,2020-06-28,2020-11-21 13:22:22 /root/data/shop-2020-11-22.dat100059,9,100225,乐居xxx日用品,1,1,2020-06-28,2020-11-22 13:22:22100060,10,100211,同仁xxx大衰弱,1,1,2020-06-28,2020-11-22 13:22:22100052,2,100236,陈腐xxx旗舰店,1,2,2020-06-28,2020-11-22 13:22:22load data local inpath '/root/data/shop-2020-11-20.dat' overwrite into table ods.ods_trade_shops partition(dt='2020-11-20');load data local inpath '/root/data/shop-2020-11-21.dat' overwrite into table ods.ods_trade_shops partition(dt='2020-11-21');load data local inpath '/root/data/shop-2020-11-22.dat' overwrite into table ods.ods_trade_shops partition(dt='2020-11-22');3.2 拉链表初始化
假如将第一天数据作为历史的所有数据
INSERT OVERWRITE TABLE dim.dim_trade_shopsSELECT shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime, CASE WHEN modifytime IS NOT NULL THEN substr(modifytime, 0, 10) ELSE substr(createtime, 0, 10) END AS startdate, '9999-12-31' AS enddateFROM ods.ods_trade_shopsWHERE dt ='2020-11-20';3.3 更新拉链表
对于增量表，个别的逻辑是，create_time或者modifytime的截取作为当天分区dt，modifytime 大于等于create_time ,这里取前两个
INSERT OVERWRITE TABLE dim.dim_trade_shopsSELECT shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime, CASE WHEN modifytime IS NOT NULL THEN substr(modifytime, 0, 10) ELSE substr(createtime, 0, 10) END AS startdate, '9999-12-31' AS enddateFROM ods.ods_trade_shopsWHERE dt = '2020-11-21'UNION ALLSELECT b.shopid, b.userid, b.areaid, b.shopname, b.shoplevel, b.status, b.createtime, b.modifytime, b.startdate, CASE WHEN a.shopid IS NOT NULL AND b.enddate ='9999-12-31' THEN date_add('2020-11-21', -1) ELSE b.enddate END AS enddateFROM (SELECT * FROM ods.ods_trade_shops WHERE dt='2020-11-21') aRIGHT JOIN dim.dim_trade_shops b ON a.shopid = b.shopid;加载拉链表的脚本如下：
dim_load_shops.sh
#!/bin/bash source /etc/profileif [ -n "$1" ]then do_date=$1else do_date=`date -d "-1 day" +%F`fi sql="INSERT OVERWRITE TABLE dim.dim_trade_shopsSELECT shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime, CASE WHEN modifytime IS NOT NULL THEN substr(modifytime, 0, 10) ELSE substr(createtime, 0, 10) END AS startdate, '9999-12-31' AS enddateFROM ods.ods_trade_shopsWHERE dt = '$do_date'UNION ALLSELECT b.shopid, b.userid, b.areaid, b.shopname, b.shoplevel, b.status, b.createtime, b.modifytime, b.startdate, CASE WHEN a.shopid IS NOT NULL AND b.enddate ='9999-12-31' THEN date_add('$do_date', -1) ELSE b.enddate END AS enddateFROM (SELECT * FROM ods.ods_trade_shops WHERE dt='$do_date') aRIGHT JOIN dim.dim_trade_shops b ON a.shopid = b.shopid;" hive -e "$sql"能够执行此脚本来加载2020-12-22 的数据，sh dim_load_shops.sh 2020-12-22
3.4 回滚拉链表到某一时间点
先创立一个长期表，tmp.shops_tmp 用来放回滚的数据
DROP TABLE IF EXISTS tmp.shops_tmp;CREATE TABLE IF NOT EXISTS tmp.tmp_shops ASSELECT shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime, startdate, enddateFROM dim.dim_trade_shopsWHERE enddate < '2020-11-21'UNION ALLSELECT shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime, startdate, '9999-12-31' AS enddateFROM dim.dim_trade_shopsWHERE startdate <= '2020-11-21' AND enddate >= '2020-11-21';INSERT OVERWRITE TABLE dim.dim_trade_shopsSELECT *FROM tmp.tmp_shops;回滚脚本和更新脚本相似，只有更新其中的sql即可，这里不再反复。
吴邪，小三爷，混迹于后盾，大数据，人工智能畛域的小菜鸟。
更多请关注