数据治理 - 数据仓库历史数据存储 - 拉链表

2024-04-06 18:48
文章标签 数据 存储 数据仓库 拉链 治理 历史数据

本文主要是介绍数据治理 - 数据仓库历史数据存储 - 拉链表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

什么是拉链表

拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

我们先看一个示例，这就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

注册日期用户编号手机号码t_start_datet_end_date
2017-01-010011111112017-01-019999-12-31
2017-01-010022222222017-01-012017-01-01
2017-01-010022333332017-01-029999-12-31
2017-01-010033333332017-01-019999-12-31
2017-01-010044444442017-01-012017-01-01
2017-01-010044324322017-01-022017-01-02
2017-01-010044324322017-01-039999-12-31
2017-01-020055555552017-01-022017-01-02
2017-01-020051151152017-01-039999-12-31
2017-01-030066666662017-01-039999-12-31

我们暂且不对这张表做细致的讲解，后文会专门来阐述怎么来设计、实现和使用它。

拉链表的使用场景

在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：

  1. 有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些。
  2. 表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。
  3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。
  4. 表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

那么对于这种表我该如何设计呢？下面有几种方案可选：

  • 方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。
  • 方案二：每天保留一份全量的切片数据。
  • 方案三：使用拉链表。

为什么使用拉链表

现在我们对前面提到的三种进行逐个的分析。

方案一

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

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

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

方案二

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

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

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

拉链表

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

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

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

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

拉链表的设计和实现

如何设计一张拉链表

下面我们来举个栗子详细看一下拉链表。

我们接上在《漫谈数据仓库之维度建模》中的电商网站的例子，现在以用户的拉链表来说明。

我们先看一下在Mysql关系型数据库里的user表中信息变化。

在2017-01-01这一天表中的数据是：

注册日期用户编号手机号码
2017-01-01001111111
2017-01-01002222222
2017-01-01003333333
2017-01-01004444444

在2017-01-02这一天表中的数据是， 用户002和004资料进行了修改，005是新增用户：

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333（由222222变成233333）
2017-01-01003333333
2017-01-01004432432（由444444变成432432）
2017-01-02005555555（2017-01-02新增）

在2017-01-03这一天表中的数据是， 用户004和005资料进行了修改，006是新增用户：

注册日期用户编号手机号码备注
2017-01-01001111111 
2017-01-01002233333 
2017-01-01003333333 
2017-01-01004654321（由432432变成654321）
2017-01-02005115115（由555555变成115115）
2017-01-03006666666（2017-01-03新增）

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表，这是最新一天（即2017-01-03）的数据：

注册日期用户编号手机号码t_start_datet_end_date
2017-01-010011111112017-01-019999-12-31
2017-01-010022222222017-01-012017-01-01
2017-01-010022333332017-01-029999-12-31
2017-01-010033333332017-01-019999-12-31
2017-01-010044444442017-01-012017-01-01
2017-01-010044324322017-01-022017-01-02
2017-01-010046543212017-01-039999-12-31
2017-01-020055555552017-01-022017-01-02
2017-01-020051151152017-01-039999-12-31
2017-01-030066666662017-01-039999-12-31

说明

  • t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期结束时间。
  • t_end_date = '9999-12-31'表示该条记录目前处于有效状态。
  • 如果查询当前所有有效的记录，则select * from user where t_end_date = '9999-12-31'。
  • 如果查询2017-01-02的历史快照，则select * from user where t_start_date <= '2017-01-02' and t_end_date >= '2017-01-02'。（此处要好好理解，是拉链表比较重要的一块。）

在Hive中实现拉链表

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

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

  1. 我们需要一张ODS层的用户全量表。至少需要用它来初始化。
  2. 每日的用户更新表。

而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

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

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

ods层的user表

现在我们来看一下我们ods层的用户资料切片表的结构：

CREATE EXTERNAL TABLE ods.user (user_num STRING COMMENT '用户编号',mobile STRING COMMENT '手机号码',reg_date STRING COMMENT '注册日期'
COMMENT '用户资料表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user';
)

