数仓拉链表[通俗易懂]

数仓拉链表[通俗易懂]拉链表一丶什么是拉链表拉链表是一种数据模型,主要是针对数据仓库设计中表存储数据的方式而定义的,顾名思义,所谓拉链,就是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题,同时也是处理缓慢变化数据的一种常见方式。百度百科的解释:拉链表是维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录,通过拉链表可以很方便的还原出拉链时点的客户记录。二丶拉链表的产生背景在数据仓库的

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

拉链表

一丶什么是拉链表

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

百度百科的解释:拉链表是维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录,通过拉链表可以很方便的还原出拉链时点的客户记录。

二丶拉链表的产生背景

在数据仓库的数据模型设计过程中,经常会遇到这样的需求:

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

对于这种表有几种方案可选:
方案一:每天只留最新的一份,比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。
方案二:每天保留一份全量的切片数据。
方案三: 每天保存一份增量数据
方案四:使用拉链表。

以上方案对比

方案一

这种方案就不用多说了,实现起来很简单,每天drop掉前一天的数据,重新抽一份最新的。

优点很明显,节省空间,一些普通的使用也很方便,不用在选择表的时候加一个时间分区什么的。

缺点同样明显,没有历史数据,先翻翻旧账只能通过其它方式,比如从流水表里面抽。

方案二

每天一份全量的切片是一种比较稳妥的方案,而且历史数据也在。

缺点就是存储空间占用量太大太大了,如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费,这点我感触还是很深的…

当然我们也可以做一些取舍,比如只保留近一个月的数据?但是,需求是无耻的,数据的生命周期不是我们能完全左右的。

方案三

每天都保存增量数据,这种方案相比较方案一二的话,数据量变少了,也记录了每条数据的变化.但是数据量还是比拉链表多,同时它要求某天的历史数据查询效率比较低,比较繁琐.比如你要求2021年10月01号的在职人数,你就需要判断入职日期小于等于10月01号的,用lead函数获取下条数据,判断下条数据的离职日期是否大于2021年10月01号.

拉链表

拉链表在使用上基本兼顾了我们的需求。

首先它在空间上做了一个取舍,虽说不像方案一那样占用量那么小,但是它每日的增量可能只有方案二的千分之一甚至是万分之一。

其实它能满足方案二所能满足的需求,既能获取最新的数据,也能添加筛选条件也获取历史的数据。

所以我们还是很有必要来使用拉链表的。

三丶在Hive中实现拉链表
在现在的大数据场景下,大部分的公司都会选择以Hdfs和Hive为主的数据仓库架构。目前的Hdfs版本来讲,其文件系统中的文件是不能做改变的,也就是说Hive的表智能进行删除和添加操作,而不能进行update。基于这个前提,我们来实现拉链表。

还是以上面的用户表为例,我们要实现用户的拉链表。在实现它之前,我们需要先确定一下我们有哪些数据源可以用。

  1. 我们需要一张ODS层的用户全量表。至少需要用它来初始化。
  2. 每日的用户更新表。
    而且我们要确定拉链表的时间粒度,比如说拉链表每天只取一个状态,也就是说如果一天有3个状态变更,我们只取最后一个状态,这种天粒度的表其实已经能解决大部分的问题了。

另外,补充一下每日的用户更新表该怎么获取,据笔者的经验,有3种方式拿到或者间接拿到每日的用户增量,因为它比较重要,所以详细说明:

  1. 我们可以监听Mysql数据的变化,比如说用Canal,最后合并每日的变化,获取到最后的一个状态。
  2. 假设我们每天都会获得一份切片数据,我们可以通过取两天切片数据的不同来作为每日更新表,这种情况下我们可以对所有的字段先进行concat,再取md5,这样就ok了。
  3. 流水表!有每日的变更流水表。

拉链表制作过程图解
在这里插入图片描述
四丶实例讲解
需求:现在需要把一张每天存有全量数据的表制作成拉链表
步骤:
1.先把已有的全量分区表改造为拉链表①


