2022年数据仓库拉链算法 .pdf

数据仓库之拉链算法（转）链:古代软兵器的中介之物,故名思意 .有着连接、衔接的意思.拉链算法是目前数据仓库领域比较 XX 的算法之一 .通用非常广 .记录数据量很大且为全量实体记录历史的操作。例如，某某移动通信公司客户资料，以河北为例，河北有客户2800W，客户资料每个一条就是 2800W 条记录算上历史客户，全量大概有5000W 条左右。作为数据仓库来存储这些信息几千万条记录不算什么。可是要是记录历史全量所用到的存储就非常的庞大。问题实例为：一般正常情况下，从河北移动的BOSS 系统上每天采集全量的日数据大概为2500W条，历史存储每天存储一个2500W 条的日表，存储三个月，就需要3*30*2500W 条的数据存储空间，数据量为20E。这只是存储三个月的历史如果存储更长时间则无法估计需要的存储。而用拉链算法存储。每日只是向历史表（HIS）中添加新增和变化的数据量。每日不过数十 W 条。存储一年也就是需要5000W 条记录的存储空间即两个日全量的空间。下面详细介绍下拉链算法：1.采集当日全量存储到 ND （ NewDay）表中。（比正常的全量表多两个字段（START_DATE&END_DATE）2.可从历史表中取出昨日全量数据存储到 OD（OldDay）表中。（比正常的全量表多两个字段（ START_DATE&END_DATE）3.用 NDOD 为当日新增和变化的数据（即每日增量）。4.用 ODND 为状态到此结束需要封链的数据。5.历史表（ HIS）比 ND 表和 OD 表多两个字段（START_DATE&END_DATE）6.针对第三部来讲，ND 和 OD 表的（ START_DATE&END_DATE）分别记录当前日期和最大日期，取意为开始日期为当前天的数据和结束日期为最大日期。注意OD 和 ND 的START_DATE ND OD 两个表进行全字段比较但是（START_DATE&END_DATE）除外。将结果记录到 W_I 表中名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 6 页 - - - - - - - - - OD ND 两个表进行全字段比较同样（START_DATE&END_DATE）除外。将结果记录到 W_U 表中7.将 W_I 表的内容全部插入到HIS 表中。8. 对历史表（ HIS）和 OD 表比较对历史表最更新操作即在历史表（HIS）中数据进行更新操作以 W_U 表为准，即对历史表与W_U 比对（ START_DATE&END_DATE除外），在历史表（ HIS）中也在W_U 表中的数据将其END_DATE 改成当前天，说明该记录对当前天失效。9。取数据时候对日期进行条件选择即可如：取20080101 日的数据条件部分为(where start-date20070801 ) 即可全部 SQL 为：(select * from table(his) where start-date20070801 ) 下面为具体例子：OD(在第一天就等于HIS) 用户标志 状态 开始时间结束时间1 1 200712 299901 2 2 200712 299901 3 3 200712 299901 4 4 200712 299901 5 5 200712 299901 ND 用户标志 状态 开始时间结束时间1 2 200801 299901 2 2 200801 299901 3 4 200801 299901 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 6 页 - - - - - - - - - 4 4 200801 299901 5 6 200801 299901 W_I=ND-OD 用户标志 状态 开始时间结束时间1 2 200801 299901 3 4 200801 299901 5 6 200801 299901 W_U=OD-ND 用户标志 状态 开始时间结束时间1 1 200712 299901 3 3 200712 299901 5 5 200712 299901 INSERT 操作 把 I 插入到 HIS 用户标志 状态 开始时间结束时间1 1 200712 299901 2 2 200712 299901 3 3 200712 299901 4 4 200712 299901 5 5 200712 299901 1 2 200801 299901 3 4 200801 299901 5 6 200801 299901 update 操作 按 U 更新 HIS 用户标志 状态 开始时间结束时间名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 6 页 - - - - - - - - - 1 1 200712 200801 2 2 200712 299901 3 3 200712 200801 4 4 200712 299901 5 5 200712 200801 1 2 200801 299901 3 4 200801 299901 5 6 200801 299901 表结构设计之拉链表(一)概念拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：（NAME ）人名（START-DATE ）开始日期（END-DT ）结束日期（STAT ）状态client 19000101 19070901 H 在家client 19070901 19130901 A小学client 19130901 19160901 B 初中client 19160901 19190901 C 高中client 19190901 19230901 D 大学client 19230901 19601231 E 公司client 19601231 29991231 H 退休在家名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 6 页 - - - - - - - - - 上面的每一条记录都是不算末尾的，比如到19070901，client 已经在 A，而不是H 了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在END-DT 那天，都不在是该条记录END-DT 那天的状态。这种现象可以理解为算头不算尾。(二)算法1 采集当日全量数据到ND(NewDay) 表；2 可从历史表中取出昨日全量数据存储到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. 定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据；CREATE TABLE A_day_full ; CREATE TABLE B ; 2 获取当日全量数据INSERT INTO A SELECT (a,b,c,cur_date, max_date) FROM SOURCE_Table 3 抽取新增或有变化的数据，从 A 临时表到 B 临时表；名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 6 页 - - - - - - - - - INSERT INTO B SELECT fieldname FROM A WHERE NOT IN (select fieldname from A_HIS where end_date=max_date); 4 更新历史表的失效记录的end_date 为 max值UPDATE A1 FROM a_his A1, B A2 SET End_Date=current_dateWHERE A1.xx=A2.xx AND A1.End_Date=max_date; 5 将新增或者有变化的数据插入目标表*/ INSERT INTO A_HIS SELECT * FROM B ; 全量主要数据表加载的策略为每次加载时需要根据主键将目标表的数据与源表数据进行比对，删除目标表中在源数据表中的相关记录，然后将源表数据全部插入目标表。表现在脚本上为先delete 相关记录，然后insert 所有记录。主表加载策略主要用于大部分主表的加载，比如客户信息等主要数据表。增量拉链是指每次加载时，将源表数据视为增量抽取后的结果，加载到目标表时需要考虑数据历史情况。一般数据发生变化时关闭旧数据链，然后开新数据链。增量拉链针对的是历史表情况，由于数据仓库中记录了大部分数据历史表变化情况，因此增量拉链加载策略在数据仓库中是使用比较广泛的一种加载策略。通常这种历史表都含有start_date和 end_date字段，首先全字段对比源数据和目标表得出真正的增量数据，这里的全字段不包含start_date和 end_date字段，然后根据主键对目标表进行关旧链操作，然后对新增数据开新链，这种拉链策略同样可以处理全量数据。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 6 页 - - - - - - - - -