ETL里的34个子系统

Ralph Kimball和Joe Caserta于2004年编写的《The Data Warehouse ETL Toolkit》一书系统的阐述了ETL这一概念及建设ETL系统的要点，将ETL从BI的一部分抽离了出来。随后，这本书里的一些思想形成了一篇文章《ETL里的38个子系统》，系统总结了ETL项目要面临的不同任务。我们还可以在网上找到原始的这篇文章https://www.informationweek.com/software/information-management/the-38-subsystems-of-etl/d/d-id/1028653。


在2008年，Wiley出版了最流行的一本BI图书的第二版：也是由Kimball和他同事编写的《The Data Warehouse Lifecycle Toolkit》。在这本书里ETL子系统被重构成了34种子系统。


接下来本文将简要介绍这34种ETL里的子系统。这34种子系统提供了一套框架，帮助我们理解ETL解决方案的实现和管理，并对其进行分类。在ETL解决方案的设计与实现之前，我们需要清楚的了解需要、已存在的系统、可用的技巧和技术来确认我们的预期是什么，以及达到预期的推动和限制因素。


这34个子系统可以分为４个组成部分，其中很多都是管理类的子系统，因为当项目发布时，系统的生命周期才刚刚开始，管理也是ETL的重中之重。

1. 抽取：ETL的第一个单词Extract就是抽取的意思，数据抽取是ETL系统的最大挑战之一，子系统1~3属于这个主题。

2. 清洗和更正：无论使用什么数据仓库架构，在某个时间点上，数据都要经过清洗以满足业务的要求。经典数据仓库模型，数据进入到数据仓库之前就被清洗了（“真实的数据只有一个版本”）。而DataVault模型，数据是按照原样进入数据仓库的（“事实的数据只有一个版本”），而清洗和更正过程发生在后面的阶段。子系统4~8属于这个主题。

3. 发布：34个子系统中有13个都是关于如何把数据发布到目标数据库中的，发布并不仅仅意味着把数据写入到目标数据库中，也包括把数据写入到维度表或事实表中的那些转换。

4. 管理：任何的信息基础架构都要可以被管理和监控，ETL系统也不例外，子系统22~34属于这一个主题。

下面的主要内容都是摘录网上和书里资料，好记性不如烂笔头。

抽取

ETL方案的第一部分就是要从不同的数据源抽取数据。访问数据源会有很多困难，政策性问题是最难以逾越的障碍。另外，基于安全性和性能方面的考虑，数据系统的管理人员不会让未经授权的用户访问系统。一些ERP系统的厂商（SAP或Oracle）也不允许其他系统访问ERP底层数据库。

子系统1：数据剖析系统

目标是要分析不同数据源的结构和内容。数据剖析提供了类似行统计、NULL值个数统计等简单的统计项，当然也有一些更复杂的分析，如单词模式分析等。

子系统2：增量数据捕获系统

目标是捕获源系统里数据的变化，CDC（Changed Data Capture，变化数据获取）常用的方式有：

1. 审计列：源系统包含审计列，比如插入或修改的时间。

2. 定时获取

3. 全差异化比较

4. 数据库日志抓取