ods层的user_update表

然后我们还需要一张用户每日更新表，前面已经分析过该如果得到这张表，现在我们假设它已经存在。

CREATE EXTERNAL TABLE ods.user_update (user_num STRING COMMENT '用户编号',mobile STRING COMMENT '手机号码',reg_date STRING COMMENT '注册日期'
COMMENT '每日用户资料更新表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user_update';
)

拉链表

现在我们创建一张拉链表：

CREATE EXTERNAL TABLE dws.user_his (user_num STRING COMMENT '用户编号',mobile STRING COMMENT '手机号码',reg_date STRING COMMENT '用户编号',t_start_date ,t_end_date
COMMENT '用户资料拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';
)

实现sql语句

然后初始化的sql就不写了，其实就相当于是拿一天的ods层用户表过来就行，我们写一下每日的更新语句。

现在我们假设我们已经已经初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的数据，我们有了下面的Sql。

然后把两个日期设置为变量就可以了。

INSERT OVERWRITE TABLE dws.user_his
SELECT * FROM
(SELECT A.user_num,A.mobile,A.reg_date,A.t_start_time,CASEWHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01'ELSE A.t_end_timeEND AS t_end_timeFROM dws.user_his AS ALEFT JOIN ods.user_update AS BON A.user_num = B.user_num
UNIONSELECT C.user_num,C.mobile,C.reg_date,'2017-01-02' AS t_start_time,'9999-12-31' AS t_end_timeFROM ods.user_update AS C
) AS T

补充

好了，我们分析了拉链表的原理、设计思路、并且在Hive环境下实现了一份拉链表，下面对拉链表做一些小的补充。

拉链表和流水表

流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。

这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。

查询性能

拉链表当然也会遇到查询性能的问题，比如说我们存放了5年的拉链数据，那么这张表势必会比较大，当查询的时候性能就比较低了，个人认为两个思路来解决：

  1. 在一些查询引擎中，我们对start_date和end_date做索引，这样能提高不少性能。
  2. 保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

0xFF 总结

我们在这篇文章里面详细地分享了一下和拉链表相关的知识点，但是仍然会有一会遗漏。欢迎交流。

在后面的使用中又有了一些心得，补充进来：

  1. 使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询。

  2. 可以加上当前行状态标识，能快速定位到当前状态。

  3. 在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如如当天修改次数，那么拉链表的作用就会更大。


作者：木东居士
链接：https://www.jianshu.com/p/799252156379
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

假如我们有一个账号account表，我们需要在hive中存储（数据是从线上mysql读取binlog同步来的，是有明细变化的）

account表结构：account_id, username, followers_count, modified_at

我们经常使用的存储方式有快照表和流水表。快照表就是以时间为粒度（比如天），生成每个时间的全量数据快照；流水表则是记录数据的每一条具体的改变。

现在有一个需求：需要记录账号的历史变更情况
快照表实现
这里以天为粒度，对每天账号最终的状态进行存储即可。

在hive中，以天为分区存储，我们需要访问某天的历史状态，直接指定分区即可访问

-- 访问20190801时某个账号的状态
select * from account_snapshot where ds = "20190801" and account_id = xxx

快照表的缺点是：当单表的数据量比较大时，每天存储全量的快照，会导致不必要的资源开支

流水表实现

流水表记录数据的每一条变化，来一条插入一条

这种存储方法对数据的使用者不太友好

-- 查询20190801时某个账号的状态
select * from (select *, row_number over(partition by account_id order by modified_at desc) as rofrom account where modified_at <= "2019-08-02 00:00:00" and account_id = xxx
) where ro = 1

以上的两种方式，多多少少都存在问题，接下来介绍拉链表的使用

拉链表
拉链表是维护历史状态、以及最新状态的一种方式。

拉链表对快照表进行了优化，根据拉链粒度（一般为时间）的不同，去除了在粒度范围内不变的数据。

