数仓中的全量表、增量表、快照表、切片表和拉链表区别和使用场景
一、全量表：df表，有无变化都要上报，只有一个分区或者没有分区，每次往全量表里面写数据都会覆盖之前的数据，不能记录数据的历史变化，只能截止到当前最新、全量的数据
二、增量表：每天新增的数据和改变的数据都会存储在当日的分区中；增量表记录每次增加的量，只报变化量，无变化的不用报；增量表设计过程，假设以18号与19号数据为例
-- 1、找出19号未修改的数据,写入当前分区 INSERT OVERWARITE dwd.user_info_di PARTITION(p_dymd='2021-12-19') WITH t1 AS ( SELECT * FROM dwd.user_info_di WHERE p_dymd = '2021-12-18' ), t2 AS ( SELECT * FROM ods.user_info WHERE p_dymd = '2021-12-19' ) SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.user_id = t2.user_id WHERE t2.user_id is null -- 2、将19号新增和变化的数据写入当前分区 INSERT INTO TABLE dwd.user_info_di PARTITION(p_dymd='2021-12-19') SELECT * FROM ods.user_info WHERW p_dymd = '2021-12-19'
-- 3、一般增量表只保存7天的数量
ALTER TABLE dwd.user_info_di DROP IF EXIST PARTITION(p_dymd='2021-12-19')
特殊增量表：da表，一般是埋点日志等，每天的数据存储在当天的分区里面，数据产生之后就不会再发生变化了。
三、快照表：因为全量表无法反映历史的变化，这时快照表就可以使用了，快照表记录截止数据日期的全量数据（每个分区都是记录截止当前分区日期的全量数据），但是在数据量大的情况下，每个分区存储的都是全量数据，数据冗余和浪费存储空间；
四、切片表：根据基础表，往往只反映某一个维度的相应数据，数据只有一个维度或者某一事实条件数据；
五、拉链表：能够解决快照表数据冗余问题，还能维护数据历史状态和最新状态，记录截止数据日期的全量数据，一个事物从开始，一直到当前状态的所有变化信息；
- 缓慢变化维（SCD）：拉链表不是SCD，它只是用来处理缓慢变化维的一种手段而已。将分析的角度存放在维度表中，但维度表里的数据可能发生一些变化，尽管可能跨越很久，例如用户信息表；缓慢变化维处理的方式一般如下：
1）重写覆盖：与业务系统保持一致，直接更新为最新的状态数据即可；适用于：数据必须正确，比如客户的身份证；不需要考虑历史变化维度，没有意义没有价值的维度；优点是直接更新即可，缺点无法恢复，不能查看历史变化
2）增加新行：更新历史数据时间戳，新增新行记录新值；适用于仅需保持历史数据的业务场景，相应的事实表的的关联需要更新为最新的id
3）增加新列：如果某个维度发生多次变化，会产生列爆炸
4）拉链表
- 拉链表的具体使用
1）同步ods层数据
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 ','; ../shops/shop-2020-07-01.dat 100050,1,100225,WSxxx营超市,1,1,2020-06-28,2020-07-01 13:22:22 100052,2,100236,新鲜xxx旗舰店,1,1,2020-06-28,2020-07-01 13:22:22 100053,3,100011,华为xxx旗舰店,1,1,2020-06-28,2020-07-01 13:22:22 100054,4,100159,小米xxx旗舰店,1,1,2020-06-28,2020-07-01 13:22:22 100055,5,100211,苹果xxx旗舰店,1,1,2020-06-28,2020-07-01 13:22:22 ../shops/shop-2020-07-02.dat 100057,7,100311,三只xxx鼠零食,1,1,2020-06-28,2020-07-02 13:22:22 100058,8,100329,良子xxx铺美食,1,1,2020-06-28,2020-07-02 13:22:22 100054,4,100159,小米xxx旗舰店,2,1,2020-06-28,2020-07-02 13:22:22 100055,5,100211,苹果xxx旗舰店,2,1,2020-06-28,2020-07-02 13:22:22 -- 加载数据 load data local inpath '/app/softwares/2020-07-01.dat' into table ods.ods_trade_shops partition(dt='2020-07-01')
2）创建拉链表
drop table if exists dim.dim_trade_shops_dz; create table dim.dim_trade_shops_dz( `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 '商家信息表';
3）初始化拉链表
-- 初始化拉链表 insert overwrite table dim.dim_trade_shops_dz 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-07-01'; -- 2号维表发生变化：1、取出当天记录与前一天关联，存在表示维表更新，将失效时间记为前一天 insert overwrite table dim.dim_trade_shops_dz 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-07-02' 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-07-02',-1) ELSE b.enddate end as enddate from (select * from ods.ods_trade_shops where dt='2020-07-02') a right join dim.dim_trade_shops b on a.shopid = b.shopid;