--创建岗位状态表,在第一次执行脚本执行,这段sql,先在已有的数据上做拉链表,之后用拉链表去和每日新增及变化的数据进行合并
--注意:如果任务执行失败,那么可以重新执行这段sql制作拉链表,结束时间改为最新时间就好了
drop table if exists tmp.ems_base;
create table  tmp.ems_base as
select
t3.*
,case when t3.hire_date >= t3.date_from and  t3.cancel_flag <> 'Y' then '入职'   
when t3.hire_date < t3.date_from and t3.cancel_flag <> 'Y' then '调岗'
when t3.cancel_flag = 'Y' then '离职'
else '其它'   end  as  type        --给每条数据标上一个状态类型,这里面的判断你们不用理解,只需要知道缓慢变化的维度是这个字段(入职,调岗,离职)三种类型
from
(select
t2.*
,row_number() over(partition by t2.emp_num,t2.date_from,t2.cancel_flag,t2.hire_date order by t2.inc_day) as rn
from
(select
lpad(t1.emp_num ,8,'0') as emp_num    --工号(没有8位的用0补齐)
,t1.curr_org_id       --当前部门组织id
,t1.org_code     --组织编码
,t1.curr_org_name     --组织名称
,t1.hire_date       --入职日期
,if(t1.date_from < t1.hire_date,t1.hire_date,t1.date_from)  as  date_from    --调入当前网络时间
,if(t1.cancel_flag = 'Y',t1.cancel_date,'00000000') as cancel_date   --离职日期
,t1.cancel_flag     --离职标识
,t1.zhrlzlx         --离职类型
,t1.inc_day
from 
(select
*
from  ods.etl_ems    --人员全量分区表
where inc_day between 开始时间 and 结束时间
) t1
) t2 
)t3
where rn = 1    
;  
--创建拉链表
drop table if exists ods.ems_zipper;
create table ods.ems_zipper as 
select 
t1.emp_num             --工号
,t1.curr_org_id       --当前部门组织id
,t1.org_code          --组织编码
,t1.curr_org_name     --组织名称
,t1.hire_date         --入职日期
,t1.date_from         --入网时间
,t1.cancel_date       --离职时间
,t1.cancel_flag       --离职标识
,t1.type              --(入职,调岗,离职)
,t1.zhrlzlx           --离职类型
,from_unixtime(unix_timestamp(case when t1.type = '调岗' then t1.date_from 
when t1.type = '入职' then t1.hire_date
when t1.type = '离职' then t1.cancel_date                --因为这三种状态的时间存储在不同字段当中,所以需要去判断是哪种状态,然后获取哪个时间字段作为开始时间
end  
,'yyyyMMdd'),'yyyy-MM-dd')                   as begin_date  --开始日期 
,case when lead(t1.type,1,'无') over(partition by t1.emp_num order by t1.inc_day) = '调岗' then date_sub(from_unixtime(unix_timestamp(lead(t1.date_from,1,'99991231') over(partition by t1.emp_num order by t1.inc_day),'yyyyMMdd'),'yyyy-MM-dd'),1) 
when lead(t1.type,1,'无') over(partition by t1.emp_num order by t1.inc_day) = '离职' then date_sub(from_unixtime(unix_timestamp(lead(t1.cancel_date,1,'99991231') over(partition by t1.emp_num order by t1.inc_day),'yyyyMMdd'),'yyyy-MM-dd'),1) 
when lead(t1.type,1,'无') over(partition by t1.emp_num order by t1.inc_day) = '入职' then date_sub(from_unixtime(unix_timestamp(lead(t1.hire_date,1,'99991231') over(partition by t1.emp_num order by t1.inc_day),'yyyyMMdd'),'yyyy-MM-dd'),1) 
else '9999-12-30'     end  as end_date --结束日期
from  tmp.ems_base t1
;

2.通过昨天的全量数据和前天的全量数据比较,获取新增及变化的临时表②


-----创建每日新增及变化的表
drop table if exists tmp.ems_change;
create table tmp.ems_change as 
select
lpad(t2.emp_num,8,'0') as emp_num     --工号
,t2.curr_org_id       --当前部门组织id
,t2.org_code     --组织编码
,t2.curr_org_name     --组织名称
,t2.hire_date       --入职日期
,if(t2.date_from < t2.hire_date,t2.hire_date,t2.date_from)  as  date_from       --调入当前网络时间
,if(t2.cancel_flag = 'Y',t2.cancel_date,'00000000')  as cancel_date  --离职日期 
,t2.cancel_flag     --离职标识
,t2.zhrlzlx         --离职类型 
,case   when t2.hire_date >= t2.date_from and  t2.cancel_flag <> 'Y' then '入职'
when t2.hire_date < t2.date_from and t2.cancel_flag <> 'Y' then '调岗'
when t2.cancel_flag = 'Y' then '离职'
else '其它'   end  as  type
from
(select
t1.emp_num     --工号
,t1.curr_org_id       --当前部门组织id
,t1.org_code     --组织编码
,t1.curr_org_name     --组织名称
,t1.hire_date       --入职日期
,t1.date_from       --调入当前网络时间
,t1.cancel_date     --离职日期
,t1.cancel_flag     --离职标识
,t1.zhrlzlx         --离职类型
from ods.etl_ems  t1
where t1.inc_day = '${v_day_1ago}'
) t2
left join
(select
t4.emp_num     --工号
,t4.curr_org_id       --当前部门组织id
,t4.org_code     --组织名称
,t4.curr_org_name     --组织代码
,t4.hire_date       --入职日期
,t4.date_from       --调入当前网络时间
,t4.cancel_date     --离职日期
,t4.cancel_flag     --离职标识
,t4.zhrlzlx         --离职类型
from ods.etl_ems  t4
where t4.inc_day = '${v_day_2ago}'
) t3
on
t2.emp_num = t3.emp_num
--注意用concat时,只要有一个字段为null那么,拼接的结果就是null,所以需要nvl把空值先转换为字符串
where concat(nvl(t2.date_from,''),nvl(t2.hire_date,''),nvl(t2.cancel_flag,'')) <> concat(nvl(t3.date_from,''),nvl(t3.hire_date,''),nvl(t3.cancel_flag,''))  
;