拉链表可以维护两个时间（start_time, end_time），来标识当前记录是否还有效，以及更好的定位历史数据

实现前提：
首先要有某一时刻的全量数据，作为起始表

其次要有流水表或者快照表两者其一，作为变化的依据

实现：

-- 原始数据
create table account(account int ,username varchar,followers_count int ,modified_at timestamp 
)-- 创建拉链表
create table account_zip(account int ,username varchar,followers_count int ,modified_at timestamp ,start_time timestamp, -- 记录的有效起始时间end_time timestamp, -- 记录的有效结束时间
)

今天是8.1，我们从7.31号的数据开始记录

首先我们将7.31号的数据导入我们的拉链表中

insert into account_zip
select *, "2019-07-31 00:00:00" as start_time ,"9999-12-31 00:00:00" as end_time
from 
account ;

这里的start_time指的是这条记录是在7.31改变生效的，end_time是指这条记录在9999-12-31前是有效的。导入拉链表后，表内的记录如下所示，
接下来，我们在8.1的时候，对账号进行修改和新增

左边是7.31的数据，右边是8.1的数据

我们可以看到8.1进行了一条记录的修改（修改mwf的followers_account）和一条记录的新增（新增account_id为5的用户）

针对修改来说：
在拉链中已经存在mwf的信息，8.1对他进行修改，

我们可以将之前那条记录的end_time修改为8.1，表示他在8.1之后失效了

然后将8.1的这次操作写入拉链表，他的start_time为8.1，end_time为9999-12-31

针对新增来说：
我们直接将它写入拉链表，start_time为8.1，end_time为9999-12-31

8.1过后，我们的拉链表变为了如下版本：

以上我们就实现了一个拉链表

查询记录
查询当前的有效记录

select * from account_zip where end_time = "9999-12-31 00:00:00"

如查询2019-07-31时的历史快照

-- 在7.31号前开始生效，且在7.31号当天时还没有失效， 此处通过两个时间刚好限定了范围
select * from account_zip
where start_time <= "2019-07-31 00:00:00" and end_time >= "2019-07-31 00:00:00"

基于快照表生成拉链表

insert into account_zip_tmp 
-- 联合两个表，写入临时的拉链表中
select * from (-- 改变原有拉链表中 失效的数据-- 这里用到了md5来比较数据是否相同select bak.account_id,bak.username ,bak.followers_count  ,bak.modified_at, bak.start_timecase when bak.end_time = "9999-12-31 00:00:00" and  md5(concat(coalesce(bak.username, 'NULL'),coalesce(bak.followers_count, 'NULL'),coalesce(bak.modified_at, 'NULL'))) != md5(concat(coalesce(new.username, 'NULL'),coalesce(new.followers_count, 'NULL'),coalesce(new.modified_at, 'NULL'))) then "2019-07-31 00:00:00" else bak.end_timeend as end_time from account_zip as bak left join (select * from account_snapshot where ds = "20190801") as new on bak.account_id = new.account_idunion -- 写入修改或新增的数据select a.account_id,a.username ,a.followers_count  ,a.modified_at, "2019-07-31 00:00:00" as start_time, "9999-12-31 00:00:00" as end_timefrom (   select * from account_snapshot where ds = "20190801") as a left join (select *from account_zipwhere end_time = "9999-12-31 00:00:00") on a.account_id = b.account_idwhere md5(concat(coalesce(a.username, 'NULL'),coalesce(a.followers_count, 'NULL'),coalesce(a.modified_at, 'NULL'))) != md5(concat(coalesce(b.username, 'NULL'),coalesce(b.followers_count, 'NULL'),coalesce(b.modified_at, 'NULL'))) 
)；-- 将临时拉链表写回拉链表
insert overwrite table account_zip
select * from account_zip_tmp

 

这篇关于数据治理 - 数据仓库历史数据存储 - 拉链表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/880569。

相关文章

Python获取中国节假日数据记录入JSON文件

