ETL架构设计(原创)

集结区

准备数据，通常也叫做数据管理，是指获取数据并将数据转化成信息，最终将这些信息提交到前端的查询界面。后台不提供查询服务，数据仓库方法论假设在后台数据访问是被严格禁止的，这是前台的唯一目的。 数据仓库的后台部分经常被称为：集结区（StagingArea）。数据集结主要是指写入磁盘，ETL的四个主要步骤都要有数据集结。下图为数据仓库组件架构图

集结区的意义
是将数据存储在物理集结区还是在内存中直接处理？这个问题是ETL架构中最根本的选择之一。开发的ETL处理的效率很大程度上取决于能否很好地均衡物理I/O 与内存处理。 能够在将数据写入集结表和保持在内存两种方法间取得理想的均衡是个很大的挑战，也是优化处理过程中必需考虑的问题。最终的决定取决于下面的两个彼此矛盾的目标：

将数据以最快的速度从数据源获取到最终目标
在处理过程发生错误时，能够进行恢复而无需从头开始

根据环境和业务需求的不同，数据集结的策略会有很大的不同。如果计划在内存中处理所有的ETL数据处理，不要忘记任何一种数据仓库，无论其架构和运行环境如何，都包含了一个某种形式的集结区。之所以要在加载到数据仓库之前集结数据，主要是基于如下的考虑：
可恢复

在大多数的企业环境中，数据从源系统中抽取出来后，会进行一系列的重要的转换，假设对于某张表，其转换的工作量很大，那么根据我们的最佳实践，应该在数据一抽取完马上就进行集结。这些集结表（在数据库或者文件系统）可以作为恢复点。一旦转换过程发生错误，利用这些表，处理过程就无需再次访问源系统。同样的道理，如果加载过程失败，也无须重新进行转换。如果集结数据纯是为了恢复的目的，那么数据应该存储在文件系统中的顺序文件中，而不是数据库。以恢复为目的的数据集结对于从业务系统中抽取数据尤其重要，因为业务系统中的数据会被覆盖和修改。
备份 

通常，巨大的数据量使得在数据库级别上进行可靠的数据仓库备份变得不可行。只要加载文件已经进行了保存、压缩和归档，那么我们就可以避免数据库故障所带来的灾难。如果集结表存储在文件系统，那么就可以被压缩成非常小的文件并存储在网络上。当需要重新向数据仓库中加载数据时，仅需要对加载文件解压缩并重新加载。
审计

很多时候，源系统和目标系统之间的数据沿袭在ETL代码中丢失，当审计ETL流程时，数据集结区的存在使得对ETL流程中的不同阶段的直接比较成为可能，因为这时候审计人员（或者程序员）可以简单的比较原始的输入文件和输出文件来检查逻辑转换规则。当源系统覆盖了历史数据时，集结数据特别有用。当一个事件发生后，你可能对于数据仓库中几天甚至几周的数据信息的完整性产生了疑问，这时候对相应时段的集结区的抽取数据进行检查将能够帮助你恢复对数据仓库的数据准确性的信心。
最终目标：将数据以最快的速度从数据源获取到最终目标；在处理的过程发生错误的时候，能够进行恢复而无需从头开始。
设计集结区

集结区按照自己的方式，为最终的数据仓库展示区来存储数据。有时候，保存集结区数据是为了支持那些需要历史数据才能完成的功能，而其它时候，集结区数据会在每个处理流程完成后就被删除。为维护历史信息而使用的集结区通常称为持久集结区（persistent staging area）。而临时集结区中的数据则在每次加载过程后被删除。大多数的数据集结区都使用混合模式，即同时使用临时和持久的集结表。
在设计集结区规模时，可以如下估算表进行估算：表名，更新策略（Truncate/insert/update），加载频率，ETL作业(操作集结区表或者文件的作业或者程序。当多个作业操作单个的表的时候，在估算表的这个字段中列出所有的作业)，初始行数，平均行长，增长情况，每月行数，初始大小等
上述这个是假设在DBMS系统创建的集结表，实际上，集结区通常会使用DBMS和文件系统的文本文件。大多数情况下，需要使用DBMS之外的平文件来集结数据以获得更高速的连续处理性能。这种情况下，需要使用文件系统规模估算表。通常的方式是在开发区中放置文件，然后记录所占用的空间，作为正式空间分配时候的估计值。
ETL系统中的数据结构