5. 消息队列监控
   

   子系统3：抽取系统

   抽取子系统从不同的数据源抽取数据，并输入到ETL流程里。Kimball明确区分了基于文件的和基于流的两种抽取。注意，这里基于流的抽取并不意味着实时数据流。这种区分方法不太恰当，因为无论从数据库，文件、实时数据源、Web Services还是其他任何数据源，只要可以访问到数据，数据都是以流的方式通过整个转换的。
   
   
   事实上唯一有区别的地方是在ETL作业运行的过程中，数据源的数据是否在发生变化。所以抽取的主要的区别不是文件或流，而是静态或动态的问题。如果转换失败，这种区分方式就显得更为重要。如果你的数据源是静态的（文件的情况基本都是如此），重新启动一个作业就可以了。而如果你的数据源是动态的。例如事务型的数据库，在你运行作业的时候，数据库里面的数据已经发生了变化。
   
   
   例如，一个加载销售数据的作业，所有的维度都正常加载，在加载销售订单的事实数据时停电了，而在加载维度数据的同时，源系统中有了一个新的客户，并产生了一个新的订单，也就是说在源系统中有了新的维度数据和事实数据。除非把所有的维度表重新跑一遍，否则在重新加载事实表时，就会发现有的客户维度没有找到。另外，CDC的实现方式不同，从这类错误中进行数据恢复也是非常困难的事情。
   

   清洗和更正数据

   世界上没有任何一个组织的数据是没有质量问题的，这也就是为什么我们在把数据加载到数据仓库之前要增加一些步骤来清洗和更正这些数据，以满足业务的需求。另外，只使用一个单一的系统来存储数据的组织也很少；通常，为了支撑业务运行，都会存在多个系统，可能每个部门都会有自己的系统。
   
   
   如财务、人力、采购或客服管理等。每个系统存储数据的方式可能都不相同。例如在系统A里，客户性别保存为F（female）、M（male）、U（unknown）；在系统B里，分别使用0、1、NULL来代表这三类数据。所有的这些系统都应该遵照数据仓库的统一标准来存储。
   

   子系统4：数据清洗和质量处理系统

   数据清洗是指修改或整理进入到ETL流程中的脏数据。虽然通常来说，数据清洗应该在原始系统中产生数据的地方进行。但往往提高原始数据质量所需要的时间不能满足开发数据仓库的时间要求。但是无论如何，我们都要给用户提供一份干净的数据。
   
   
   所以一般就需要使用ETL项目来提高数据质量，ETL项目的优势在于：首先，在ETL的数据剖析阶段，可以找出有哪些错误数据；其次，在源系统中需要的数据清洗规则，同样可以使用于ETL环境中。最后，最终使用数据的业务人员可以加入到ETL开发中，只有业务人员才能告诉我们哪些数据是正确的数据。理想情况下，业务人员/数据所有者、源系统开发人员/管理者和ETL开发人员需要共同完成提高数据质量的工作。在很多情况下，不正确数据主要来源于那些把数据输入到系统里的业务人员。
   
   
   例如：一套包括ETL流程的数据质量解决方案，这个方案读取并转换业务系统中的数据，最后把数据加载到一个检查系统，在这个检查系统里用户可以可视化查看数据，并给不正确数据打标记。另外，ETL流程还可以自动给某些常见的错误打标记，如字段为空、不正确的格式或错误的电话号码等。
   
   
   每周数据质量的检查结果就会报告给数据管理人员。尽管业务上要求100%没有错误数据，但实际上，在没有做这个数据质量项目之前，正确数据的比例低于50%，在做了这个可视化的数据质量项目后，第一年，正确数据的比例已经几乎达到了90%。这个例子显示了一个简单的ETL流程再加上一些报告，如何使用户重视并提高数据质量问题。
   

   子系统5：错误事件处理

   错误事件处理的目的是记录下ETL过程中的每一个错误。这样便于管理员定期监控和分析错误是数据质量错误还是系统错误或其他错误。Kimball提到要使用一个独立的错误事件模式来保留这些错误。
   

   子系统6：审计维度

   尽管错误事件模式和数据仓库的业务数据是独立的，但审计维度表却是数据仓库内部的。审计维度表是一类特殊的维度表，数据仓库里的所有事实表都和审计维度表关联，审计维度表包含了对事实表变更的元数据，如加载数据的日期和时间、数据的质量指标等。实际上，给数据仓库增加审计维度，可以带来很多好处。就像在多维数据仓库上使用Data Vault架构所带来的好处一样。
   

   子系统7：排除重复记录系统

   排除重复记录可能是ETL中最棘手的问题，大部分ETL工具也没有能自动处理重复数据的能力。在大多数情况下，排重是指删除重复的数据，或者把不同系统里互相冲突的数据统一。
   

   子系统8：数据一致性

   数据经过数据排重子系统和前面提到的其他数据质量步骤处理后，就交给数据一致性子系统来处理。这个步骤的目的就是使来源于多个业务系统的事实数据遵照相同的维度。
   
   
   例如，一个公司有一个客服管理系统，这个系统有自己的客户数据库，为了把客服管理系统和销售系统放到同一个数据仓库里，需要把客服管理系统的客户数据和销售系统的客户数据统一成一个客户维度表，当分别加载来自这两个系统的事实数据时，需要把来自两个系统的事实数据指向同一个客户维度表。解决这个问题最常用的方法就是维度表中保留从不同系统带来的自然键，在加载事实数据时，可以查找维度表中的这些源系统的自然键。
   

   数据发布

   发布新的数据并不只是往目标数据库里插入新的数据这么简单，发布新的数据其实有很多工作。首先，我们从不同缓慢变更维度技术可以看到，更新维度表就有很多种方式。另外，你需要生成代理键、查询正确的维度键、确保维度数据在事实数据加载前就已经加载完、准备要加载的事实数据。加载事实数据本身也是一项有挑战性的工作：事实数据的数据量可能比较大，也有可能还要更新事实数据，或者同时出现这两种情况。所以需要特别关心表和存储的功能，如OLAP数据库。这也是为什么34种子系统里有很多都属于数据发布范畴。
   

   子系统9：缓慢变更维度处理

   缓慢变更维度（SCDs）是多维数据仓库或者总线架构的基础。我们知道维度表里保存了用来对事实进行分析或分组的信息。例如，客户维度里有客户所在城市字段，这样我们就可以统计或列出某个城市里客户的销售情况。如果客户换了另一个城市，业务系统里肯定要相应修改这个客户所在的城市，缓慢变更维度的过程也会根据不同的规则来变更数据仓库中的客户维度。总的来说，有以下几种缓慢变更维度的方法。

