数仓面试题，表类型问题(流水、快照、拉链)(二)

媛数据 2021-04-08

2167

“ 上节主要讲了数仓中几个模型，及其atlas元数据管理。这一节我们来讲面试题中常见的其它问题，比如几种常见的表，拉链表怎么处理，还有缓慢变化维”

问题：

数仓各层中分别用了哪些表类型(流水、快照、拉链)，拉链表增量更新与回滚整明白

重点

—

数仓各层的表类型

数据仓库之拉链表，流水表，全量表，增量表

全量表：每天的所有写最新状态的数据

1、有无变化，都要报

2、每次上报的数据都是所有的数据（变化的+没有变化的）

增量表：新增的数据

1、增量表，只报变化量，无变化不用报

拉链表：

1、记录一个事物从开始，一直到当前状态的所有变化的信息

2、拉链表每次上报的都是历史记录的最终状态，是记录在当前时候的历史总量

流水表：对于表的每一个修改都会记录，可以用户反映实际记录的变更

1、拉链表通常是对账户信息的历史变动进行处理保留的结果，流水表是对每天的交易形成的历史

2、流水表用于统计业务相关情况，拉链表用于统计账户及客户的情况

快照表：数据包含前一天的全量数据，按照每一天进行分区

所有表的优缺点对比：

全量表：可存放最新记录和历史，但是存储消耗大。

快照表：也可以查看历史记录，但是存储消耗大。

拉链表：既可以满足查看历史记录，又可以减少存储消耗。

流水表：在拉链表基础上当需要知道更细的修改记录时，需查看流水表。

增量表：适合订单等存储，而流水表更倾向于用户信息等的变更，两者应用环境不同。

拉链表的应用案例：

案例：

一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些。

表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。

需求：

需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。

表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

方案比较：

方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。

每天drop掉前一天的数据，重新抽一份最新的。

优点：节省数据存储空间，使用数据不用加分区

缺点：没有历史数据，数据资产不完整

方案二：

优点：每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。

缺点：有部分数据每天都是重复的，存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费。

方案三：

1、记录一个事物从开始，一直到当前状态的所有变化的信息

2、拉链表每次上报的都是历史记录的最终状态，是记录在当前时候的历史总量

如何设计一张拉链表？

在2017-01-01这一天表中的数据是：

注册日期用户编号手机号码
2017-01-01 001 111111
2017-01-01 002 222222
2017-01-01 003 333333
2017-01-01 004 444444
在2017-01-02这一天表中的数据是，用户002和004资料进行了修改，005是新增用户：

注册日期用户编号手机号码备注
2017-01-01 001 111111
2017-01-01 002 233333 （由222222变成233333）
2017-01-01 003 333333
2017-01-01 004 432432 （由444444变成432432）
2017-01-02 005 555555 （2017-01-02新增）
在2017-01-03这一天表中的数据是，用户004和005资料进行了修改，006是新增用户：

注册日期用户编号手机号码备注
2017-01-01 001 111111
2017-01-01 002 233333
2017-01-01 003 333333
2017-01-01 004 654321 （由432432变成654321）
2017-01-02 005 115115 （由555555变成115115）
2017-01-03 006 666666 （2017-01-03新增）
如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表，这是最新一天（即2017-01-03）的数据：

注册日期用户编号手机号码 t_start_date t_end_date
2017-01-01 001 111111 2017-01-01 9999-12-31
2017-01-01 002 222222 2017-01-01 2017-01-01
2017-01-01 002 233333 2017-01-02 9999-12-31
2017-01-01 003 333333 2017-01-01 9999-12-31
2017-01-01 004 444444 2017-01-01 2017-01-01
2017-01-01 004 432432 2017-01-02 2017-01-02
2017-01-01 004 654321 2017-01-03 9999-12-31
2017-01-02 005 555555 2017-01-02 2017-01-02
2017-01-02 005 115115 2017-01-03 9999-12-31
2017-01-03 006 666666 2017-01-03 9999-12-31

ods层的用户资料切片表：