3.拉链表和临时表②合并获取临时拉链表,用临时拉链表覆盖拉链表,得到新的拉链表


----拉链表去和每日新增及变化的数据合并
drop table if exists tmp.ems_zipper_tmp;
create table tmp.ems_zipper_tmp as 
select
t1.emp_num     --工号
,t1.curr_org_id       --当前部门组织id
,t1.org_code     --组织名称
,t1.curr_org_name     --组织代码
,t1.hire_date       --入职日期
,t1.date_from       --调入当前网络时间
,t1.cancel_date  --离职日期
,t1.cancel_flag     --离职标识
,t1.zhrlzlx         --离职类型
,t1.type
,from_unixtime(unix_timestamp(case when t1.type = '调岗' then t1.date_from 
when t1.type = '入职' then t1.hire_date
when t1.type = '离职' then t1.cancel_date
end  
,'yyyyMMdd'),'yyyy-MM-dd')    as begin_date 
,'9999-12-30' as end_date
from   tmp.ems_change t1
union all
select
a.emp_num     --工号
,a.curr_org_id       --当前部门组织id
,a.org_code     --组织名称
,a.curr_org_name     --组织代码
,a.hire_date       --入职日期
,a.date_from       --调入当前网络时间
,a.cancel_date     --离职日期
,a.cancel_flag     --离职标识
,a.zhrlzlx         --离职类型
,a.type            --(入职,调岗,离职)
,a.begin_date      --开始日期
,if(b.emp_num is not null and a.end_date = '9999-12-30',case when b.type = '调岗' then date_sub(from_unixtime(unix_timestamp(b.date_from,'yyyyMMdd'),'yyyy-MM-dd'),1) 
when b.type = '离职' then date_sub(from_unixtime(unix_timestamp(b.cancel_date,'yyyyMMdd'),'yyyy-MM-dd'),1) 
when b.type = '入职' then date_sub(from_unixtime(unix_timestamp(b.hire_date,'yyyyMMdd'),'yyyy-MM-dd'),1) 
else '9999-12-30' end ,a.end_date) as end_date   
from ods.ems_zipper a  --拉链表
left join tmp.ems_change b  --新增及其变化表
on a.emp_num = b.emp_num
;
----用临时拉链表覆盖拉链表
insert overwrite table ods.ems_zipper
select 
t1.emp_num   
,t1.curr_org_id 
,t1.org_code 
,t1.curr_org_name  
,t1.hire_date         --入职日期
,t1.date_from         --入网时间
,t1.cancel_date       --离职时间
,t1.cancel_flag       --离职标识
,t1.type              --(入职,调岗,离职)
,t1.zhrlzlx           --离职类型
,t1.begin_date
,t1.end_date
from tmp.ems_zipper_tmp t1
;
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/181283.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • mybatis log plugin激活码(JetBrains全家桶)

    (mybatis log plugin激活码)好多小伙伴总是说激活码老是失效,太麻烦,关注/收藏全栈君太难教程,2021永久激活的方法等着你。https://javaforall.cn/100143.htmlIntelliJ2021最新激活注册码,破解教程可免费永久激活,亲测有效,上面是详细链接哦~MLZPB5EL5Q-eyJsaWNlbnNlSWQiOi…

  • tensorflow2.0手写数字识别_tensorflow手写汉字识别

    tensorflow2.0手写数字识别_tensorflow手写汉字识别手写识别的应用场景有很多,智能手机、掌上电脑的信息工具的普及,手写文字输入,机器识别感应输出;还可以用来识别银行支票,如果准确率不够高,可能会引起严重的后果。当然,手写识别也是机器学习领域的一个HelloWorld任务,感觉每一个初识神经网络的人,搭建的第一个项目十之八九都是它。我们来尝试搭建下手写识别中最基础的手写数字识别,与手写识别的不同是数字识别只需要识别0-9的数字,样本数据集也只需…

  • Python 爬虫一 简介

    Python 爬虫一 简介什么是爬虫?爬虫可以做什么?爬虫的本质爬虫的基本流程什么是request&response爬取到数据该怎么办什么是爬虫?网络爬虫(又被称为网页蜘蛛,网络机器人,在FOAF社区中间

  • ssl原理及应用_ssl的理解

    ssl原理及应用_ssl的理解今天学习网络通信,看到使用ssl(SecureSocketsLayer)进行加密,由于对ssl只是有些概念上的了解,对于具体应用原理、过程和如何使用不慎了解,于是学习了一番,总结如下:1.为什么要使用ssl?确保数据传输的安全性2.用ssl加密传输的实际过程?建立连接时使用非对称加密,而连接完成后在传输数据时使用对称加密(速度快)3.ssl加密方式?有两种方式:单

    2022年10月31日
  • Java使用RSA加密解密签名及校验

    Java使用RSA加密解密签名及校验

  • linux(11)配置环境变量[通俗易懂]

    linux(11)配置环境变量[通俗易懂]前言在自定义安装软件的时候,经常需要配置环境变量,下面进行详细解析&nbsp;环境变量配置文件|用户|配置文件||:|:||系统环境|/ect/profil

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号