6. 覆盖：直接用新值代替旧值。

7. 增加新行：把当前行标记为“old”并设置一个“结束”时间戳，同时创建一个新行，标记为“current”，并设置一个“开始”时间戳。

8. 增加新列：给表增加一个新列，来存储新值，同时保留原来的值不变。

9. 增加一个小维度表：把经常变更的表属性从主维度表里分离出来，保存在自己的表里。

10. 分离历史表：把每次变化保存到一个历史表里，同时保存变化的类型和变化的时间。这样的历史表可以回答类似“去年有多少客户从成都移动到了重庆”这样的问题。

11. 混合型：把类型1、2、3结合起来（1+2+3=6）。
    

    子系统10：代理键生成系统

    ETL流程应该可以生成代理键。一般有三种方式：

12. 使用表里现在代理键的最大值+1。

13. 使用数据库序列。

14. 使用一个自增的字段。后面一种方法也可用于表输出步骤。
    

    子系统11：层次维度构建

    在数据仓库里还要考虑如何构建和维护数据仓库里的层次。实际上，这个子系统的完整的名称是“构建固定的，可变深度的，可有级别缺失的层次维度系统”。层次可以让用户分析查看维度不同级别上的数据。最简单的层次概念就是时间维度的层次。
    
    
    在现实中，时间维度都需要至少一个以上的层次。例如有“年—季—月—日”这样的层次，也有“年—周—日”这样的层次。时间维度也是“平衡层次”的一个例子。在时间维度里，所有级别的深度都是固定一样的。组织结构的维度更复杂一些，这种维度通常都是“不平衡的”或称为“可变深度的”（子树的深度不同）或“级别缺失的”（在层次上缺失了一些级别）。关于后面“缺失的”，可以想一下地理维度，地理维度通常的层次是“国家—地区—州（省）—城市”。一些国家可能没有“地区”或“州（省）”或都没有，这就是缺失了级别。在源系统里，通常使用“递归”的关系来实现“不平衡的”或“级别缺失的”的情况。
    

    子系统12：特殊维度生成系统

    除了缓慢变化维度，基于多维模型的数据仓库，至少都包含一个特殊维度，时间维度。下面一些类型的维度也都是特殊维度。

15. 杂项维度（也称为垃圾维度）：一些零散的属性，分析需要但又不适合放在其他维度表里。例如状态标志、yes/no和其他低阶（lowcardinality）字段都可以放在杂项维度表里。

16. 小维度：从大维度表里分离出经常发生变化的一些属性，单独放在一个小维度表里。我们也把这种小维度表称为SCD4。

17. 收缩的或上卷的维度：普通维度表的子集，为了避免冲突，这种维度是根据普通维度表创建和更新的。这种维度适用于聚集的数据，例如底层保存的是每天的数据，聚集的数据是按月保存的。