CREATE EXTERNAL TABLE ods.user ( user_num STRING COMMENT '用户编号', mobile STRING COMMENT '手机号码', reg_date STRING COMMENT '注册日期'COMMENT '用户资料表'PARTITIONED BY (dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS ORC LOCATION '/ods/user';)

ods层的user_update表：

CREATE EXTERNAL TABLE ods.user_update ( user_num STRING COMMENT '用户编号', mobile STRING COMMENT '手机号码', reg_date STRING COMMENT '注册日期'COMMENT '每日用户资料更新表'PARTITIONED BY (dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS ORC LOCATION '/ods/user_update';)

拉链表：

CREATEEXTERNALTABLE dws.user_his ( user_num STRING COMMENT '用户编号', mobile STRING COMMENT '手机号码', reg_date STRING COMMENT '用户编号', t_start_date , t_end_date )COMMENT '用户资料拉链表'ROW FORMAT DELIMITED FIELDS TERMINATED BY'\t' LINES TERMINATED BY'\n'STORED AS ORC LOCATION '/dws/user_his';

假设我们已经已经初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的数据：

INSERT OVERWRITE TABLE dws.user_his

SELECT * FROM (

SELECT

A.user_num,

A.mobile,

A.reg_date,

A.t_start_time,

CASE WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01' ELSE

A.t_end_time END AS t_end_time

FROM dws.user_his AS A LEFT

JOIN

ods.user_update AS B ON A.user_num = B.user_num UNION SELECT C.user_num, C.mobile, C.reg_date, '2017-01-02' AS t_start_time, '9999-12-31' AS t_end_time FROM ods.user_update AS C ) AS T

拉链表增量更新和回滚：

1 背景

　　本文前面的内容时参考了'lxw的大数据田地',具体可查看最后的'参考文章'，个人加入了'拉链表的回滚'部分的内容sql，如果有实践的，可以互相交流学习，谢谢

　　在数据仓库的数据模型设计过程中，经常会遇到这样的需求：

　　　　1.1 数据量比较大;

　　　　1.2 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;

　　　　1.3 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等;

　　　　1.4 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;

　　综上所述：引入'拉链历史表'，既能满足反应数据的历史状态，又可以最大程度的节省存储;

2 具体表结构

　　2.1 例如

　　　　有一张订单表,6月20号有3条记录：

订单创建日期	订单编号	订单状态
2012-06-20	001	创建订单
2012-06-20	002	创建订单
2012-06-20	003	支付完成

　　　　到6月21日，表中有5条记录：

订单创建日期	订单编号	订单状态
2012-06-20	001	支付完成（从创建到支付）
2012-06-20	002	创建订单
2012-06-20	003	支付完成
2012-06-21	004	创建订单
2012-06-21	005	创建订单

　　　　到6月22日，表中有6条记录：

订单创建日期	订单编号	订单状态
2012-06-20	001	支付完成（从创建到支付）
2012-06-20	002	创建订单
2012-06-20	003	已发货（从支付到发货）
2012-06-21	004	创建订单
2012-06-21	005	支付完成（从创建到支付）
2012-06-22	006	创建订单

　　2.2 常用的解决方案以及存在的问题：

　　　　1 快照表：只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；

　　　　2 全量历史表：每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；

　　2.3 如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表：

订单创建日期	订单编号	订单状态	dw_begin_date	dw_end_date
2012-06-20	001	创建订单	2012-06-20	2012-06-20
2012-06-20	001	支付完成	2012-06-21	9999-12-31
2012-06-20	002	创建订单	2012-06-20	9999-12-31
2012-06-20	003	支付完成	2012-06-20	2012-06-21
2012-06-20	003	已发货	2012-06-22	9999-12-31
2012-06-21	004	创建订单	2012-06-21	9999-12-31
2012-06-21	005	创建订单	2012-06-21	2012-06-21
2012-06-21	005	支付完成	2012-06-22	9999-12-31
2012-06-22	006	创建订单	2012-06-22	9999-12-31

　　　　说明：

　　　　　　2.3.1 dw_begin_date表示该条记录的生命周期开始时间（周期快照时的状态），dw_end_date表示该条记录的生命周期结束时间；

　　　　　　2.3.2 dw_end_date = '9999-12-31'表示该条记录目前处于有效状态；

　　　　　　2.3.3 如果查询当前所有有效的记录，则select * from order_his where dw_end_date = '9999-12-31'

　　　　　　2.3.4 如果查询2012-06-21的历史快照，则select * from order_his where dw_begin_date <= '2012-06-21' and end_date >='2012-06-21'，这条语句会查询到以下记录：

订单创建日期	订单编号	订单状态	dw_begin_date	dw_end_date
2012-06-20	001	支付完成	2012-06-21	9999-12-31
2012-06-20	002	创建订单	2012-06-20	9999-12-31
2012-06-20	003	支付完成	2012-06-20	2012-06-21
2012-06-21	004	创建订单	2012-06-21	9999-12-31
2012-06-21	005	创建订单	2012-06-21	2012-06-21

　　　　　　　　和源表在6月21日的记录完全一致：

订单创建日期	订单编号	订单状态
2012-06-20	001	支付完成（从创建到支付）
2012-06-20	002	创建订单
2012-06-20	003	支付完成
2012-06-21	004	创建订单
2012-06-21	005	创建订单

　　可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；

3 拉链表更新方案

　　假设：

　　　　3.1 前提：

　　　　　　3.1.1 数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据；

　　　　　　3.1.2 如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史；

　　　　　　3.1.3 订单状态包括三个：创建、支付、完成；

　　　　　　3.1.4 创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；

　　　　　　　　-- 例如DB中的binlog解析，或者通过sqoop同步，只同步有过修改的数据(新增 or 修改)

　　　　　　3.1.5 本文中的表和SQL都使用Hive的HQL语法；

　　　　　　3.1.6 源系统中订单表结构为：

　　　　　　　　CREATE TABLE orders (
　　　　　　　　　　orderid INT,
　　　　　　　　　　createtime STRING,
　　　　　　　　　　modifiedtime STRING,
　　　　　　　　　　status STRING
　　　　　　　　) stored AS textfile;

　　　　3.2 在数据仓库的ODS层，有一张订单的增量数据表，按天分区，存放每天的增量数据：

　　　　　　CREATE TABLE t_ods_orders_inc (
　　　　　　　　orderid INT,
　　　　　　　　createtime STRING,　　
　　　　　　　　modifiedtime STRING,
　　　　　　　　status STRING
　　　　　　) PARTITIONED BY (day STRING)
　　　　　　stored AS textfile;

　　　　3.3 在数据仓库的DW层，有一张订单的历史数据拉链表，存放订单的历史状态数据：

            CREATE TABLE t_dw_orders_his (
　　　　　　　　orderid INT,
　　　　　　　　createtime STRING,　　
　　　　　　　　modifiedtime STRING,
　　　　　　　　status STRING,
　　　　　　　　dw_start_date STRING,
　　　　　　　　dw_end_date STRING
　　　　　　) stored AS textfile;

　　3.4 2015-08-21至2015-08-23，每天原系统订单表的数据如下，红色标出的为当天发生变化的订单，即增量数据：

　　3.5 具体步骤：

　　　　在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前，需要做一次全量的初始化，就是从源订单表中昨天以前的数据全部抽取到ODW，并刷新到DW。

　　　　以上面的数据为例，比如在2015-08-21这天做全量初始化，那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW：

　　　　3.5.1 抽取全量数据到ODS：

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-20′)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime <= ‘2015-08-20′;

　　　　3.5.2 从ODS刷新到DW：

INSERT overwrite TABLE t_dw_orders_his
SELECT orderid,createtime,modifiedtime,status,
createtime AS dw_start_date,
‘9999-12-31′ AS dw_end_date
FROM t_ods_orders_inc
WHERE day = ‘2015-08-20′;

　　　　　　完成后，DW订单历史表中数据如下：

spark-sql> select * from t_dw_orders_his;
1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
3 2015-08-19 2015-08-21 支付 2015-08-19 9999-12-31
4 2015-08-19 2015-08-21 完成 2015-08-19 9999-12-31
5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31
7 2015-08-20 2015-08-21 支付 2015-08-20 9999-12-31
Time taken: 2.296 seconds, Fetched 7 row(s)

　　3.5.3 增量抽取

　　　　每天，从源系统订单表中，将前一天的增量数据抽取到ODS层的增量数据表。
　　　　这里的增量需要通过订单表中的创建时间和修改时间来确定：

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '${day}')
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = '${day}' OR modifiedtime = '${day}';

