数据仓库拉链算法

1、数据仓库之拉链算法（转）链:古代软兵器的中介之物,故名思意.有着连接、衔接的意思.拉链算法是目前数据仓库领域比较XX的算法之一.通用非常广.记录数据量很大且为全量实体记录历史的操作。例如，某某移动通信公司客户资料，以河北为例，河北有客户2800W，客户资料每个一条就是2800W条记录算上历史客户，全量大概有5000W条左右。作为数据仓库来存储这些信息几千万条记录不算什么。可是要是记录历史全量所用到的存储就非常的庞大。问题实例为：一般正常情况下，从河北移动的BOSS系统上每天采集全量的日数据大概为2500W条，历史存储每天存储一个2500W条的日表，存储三个月，就需要3*30*2500W条的数据

2、存储空间，数据量为20E。这只是存储三个月的历史如果存储更长时间则无法估计需要的存储。而用拉链算法存储。每日只是向历史表（HIS）中添加新增和变化的数据量。每日不过数十W条。存储一年也就是需要5000W条记录的存储空间即两个日全量的空间。下面详细介绍下拉链算法：1.采集当日全量存储到 ND（NewDay）表中。（比正常的全量表多两个字段（START_DATE&END_DATE）2.可从历史表中取出昨日全量数据存储到 OD（OldDay）表中。（比正常的全量表多两个字段（START_DATE&END_DATE）3.用NDOD为当日新增和变化的数据（即每日增量）。4.用ODND为状态到此结束需要封

3、链的数据。5.历史表（HIS）比ND表和OD表多两个字段（START_DATE&END_DATE）6.针对第三部来讲，ND和OD表的（START_DATE&END_DATE）分别记录当前日期和最大日期，取意为开始日期为当前天的数据和结束日期为最大日期。注意OD和ND的START_DATENDOD两个表进行全字段比较但是（START_DATE&END_DATE）除外。将结果记录到W_I表中ODND两个表进行全字段比较同样（START_DATE&END_DATE）除外。将结果记录到W_U表中7.将W_I表的内容全部插入到HIS表中。8. 对历史表（HIS）和OD表比较对历史表最更新操作即在历史表（

4、HIS）中数据进行更新操作以W_U表为准，即对历史表与W_U比对（START_DATE&END_DATE除外），在历史表（HIS）中也在W_U表中的数据将其END_DATE改成当前天，说明该记录对当前天失效。9。取数据时候对日期进行条件选择即可如：取日的数据条件部分为(where start-date )即可全部SQL为：(select * from table(his) where start-date ) 下面为具体例子：OD(在第一天就等于HIS)用户标志 状态 开始时间 结束时间1 1 2 2 3 3 4 4 5 5 ND用户标志 状态 开始时间 结束时间1 2 2 2 3 4 4 4

5、 5 6 W_I=ND-OD用户标志 状态 开始时间 结束时间1 2 3 4 5 6 W_U=OD-ND用户标志 状态 开始时间 结束时间1 1 3 3 5 5 INSERT操作 把I插入到HIS用户标志 状态 开始时间 结束时间1 1 2 2 3 3 4 4 5 5 1 2 3 4 5 6 update操作 按U更新HIS用户标志 状态 开始时间 结束时间1 1 2 2 3 3 4 4 5 5 1 2 3 4 5 6 表结构设计之拉链表(一)概念拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。在历史表中

6、对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：（NAME）人名（START-DATE）开始日期（END-DT）结束日期（STAT）状态client H在家client A小学client B初中client C高中client D大学client E公司client H退休在家上面的每一条记录都是不算末尾的，比如到，client已经在A，而不是H了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在END-DT那天，都不在是该条记录END-DT那天的状态。这种现象可以理解为算头不算尾。(二)算法1采集当日全量数据到ND(NewDay)表；2可

7、从历史表中取出昨日全量数据存储到OD(OldDay)表;3(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示；4(OD-ND)为状态到此结束需要封链的数据，用W_U表示；5将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值；6对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作；拉链表 实际上是一个数据的有效 更新处理方法。 在定义了对于该方法支持的几个字段后可以对数据进行处理。讲解一个加了几个字段的的一种处理方法模拟场景 ;1.定义两个临时表，一个为当日全量数据

8、，另一个为需要新增或更新的数据；CREATE TABLE A_day_full ;CREATE TABLE B ;2获取当日全量数据INSERT INTO A SELECT (a,b,c,cur_date, max_date) FROM SOURCE_Table3抽取新增或有变化的数据，从 A 临时表到B 临时表；INSERT INTO BSELECT fieldname FROM AWHERE NOT IN (select fieldname from A_HIS where end_date=max_date);4更新历史表的失效记录的end_date为max值UPDATE A1 FROM

9、 a_his A1, B A2SET End_Date=current_dateWHERE A1.xx=A2.xx AND A1.End_Date=max_date;5将新增或者有变化的数据插入目标表*/INSERT INTO A_HIS SELECT * FROM B ;全量主要数据表加载的策略为每次加载时需要根据主键将目标表的数据与源表数据进行比对，删除目标表中在源数据表中的相关记录，然后将源表数据全部插入目标表。表现在脚本上为先delete相关记录，然后insert所有记录。主表加载策略主要用于大部分主表的加载，比如客户信息等主要数据表。增量拉链是指每次加载时，将源表数据视为增量抽取后的结果，加载到目标表时需要考虑数据历史情况。一般数据发生变化时关闭旧数据链，然后开新数据链。增量拉链针对的是历史表情况，由于数据仓库中记录了大部分数据历史表变化情况，因此增量拉链加载策略在数据仓库中是