拉链表制作

1.拉链表的应用场景

拉链表适合于：数据会发生变化，但是大部分是不变化的（即：缓慢变化维。还需要保留历史数据做分析的场景）

2.拉链表的形成过程

关键的过程

第四步：获取变化的数据（创建和修改的数据）

第五步：创建和修改的得到维护开始日期和结束日期，匹配到历史拉链表中更新的数据、将结束日期修改为同步时间 减1

 3.拉链表的创建实操

3.1三张表的建表语句

dim_consignor_belong 原始同步表、每天全量同步，

dim_consignor_belong_lz 拉链表，

dim_consignor_belong_lz_temp 拉链临时表。

CREATE TABLE IF NOT EXISTS dim_consignor_belong(
    company_id BIGINT COMMENT '企业货主id',
    consignor STRING COMMENT '企业货主',
    belong_manager STRING COMMENT '归属业务经理',
    is_count BIGINT COMMENT '0 代表不统计，1代表统计') 
partition by (dt string) 
STORED AS ALIORC ;

CREATE TABLE IF NOT EXISTS dim_consignor_belong_lz(
    company_id BIGINT COMMENT '企业货主id',
    consignor STRING COMMENT '企业货主',
    belong_manager STRING COMMENT '归属业务经理',
    is_count BIGINT COMMENT '0 代表不统计，1代表统计',
    start_time string comment '有效开始时间',
    end_time string comment '有效结束时间') 
STORED AS ALIORC ;

CREATE TABLE IF NOT EXISTS dim_consignor_belong_lz_temp(
    company_id BIGINT COMMENT '企业货主id',
    consignor STRING COMMENT '企业货主',
    belong_manager STRING COMMENT '归属业务经理',
    is_count BIGINT COMMENT '0 代表不统计，1代表统计',
    start_time string comment '有效开始时间',
    end_time string comment '有效结束时间') 
STORED AS ALIORC ;

3.2初始化拉链表

insert overwrite table muyuanwuliu.dim_consignor_belong_lz 
select
company_id,
consignor,
belong_manager,
is_count,
'19000101',
'99999999'
from muyuanwuliu.dim_consignor_belong
where dt = '20230426';

 3.3得到变化的数据

这里根据数据的值来判断、如果能够同步的时候有创建时间和更新时间那就最方便了

select dim.company_id,
dim.consignor,
dim.belong_manager,
dim.is_count,
dim.dt,
'99999999' from 
dim_consignor_belong dim 
left join dim_consignor_belong_lz lz on dim.company_id = lz.company_id 
     and lz.end_time = '99999999' and dim.dt = '${bizdate}'
where dim.consignor != lz.consignor or dim.belong_manager != lz.belong_manager or dim.is_count != lz.is_count and lz.company_id is null

3.4变化数据和原始拉链表合并

关键点： 拉链表数据和更新数据关联的时候记得增加lz.end_time='99999999'  字段

               关联到的数据end_time 需要取 date_add(分区时间，-1)

with dim_consignor_belong_di as (
select dim.company_id,
dim.consignor,
dim.belong_manager,
dim.is_count,
dim.dt,
'99999999' from 
dim_consignor_belong dim 
join dim_consignor_belong_lz lz on dim.company_id = lz.company_id 
     and lz.end_time = '99999999' and dim.dt = '${bizdate}'
where dim.consignor != lz.consignor or dim.belong_manager != lz.belong_manager or dim.is_count != lz.is_count
)


insert overwrite table dim_consignor_belong_lz_temp
SELECT 
*
from 
dim_consignor_belong_di
union ALL 
select 
	lz.company_id,
	lz.consignor,
	lz.belong_manager,
	lz.is_count,
    lz.start_time,
    replace(if(di.company_id is null, lz.end_time, date_add(concat(SUBSTR(di.dt,1,4),'-',SUBSTR(di.dt,5,2),'-',SUBSTR(di.dt,7,2)),-1)),'-','') end_time
from dim_consignor_belong_lz lz left join 
dim_consignor_belong_di di on lz.company_id=di.company_id and lz.end_time='99999999' 

4.拉链表的应用

取最新的数据：

select * from dim_consignor_belong_lz where end_time = '99999999';

取指定区间的数据：

select * from dim_consignor_belong_lz where start_time <= '20230331' and  end_time >=  '20230331';

看数据的变化：

select * from dim_consignor_belong_lz order by company_id,start_time;