Hive---拉链表
创始人
2024-05-30 04:37:17
0

拉链表


文章目录

  • 拉链表
  • 定义
  • 用途
  • 案例
    • 全量流程
    • 增量流程
    • 合并过程
      • 第一步
      • 第二步
      • 第三步
  • 案例二(含分区)
    • 创建外部表orders
    • 增量分区表
    • 历史记录表


定义

拉链表是一种数据模型,主要是针对数据仓库设计中表存储数据的方式而定义的,顾名思义,所谓拉链,就是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题,同时也是处理缓慢变化数据(SCD2)的一种常见方式。

用途

1.数据量比较大
2.表中的部分字段会被update,如用户的地址,产品的描述信息,订单的状态等等
3。需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态,比如,查看某一个用户在过去某一段时间内,更新过几次等等
4.变化的比例和频率不是很大,比如,总共有1000万的会员,每天新增和发生变化的有10万左右
5.如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费

拉链历史表,既能满足反应数据的历史状态,又可以最大程度的节省存储

案例

全量流程

这里有张info表
在这里插入图片描述

通过sqoop导出到Hive的ODS层

日期:2021-09-26
start_time:2021-09-26(抽取时间)
表名:ods_info
在这里插入图片描述

增量流程

日期:2021-09-27
info表增加了一条数据并且数据进行了修改
在这里插入图片描述

sqoop导出
新增数据+更新数据 都要采集

select sid,sname,address,Create_time,Update_time from info where Create_time='2021-09-26' or Update_time='2021-09-26'

在ODS层再创建一张保存当天的新增和更新的数据
ods_info_new
表结构和ods_info一致

在这里插入图片描述

原始拉链表数据和更新的数据进行合并处理

也就是ods_info和ods_info_new进行合并处理
在这里插入图片描述

增量流程日期:2021-09-28
数据发生变化
在这里插入图片描述

sqoop导出
新增数据+更新数据 都要采集

select sid,sname,address,Create_time,Update_time from info where Create_time='2021-09-27' or Update_time='2021-09-27'

原表删除 ,建ods_info_new存放更改的数据
在这里插入图片描述

9.27拉链表数据和更新的数据进行合并处理

也就是ods_info和ods_info_new进行合并处理
在这里插入图片描述

这个整个过程就是拉链表的过程

合并过程

在这里插入图片描述

第一步

更改原始拉链表(ods_info)的数据
select
a.sid,
a.sname,
a.address,
a.Create_time,
a.Update-time,
a.Start_time
if(a.end_time=‘9999-12-31’ and b.sid is not null,b.start_time,a.end_time) as End_time
from ods_info a
left join ods_info_new b
on a.sid=b.sid

第二步

将left join 的结果和新增更新表(ods_info_new)进行union all 合并操作 保存在临时表中(ods_info_tmp)

第三步

将临时表(ods_info_tmp)覆盖到原始拉链表(ods_info)中 Insert overwrite ods_info select * from ods_info_tmp

insert into ods_info_tmp
from ((select sid, sname, address, Create_time, Update_time, Start_time, End_time from ods_info_new)union all(select a.sid,a.sname,a.address,a.Create_time,a.Update_time,a.Start_time,if(a.end_time = '9999-12-31' and b.sid is not null, b.start_time, a.end_time) as End_timefrom ods_info aleft join ods_info_new b on a.sid = b.sid));
insert overwrite table ods_info from ods_info_new;         

案例二(含分区)

原始表数据
在这里插入图片描述

创建外部表orders

create external table orders(orderid int,createdate string,modifiedtime string,status string
)
row format delimited fields terminated by '\t'
location '/tmp/lalian/orders';

增量分区表

create table ods_orders_inc(orderid int,createdate string,modifiedtime string,status string
)
partitioned by (day string)
row format delimited fields terminated by '\t';
insert overwrite table ods_orders_inc partition (day='2023-03-03')

历史记录表

create table dws_orders_his(orderid int,createdate string,modifiedtime string,status string,start_time string,end_time string
)
row format delimited  fields terminated by '\t';insert overwrite table dws_orders_his
select orderid,createdate,modifiedtime,status,modifiedtime,'9999-12-31' from ods_orders_inc where day='2023-03-03';

2023-03-04凌晨

ods_orders_inc 分区表 按日期分区
将orders数据导入到2023一03一03分区中
在这里插入图片描述
2023-03-05凌晨

原表数据发生更改
在这里插入图片描述

将orders数据导入到2023-03-04分区中

insert overwrite table ods_orders_inc partition(day='2023-03-04')
select orderid, createdate, modifiedtime, status
from orders
where modifiedtime='2023-3-4';

合并操作

select tb.orderid,tb.createdate,tb.modifiedtime,tb.status,tb.start_time,tb.end_timefrom(
(select orderid, createdate, modifiedtime,status,modifiedtime as start_time,'9999-12-31' as end_time
from ods_orders_inc where day='2023-03-04')
union all
(selectt1.orderid,t1.createdate,t1.modifiedtime,t1.status,t1.start_time,case when t2.orderid is not nulland t1.end_time >'2023-3-4' then '2023-3-4' else t1.end_time end end_time
from dws_orders_his as t1left join (select orderid from ods_orders_inc where day='2023-03-04') as t2 on t1.orderid=t2.orderid))tb
order by tb.orderid,tb.start_time;

在这里插入图片描述
2023-03-06凌晨
原表数据发生更改
在这里插入图片描述

将orders数据导入到2023-03-05分区中