　　　　注意：在ODS层按天分区的增量表，最好保留一段时间的数据，比如半年，为了防止某一天的数据有问题而回滚重做数据。

　　3.5.4 增量刷新历史数据

　　　　从2015-08-22开始，需要每天正常刷新前一天（2015-08-21）的增量数据到历史表。

　　　　3.5.4.1 通过增量抽取，将2015-08-21的数据抽取到ODS：

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-21')
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = '2015-08-21' OR modifiedtime = '2015-08-21';

　　　　　　ODS增量表中2015-08-21的数据如下：

spark-sql> select * from t_ods_orders_inc where day = '2015-08-21';
3 2015-08-19 2015-08-21 支付 2015-08-21
4 2015-08-19 2015-08-21 完成 2015-08-21
7 2015-08-20 2015-08-21 支付 2015-08-21
8 2015-08-21 2015-08-21 创建 2015-08-21
Time taken: 0.437 seconds, Fetched 4 row(s)

　　　　3.5.4.2 通过DW历史数据(数据日期为2015-08-20)，和ODS增量数据(2015-08-21)，刷新历史表：

先把数据放到一张临时表中：

DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp 
AS
SELECT 
orderid,
createtime,
modifiedtime,
status,
dw_start_date,
dw_end_date
FROM 
(SELECT 
a.orderid,
a.createtime,
a.modifiedtime,
a.status,
a.dw_start_date,
CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date
FROM t_dw_orders_his a
left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-21') b
ON (a.orderid = b.orderid)

UNION ALL

SELECT 
orderid,
createtime,
modifiedtime,
status,
modifiedtime AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM t_ods_orders_inc
WHERE day = '2015-08-21'
) x
ORDER BY orderid,dw_start_date;