Python获取中国节假日数据记录入JSON文件

《Python获取中国节假日数据记录入JSON文件》项目系统内置的日历应用为了提升用户体验,特别设置了在调休日期显示“休”的UI图标功能,那么问题是这些调休数据从哪里来呢？我尝试一种更为智能的方法：P... 目录节假日数据获取存入jsON文件节假日数据读取封装完整代码项目系统内置的日历应用为了提升用户体验，
阅读更多...
C# WinForms存储过程操作数据库的实例讲解

C# WinForms存储过程操作数据库的实例讲解

《C#WinForms存储过程操作数据库的实例讲解》：本文主要介绍C#WinForms存储过程操作数据库的实例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、存储过程基础二、C# 调用流程1. 数据库连接配置2. 执行存储过程（增删改）3. 查询数据三、事务处
阅读更多...
Java利用JSONPath操作JSON数据的技术指南

Java利用JSONPath操作JSON数据的技术指南

《Java利用JSONPath操作JSON数据的技术指南》JSONPath是一种强大的工具,用于查询和操作JSON数据,类似于SQL的语法,它为处理复杂的JSON数据结构提供了简单且高效... 目录1、简述2、什么是 jsONPath？3、Java 示例3.1 基本查询3.2 过滤查询3.3 递归搜索3.4
阅读更多...
MySQL大表数据的分区与分库分表的实现

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区？1.2 分区的类型1.3 分区的优点1.4 分
阅读更多...
Mysql删除几亿条数据表中的部分数据的方法实现

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T
阅读更多...
Python Dash框架在数据可视化仪表板中的应用与实践记录

Python Dash框架在数据可视化仪表板中的应用与实践记录

《PythonDash框架在数据可视化仪表板中的应用与实践记录》Python的PlotlyDash库提供了一种简便且强大的方式来构建和展示互动式数据仪表板,本篇文章将深入探讨如何使用Dash设计一... 目录python Dash框架在数据可视化仪表板中的应用与实践1. 什么是Plotly Dash？1.1
阅读更多...
Redis 中的热点键和数据倾斜示例详解

Redis 中的热点键和数据倾斜示例详解

《Redis中的热点键和数据倾斜示例详解》热点键是指在Redis中被频繁访问的特定键,这些键由于其高访问频率,可能导致Redis服务器的性能问题,尤其是在高并发场景下,本文给大家介绍Redis中的热... 目录Redis 中的热点键和数据倾斜热点键（Hot Key）定义特点应对策略示例数据倾斜（Data S
阅读更多...
Python实现将MySQL中所有表的数据都导出为CSV文件并压缩

Python实现将MySQL中所有表的数据都导出为CSV文件并压缩

《Python实现将MySQL中所有表的数据都导出为CSV文件并压缩》这篇文章主要为大家详细介绍了如何使用Python将MySQL数据库中所有表的数据都导出为CSV文件到一个目录,并压缩为zip文件到... python将mysql数据库中所有表的数据都导出为CSV文件到一个目录，并压缩为zip文件到另一个
阅读更多...
SpringBoot整合jasypt实现重要数据加密

SpringBoot整合jasypt实现重要数据加密

《SpringBoot整合jasypt实现重要数据加密》Jasypt是一个专注于简化Java加密操作的开源工具,：本文主要介绍详细介绍了如何使用jasypt实现重要数据加密,感兴趣的小伙伴可... 目录jasypt简介 jasypt的优点SpringBoot使用jasypt创建mapper接口配置文件加密
阅读更多...
使用Python高效获取网络数据的操作指南

使用Python高效获取网络数据的操作指南

《使用Python高效获取网络数据的操作指南》网络爬虫是一种自动化程序,用于访问和提取网站上的数据,Python是进行网络爬虫开发的理想语言,拥有丰富的库和工具,使得编写和维护爬虫变得简单高效,本文将... 目录网络爬虫的基本概念常用库介绍安装库Requests和BeautifulSoup爬虫开发发送请求解
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2