18. 静态维度：通常是小的字典表或参照表，这类表在源系统中没有对应的数据，如状态编码描述或性别。

19. 用户自定义维度：源系统里没有的而报表需要的自定义的描述、分组和层次。可以是任意的维度。唯一区分它们的就是这些维度是通过用户来维护的，而不是通过数据仓库团队或一个ETL过程。
    

    子系统13：事实表加载（事务粒度、周期快照粒度、累积快照粒度事实表的加载系统）

    在往数据仓库加载事实表之前，需要把数据准备好。加载事实表过程并不是重点，之所以把加载事实表单独作为一个子系统分出来，主要是为了强调如下三种不同类型的事实表。

20. 事务粒度事实表：以每一个事务或事件为单位，例如一个销售记录、一个电话呼叫记录，作为事实表里的一行数据。

21. 周期快照事实表：事实表里并不保存全部数据，只保存固定时间间隔的数据，例如每天或每月的库存水平，或每月的账户余额。

22. 累积快照事实表：当有新的数据时，更新事实表里的记录。数据仓库里总是保存最新的数据。例如订单过程，订单过程里有很多独立的日期，如订单日期、期望发货日期、实际发货日期、期望收货日期、实际收货日期和付款日期。当这个过程进行时，随着上面各种时间的出现，事实表里的记录也在不断更新。

加载事实表，通常要加载几百万行数据。为了快速加载，大多数数据库系统都提供了批量加载方式，批量加载方式通常规避了数据库的事务引擎，直接把数据写入到目标表。有时为了提高处理数据的速度，要删除事实表上的所有索引，在加载完后再重建索引。

子系统14：代理键管道这个子系统

负责抽取正确的代理键，用于加载事实表。这里用“管道”一词是因为事实表的加载看起来像一个工序，工序里的每个环节都使用数据的自然键去查找维度表里的代理键。为了让这个查询过程更高效，最好把要查询的维度数据预先装载到内存里。

子系统15：多值维度桥接表生成系统

处理不同深度的层次时需要桥接表。例如一个客户，是一个公司，它有子公司和子子公司。每一级的公司都可能去购买商品，如果想从母公司的角度去看一共购买了多少商品，就需要使用桥接表来实现。当有多个维度项和事实表或其他维度表关联时，也要使用桥接表。


例如：电影票和电影演员，如果想汇总一个演员有多少电影票收入，就需要在电影和电影演员维度之间建立一个桥接表，这个桥接表把电影和电影演员关联起来，桥接表里还可以设置电影演员的权重因子。

子系统16：迟到数据处理

到目前为止，我们的讨论都是在要处理数据同时到达的假设前提下。但在一些场合下，并非如此：事实表数据和维度表数据都可能晚到。对事实表来说这不是什么大问题，唯一不同的就是要根据维度的有效时间查找业务发生时的维度代理键。只要在查询条件里增加 valid_from 和 valid_to 两个字段就可以。“维度查询和更新”步骤默认就有这两个字段。


如果维度表数据晚到，情况就要麻烦一些。如果事实表已经加载完了，但维度表的数据不是最新的。当要更新的维度数据过来后，按照SCD2，会在维度表里增加一条记录，此时要使用新创建的维度的代理键来更新事实表里有上一个代理键的数据。另外还有一个方法，当事实数据过来，但根据事实表里的维度自然键，从维度表里找不到对应的代理键。此时先创建一个新的维度记录，所有的字段都设置成默认值和空值，使用这条记录的代理键。然后当正确的维度数据从源系统中过来时，再更新这些默认值和空值。

子系统17：维度管理系统（中心控制系统）

“中心控制系统，用来准备和向数据仓库发布正确的维度”。中心控制系统不只是组织，还负责管理所有和维度相关的任务。

子系统18：事实表管理系统

这个子系统负责任何创建、组织、管理和事实表相关的任务。子系统17和18在一起结伴工作：事实表管理系统获取到由维度管理系统管理的维度，并把这些维度放到事实表中。

子系统19：聚集构建

如果数据库是用于分析的，一定会有性能方面的要求。这种对速度的要求产生了几种解决方案，在这几种方案里，聚集表对性能的提升最大。如果能把平均30分钟的响应时间降低到几毫秒，客户会非常高兴。聚集表就可以达到这样的效果。


