关于大数据:大数据开发数仓拉链表概述以及如何迭代或者回滚

46次阅读

共计 5272 个字符，预计需要花费 14 分钟才能阅读完成。

拉链表是什么，在数仓建设时候，一种重要的表数据处理形式，能够将数据结构于算法，类比于拉链表于数仓，旨在解决数仓建设外面的 SCD 需要，那么什么是 SCD，就是迟缓变动维，随着工夫流逝，数据绝对事实表产生迟缓变动。

SCD 的罕用解决形式有以下几种：

保留原值
间接笼罩
减少新属性列
快照表
拉链表

本文次要解说拉链表来解决 SCD 的问题，其特点演绎以下，有以下几种场景时候，能够应用拉链表。

1. 表数据量较大，用全量表会占用很多存储

2. 表数据会有批改，用增量表，难以解决反复且批改数据

3. 有回溯的需要，须要晓得历史某个工夫点的全量数据

4. 数据有批改，然而频率和量不是很大比方只有百万分之一有批改

首先拉链表是一个全量表且不是分区表，为了达到后面形容的各种成果，必然须要一个两头表来做两头跳板，这个两头跳板表是一个分区表，数据是增量数据，增量内容包含批改和减少，即经常是create_time or update_time 落在以后天，对于拉链表须要减少两个与原始数据没有关系的两个字段来标识数据开始工夫和无效截至工夫，在示例中，这两个日期别离为start_date 和 end_date，拉链表其解决形式次要有以下三种：初始化，每天更新数据，回滚数据。

其每天的滚动形式如下图：

初始化局部，是拉链全量表的开始工夫，也奠定了回滚时候可能回滚的最早工夫，每天更新逻辑如上图，新增数据会分为两局部，一部分是每天新增的数据，对于当天分区外面有雷同变动或者未变动的数据时候，别离批改对应的start_date 和 end_date 即可达到更新数据。

对于下面的更新逻辑，咱们来思考如何回滚数据，即回到历史的某个工夫点，对于拉链表来说是全量表，所以只有一个回滚即可。回滚策略能够依据回滚工夫点和数据生成的start_date 和 end_date，具体怎么回滚，咱们来看上面的示意图：

在end_date < rollback_date 的数据要保留，对于解决end_date ≥ rollback_date ≥ start_date 设置end_date 为9999-12-31，对于回滚的后果，个别为了保持数据的完整性，能够将回滚的数据放在一个新的拉链长期表中。

对于数仓的罕用分层 DIM 即维度层是拉链表的罕用场景，上面有个例子拉看看拉链表怎么做新增和回滚。

用拉链表实现外围交易剖析中 DIM 层商家维表，并实现该拉链表的回滚。

其中商家维表构造如下：

-- 创立商家信息表(增量表 分区表)
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.dat
100050,1,100225,WSxxx 营超市,1,1,2020-06-28,2020-11-20 13:22:22
100052,2,100236, 陈腐 xxx 旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100053,3,100011, 华为 xxx 旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100054,4,100159, 小米 xxx 旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100055,5,100211, 苹果 xxx 旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
 
 
/root/data/shop-2020-11-21.dat
100057,7,100311, 三只 xxx 鼠零食,1,1,2020-06-28,2020-11-21 13:22:22
100058,8,100329, 良子 xxx 铺美食,1,1,2020-06-28,2020-11-21 13:22:22
100054,4,100159, 小米 xxx 旗舰店,2,1,2020-06-28,2020-11-21 13:22:22
100055,5,100211, 苹果 xxx 旗舰店,2,1,2020-06-28,2020-11-21 13:22:22
 
 
/root/data/shop-2020-11-22.dat
100059,9,100225, 乐居 xxx 日用品,1,1,2020-06-28,2020-11-22 13:22:22
100060,10,100211, 同仁 xxx 大衰弱,1,1,2020-06-28,2020-11-22 13:22:22
100052,2,100236, 陈腐 xxx 旗舰店,1,2,2020-06-28,2020-11-22 13:22:22

load 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');

假如将第一天数据作为历史的所有数据

INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT 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 enddate
FROM ods.ods_trade_shops
WHERE dt ='2020-11-20';

对于增量表，个别的逻辑是，create_time或者 modifytime 的截取作为当天分区 dt，modifytime 大于等于create_time , 这里取前两个

INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT 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 enddate
FROM ods.ods_trade_shops
WHERE dt = '2020-11-21'
UNION ALL
SELECT 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 enddate
FROM
  (SELECT *
   FROM ods.ods_trade_shops
   WHERE dt='2020-11-21') a
RIGHT JOIN dim.dim_trade_shops b ON a.shopid = b.shopid;

加载拉链表的脚本如下：

dim_load_shops.sh

#!/bin/bash
 
source /etc/profile
if [-n "$1"]
then
  do_date=$1
else
  do_date=`date -d "-1 day" +%F`
fi
 
sql="
INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT 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 enddate
FROM ods.ods_trade_shops
WHERE dt = '$do_date'
UNION ALL
SELECT 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 enddate
FROM
  (SELECT *
   FROM ods.ods_trade_shops
   WHERE dt='$do_date') a
RIGHT 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

先创立一个长期表，tmp.shops_tmp 用来放回滚的数据

DROP TABLE IF EXISTS tmp.shops_tmp;
CREATE TABLE IF NOT EXISTS tmp.tmp_shops AS
SELECT shopid,
       userid,
       areaid,
       shopname,
       shoplevel,
       status,
       createtime,
       modifytime,
       startdate,
       enddate
FROM dim.dim_trade_shops
WHERE enddate < '2020-11-21'
UNION ALL
SELECT shopid,
       userid,
       areaid,
       shopname,
       shoplevel,
       status,
       createtime,
       modifytime,
       startdate,
       '9999-12-31' AS enddate
FROM dim.dim_trade_shops
WHERE startdate <= '2020-11-21'
  AND enddate >= '2020-11-21';


INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT *
FROM tmp.tmp_shops;

回滚脚本和更新脚本相似，只有更新其中的 sql 即可，这里不再反复。
吴邪，小三爷，混迹于后盾，大数据，人工智能畛域的小菜鸟。
更多请关注

正文完

大数据

发表至：大数据

2020-12-13

0

关于大数据:图解大数据-大数据分析挖掘Spark初步

关于大数据:抖音数据采集Frida教程Frida-Java-Hook-详解代码及示例下

关于大数据:火山引擎-ByteHouse两个关键技术揭秘-OLAP-引擎中的数据导入技术

关于大数据:星环科技TDH80使用必读2-10种数据模型全支持-未来属于多模型大数据平台

关于大数据:大数据开发数仓ads层指标计算

关于大数据:大数据开发数仓拉链表概述以及如何迭代或者回滚

1. 背景

2. 拉链表处理实践

2.1 初始化和新增数据

2.1 数据回滚

3. 拉链表处理案例

3.1 创立表并导入数据

3.2 拉链表初始化

3.3 更新拉链表

3.4 回滚拉链表到某一时间点

Just My Socks（注册教程内含优惠码）

关于大数据:大数据开发数仓拉链表概述以及如何迭代或者回滚

1. 背景

2. 拉链表处理实践

2.1 初始化和新增数据

2.1 数据回滚

3. 拉链表处理案例

3.1 创立表并导入数据

3.2 拉链表初始化

3.3 更新拉链表

3.4 回滚拉链表到某一时间点

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）