ETL加载及增量策略.doc

ETL数据加载及增量策略本文所提到的数据加载策略为OLTP系统作为源系统，并进行ETL数据加载到OLAP系统中所采用的一般数据加载策略。根据该方式的特定性，此时ETL数据加载一般存在以下四种方案：一、 时戳方式需要在OLTP系统中业务表中统一添加时间字段作为时戳（如表中已有相应的时间字段，可以不必添加），每当OLTP系统中更新修改业务数据时，同时修改时戳字段值。当作ETL加载时，通过系统时间与时戳字段的比较来决定进行何种数据抽取。优点：ETL系统设计清晰，源数据抽取相对清楚简单，速度快。可以实现数据的递增加载。缺点：时戳维护需要由OLTP系统完成，需要修改原OLTP系统中业务表结构；且所有添加时戳的表，在业务系统中，数据发生变化时，同时更新时戳字段，需要对原OLTP系统业务操作程序作修改，工作量大，改动面大，风险大。Informatica8具体做法：一个原表：主键为：MON 和 KINDID一个目标表：主键为：KINDID一个mapping为m_Source_Have_TimetStamp:具体展开为：FILTRANS组件最关键：过滤条件是：时间戳字段等于系统时间，或者时间戳字段等于ETL的时间，或者时间戳字段等于指定的数据。Look up组件查找是目标表，匹配条件是：update组建最关键：代码是：iif(isnull(DISTRICTNUM_Tgt),dd_insert,iif(DISTRICTNUM_Tgt != DISTRICTNUM_Src or DISTRICTNAME_Tgt != DISTRICTNAME_Src ),dd_update,dd_reject) )二、 日志表方式在OLTP系统中添加系统日志表，当业务数据发生变化时，更新维护日志表内容，当作ETL加载时，通过读日志表数据决定加载那些数据及如何加载。 优点：不需要修改OLTP表结构，源数据抽取清楚，速度较快。可以实现数据的递增加载。缺点：日志表维护需要由OLTP系统完成，需要对OLTP系统业务操作程序作修改，记录日志信息。日志表维护较为麻烦，对原有系统有较大影响。工作量较大，改动较大。有一定风险。Informatica8具体做法：一个原表：主键为：ID 一个目标表：主键为：ID在OLTP系统中建设一个触发器：触发器记录的是增加和修改和删除业务数据的时间和操作类型。代码为：create or replace trigger a_log_trigger before update or insert or delete on a for each rowdeclare optype char(1); id varchar2(10);begin id := :new.id; if updating then optype := U; elsif deleting then optype := D; id := :old.id; else optype := I; end if; insert into a_log(optype,id) values(optype,id);end;比如：业务表A 和 表A的日志表，表A的日志表信息：这样日志信息不是很多吗？怎么定时维护呢。是手工？读取完就可以删除，或标记已经读取就可以触发三、 全表比对方式在ETL过程中，抽取所有源数据，并进行相应规则转换，完成后先不插入目标，而对每条数据进行目标表比对。根据主键值进行插入与更新的判定，目标表已存在该主键值的，表示该记录已有，并进行其余字段比对，如有不同，进行Update操作，如目标表没有存在该主键值，表示该记录还没有，即进行Insert操作。优点：对已有系统表结构不产生影响，不需要修改业务操作程序，所有抽取规则由ETL完成，管理维护统一，可以实现数据的递增加载。没有风险。缺点：ETL比对较复杂，设计较为复杂，速度较慢Informatica8具体做法：一个原表：主键为：MON 和 DISTRICTNUM一个目标表：主键为：DISTRICTNUM一个mapping为m_All_Table_Compare：具体展开为：Look up组建查找是目标表，匹配条件是：update组建最关键：注意：还有一个关键的是，在一个seesion中对目标表进行不要进行删除。结束，这样增加、删除、修改，都可以更新目标数据了。四、 全表有条件的删除方式总体的意思是：原表一般是设定时间字段的条件取出想要的字段，然后在目标表中有条件的删除，再做ETL，这样就可以实现有条件的增量。优点：取的字段少，效率高，不容易错，修改比较容易缺点：手动写SQL语句，要修改时间条件的时候要手动修改。1) 在原表中设置,通过时间字段有条件的取出想要的字段， Sql Query :SELECT PRO_POWER_SUPPLY.ITEM_NAME, PRO_POWER_SUPPLY.SORT_CHAR, PRO_POWER_SUPPLY.M_SUPPLY, PRO_POWER_SUPPLY.Y_SUPPLY, PRO_POWER_SUPPLY.M_SELLFROM stat.PRO_POWER_SUPPLY WHERE item_name like %电业局%and (tab_year =to_number(to_char(sysdate,yyyy) or (tab_year =to_number(to_char(sysdate,yyyy)-1 and tab_month9)2) 在目标表中设置Pre SQL写:deletefrom corporationprovidepowerinfo twhere (substr(videpowermonth,1,4) =(to_char(sysdate,yyyy) or (substr(videpowermonth,1,4)=(to_char(sysdate,yyyy)-1 and substr(videpowermonth,5,2)9)3) 在目标表中设置，把Truncate target table钩去掉。五、 带变量的有条件的取数据有条件的删除数据方式总体的意思是：原表一般是设定时间字段的条件用变量取出想要的字段，然后在目标表中用变量有条件的删除，设置变量为系统时间，再做ETL，这样就可以实现有条件的增量。优点：取的字段少，数据量准确，效率高，不容易错，不需要什么修改。缺点：手动写SQL语句，要修改时间条件的时候要手动修改，必须设置变量的值，调整系统时间为正确的时间。1）在原表中设置,通过时间字段有条件的取出想要的字段， Sql Query :SELECT MON, KINDID, KINDNAME FROM POWERKIND WHERE MON=to_date($End_Data,MM/DD/YYYY hh24:mi:ss)2）在目标表中设置Pre SQL写:delete POWERKIND WHERE MON=to_date($End_Data,MM/DD/YYYY hh24:mi:ss)3）在变量窗口中配置一个时间变量。4）表达式的设置，增加一个设置变量为系统时间。NEWFIELD的表达式：SETVARIABLE($End_Data,SYSDATE)5）在目标表中设置，把Truncate target table钩去掉。测试结果：Informatica服务端的系统时间为：原数据：目标数据：六、 全表删除插入方式每次ETL操作均删除目标表数据，由ETL全新加载数据。优点：ETL加载规则简单，速度快缺点：对于维表加代理键不适应，当OLTP系统产生删除数据操作时，OLAP层将不会记录到所删除的历史数据。不可以实现数据的递增加载。这个比较简单，Informatica8具体做法：在一个seesion中对目标表进行删除就好了。结束。七、 总结：当作系统数据加载策略方案时，基于以上所列方法，及现有系统考虑：（1）、如果所集成OLTP系统为其他产商产品，则应尽量的降低因ETL而对现有系统产生的影响，及系统风险性。而性能的影响则可以通过两方面解决，一部分由硬件的升级进行解决，因为ETL除读表及写表操作外，所有转换均由ETL服务器在内存中完成，故高配置服务器将大大提升ETL运行速度；一部分由加载时机进行控制，加载时机采取在系统较为空闲时加载，同时并行多个加载等，可以降低对运行系统的影响。所以可以