但仅有聚集表是不行的，还需要维护聚集表，数据库还需要知道聚集表的存在以利用聚集表。这也就是MySQL、PostgreSQL、Ingres这些开源产品和Oracle，SQLServer及DB2这些商业产品的差距所在（这些商业产品都有自动聚集导航功能）。


有聚集表功能的唯一的一个开源产品是Mondrian，但这些聚集表还是需要由Mondrian聚集表设计器来创建和维护。另外，也可以使用特殊的分析型数据库，如LucidDB、InfoBright、MonetDB、InfiniDB、Ingres/Vectorwise，或把分析型数据库如LucidDB和Pentaho聚集表设计器结合起来。生成和加载聚集表数据只是一次性的工作，但当数据仓库的数据发生变化后，LucidDB和Pentaho聚集表设计器都不会去维护聚集表。

子系统20：OLAPCube构建系统

OLAP数据库有特殊的存储结构，当加载的时候，可以预先聚集数据。一些OLAP数据库只能写不能更新，所以，在做更新之前要把源数据清除。其他OLAP数据库（如微软的分析服务器）可以更新事实表，但有它自己的更新机制。

子系统21：数据整合管理系统

这个子系统用来从数据仓库获取数据，并把数据发送到其他环境中，通常用于离线数据分析或者其他特殊目的，如给特定客户发送报表。

管理ETL环境

后将介绍14个ETL子系统，这些子系统用来完成管理功能。

子系统22：作业调度

任务的ETL作业任务都需要跑起来，这时就需要作业高度系统来管理这些任务。

子系统23：备份系统

备份ETL过程中产生的中间数据也应该是ETL方案的一部分。Ralph Kimball推荐在ETL流程中的三个地方缓存（备份）这些数据：

• 从源系统中抽取之后，做任何改动之前。
• 清洗、排重、更正之后，此时可能还在文本文件中或使用正规化的格式。
• 已经做完最后处理，可以写入到数据仓库之前。备份数据仓库本身通常不是ETL团队的工作，但ETL团队可以和DBA紧密合作来实现错误恢复的方案。

子系统24：恢复和重新启动系统

ETL设计的一个重要部分就是在ETL失败时，可以重新启动。我们要尽量避免丢失数据和重复数据的情况，所以这个子系统非常重要。遵照前面子系统描述的策略可以更容易重新启动一个失败的作业。

子系统25：版本控制系统；

子系统26：从开发环境到测试、生产环境的版本移植系统

有很多种方法可以实现版本控制，可以使用Git这样的版本控制系统来管理。版本控制系统也不应该成为一个事后才想到的问题。在这里引用Ralph Kimball对版本控制系统的观点。


你需要给ETL系统里的每个部分都确定一个主版本号，另外ETL系统作为一个整体也要有一个主版本号。这样如果今天发布的版本发生严重的错误，可以快速恢复到昨天的ETL版本。详细见：《The Data Warehouse Lifecycle Toolkit，2nd Edition》。

子系统27：工作流监控

是否尝试过不使用计时器和温度指示器来烤蛋糕？非常难吧？同样运行一个ETL作业，而不使用任何方法去监控运行过程，不显示执行作业的执行细节，也会使运行ETL作业非常困难。


已经处理了多少行，处理的速度有多快？消耗了多少内存？哪条记录出错了，为什么出错？监控过程应该可以回答所有的这些问题，一般日志框架就是监控过程。

子系统28：排序系统

对于一些操作（如分组、排序合并操作），数据事先要进行排序。一般这个步骤在内存里操作，但如果数据太大了会在硬盘上分页。对于非常大的文件，可以需要独立的排序工具。我们不讨论这些专用的排序工具而只是使用我们的“排序”步骤来完成我们的排序工作。

子系统29：血统和依赖分析

ETL系统应该同时提供血统分析和影响分析功能。血统分析从处理后的数据开始向后追溯查看这个数据起源于哪里，然后在中间的环节对这个数据进行了哪些处理。依赖或影响分析的方向和血统分析相反，即从数据的起源开始，查看哪些步骤或转换使用了这个数据，这样可以显示出如果一个数据或表发生了变化会影响到系统的哪些部分。

子系统30：问题报告系统

