ETL----如何抽取变化的数据

数据仓库建模 同时被 3 个专栏收录
19 篇文章 0 订阅
7 篇文章 0 订阅
        在初始化加载时,捕获源数据中的数据内容的变化不是很重要,因为很可能会导出整个数据源或其中的一部分。然而一旦初始加载完成,捕获源系统中的数据变化立即变成非常重要的任务。如果等到初始加载完成才开始规划数据变化的捕获技术,则将遇到很大的麻烦,捕获数据变化绝不是一个简单的任务,必须制定相应的策略来在项目中捕获源数据中不断增长的变化。 
        ETL 小组在后续的加载过程中负责捕获数据内容的变化。需求由用户提出,而这些需求的实际可行性由源系统的DBA小组决定(如果运气好的话)。更多的时候并不是这样,需要针对特定的情形做一些研究来决定最有可能的增量加载策略。在这一节,我们提供几种选项并讨论每一种的优缺点。当然,不必为每一种情形选择所有这些技术。选择在整个项目过程中最能满足每个ETL需求的方法。 
        为识别源系统中变化的数据制定适当的策略可能要进行一些分析工作。当分析源系统时,绝对不要假设所见即所得。在许多案例中,还有许多未加使用的甚至是禁止使用的审计列,或更糟糕一些的是完全不一致的列用法。确保为增量加载过程分配了足够的分析时间来调查和决定捕获数据内容变化的最佳方法。 


监测变化 

        当管理者谈论数据仓库维护时,他们最经常讨论的是保持数据最新,也就是数据真正反映了公司业务系统的状态。捕获源系统内容的变化对一个数据仓库的成功至关重要,数据内容的维护则依赖于增量加载过程。有几种捕获源数据变化的方法,每一种在它们相应的环境中都是有效的。 

  • 使用审计列 
        在大多数情形下,源系统都包含审计列。审计列附在每个表的最后用来存储记录增加或修改的日期和时间。审计列一般通过数据库触发器产生,当插入或更新记录时自动生成。有时,为了提高性能,这些列由前端应用程序而不是由数据库触发器产生。当这些字段不是由数据库触发器以外的任意其它方法加载时,必须要特别注意它们的完整性。必须分析并测试每个列以确保其是表示变化数据的可靠的数据源。如果发现任何NULL值,则必须另寻替代办法来监测数据变化。 
        阻止ETL过程使用审计列的最常见情形是当这些字段是由前端应用程序产生的并且DBA小组允许后台脚本修改数据时。如果是这种情形,则将在增量加载过程中面临很高的错过变化数据的风险。最小化这一风险防范措施之一是规定所有的后台脚本由一个质量保证小组来验证,在验收之前坚持并测试由脚本产生的审计字段。 

        一旦确信审计列是可靠的,则需要制定一个策略来利用它们。有各种各样利用审计列来捕获变化数据的方法,并且所有的方法都拥有同样的逻辑目标:比较每个记录最后修改的日期和时间与上次加载后的最后的日期和时间,取它们中较晚的值。 
        已经发现一个利用源系统中审计列的有效方法。实际上,这个过程就是从创建日期和最后修改日期列中选择最后日期和时间。有些最后修改列是随着插入或更新记录的变化而修改的,其它最后修改列在记录插入时是NULL,只有当这个记录更新时才插入相应日期值。当最后修改日期没有填充时,为了不丢失新记录必须给定任意一个非常早的日期作为默认值。以下代码可以帮助解决NULL修改日期: 

              select max(greatest(nvl(create_date,'01-JAN-0001'), 
              nvl(last_mod_date,'01-JAN-0001'))) 
对事实表中的行只插入不更新的情形,可以简单的从源系统选择创建日期和时间大于上次加载的最后日期和时间来实现,从而忽略最后修改日期列。 由于事实表和维表可以来源于许多不同的表和系统,并且由于事实表只包含外键和度量,因此不能直接存储审计日期列到事实表中。在每次导出的时候,需要建立一个ETL 的最后修改表来捕获每个源表和在源系统审计列找到的最大日期。如果事实表需要对行数进行审计统计,考虑使用第4章所讲述的建立一个审计维的方法。 

  • 数据库日志的获取和提取 
        日志获取是在调度点(一般在深夜)对数据库重做日志进行有效的快照并把它作为对ETL加载过程中所关心的表产生影响的数据源。提取包含重做日志的登记,并从中捕获相应事务数据。抓取日志可能是所有技术中最笨的。事务日志经常会溢出,这意味日志被写满并阻止新事务的产生。当这种溢出发生在一个生产环境时,负责的DBA最快的反应会是清空日志的内容以使业务操作重新恢复。但是当日志清空时,里面所有的事务也都丢失了。如果已经用尽了所有的其它技术,然后发现日志抓取是为寻找新的或变化的记录的最后求助的手段,那么就试图说服DBA创建一个特殊的日志来满足这一特殊需要。大概只需要源数据库成百张表中很少的某些交易表,当插入或更新这些表时触发动作写入专用日志中。 

       如果想继续日志提取,我们建议调查市场上可用的ETL工具来寻找验证有效的方案而不是试图从抓取开始手工写这个处理过程。许多实时ETL方案提供商都利用了日志提取技术。 

  • 按时抽取 
        选择所有创建或修改的日期等于SYSDATE-1的行,即获得昨天的所有记录。看起来很不错,对吗?其实是错误的。纯粹的按照时间来加载记录是大多数刚开始进行ETL开发的人常犯的错误,这种处理方式极其不可靠。 

        当从中间处理失败后重启时,基于时间的数据选择会加载重复的行。这意味着无论什么原因使处理失败都需要人工干预和数据清洗。其间,如果某天的加载处理没有运行并错过了一天,存在的风险是错过那天的数据将永远不会进入数据仓库中。因此除非ETL处理非常简单并且数据量特别小,否则不要纯粹基于时间加载数据。 

  • 排除处理 
        排除处理在集结区保存了每个上次导出数据副本为将来所用。在下一次运行过程中,整个源表被拿到集结区与最后一次处理留下的数据副本进行比较。只有不同(增量)部分会送到数据仓库。虽然这不是最有效的技术,但排除处理是所有捕获变化数据的增量加载技术中最可靠的。因为这个处理进行了逐行的比较并查找出变化的行,它原则上不可能遗漏任何数据。这种技术对找到那些从源数据中删除的行也有帮助,而这些删除的行往往被其它的技术遗漏掉。 
