0


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

一、全量表

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

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

二、增量表

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

三、快照表

按日分区,记录截止数据日期的全量数据(每个分区都是记录截止当前分区日期的全量数据)。

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

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

四、切片表

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

五、拉链表

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;
标签: 数据仓库

本文转载自: https://blog.csdn.net/chimchim66/article/details/126939159
版权归原作者 chimchim66 所有, 如有侵权,请联系我们删除。

“数仓之全量表、增量表、快照表、切片表、拉链表”的评论:

还没有评论