平面文件

平面文件：如果没用专门的ETL工具，而是在数据库中使用SQL完成所有的etl任务，那么就需要创建DBMS表来存储所有的集结数据。如果是用的ETL工具，那么可以在文件系统中使用简单的文件来存储集结数据。当集结数据像数据库表那样按照行和列存储在文件系统中的时候，我们称之为平面文件或者顺序文件，ETL工具或者脚本语言可以像是操作数据库表一样方便的操作ASCII平面文件，而且某些情况下处理速度更快。
DBMS需要很高的代价来维护要处理数据的源数据，这在一些简单的数据集结区环境下并不真正重要，而且根据经验，诸如排序，合并，删除，替换以及其他的一些数据迁移功能，在DBMS之外进行操作要快的多。有很多专门的程序工具用来处理文本文件。
如果是脚本语言操作平面文件的时候 ，你有义务为所做的转换提供元数据追踪表。
平面文件的劣势：平面文件不能为快速查找建立索引。对数据的截断或者插入操作，关系表要比平面文件快一些，对ETL系统中的集结区表读数据的时候，如果需要进行筛选或者需要在不同的表之间 进行连接的时候，使用数据库存储是更好的选择
当基本需求是以下方面的时候，ETL过程使用平面文件更加实际：

1、出于保护和恢复的目的进行的源数据的集结。如果数据抽取出来之后，后面的ETL发生错误，必须能够重新执行ETL过程而不需要再次访问源系统，确保能够进行恢复而不用频繁的访问源系统的最好方法就是把抽取出来的数据在平面文件内部保存

2、数据排序。文件系统中的排序比order by效率更高。

3、过滤。grep比建立索引之后where语句效率高

4、替换/部分替换文本串，操作系统级别比数据库高效的多

5、聚合。在将数据加载到数据库之前的ETL数据流中；或者，如果聚合数据只用通过数据库筛选来获得，就在数据库中完成。如果在数据库外完成聚合，往往需要专门的排序软件包。如果是在数据库中完成，将会大量地使用数据库的排序功能，虽然偶尔也会将大量的数据导出，使用排序软件包

6、源数据的引用。在规范化的事务处理系统中，通常会有一个唯一的参照表来支持其他的表。例如，一个通用状态表可以支持订单的状态、发货状态和付款状态。除了在源数据中反复地查询同一张参照表之外，更为
高效的一种方法是一次性地将参照表抽取到数据集结区。ETL 工具将从集结区中查找所参照的数据。大多数的ETL工具可以将参照表加载到内存中，并使其在ETL整个处理过程中有效。从内存中访问参照表速度极快。另外，使用集结区中的参照表可以使源系统中的很多表联接可以被省略，这样对源系统的查询将更加的简单和高效。
XML数据集
XML数据集在ETL系统中通常不用于永久存储集结区数据，他们更适合作为ETL系统的输入和输出的标准格式。
XML和HTML区别：XML利用普通文本文档的形式来存储数据以及元数据，但是不包含格式信息。HTML中包含了数据和格式化的信息，但是不包含元数据。
如果不考虑XML层系统结构的复杂性，XML是目前在不兼容系统中转移数据的最有效的中间层，因为XML提供了足够多的信息，可以为关系型数据库生成完全的CREATE TABLE语句，并按照正确的列类型提供数据。XML定义了数据共享的通用语言，这是他的优势。对大数据量的传输而言，XML的劣势在于XML文档结构的冗余。 使用XML需要双方通过交换特定的类型定义文档（DTD）来识别可能的标签。DTD为双方交换XML建立了元数据理解的基础。
关系表

集结区数据可以存储在关系型DBMS中。尤其是没有使用专门的ETL工具时，使用数据库表就最合适。使用数据库存储集结区表有下列的优点：

直观的元数据。使用平文件的主要缺陷是缺乏直观的元数据。使用关系表存储数据，DBMS自动的维护技术元数据，业务元数据可以很容易的附加在数据库表上。列名称、数据类型和长度以及基数的信息可以从数据库系统继承。表和列的业务描述通常作为元素加在DBMS数据目录中。
关系能力。实体之间的强制的数据完整性或参照完整性可以在关系数据库环境中很容易的维护。如果接收来自非关系型系统中的数据，在转换为维度模型之前，将数据集结在一个规范模型中就很有必要。
开放的资料库。一旦数据进入DBMS，那么数据可以很容易的通过任何支持SQL的工具来访问（如果赋予了相应的权限）。进行质量保证测试和审计时，确保对数据的访问至关重要。

