拉链表

1.数据准备

create table sospdm.tmp_ods_user
(
     cust_num   string comment '会员编码'
    ,mbl_phone  string comment '会员手机号'
)partitioned by (statis_date string comment '统计时间')
stored as rcfile 
;

set hive.exec.dynamic.partition=true;   
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table sospdm.tmp_ods_user partition (statis_date)
select '001' as cust_num,'111111' as mbl_phone,'20170101' as statis_date from sospdm.dual union all
select '002' as cust_num,'222222' as mbl_phone,'20170101' as statis_date from sospdm.dual union all
select '003' as cust_num,'333333' as mbl_phone,'20170101' as statis_date from sospdm.dual union all
select '004' as cust_num,'444444' as mbl_phone,'20170101' as statis_date from sospdm.dual union all

select '001' as cust_num,'111111' as mbl_phone,'20170102' as statis_date from sospdm.dual union all
select '002' as cust_num,'233333' as mbl_phone,'20170102' as statis_date from sospdm.dual union all
select '003' as cust_num,'333333' as mbl_phone,'20170102' as statis_date from sospdm.dual union all
select '004' as cust_num,'432432' as mbl_phone,'20170102' as statis_date from sospdm.dual union all
select '005' as cust_num,'555555' as mbl_phone,'20170102' as statis_date from sospdm.dual union all

select '001' as cust_num,'111111' as mbl_phone,'20170103' as statis_date from sospdm.dual union all
select '002' as cust_num,'233333' as mbl_phone,'20170103' as statis_date from sospdm.dual union all
select '003' as cust_num,'333333' as mbl_phone,'20170103' as statis_date from sospdm.dual union all
select '004' as cust_num,'654321' as mbl_phone,'20170103' as statis_date from sospdm.dual union all
select '005' as cust_num,'115115' as mbl_phone,'20170103' as statis_date from sospdm.dual union all
select '006' as cust_num,'666666' as mbl_phone,'20170103' as statis_date from sospdm.dual union all

select '001' as cust_num,'111111' as mbl_phone,'20170104' as statis_date from sospdm.dual union all
select '002' as cust_num,'233333' as mbl_phone,'20170104' as statis_date from sospdm.dual union all
select '003' as cust_num,'333333' as mbl_phone,'20170104' as statis_date from sospdm.dual union all
select '004' as cust_num,'654321' as mbl_phone,'20170104' as statis_date from sospdm.dual union all
select '005' as cust_num,'115115' as mbl_phone,'20170104' as statis_date from sospdm.dual union all
select '006' as cust_num,'666767' as mbl_phone,'20170104' as statis_date from sospdm.dual;

2.拉链逻辑

-- 全量拉链
-- 初始化拉链表
create table sospdm.tmp_dwd_his
(
     cust_num string comment '会员编码'
    ,mbl_phone string comment '手机号'
    ,start_date string comment '开始时间'
)partitioned by (end_date string comment '结束时间')
;

insert overwrite table sospdm.tmp_dwd_his partition(end_date)
select
     cust_num
    ,mbl_phone
    ,'20170101' as statis_date
    ,'99991231' as end_date
from tmp_ods_user where statis_date='20170101'
;

-- 取开链数据与今日全量数据对比 找出变化的

-- 字段改变或者不变的
insert overwrite table sospdm.tmp_dwd_his partition(end_date)
select 
     t1.cust_num
    ,t1.mbl_phone
    ,'${statis_date}' as start_date
    ,'99991231' as end_date
from
(
    select
         cust_num
        ,mbl_phone
    from tmp_ods_user where statis_date='${statis_date}'
) t1
left join 
(
    select
         cust_num
        ,mbl_phone
    from tmp_dwd_his where start_date<='${statis_date}' and end_date>='${statis_date}'
) t2 
on t1.cust_num=t2.cust_num
where t2.cust_num is null -- 新增
or t1.mbl_phone <> t2.mbl_phone -- 改变

union all 

select 
     t1.cust_num
    ,t1.mbl_phone
    ,start_date
    ,'${statis_date}' as end_date   -- 改变的进行闭链
from
(
    select
         cust_num
        ,mbl_phone
    from tmp_ods_user where statis_date='${statis_date}'
) t1 
inner join 
(
    select
         cust_num
        ,mbl_phone
        ,start_date
        ,end_date
    from tmp_dwd_his where start_date<='${statis_date}' and end_date>='${statis_date}'
) t2 
on t1.cust_num=t2.cust_num  
where t1.mbl_phone <> t2.mbl_phone-- 改变

union all 

select 
     t1.cust_num
    ,t1.mbl_phone
    ,start_date
    ,end_date
from
(
    select
         cust_num
        ,mbl_phone
    from tmp_ods_user where statis_date='${statis_date}'
) t1 
inner join 
(
    select
         cust_num
        ,mbl_phone
        ,start_date
        ,end_date
    from tmp_dwd_his where start_date<='${statis_date}' and end_date>='${statis_date}'
) t2 
on t1.cust_num=t2.cust_num and t1.mbl_phone = t2.mbl_phone -- 不变
;

3.拉链回滚

-- 回滚
-- 回滚日期之前的闭链 还是闭链

select 
     cust_num
    ,mbl_phone
    ,start_date
    ,end_date
from sospdm.tmp_dwd_his where end_date < '20170103'
--回滚日期之前的开链
union all 
select 
     cust_num
    ,mbl_phone
    ,start_date
    ,'99991231' as end_date
from sospdm.tmp_dwd_his where end_date >= '20170103'
;

 

转载于:https://www.cnblogs.com/yin-fei/p/10840986.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 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、付费专栏及课程。

余额充值