ETL工具箱2 ETL数据结构

数据仓库的后台部分经常被称为：集结区（StagingArea）。数据集结主要是指写入磁盘。并且建议ETL的四个主要检查点都要有数据集结。

 

是将数据存储在物理集结区还是在内存中直接处理，这个问题是ETL架构中的最根本的选择之一。开发的ETL处理的效率很大程度上取决于能否很好的均衡物理IO与内存处理。

能够在把数据写入集结表和保持在内存两种方法取得理想的均衡是个很大的挑战，也是优化处理过程中必须考虑的问题。

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

 

如果计划在内存中处理所有的ETL数据处理，不要忘记任何一种数据仓库，无论其架构和运行环境如何，都包含了一个某种形式的集结区。之所以要在加载到数据仓库之前集结数据，主要是下列原因：1  可恢复  2 备份 3 审计

 

设计集结区：由临时的和持久的集结表构成一般是这样用。集结区不仅仅是为了支持下一个作业所创建的临时文件，还可以在后续的过程中发生了严重的问题时候用于恢复工作流，还可以用于审计和对处理过的数据内容进行验证。有专门的集结区规模估算表，其中包含：表名，更新策略（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 聚合（数据库外完成聚合，需要专门的排序软件包，数据库中完成那么大量的使用数据库的排序） 6源数据的引用（大多数的ETL工具可以将参照表加载到内存中，并在其整个ETL过程中有效，从内存中访问参照表速度极快）

 

XML数据集

XML数据集在ETL系统中通常不用于永久存储集结区数据，他们更适合作为ETL系统的输入和输出的标准格式。

XML和HTML区别：XML利用普通文本文档的形式来存储数据以及元数据，但是不包含格式信息。HTML中包含了数据和格式化的信息，但是不包含元数据。

如果不考虑XML层系统结构的复杂性，XML是目前在不兼容系统中转移数据的最有效的中间层，因为XML提供了足够多的信息，可以为关系型数据库生成完全的CREATE TABLE语句，并按照正确的列类型提供数据。XML定义了数据共享的通用语言，这是他的优势。对大数据量的传输而言，XML的劣势在于XML文档结构的冗余。 DTD为双方交换XML建立了元数据理解的基础。

关系表：

优点如下：直观的元数据，关系能力，开放的资料库，DBA支持，SQL接口

独立的DBMS工作表：

事务处理数据库为数据进入进行设计，维度模型为数据输出进行设计，而集结区设计则同时包含两者。采用独立表的原因是：简单的就是最好的。大多数时候，创建独立集结表目的是为了存储数据以便于能够使用SQL或者脚本语言再次操作数据，由于独立表无需规范化，因此不能识作为转储文件。

三范式实体/关系模型：

不要假定集结区必须规范化，ETL处理只有两个目标：快速和可恢复

 

非关系数据源：

通常建立集结区环境的一个重要的原因是为了集成非关系型数据。集成非关系数据通常需要作一些完整性检查，数据完整性的保证不是没有代价的，通常需要在数据集结区中的实际的存储，并且需要对ETL过程进行客户化以保证业务规则的正确，而这些业务规则在源系统的关系型数据库中是自动维护的。这意味着表之间有联系，通常为父子关系。孤儿记录的出现是参照完整性被破坏的标志。 ETL过程必须能够知道如何自动的处理数据异常，ETL过程不能简单地拒绝所有的数据完整性错误，因为没有人会及时地重新输入正确的数据。

 

维度数据模型：从后台提交到前台的成果

事实表：维度模型是围绕着度量过程建立的。事实表的粒度定义了构成事件表的记录的级别，在维度模型的规范中，粒度是在业务术语设计阶段而不是数据库语设计阶段进行定义的。

维表：维度模型的一个最大的优点：可以在度量事件的上下文支持某个维度的情况下灵活的增加维度。类似的，最佳的维度表设计应该包含对维度实体的尽量详细的描述，所以维度应该有很多描述性的字段，称之为维度属性，数据仓库架构师的职责包括识别出这些需要增加的属性，要求ETL小组加入这些属性。维度属性通常是文本型或者离散的数值型，维度表应该有一个主键，键值使用的是由ETL过程生成的无实际意义的整形值，这些值称为代理建。每个维表的主代理建应该与事实表中的相应的外键相匹配，当主外键关系建立后，我们说表符合参照完整性。满足参照完整性是维度模型的基本要求。

 

原子事实表和聚合事实表

在实践中，将集结区中的事实表进行分区是一个比较好的办法。创建分区减少了数据库表的全表扫描，可以直接基于包含某个时间周期的数据分区创建聚合。集结区中的按照维度模型设计的表很多时候需要用来生成OLAP立方体。

代理键映射表

代理建映射表表示用来建立各个源系统的自然键到主数据仓库代理建之间的映射，影射表是维护数据仓库代理建的一种非常有效的方法。由于同一个维度可以有不同的源，因此映射表中要为每一个源的自然建创建单独的列。映射表无论是放在数据库还是文件系统都可以有很高的效率。建映射表没有分析价值，因此不会展示。

 

影响分析：一旦在数据集结区中创建了表，在作任何修改之前，都必须作影响分析。数据仓库依赖的任何系统发生变化都需要相应的影响分析。

元数据捕获：设计集结区数据库的时候使用的数据建模工具可以可视化的展示元数据，数据建模工具在他的字迹的资料库中存储这些可用的元数据。

从集结区衍生出来的元数据类型包括：  数据谱系：逻辑数据映射，阐述了数据元素从原始数据源到最终数据仓库目标之间是如何转换的；业务定义：集结区中的创建的所有表都是从业务定义中衍生出来的，业务定义可以从很多地方获取，包括建模工具，ETL工具，word文档等。 技术定义：技术定义应该描述数据元素的所有物理特性，包括结构，格式，位置，建立这个防止表一次次重建，防止数据重复等。 过程元数据：数据集结区表的加载过程的统计必须和数据仓库表加载的统计一起记录。，成功还是失败等，每个过程成功失败统计结果，加载了多少条等

面向结构的元数据 占 25%，描述数据清理的25%，过程远数据占50%