其中：　　

　　　　UNION ALL的两个结果集中，第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量，能关联上的，并且dw_end_date > ${yyy-MM-dd}，说明状态有变化，则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的，说明状态无变化，dw_end_date无变化。
　　　　第二个结果集是直接将增量数据插入历史表。

　　　3.5.5 最后把临时表中数据插入历史表：

INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;

　　　　刷新完后，历史表中数据如下：

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20
4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31
7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
8 2015-08-21 2015-08-21 创建 2015-08-21 9999-12-31
Time taken: 0.717 seconds, Fetched 11 row(s)

由于在2015-08-21做了8月20日以前的数据全量初始化，而订单3、4、7在2015-08-21的增量数据中也存在，因此都有两条记录，但不影响后面的查询。

4 拉链表回滚

　　4.1 具体操作方案

　　　　假设恢复到t天之前的数据，即未融合t天数据之前的拉链表，假设标记的开始日期和结束日期分别为s、t，具体分析如下：

1 当t-1>e时，s数据、e数据在t天之前产生，保留即可
2 当t-1=e时，e数据在t天产生，需修改
3 当s<t<=e时，e数据在t+n天产生，需修改
4 当s>=t时，s数据、e数据在t+n天产生，删除即可

　　　　具体例子：

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
1       2015-08-18      2015-08-22      支付    2015-08-22      2015-08-22
1       2015-08-18      2015-08-23      完成    2015-08-23      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      2015-08-22
3       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      2015-08-22
5       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      2015-08-21
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21
8       2015-08-21      2015-08-22      支付    2015-08-22      2015-08-22
8       2015-08-21      2015-08-23      完成    2015-08-23      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
11      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
12      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
13      2015-08-23      2015-08-23      支付    2015-08-23      9999-12-31

　　　　比如在插入2015-08-23的数据后，回滚2015-08-22的数据，使拉链表与2015-08-21的一致，具体操作过程如下

1 增加临时表t_dw_orders_his_tmp1，用来记录t-1>e的数据
CREATE TABLE t_dw_orders_his_tmp1
AS
SELECT 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　dw_end_date
FROM 
　　t_dw_orders_his
WHERE 
　　dw_end_date < '2015-08-21'

3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20

2 增加临时表t_dw_orders_his_tmp2，用来记录t-1=e的数据 
CREATE TABLE t_dw_orders_his_tmp2 
AS 
SELECT 　　
　　orderid,
　　createtime, 　　
　　modifiedtime, 　　
　　status, 　　
　　dw_start_date, 　　
　　'9999-12-31' AS dw_end_date 
FROM 
　　t_dw_orders_his
WHERE 
　　dw_end_date = '2015-08-21'