DBA支持。在很多企业环境中，DBA小组仅对DBMS中的数据负责。数据库外的数据，如文件系统通常不由DBA维护。如果集结区数据不在DBMS中，那么ETL小组必须承担空间分配、备份和恢复、归档以及安全等任务。
SQL接口。很多的时候我们需要写SQL来操作数据，按照正确的格式获得数据。大家都知道SQL是标准语言，易于书写且功能强大。在IT 领域，恐怕SQL是被最广泛掌握的程序语言了。大多数的数据库系统都提供了强大的SQL 函数，帮助用户节省手工编程的时间。除了强制参照完整性检查，能够使用自带的SQL是在数据库环境中存储集结区数据的主要原因。
独立的DBMS工作表
事务处理数据库为数据进入进行设计，维度模型为数据输出进行设计，而集结区设计则同时包含两者。采用独立表的原因是：简单的就是最好的。之所以被称为“独立表”，是因为这些表与数据库中的其他表没有任何依赖性。在事务处理环境，这些表被称作孤表，因为它和模型中的其他表没有任何关系。因为独立集结表没有任何关联关系，因此独立表是在关系型数据库外建立存储的主要候选方式。
大多数时候，创建集结表目的是为了存储数据以便于能够使用SQL或脚本语言再次操作数据。很多情况下，尤其是小型数据仓库项目，独立表可以满足全部的数据集结区要求。由于独立表不需要规范化，因此不能视作转储文件。转储文件通常任意的创建，无须考虑磁盘空间或者查询效率。独立文件或独立表的每个字段必须有主题和逻辑定义。多余的列会在任何独立表的设计中被忽略。

对于数据库表，需要在所有的独立表上建立并实现一个合理的索引规划。由于访问独立表的进程只有大多数时候，ETL过程，这里没有必要建立在展示区中才用到的位图索引，位图索引是为最终
用户工具和即席查询所用的。在ETL系统中，会更多地在单列或复合列使用B树
索引。
三范式实体/关系模型
事实上，我们很少将数据集结区建成第三范式模型。在很多案例中，一个层系中的数据元素来自多个不同的数据源，具有不同的粒度，还包括很多来自非关系型数据源的外部数据。这种情况下，通常是在加载到维度数据模型之前，消除数据冗余和强制完整性。理想的处理方式是集中处理独立的“问题维表”，对它进行彻底检查以确保原先的脏数据已经被正确地清洗。记住规范化的主要结果是强制明确的多对一关系，除非需要人工检查模型的图形描述，否则，实体关系图的注释既不强求也无须解释。建模的时候应该具体情况具体考虑，只有在必要的时候才进行实体规范化。
一般情况下不要假定数据集结区必须作规范化。设计ETL处理应依据两个基本目标：快速和可恢复。如果ETL过程中所作的集结既没有物理的数据操作，也不能加快速度或支持恢复，那么它就应该被移除。
非关系数据源
通常建立集结区环境的一个重要的原因是为了集成非关系型数据。集成非关系数据通常需要作一些完整性检查，数据完整性的保证不是没有代价的，通常需要在数据集结区中的实际的存储，并且需要对ETL过程进行客户化以保证业务规则的正确，而这些业务规则在源系统的关系型数据库中是自动维护的。这意味着表之间有联系，通常为父子关系。孤儿记录的出现是参照完整性被破坏的标志。例如，如果一个订单表中有一个状态列，具体的值如果在一个独立的状态参照表中不存在就不能够录入。在这个场景中，状态表是订单表中对应列的父。状态表中的父不能随便地删除，除非所有的子已被删除，否则它的子就变成了孤儿记录。孤儿记录指的是任何没有父的子记录。同样，如果状态表中有外键引用到订单，那么任何的订单的主键也不能删除。孤儿记录的出现是参照完整性被破坏的标志。