这种技术可以在数据库管理系统的内部或外部完成。如果选择使用DBMS,则为了提高效率必须成批的将数据加载到集结数据库来处理。 

  • 初始和增量加载 
        创建两个表:previous_load 和  current_load。 初始化处理批量加载到current_load表。因为初始加载过程中变化检测是无关的,因此数据连续被转换并加载到最终目标事实表中。 当这个处理完成后,就删除这个previous_load表,并把current_load表重命名为previous_load表,然后创建一个空的current_load表。由于这些任务都没有
涉及数据库日志,因此速度会非常快! 下一次加载过程运行时,这个current_load表就填充了数据。 选择current_load表MINUS(减去)previous_load表,转换并加载结果集到数据仓库中。 完成后删除previous_load表并再次重命名current_load表为previous_load。最后,创建一个空的current_load表。 由于在数据库管理系统中MINUS是非常慢的技术,因此最好使用ETL工具或第三方应用程序来执行例行的排除处理。 


抽取的技巧 

当进行抽取处理时考虑以下几点: 

  强制列索引。和DBA一起工作来确保源系统中所有在WHERE语句中的列都有索引,否则将可能引起对整个生产数据库的全表扫描。 
  获取需要的数据。优化的查询会正确返回需要的数据。不应该获取整个表,然后在ETL工具中再过滤掉不想要的数据。有一种情况可能不遵守这个规则,就是交易系统的DBA拒绝对你的查询需要限制返回行的列索引时。另一个例外是当必须下载整个源数据表来查询增量时。 
  谨慎使用DISTINCT。DISTINCT语句非常慢。在抽取查询中执行DISTINCT和在ETL工具中对结果进行聚合或分组两种方式如何平衡是一个挑战,一般会随着数据源中重复百分比的不同而不同。因为有许多其它因素可以影响这种决定,所有我们所能建议的就是谨慎的测试每个策略,从而找出最有效的结果。 

  谨慎使用SET操作符。UNION,MINUS和INTERSECT 都是SET操作符。这些和DISTINCT一样都是非常慢的。可以理解的是有时这些操作符是不可避免的。一个技巧是使用UNION ALL而不要使用UNION。UNION执行和DISTINCT相当,处理缓慢,而UNION ALL的缺点是会返回重复行,因此都要谨慎处理。 
  根据需要使用HINT。大多数数据库支持HINT关键字。可以使用HINT做许多事情,但最重要的是强制查询使用常规的索引,尤其当使用IN 或OR操作符时这个功能尤其重要,因为这时往往会进行全表扫描而不是使用索引,即使有可用的索引存在。 
  避免NOT。如果有可能,尽量避免不等于的限制和连接。无论使用关键字NOT还是操作符‘<>’,数据库此时也会选择扫描整个表而不是利用索引。 
  避免在where子句中使用函数。这是很难避免的一种,尤其是当包含日期和类似日期的处理时。在犯下在WHERE语句中使用函数的错误前,先要用不同的技术进行试验。无论如何尽量尝试使用比较关键字而不是函数。比如: 

               LIKE 'J%' 而不是  SUBSTR('LAST_NAME',1,1 ) = 'J' 
               EFF_DATE BETWEEN '01-JAN-2002' AND '31-JAN-2002' 而不是 
               TO_CHAR(EFF_DATE, 'YYY-MON' ) = '2002-JAN' 


       抽取查询的目的是获得所有相关的自然键和度量。它可能和从一个表选择多个列那么简单,也可能和实际创建不存在的数据那样复杂,范围可能从很少几个表的关联到不同数据源的许多表的关联。在一个特定项目中,我们必须建立一个周期性的快照事实表来表示库存中每个产品的销量,即使在这个期间没有销售任何产品。我们不得不产生一个产品列表,获得所有产品的销售,并执行一个产品列表和产品销售之间的外关联,没有销售的产品的销售量默认为0。 

监测数据源中删除或覆盖的事实记录 

       如果没有删除或覆盖事件发生的通知机制,对于源系统中删除或覆盖了的度量(事实)记录如何发现可能会给数据仓库带来非常大的挑战。由于一般不可能重复抽取旧的交易记录,关于如何查找这些被删除的和修改的数据,能提供最好的方法如下: 
  和源系统的所有者商讨,如果可能,明确的通知所有删除或覆盖的度量。 
  周期性的检查来自源系统的度量的历史汇总并通知ETL人员有些内容变化了。当发现变化时,尽可能的钻取下去找到这个变化。 
        当识别出一个删除或修改的度量记录时,就可以使用前面所述的最后返回数据技术了。对于删除或修改事实记录的情形,不仅在数据仓库中执行删除或更新,还推荐插入一条新的记录来实现在事实表中消除或抵消初始放的值。在许多应用中,这将汇总报表事实得到正确的数量(如果它是添加的)并提供一种纠错发生时的审计跟踪功能。在这些情形下,它可能也方便的携带额外的管理时间戳来识别这些数据库行为发生的时间。 

  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

Cormier-an

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值