《ETL中增量更新》由会员分享，可在线阅读，更多相关《ETL中增量更新（5页珍藏版）》请在金锄头文库上搜索。
1、ETL中增量更新是一个比较依赖与工具和设计方法的过程，Kettle中主要提供 Insert / Update步骤，Delete步骤和Database Lookup步骤来支持增量更新， 增量更新的设计方法也是根据应用场景来选取的，虽然本文讨论的是Kettle的 实现方式，但也许对其他工具也有一些帮助。本文不可能涵盖所有的情况，欢迎 大家讨论。应用场景 增量更新按照数据种类的不同大概可以分成：1. 只增加，不更新，2. 只更新，不增加3. 即增加也更新4. 有删除，有增加，有更新其中1 ，2， 3种大概都是相同的思路，使用的步骤可能略有不同，通用的方法 是在原数据库增加一个时间戳，然后在转换之后的
2、对应表保留这个时间戳，然后 每次抽取数据的时候，先读取这个目标数据库表的时间戳的最大值，把这个值当 作参数传给原数据库的相应表，根据这个时间戳来做限定条件来抽取数据，抽取 之后同样要保留这个时间戳，并且原数据库的时间戳一定是指定默认值为 sysdate当前时间（以原数据库的时间为标准），抽取之后的目标数据库的时间 戳要保留原来的时间戳，而不是抽取时候的时间。对于第一种情况，可以使用Kettle的Insert / Update步骤，只是可以勾 选Don t perform any update选项，这个选项可以告诉Kettle你只会执行 Insert 步骤。 对于第二种情况可能比较用在数据出现错
3、误然后原数据库有一些更新，相应的目 标数据库也要更新，这时可能不是更新所有的数据，而是有一些限定条件的数据， 你可以使用Kettle的Update步骤来只执行更新。关于如何动态的执行限定条 件，可以参考前一篇文章。第三种情况是最为常见的一种情况，使用的同样是Kettle的Insert / Update 步骤，只是不要勾选Don t perform any update选项。 第四种情况有些复杂，后面专门讨论。对于第1，2，3种情况，可以参考下面的例子。get data from original tableinsert / Update这个例子假设原数据库表为customers ,含有一个id
4、 , firstname , lastname , age字段，主键为id ,然后还加上一个默认值为sysdate的时间戳字段。转换 之后的结果类似：id , firs tn ame , las tn ame , age , upda teda te . 整个设 计流程大概如下：get max date from target cus|omes.图1 其中第一个步骤的 sql 大概如下模式：Select max(updatedate) from target_customer ; 你会注意到第二个步骤和第一个步骤的连接是黄色的线，这是因为第二个 table input 步骤把前面一个步骤的输出
5、当作一个参数来用，所有 Kettle 用黄色的线 来表示，第二个 table input 的 sql 模式大概如下：Select field1 , field2 , field3 from customers where updatedate ? 后面的一个问号就是表示它需要接受一个参数，你在这个 table input 下面需 要指定 replace variable in script 选项和 execute for each row 为选中状 态，这样，Kettle就会循环执行这个sql ,执行的次数为前面参数步骤传入的 数据集的大小。Enable I吟.conver5ionReplac
6、e variables in script? Insert data from step 溯 m_date from target customers Execute for each row?. Limit size 0OKCancel Preview图2关于第三个步骤执行 insert / update 步骤需要特别解释一下，图3Kettle 执行这个步骤是需要两个数据流对比，其中一个是目标数据库，你在 Target table 里面指定的，它放在 The keys to look up the values(s) 左边 的 Table field 里面的，另外一个数据流就是你在前一个步骤
7、传进来的，它放 在 The keys to look up the value(s) 的右边， Kettle 首先用你传进来的 key 在数据库中查询这些记录，如果没有找到，它就插入一条记录，所有的值都跟你 原来的值相同，如果根据这个key找到了这条记录，kettle会比较这两条记录, 根据你指定 update field 来比较，如果数据完全一样， kettle 就什么都不做， 如果记录不完全一样，kettle就执行一个update步骤。所以首先你要确保你指 定的 key 字段能够唯一确定一条记录，这个时候会有两种情况：1.维表2.事实表 维表大都是通过一个主键字段来判断两条记录是否匹配，可
8、能我们的原数据库的 主键记录不一定对应目标数据库中相应的表的主键，这个时候原数据库的主键就 变成了业务主键，你需要根据某种条件判断这个业务主键是否相等，想象一下如 果是多个数据源的话，业务主键可能会有重复，这个时候你需要比较的是根据你 自定义生成的新的实际的主键，这种主键可能是根据某种类似与 sequence 的生 成方式生成的，事实表在经过转换之后，进目标数据库之前往往都是通过多个外键约束来确定唯 一一条记录的，这个时候比较两条记录是否相等都是通过所有的维表的外键决定 的，你在比较了记录相等或不等之后，还要自己判断是否需要添加一个新的主键 给这个新记录。上面两种情况都是针对特定的应用的，如果
9、你的转换过程比较简单，只是一个原 数据库对应一个目标数据库，业务主键跟代理主键完全相同的时候完全可以不用 考虑这么多。有删除，有增加，有更新 首先你需要判断你是否在处理一个维表，如果是一个维表的话，那么这可能是一 个 SCD 情况，可以使用 Kettle 的 Dimension Lookup 步骤来解决这个问题，如 果你要处理的是事实表，方法就可能有所不同，它们之间的主要区别是主键的判 断方式不一样。事实表一般都数据量很大，需要先确定是否有变动的数据处在某一个明确的限定 条件之下，比如时间上处在某个特定区间，或者某些字段有某种限定条件，尽量 最大程度的先限定要处理的结果集，然后需要注意的是要先
10、根据 id 来判断记录 的状态，是不存在要插入新纪录，还是已存在要更新，还是记录不存在要删除， 分别对于 id 的状态来进行不同的操作。处理删除的情况使用 Delete 步骤，它的原理跟 Insert / Update 步骤一样，只不过在找到了匹配的id之后执行的是删除操作而不是更新操作，然后处理Insert / Update 操作，你可能需要重新创建一个转换过程，然后在一个 Job 里 面定义这两个转换之间的执行顺序。如果你的数据变动量比较大的话，比如超过了一定的百分比，如果执行效率比较 低下，可以适当考虑重新建表。另外需要考虑的是维表的数据删除了，对应的事实表或其他依赖于此维表的表的 数据
11、如何处理，外键约束可能不太容易去掉，或者说一旦去掉了就可能再加上去 了，这可能需要先处理好事实表的依赖数据，主要是看你如何应用，如果只是简 单的删除事实表数据的话还比较简单，但是如果需要保留事实表相应记录，可以 在维表中增加一条记录，这条记录只有一个主键，其他字段为空，当我们删除了 维表数据后，事实表的数据就更新指向这条空的维表记录。定时执行增量更新可能有时候我们就是定时执行更新操作，比如每天或者一个星期一次，这个时候 可以不需要在目标表中增加一个时间戳字段来判断ETL进行的最大时间，直接在 取得原数据库的时间加上限定条件比如：Startdate ? and enddate ?（昨天的时间或者
12、上个星期的时间）这个时候需要传一个参数，用 get System Info 步骤来取得，而且你还可以控 制时间的精度，比如到天而不是到秒的时间。当然，你也需要考虑一下如果更新失败了怎么处理，比如某一天因为某种原因没 有更新，这样可能这一天的记录需要手工处理回来，如果失败的情况经常可能发 生，那还是使用在目标数据库中增加一个时间字段取最大时间戳的方式比较通 用，虽然它多了一个很少用的字段。执行效率和复杂度 删除和更新都是一项比较耗费时间的操作，它们都需要不断的在数据库中查询记 录，执行删除操作或更新操作，而且都是一条一条的执行，执行效率低下也是可 以预见的，尽量可能的缩小原数据集大小。减少传输的
13、数据集大小，降低 ETL 的复杂程度时间戳方法的一些优点和缺点优点: 实现方式简单，很容易就跨数据库实现了，运行起来也容易设计 缺点：浪费大量的储存空间，时间戳字段除ETL过程之外都不被使用，如果是 定时运行的，某一次运行失败了，就有可能造成数据有部分丢失.其他的增量更新办法： 增量更新的核心问题在与如何找出自上次更新以后的数据，其实大多数数据库都 能够有办法捕捉这种数据的变化，比较常见的方式是数据库的增量备份和数据复 制，利用数据库的管理方式来处理增量更新就是需要有比较好的数据库管理能 力，大多数成熟的数据库都提供了增量备份和数据复制的方法，虽然实现上各不 一样，不过由于ETL的增量更新对数
14、据库的要求是只要数据，其他的数据库对象 不关心，也不需要完全的备份和完全的st and by数据库，所以实现方式还是比 较简单的.，只要你创建一个与原表结构类似的表结构，然后创建一个三种类型 的触发器，分别对应 insert , update , delete 操作，然后维护这个新表，在 你进行ETL的过程的时候，将增量备份或者数据复制停止，然后开始读这个新表, 在读完之后将这个表里面的数据删除掉就可以了，不过这种方式不太容易定时执 行，需要一定的数据库特定的知识。如果你对数据的实时性要求比较高可以实现 一个数据库的数据复制方案，如果对实时性的要求比较低，用增量备份会比较简 单一点。几点需要注
15、意的地方：1.触发器 无论是增量备份还是数据复制，如果原表中有触发器，在备份的数据库上都不要 保留触发器，因为我们需要的不是一个备份库，只是需要里面的数据，最好所有 不需要的数据库对象和一些比较小的表都不用处理。2.逻辑一致和物理一致 数据库在数据库备份和同步上有所谓逻辑一致和物理一致的区别，简单来说就是 同一个查询在备份数据库上和主数据库上得到的总的数据是一样的，但是里面每 一条的数据排列方式可能不一样，只要没有明显的排序查询都可能有这种情况 （包括 groupby,distinct,union 等），而这可能会影响到生成主键的方式， 需要注意在设计主键生成方式的时候最好考虑这一点，比如显式的增加 order 排序. 避免在数据出错的时候，如果需要重新读一遍数据的时候主键有问题.总结增量更新是 ETL 中一个常见任务，对于不同的应用环境可能采用不同的策 略，本文不可能覆盖所有的应用场景，像是多个数据源汇到一个目标数据库，id 生成策略，业务主键和代理主键不统一等等，只是希望能给出一些思路处理比较