非关系数据源不能保证参照完整性，非关系系统本质上是无关的表的集合。在很多遗留的事务系统中，父子关系只能通过前置应用来保证。不幸的是，经过多年运行，由于不可避免的脚本操作或者应用之外的数据操作，导致数据库系统中的任何数据完整性都已不能保证。可以肯定非关系数据源中多多少少都会有数据质量问题。
与事务系统不同，设计数据集结区的最佳实践是在ETL过程而不是在数据库中进行完整性检查。其中的区别在于，事务系统期望数据输入是正确的，并且人为的输入错误将导致错误抛出，提示重新输入正确的值。与此相反，ETL过程则必须知道如何自动地处理数据异常。ETL过程不能简单地拒绝所有的数据完整性错误，因为现在没有人会及时地重新输入正确的数据。我们需要为不同的数据质量错误场景定义不同的业务规则，并在ETL过程中实现这些规则。当错误的数据通过ETL流程时，有时你希望直接转换；有时不作修改直接加载；有时需要追加相关的代码或者追加一些对于影响及环境的描述，然后进行加载；或者如果数据不可接受，那就完全拒绝数据，并将其写入拒绝文件供进一步的调查。记住不要过度使用拒绝文件！拒绝文件用于那些我们需要后续专门处理的转储数据。当记录进入拒绝文件后，除非在下一个主要加载步骤开始运行前完成对其处理，否则数据仓库和生产系统之间的同步就被破坏了。

基本的数据库参照完整性并不能完全处理上述的每一种情景。ETL 过程通常都需要手工编码逻辑，来保证成功集成非关系数据源。
代理键映射表
代理键映射表示用来建立各个源系统的自然键到主数据仓库代理键之间的映射，映射表是维护数据仓库代理键的一种非常有效的方法。这些表结构紧凑，专门用于为高速处理。映射表中仅仅包含那些最近期要访问的代理键值及其对应的源系统中的自然键值。由于同一个维度可以有不同的源，因此映射表中要为每个源的自然键创建单独的列。
映射表无论是存储在数据库还是文件系统中都可以有同样的高效率。如果使用数据库，可以利用数据库顺序号生成器来创建代理键，如果索引使用恰当，键值的查找会非常得高效。
由于键映射表并没有分析价值，因此，不能将其建在数据仓库的展示层，也不能暴露给最终用户。
影响分析
影响分析的作用在于检查与对象（这里指的是表或者列）关联的元数据，并判断对象的变化对其内容和结构有何影响。对数据集结区对象的更改可能会破坏数据仓库的加载流程。允许任意修改数据集结区对象会对项目的成功造成危害。 一旦在数据集结区中创建了表，在做任何修改之前，都必须进行影响分析。
元数据捕获

设计集结区数据库的时候使用的数据建模工具可以可视化的展示元数据，数据建模工具在他的字迹的资料库中存储这些可用的元数据。另外使用ETL工具会生成关于过程的元数据，并能够对其中的所有的转换过程进行展示。从集结区衍生出来的元数据类型包括：
数据谱系：所有数据仓库元数据库中最有趣的元数据可能就是数据谱系，或者称为逻辑数据映射，阐述了数据元素从原始数据源到最终数据仓库目标之间是如何转换的。
业务定义：数据集结区中创建的所有表都是从业务定义中衍生出来的。业务定义可以从很多地方获得，包括数据建模工具、ETL 工具、数据库自身或者电子表格和Word文档。无论如何，需要使用在数据仓库展示层上获取业务定义来维持其一致性。
技术定义：尤其对于数据集结区，技术定义要比业务定义更加的普遍。要记住，如果没有文档记录，那么就意味着技术定义不存在！如果数据集结区中表的技术定义没有详细的文档，那么这张表将可能被一次次的
重建，会在数据集结区中产生大量的数据重复，导致数据爆炸。技术定义应该描述数据元素的所有物理属性，包括结构、格式和位置。对集结区中所有表进行技术元数据文档化记录可以将不确定性降至最低，并提
高重用性。
过程元数据：数据集结区表的加载过程的统计必须和数据仓库表加载的统计一起记录。尽管数据集结区加载过程的信息不需要展示给最终用户，但是ETL小组需要知道每个表中加载了多少记录，每个过程成功或失败
的统计结果。而数据刷新频度方面的信息对ETL管理员和最终用户都是有用的。

参考至:《The Data Warehouse ETL Toolkit》Ralph Kimball著

 

                  http://www.cnblogs.com/honkcal/archive/2012/09/16/2684821.html

 

本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]