数据仓库数据模型之:极限存储--历史拉链表

简介: 摘要: 在数据仓库的数据模型设计过程中,经常会遇到文内所提到的这样的需求。而历史拉链表,既能满足对历史数据的需求,又能很大程度的节省存储资源。在数据仓库的数据模型设计过程中,经常会遇到这样的需求:1. 数据量比较大;2. 表中的部分字段会被update,如用户的地址,产品的描述信息,订单的状态等等;3. 需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的

摘要: 在数据仓库的数据模型设计过程中,经常会遇到文内所提到的这样的需求。而历史拉链表,既能满足对历史数据的需求,又能很大程度的节省存储资源。


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


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

举个简单例子,比如有一张订单表,6月20号有3条记录:

133510qxpmmh8qp8prr7lh.png

到6月21日,表中有5条记录:

1335321gqiay5titqmigna.png

到6月22日,表中有6条记录:

133609tiaag1dba8atwiag.png

数据仓库中对该表的保留方法:

 

1. 只保留一份全量,则数据和6月22日的记录一样,如果需要查看6月21日订单001的状态,则无法满足;

2. 每天都保留一份全量,则数据仓库中的该表共有14条记录,但好多记录都是重复保存,没有任何变化,如订单002,004,数据量大了,会造成很大的存储浪费;

 


如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表:13363028cg41y11ko2k8ko.png

说明:

 

1. dw_begin_date表示该条记录的生命周期开始时间,dw_end_date表示该条记录的生命周期结束时间

2. dw_end_date = '9999-12-31'表示该条记录目前处于有效状态;

3. 如果查询当前所有有效的记录,则select * from order_his where dw_end_date = '9999-12-31'


4. 如果查询2012-06-21的历史快照,则select * from order_his where dw_begin_date <= '2012-06-21' and dw_end_date >= '2012-06-21',这条语句会查询到以下记录:133645s3zof33958xz9oxq.png

和源表在6月21日的记录完全一致:

1337024e4229tzn5ttpmp6.png



拉链表设计


 在企业中,由于有些流水表每日有几千万条记录,数据仓库保存5年数据的话很容易不堪重负,因此可以使用拉链表的算法来节省存储空间。

1.采集当日全量数据存储到 ND(当日) 表中。 
2.可从历史表中取出昨日全量数据存储到 OD(上日数据)表中。
3.用ND-OD(minus差集)为当日新增和变化的数据(即日增量数据)。

两个表进行全字段比较,将结果记录到tabel_I表中

4.用OD-ND为状态到此结束需要封链的数据。 (需要修改END_DATE)

两个表进行全字段比较,将结果记录到tabel_U表中 
5.历史表(HIS)比ND表和OD表多两个字段(START_DATE,END_DATE) 
6.将tabel_I表的内容全部insert插入到HIS表中。START_DATE='当日',END_DATE可设为'9999-12-31' 
7.更新封链记录的END_DATE

历史表(HIS)和tabel_U表比较,START_DATE,END_DATE除外,以tabel_U表为准,两者交集(INTERSECT)将其END_DATE改成当日,说明该记录失效。 
8。取数据时对日期进行条件选择即可,如:取20100101日的数据为 
(where START_DATE<='20100101' and END_DATE>='20100101' )


union all  并集,并排除重复记录:

union   并集,并包含重复记录


参考文章:

http://www.cnblogs.com/zhangchenliang/archive/2012/09/11/2680945.html


原文链接:

http://www.dataguru.cn/portal.php?mod=view&aid=3272



目录
相关文章
|
1月前
|
SQL 存储 大数据
数据仓库(10)数仓拉链表开发实例
拉链表是数据仓库中特别重要的一种方式,它可以保留数据历史变化的过程,这里分享一下拉链表具体的开发过程。 维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录,通过拉链表可以很方便的还原出拉链时点的客户记录。
162 13
数据仓库(10)数仓拉链表开发实例
|
4月前
|
SQL 存储 分布式计算
【大数据技术Hadoop+Spark】Hive数据仓库架构、优缺点、数据模型介绍(图文解释 超详细)
【大数据技术Hadoop+Spark】Hive数据仓库架构、优缺点、数据模型介绍(图文解释 超详细)
157 0
|
4月前
|
存储
云数据仓库ADB存储节点信息?
云数据仓库ADB存储节点信息?
33 1
|
存储 数据采集 分布式计算
一篇文章搞懂数据仓库:四种常见数据模型(维度模型、范式模型等)
一篇文章搞懂数据仓库:四种常见数据模型(维度模型、范式模型等)
一篇文章搞懂数据仓库:四种常见数据模型(维度模型、范式模型等)
|
存储 SQL 大数据
一篇文章搞懂数据仓库:三种事实表(设计原则,设计方法、对比)
一篇文章搞懂数据仓库:三种事实表(设计原则,设计方法、对比)
一篇文章搞懂数据仓库:三种事实表(设计原则,设计方法、对比)
|
6月前
|
存储 数据挖掘 关系型数据库
数仓学习---6、数据仓库概述、 数据仓库建模概述、维度建模理论之事实表、维度建模理论之维度表
数仓学习---6、数据仓库概述、 数据仓库建模概述、维度建模理论之事实表、维度建模理论之维度表
|
3月前
|
数据挖掘 数据库
离线数仓6.0--- 数据仓库 ER模型-范式理论,维度模型、维度建模理论之事实表、维度建模理论之维度表
离线数仓6.0--- 数据仓库 ER模型-范式理论,维度模型、维度建模理论之事实表、维度建模理论之维度表
106 0
|
4月前
|
存储 大数据 数据管理
数据仓库(08)数仓事实表和维度表技术
所谓的事实表和维度表技术,指的就是如何和构造一张事实表和维度表,是的事实表和维度表,可以涵盖现在目前的需要和方便后续下游数据应用的开发
49 1
|
10月前
|
SQL 存储 HIVE
数据仓库系列--维度表技术
数据仓库系列--维度表技术
104 0
|
存储 弹性计算 监控
波克城市引入阿里云数据仓库AnalyticDB,助力万亿级数据秒级分析,节省80%以上存储成本
AnalyticDB是阿里云自研、经过大规模验证的云原生数据仓库,曾在权威评测机构TPC组织的TPC-DS和TPC-H测试中获得性能和性价比全球第一的成绩。
波克城市引入阿里云数据仓库AnalyticDB,助力万亿级数据秒级分析,节省80%以上存储成本