万一运行中出了错误（相信我们，肯定会出错的），你需要尽快知道运行中发生了错误。

子系统31：并行/管道系统

为了能在短时间内处理大量数据，任务应该可以并行运行，甚至在多台机器上同时运行。现在的Reactive Stream（反应式流）规范是数据处理很好的一个方式。在云计算环境下（如阿里云的ECS、Amazon的EC2）运行ETL作业，可以避免大规模的硬件投资，以很低的运营成本带来大规模的按需可扩展的计算能力。

子系统32：安全系统

在现在的IT领域，安全和合规是很热门的主题。数据仓库里保存了企业所有的数据，也是最容易产生风险的地方。另外在很多情况下，ETL过程可以直接访问到很多源系统，所以ETL解决方案本身也是易被攻击的地方。

子系统33：合规报告系统

确保一个ETL流程遵照规章制度，所需要的大多数方法已经在其他子系统中涉及到了。合规意味着要对数据进行详细的审计，审计包括数据从哪里来，在数据上面执行了什么操作（血统），数据在写入到数据仓库之前是什么样子（基于时间戳的备份），在每个时间点的值是什么（审计表，SCD2），谁访问了数据（日志）。Data Vault是一种提供了很好审计功能的数据模型。

子系统34：元数据资源库管理系统

这个子系统的目标就是捕获到和ETL相关的所有业务、过程和技术元数据。这个子系统中重要的一部分就是把系统文档化，在第11章介绍，当然Kettle的全部架构也是元数据驱动的，我们曾经在第2章讨论过。

总结

介绍和解释了Ralph定义的34种ETL子系统。这些子系统的列表也可看成是ETL架构的通用的定义：它描述了每个子系统应该去做那些工作，而不是如何去做或者拿什么工具去做。这34种子系统涉及的四个主要方面如下。

• 抽取：从不同的数据源里获取数据。
• 清洗和更正数据：转换和集成数据，为数据进数据仓库之前做准备。
• 发布数据：加载和更新数据仓库里的数据。
• 管理环境：控制和监控ETL解决方案所有组件的处理过程。

**补充：**

Data Vault2.0(DV2)是一个商业智能系统，包括建模、方法论、架构和实施这四个方面的最佳实践，包括以下四个组件：a. DV2建模（对模型性能和可扩展性的更改）； b. DV2方法论（遵循Scrum和敏捷最佳实践）； c. DV2架构（包含NoSQL系统和大数据系统）； d. DV2实施（基于模式、自动化生成能力成熟度模型集成（CMMI）第五层级）

Data Vault模型是一种中心辐射式模型，其设计重点围绕着业务键的集成模式。这些业务键是存储在多个系统中的、针对各种信息的键（最好是主密钥），用于定位和唯一标识记录或数据。


模型中有三种基本的实体（结构）：a. 中心表：唯一业务键的列表，表示了以横向方式贯穿企业的实际业务键或者主密钥集合； b. 链接表：键与键之间唯一关系的列表，表示了企业中存在于业务键之间的关系和联系；
c. 卫星表：历史的描述性数据，真正的数据仓库组件，存储了岁时间推移的非易失数据。


Data Vault建模的基本规则：a. 业务键是按照粒度和语义内涵进行分割的； b. 关系、事件和跨两个或者多个业务键的交叉关系都要存放在链接结构中； c. 链接结构没有开始或者结束日期，他们只是对数据到达数据仓库那一时刻的关系的一种表达； d. 卫星表是按照数据类型以及变更的类别和速度进行分割的。数据类型一般都是单一的源系统。


多链接结构是为了允许Data
Vault模型中的关系随时间不断扩展，否则数据模型和ELT/ETL装载程序都需要重新构建。


散列键代替顺序号是为了在装载数据时消除依赖性，采用顺序方式会迫使负载堆叠到一起，不仅减缓了装载过程，还扼杀了并行处理的可能性，甚至切断了参照完整性。


3. Data Vault架构介绍
DV基于三层数据仓库架构：集结区（登陆区）、数据仓库和信息交付层（或数据集市）。多层结构使实现人员可以对企业数据仓库去耦合化，将数据来源和获取功能与信息交付和数据供应功能分解开来。

转载自ETL里的34个子系统