1       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      9999-12-31

 
3 增加临时表t_dw_orders_his_tmp3，用来记录s<t<=e的数据
CREATE TABLE t_dw_orders_his_tmp3
AS
SELECT 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　'9999-12-31' dw_end_date
FROM 
　　t_dw_orders_his
WHERE 
　　dw_start_date < '2015-08-22' AND dw_end_date >= '2015-08-22'

3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31

4 所有数据插入新表t_dw_orders_his_new
CREATE TABLE t_dw_orders_his_new
AS
SELECT * FROM t_dw_orders_his_tmp1
UNION ALL
SELECT * FROM t_dw_orders_his_tmp2
UNION ALL
SELECT * FROM t_dw_orders_his_tmp3

1       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31 
6       2015-08-20      2015-08-20      创建    2015-08-20      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      9999-12-31

与原数据一致，验证无错

　　4.2 备用方案

　　　　可以采用备份的方案，保证无误和可行。（保存增量数据，并对t_dw_orders_his表每个月备份一次全量数据。如需回滚，最多重跑30天数据即可）

—

处理缓慢变化维策略

什么是维度

在数据仓库的DW层中，表根据用途往往会分为2个类型：FACT（事实表）和 DIM（维度表）。

举个例子，如果我们要描述一个餐饮过程：

小明 2020年4月19日下午3点20分在海底捞(万达广场) 吃了5道菜，每道菜的单价是4元，总价是20元。

那么这个过程在数仓中，会如此划分：

事实：餐饮过程，单价、数量、总价

维度：小明，餐饮时间，餐饮门店，菜名。

也就是说：

吃了多少东西，多少钱——这些属于事实；

在哪里吃、什么时候吃？这些属于维度。

缓慢变化维概念介绍

什么是缓慢变化维

缓慢变化维（Slowly Changing Dimensions,SCD）: 它的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流失发生缓慢的变化。

2.怎样处理缓慢变化维？

三种常见的方法

直接覆盖原值 ---无法看到历史值
直接添加维度行---关联使用的时候，没法区分该用哪条数据
添加维度行 + 代理键 + 时间序列 ----数据标识哪条是最新变更，且有效数据
添加属性列---将后面的数据变为新属性，之前的成为旧属性(优点：可以同时分析当前及前一次变化的属性值)

处理方法通常分为3种：

假设有这样一条数据：

id name city

101 luna Chongqing

现在luna离开重庆，前往成都分公司工作，所以需要对city数据进行更新

第一种：直接覆盖原值

id name city

101 luna Chengdu

这样处理，最容易实现，但是没有保留历史数据，无法分析历史变化信息

第二种：添加维度行

当有维度属性发生变化时，生成一条新的维度记录，如下：

id name city

101 luna Chongqing

101 luna Chengdu

但是这样，当与别的表通过id关联时，有两个101的id数据，这样是有问题的；所以就需要代理键的支持。（在数据仓库的术语里面，这个唯一标识数据仓库表记录的键我们称之为 Surrogate Key 代理键）

sk_id id name city

001 101 luna Chongqing

002 101 luna Chengdu

现在每条数据都唯一，但又一个问题，现在不知道哪个是当前在用的数据，虽然可以通过代理键找最大的（因为主键往往是自增的，最大的通常是最新的数据），但某些情况下要插入历史数据就不好找了，所以在维度表中加入时间序列,用NULL来标识哪条是当前最新数据，有变化再进行更新。

sk_id id name city start_time end_tinme

001 101 luna Chongqing 2017/08/27 2018/06/20

002 101 luna Chengdu 2018/06/20 NULL

第三种：添加属性列

id name curr_city old_city

101 luna Chengdu Chongqing

#我是媛姐，一枚有多年大数据经验的程序媛，早期做过电商也有外贸，打过螺丝搬过砖，关注数仓，关注分析。愿你我走得更远！

面试

文章转载自媛数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数仓面试题，表类型问题(流水、快照、拉链)(二)

数据仓库之拉链表，流水表，全量表，增量表

什么是维度

评论