拉链表制作

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;


  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Hive是一个开源的数据仓库和查询工具,用于将大数据处理和分析集成在Hadoop生态系统中。拉链表是一种在Hive中实现的数据处理技术,主要用于处理维度数据的历史变化。 拉链表的实现思路是将每个维度表根据指定的生效日期和失效日期进行拆分,生成多个对应不同时间段的维度数据,以保留维度表的历史变化记录。在Hive中,可以通过以下步骤来实现拉链表: 1. 创建维度表和事实表:首先,创建维度表和事实表的Hive表。维度表用于存储维度字段的详细信息,例如员工表、产品表等;事实表用于存储与维度表关联的度量数据,例如销售事实表。 2. 设计拉链表结构:在维度表中添加生效日期(start_date)和失效日期(end_date)字段,用于标识每条记录的有效时间段。通常,失效日期为空或未来日期表示当前有效数据。 3. 插入初始数据:将初始数据插入维度表,即没有历史记录的部分。在start_date字段中填写最早的日期,end_date字段中填写NULL或未来日期。 4. 插入新数据:当维度表中的记录有更新或新增时,需要按照拉链表的原则进行插入。具体操作是将原有的生效日期字段(start_date)的end_date字段更新为当前日期,并将新数据插入到维度表中。 5. 查询数据:在查询维度表和事实表时,可以通过使用日期条件和JOIN操作,将最近生效的维度数据关联到事实数据上,以获得正确的历史维度信息。 拉链表的实现使得Hive可以处理维度数据的历史变化情况,并提供了便捷的方式来查询和分析历史数据。它对于构建具有时间依赖性的报表和分析非常有用。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值