数仓之全量表、增量表、快照表、切片表、拉链表

目录

一、全量表

二、增量表

三、快照表

四、切片表

五、拉链表

1、概念

2、拉链表的使用场景

3、拉链表的实现方法

1.同步ods数据

2.创建拉链表

3.初始化拉链表

4.新增增量数据

5.对比上日变化数据关链操作

---

一、全量表

记录每天的所有的最新状态的数据，有无变化都要上报，每次往全量表里面写数据都会覆盖之前的数据

缺点：不能记录数据的历史变化，只能截止到当前最新、全量的数据

二、增量表

记录每天的新增的数据和改变的数据。

三、快照表

按日分区，记录截止数据日期的全量数据（每个分区都是记录截止当前分区日期的全量数据）。

优点：可以反映历史的变化 

缺点：在数据量大的情况下，每个分区存储的都是全量数据，数据冗余和浪费存储空间

四、切片表

切片表根据基础表，往往只反映某一个维度的相应数据。其表结构与基础表结构相同，但数据往往只有某一维度，或者某一个事实条件的数据

五、拉链表

1、概念

记录一个事物从开始，一直到当前状态的所有变化的信息。（极限存储）

优点：能够解决快照表数据冗余问题，还能维护数据历史状态和最新状态，记录截止数据日期的全量数据

2、拉链表的使用场景

缓慢变化维SCD（表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等；表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。）

数据量很大（比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些；需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态）

3、拉链表的实现方法

1.同步ods数据

drop table if exists ods.shops;
create table ods.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 (`ds` string)
row format delimited fields terminated by ',';

../shops/shop-2022-09-19.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-2022-09-20.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 '/2022-09-19.dat' into table ods.shops partition(ds='2022-09-19')

2.创建拉链表

drop table if exists dim.shops_his;
create table dim.shops_his(
  `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  '修改日期',
  -- 拉链表新增两列，生效起始时间和失效结束时间
  `start_date` string  COMMENT '生效起始日期',
  `end_date` string  COMMENT '失效结束日期'
) comment '商家信息表';

3.初始化拉链表

-- 初始化拉链表
insert overwrite table dim.shops_his
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 start_date 
    ,'9999-12-31' as end_date
from ods.shops
where ds = '2022-09-19'; 

4.新增增量数据

insert into table dim.shops_his
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.shops
where ds = '2022-09-20'; 

5.对比上日变化数据关链操作

insert into table dim.shops_his
select 
     b.shopid
    ,b.userid
    ,b.areaid
    ,b.shopname
    ,b.shoplevel
    ,b.status
    ,b.createtime
    ,b.modifytime
    ,CASE WHEN a.shopid is not null and b.enddate ='9999-12-31' THEN date_add('2022-09-20',-1) 
          ELSE b.enddate 
     end as end_date 
     --上日若存在结束日期置为上日，不存在置为最大日期
from (select * from ods.shops where ds='2022-09-20') a
right join dim.shops_his b on a.shopid = b.shopid;