insert overwrite table ods_orders_inc partition (day='2023-03-05')
select orderid,createdate,modifiedtime,status from orders
where modifiedtime='2023-3-5' or (createdate='2023-3-5' and modifiedtime='2023-3-5');

合并操作

select tb1.*
from ((select orderid, createdate, modifiedtime, status, modifiedtime start_time, '9999-12-31' end_timefrom ods_orders_incwhere day = '2023-03-05')union all(select t1.orderid,t1.createdate,t1.modifiedtime,t1.status,t1.start_time,`if`(t2.orderid is not null and t1.end_time > '2023-03-05', '2023-03-05', t1.end_time) end_timefrom dws_orders_his t1left join (select orderid, modifiedtimefrom ods_orders_incwhere day = '2023-03-05') t2 on t1.orderid = t2.orderid)) tb1 order by tb1.orderid,tb1.modifiedtime;

在这里插入图片描述
以此类推,整个过程即为拉链表

每日的用户更新表该怎么获取,有3种方式拿到或者间接拿到每日的用户增量,因为它比较重要,所以详细说明:
我们可以监听 Mysql 库数据的变化,比如说用 Canal,最后合并每日的变化,获取到最后的一个状态。
假设我们每天都会获得一份切片数据,我们可以通过取两天切片数据的不同来作为每日更新表,这种情况下我们可以对所有的字段先进行 concat,再取 md5,这样就 ok 了。
流水表!有每日的变更流水表。通过etl工具对操作型数据库按照时间字段增量抽取到ods或者数据仓库(每天抽取前一天的数据),形成每天的增量数据(实际中使用最多的情形)。

相关内容

热门资讯

什么叫安卓双系统,一机多能的智... 你有没有想过,为什么你的手机可以同时运行两个操作系统呢?这就是我们今天要聊一聊的安卓双系统。想象一边...
安卓系统有哪些表情,轻松表达你... 你知道吗?安卓系统里的表情包那可真是五花八门,让人眼花缭乱。今天,就让我带你一起探索一下安卓系统里那...
安卓系统怎么下载树洞,安卓系统... 你有没有想过,在安卓手机上下载一个可以让你畅所欲言、分享心情的树洞应用呢?树洞,顾名思义,就是一个可...
山寨安卓导航升级系统,功能升级... 你有没有发现,手机导航越来越智能了?但是你知道吗,市面上那些看似高大上的安卓导航升级系统,其实有不少...
诺基亚原生安卓系统怎,创新与体... 你有没有想过,那个曾经风靡一时的诺基亚手机,竟然也玩起了原生安卓系统?没错,就是那个经典的“诺基亚”...
如果安卓系统换成ios,系统更... 想象如果安卓系统突然宣布:“咱们来个大的改变,把安卓换成iOS!”这得是多大的新闻啊!想象你的手机、...
安卓开发新手引导系统,掌握核心... 你刚踏入安卓开发的广阔天地,是不是有点晕头转向呢?别担心,今天我就要给你来一场新手引导大作战,让你从...
国外安卓操作系统,全球视角下的... 你知道吗?在手机的世界里,操作系统可是个大头戏呢!今天,咱们就来聊聊国外那些风头正劲的安卓操作系统,...
安卓17系统窗口调用,Andr... 你知道吗?最近安卓系统又出新花样了!安卓17系统窗口调用,这可是个大招啊!今天,我就要带你深入了解一...
苹果庄安卓通讯系统,通讯系统融... 你知道吗?在手机江湖里,有两个门派可是鼎鼎大名,一个是以苹果为首的iOS,另一个则是安卓阵营。今天,...
安卓系统10寸屏幕,功能与体验... 你有没有想过,拥有一台10寸屏幕的安卓系统设备,会是怎样的体验呢?想象小巧的机身,却拥有强大的功能,...
安卓刷入linux系统,轻松刷... 你有没有想过,你的安卓手机其实可以变身成一个强大的Linux工作站呢?没错,就是那个在服务器上大显神...
安卓系统 看电视软件,便捷观影... 亲爱的手机控们,你是不是也和我一样,对安卓系统上的看电视软件情有独钟?没错,那种随时随地都能追剧的快...
安卓13系统图片备份,随时随地... 亲爱的安卓用户们,你是否在期待着安卓13系统的到来呢?听说这个新系统将会带来许多令人兴奋的新功能,但...
安卓手机系统壁纸大全,海量精美... 你有没有发现,手机屏幕上那一抹色彩,有时候能瞬间提升你的心情呢?没错,说的就是那些精美的壁纸!今天,...
白板 安卓系统内置电脑,融合移... 亲爱的读者,你是否曾想过,在你的安卓手机上,竟然可以变身为一台迷你电脑?没错,这就是今天我要跟你分享...
安卓系统游戏昵称英文,Unlo... 你有没有想过,在安卓系统的游戏世界里,一个独特的昵称能给你带来怎样的不同体验呢?想象当你化身成为一名...
原生安卓6.0系统占用,原生安... 你有没有发现,自从你的手机升级到了原生安卓6.0系统,它的反应速度好像变得不那么敏捷了?别急,今天就...
使命召唤10安卓系统,安卓平台... 亲爱的玩家们,你是否曾在深夜里,手握手机,沉浸在紧张刺激的战场中?今天,我要和你聊聊一款让无数玩家热...
鸣言系统安卓版,畅享智能语音交... 你知道吗?最近我在手机上发现了一个超级酷炫的应用——鸣言系统安卓版!这款应用简直是我的新宠,让我来给...