数据仓库拉链算法

数据仓库之拉链算法（转）链:古代软兵器的中介之物，故名思意.有着连接、衔接的意思.拉链算法是目前数据仓库领域比较XX的算法之一..通用非常广.记录数据量很大且为全量实体记录历史的操作。例如，某某移动通信公司客户资料，以河北为例，河北有客户2800W，客户资料每个一条就是2800W条记录算上历史客户，全量大概有5000W条左右。作为数据仓库来存储这些信息几千万条记录不算什么。可是要是记录历史全量所用到的存储就非常的庞大。问题实例为：一般正常情况下，从河北移动的BOSS系统上每天采集全量的日数据大概为2500W条，历史存储每天存储一个2500W条的日表，存储三个月，就需要3*30*2500W条的数据存储空间，数据量为20E。这只是存储三个月的历史如果存储更长时间则无法估计需要的存储。而用拉链算法存储。每日只是向历史表（HIS）中添加新增和变化的数据量。每日不过数十W条。存储一年也就是需要5000W条记录的存储空间即两个日全量的空间。下面详细介绍下拉链算法：采集当日全量存储到ND（NewDay）表中。（比正常的全量表多两个字段（START_DATE&END_DATE））可从历史表中取出昨日全量数据存储到OD（OldDay）表中。（比正常的全量表多两个字段（START_DATE&END_DATE））用ND-OD为当日新增和变化的数据（即每日增量）。用OD-ND为状态到此结束需要封链的数据。历史表（HIS）比ND表和OD表多两个字段（START_DATE&END_DATE）针对第三部来讲，ND和OD表的（START_DATE&END_DATE）分别记录当前日期和最大日期，取意为开始日期为当前天的数据和结束日期为最大日期。注意OD和ND的START_DATEND——OD两个表进行全字段比较但是（START_DATE&END_DATE）除外。将结果记录到W_I表中OD——ND两个表进行全字段比较同样（START_DATE&END_DATE）除外。将结果记录到W_U表中将W_I表的内容全部插入到HIS表中。对历史表（HIS）和OD表比较对历史表最更新操作即在历史表（HIS）中数据进行更新操作以W_U表为准，即对历史表与W_U比对（START_DATE&END_DATE除外），在历史表（HIS）中也在W_U表中的数据将其END_DATE改成当前天，说明该记录对当前天失效。9。 取数据时候对日期进行条件选择即可如：取20080101日的数据条件部分为（wherestart-date<='20080101'andend_date>20070801）即可全部SQL为：（select*fromtable（his）wherestart-date<='20080101'andend_date>20070801）下面为具体例子：OD（在第一天就等于HIS）用户标志状态开始时间结束时间12007122999012200712299901320071229990142007122999015200712299901ND用户标志状态开始时间结束时间22008012999012200801299901420080129990156200801299901W_I=ND-OD用户标志状态开始时间结束时间22008012999013420080129990156200801299901W_U=OD-ND用户标志状态开始时间结束时间12007122999013320071229990155200712299901INSERT操作把I插入到HIS用户标志状态开始时间结束时间12007122999012200712299901320071229990142007122999015200712299901122008012999013420080129990156200801299901update操作按U更新HIS用户标志状态开始时间结束时间2200712299901320071220080142007122999015200712200801122008012999013420080129990156200801299901表结构设计之拉链表（一）概念拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：（NAME）人名（START-DATE）开始日期（END-DT）结束日期（STAT）状态client1900010119070901H在家client1907090119130901A小学client1913090119160901B初中client1916090119190901C高中client1919090119230901D大学client1923090119601231E公司client1960123129991231H退休在家上面的每一条记录都是不算末尾的，比如到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.定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据；CREATETABLEA_day_full;CREATETABLEB;2获取当日全量数据INSERTINTOASELECT(a,b,c,cur_date,max_date)FROMSOURCE_Table3抽取新增或有变化的数据，从A临时表到B临时表;INSERTINTOBSELECTfieldnameFROMAWHERENOTIN(selectfieldnamefromA_HISwhereend_date=max_date’);4更新历史表的失效记录的end_date为max值UPDATEA1FROMa_hisA1,BA2SET End_Date=’current_date’WHEREA1.xx=A2.xxANDA1.End_Date=’max_date’;5将新增或者有变化的数据插入目标表*/INSERTINTOA_HISSELECT*FROMB;全量主要数据表加载的策略为每次加载时需要根据主键将目标表的数据与源表数据进行比对，删除目标表中在源数据表中的相关记录，然后将源表数据全部插入目标表。表现在脚本上为先delete相关记录，然后insert所有记录。主表加载策略主要用于大部分主表的加载，比如客户信息等主要数据表。增量拉链是指每次加载时，将源表数据视为增量抽取后的结果，加载到目标表时需要考虑数据历史情况。一般数据发生变化时关闭旧数据链，然后开新数据链。增量拉链针对的是历史表情况，由于数据仓库中记录了大部分数据历史