etl数据仓库bi 《数据仓库etl工具箱》

转载

幸福的地图 2023-08-18 16:52:56

文章标签 etl数据仓库bi 数据仓库数据元数据 文章分类 数据仓库大数据

ETL开发完成后，主要工作是执行ETL开发的这些过程。在装载数据仓库过程中，不管作业时实时执行还是批处理方式执行，其调度时间、执行顺序和执行环境都是关键点。另外，当新作业创建后，执行方式必须与现有ETL过程无缝集成。ETL执行策略主要分为调度和支持两种：

调度是一个综合应用。一个有效的调度包括ETL作业间的关联和依赖关系的指定，并作为一个可靠的机制来管理执行策略的物理实施。
支持是指数据仓库对下游系统和用户的支持，为了确保ETL过程运行并完成，数据参考古必须能够被监控活动状况，并且必须由专人来支持

数据仓库具有最好的维度数据模型，最优秀的商务智能工具并为最高领导层服务。新数据仓库的目标是创建一个一致的、可靠的数据源的备份，以此来支持企业数据分析，从而提高业务能力。为了保证成功，ETL必须完成一下三个关键标准：

可靠性：ETL过程必须持续无误运行，数据必须在任何粒度级别都是可靠的

可用性：数据仓库必须稳定的运行，ETL作业必须在分配的装载窗口中执行并完成

可管理性：可管理性包括可扩展性，ETL工具必须随着数据仓库的发展而发展，尽可能的使过程变得简单，把复杂的流程切成更小的、更简单的单元，同时避免作业启动过程的过于集中。另外，设计执行策略必须确保支持ETL的能力。

ETL管理员必须为数据仓库每一个阶段进行评价，通过以上三种特性（RAM）标准来为项目打分。作业和调度方法必须通过这三个标准，并且确保有元数据和恢复文档。

在ETL的调度过程中，ETL的作业启动应该是自动化完成的，为了节省调度时间和运行效率，在ETL设计时就要理解工具的使用方式和适应环境并且有能力合理地调度并自动化ETL过程，从而持续的装载数据仓库。任何企业数据仓库都必须拥有一个健壮的企业级ETL调度机制，可以基于变化的关键条件来触发作业。

ETL调度器必须包括以下功能：

令牌识别：通常数据仓库从外部数据源获取数据需要判断该数据文件是否在文件系统中已经存在并自动开始执行。令牌是一些由文件系统创建的文件，用来触发ETL事件，具有令牌功能的应用系统可以轮询一个目录或数据库表，用来标识令牌文件的到达。当处理文本文件、web日志或者外部数据源时，必须防止重复处理同一文件，还要确保当文件迟到时不会错失ETL过程执行

日内执行：由于对实时性的要求越来越高，ETL必须拥有在一天当中多次执行的能力和随需执行的能力。
实时能力：为了支持操作应用系统，数据仓库必须实时更
命令行执行：ETL产品大多把主要精力放在GUI上，但是必须能够从命令行执行数据仓库的ETL过程
通知和呼叫：一旦ETL的开发和部署完成，它的执行方式就变成了自动操作。包括告警通知自动化可以以下列方式实现：

集成ETL工具

第三方消息应用系统

客户化脚本

嵌套批处理：批处理是一组作业或程序，作为一个简单的操作一起运行。通常ETL作业被分组分批来装载一个数据集市。而由数据集市组合而成的数据仓库，则由一批数据集市用批处理装载，批处理管理包括下列内容：

图形界面

依赖关系管理

参数共享

平滑重启

顺序/并发执行

预先/后续执行动作

元数据捕获

参数管理：为了方便ETL在整个开发周期中在不同的环境间迁移而不必要的修改代码，因此不能将参数在代码中写死，常用的参数列表包括：

服务器名

数据库或实例名

结构描述文件名

数据库连接信息（不要把口令放到文本文件中）

元数据库连接信息

调度器必须能管理全局参数和局部参数

书中提供了5种创建ETL调度器的方案：

集成的ETL工具
第三方调度器
操作系统
实时执行
客户化应用

集成的ETL工具：一般的ETL工具都会协同调度系统来执行由其工具集创建的ETL作业，集成的ETL工具解决方案优势包括厂商的支持、调度器与ETL引擎的集成、ETL小组中的工具集知识
第三方调度器：许多生产支持部门都制定了一个独立的调度系统标准，所有其它应用系统都必须适应它，在企业环境中，数据仓库同其它应用系统一样，必须遵守生产支持团队指定的规则
操作系统：企业经常使用本地操作系统调度系统来执行ETL过程，在执行脚本中使用电子表格或数据表维护元数据
实时执行：通常数据仓库中的一部分需要实时加载，在开发设计时要选择一种机制来实现实时需求
客户化应用：通常使用脚本编程再加上元数据表的方式实现客户化ETL调度

在作业间定义依赖关系是批处理ETL作业最重要的内容。作业间依赖关系属于元数据，ETL功能需要操作元数据来进行恰当的操作。依赖关系保证了桥接表和维表间层系映射表和维表间的逻辑一致。具体的依赖关系定义参考逻辑如下：

在子维表成功完成之前不要装载维表
在维表成功完成之前不要装载桥接表
在父表（包括桥接表和维表）装载完成之前不要装载事实表

元数据是数据可用性的关键，同时也是响应用户期望的关键机制。用来通知用户数据到达的元数据属于过程元数据的类型。过程元数据捕获ETL过程中的操作统计。典型的数据包括装载成功的行数、拒绝行数、消耗时间、每秒处理行数以及已完成每行所用的时间。在清理和提交步骤中收集的元数据有多找操作角色。所有由批处理管理器控制的元数据都必须能够捕获、存储和发布，元数据应该存储在开放资料库中，从而可与其它应用共享。元数据至少应该有生成报告的能力，因此用户和开发者可以深入了解数据仓库ETL操作的具体细节。

迁移到生产系统

ETL在开发测试通过后需要迁移到生产系统。迁移过程根据因素不同有许多种情况，这些因素包括指导思想、技术架构以及ETL工具集。一旦ETL过程经过了开发和测试，则第一级的数据仓库操作支持和ETL工作应该由一个专门作生产监控管理的特定队伍提供，而不是由数据仓库开发团队来完成。只有当操作支持团队被问题困扰已久而不能解决是才需要求助于数据仓库团队。针对每一个数据仓库版本，开发团队应该提供发布过程文档。文档包括下列内容：

环境：包括源环境和目标环境
服务器名：迁移过程中服务器的物理名称，理出ETL和DW服务器列表
ETL程序：列出程序所在目录，标识正确的程序和作业
数据库名：迁移源或目的数据库
文档文件名：包括迁移信息的文件名，恢复过程的操作步骤等
最后修改日期：发布文档修改的最近时间
修改类型：关于发布类型的描述，类型包括主体发布、小版本发布或打包
发布号：数据仓库称为最后发布结果的版本号
修改请求号：对应的是部署过程中作为范围管理过程规定并包含的请求号
过程：迁移作业的操作步骤：

1.项目代码：版本管理工具中的字段，用来查找用于创建数据仓库的版本的代码

2.表/视图：为新的数据集市创建新结构和索引所用的DDL

3.安全：支持发布的任何新安全策略或需求

4.SQL包：ETL使用的数据库存储过程

5.配置修改：针对发布所需的全局设置和配置项

6.ETL作业：发布所需的ETL作业，通常指版本控制管理器或ETL工具中某个域

7.Shell脚本：ETL依赖的操作系统级前置/后置执行的shell脚本

8.SQL脚本：ETL依赖的操作系统级前置/后置执行的SQL脚本

9.文本文件：文本文件列表，包括新数据源或staging文件的路径

10.编辑参数文件：用于带有可管理参数的环境，在这里列出针对ETL过程所用的新的源数据系统数据库或任何新的修改了的参数

11.附加注释：任何其它关于发布的指示或注释，可用于帮助系统操作团队完成迁移工作

12.执行步骤：执行作业直接的指导，对于新的数据集市或独立的ETL过程，可以指定运行的调度计划或频率

ETL的实施支持同所有软件一样包括四个级别：

第一级支持：通常是针对各种情况的帮助桌面
第二级支持：通常是精通技术，可以提供常规架构类型失败的支持，一般是系统管理员或DBA
第三级支持：拥有解决大多数生产环境问题的知识，一般为ETL管理者
第四级支持：需要实际的ETL作业的开发者分析代码来发现bug或解决问题

在较小的环境中，一般会将支持级别三和支持级别四进行合并。

评估ETL系统时，经常把抽取、转换、装载过程作为一个整体来看待，但是当评估一个很长的初始化装载时应该将ETL系统分成三个独立的过程：

从源系统抽取数据
将数据转换成维度模型
把数据装载到数据仓库

从源系统抽取数据是ETL过程最耗时的部分，装载数据仓库的历史数据过程从一个查询抽取了大量的数据，而且OLTP系统并未设计成可以返回如此大的数据集，然而一次性历史数据库装载与每天的增量装载是大不一样的。OLTP系统没有设计成按生成事实表所需的方式提供数据，ETL抽取过程经常需要其它方法。在抽取开始前经常要对执行时间做一下预估，影响预估的因素如下：

测试环境的硬件通常比生产环境配置低。基于测试环境估计的ETL过程执行时间不准确
由于抽取历史数据的ETL作业可能持续几天，因此在完整数据集上执行测试时不可行的
由于海量数据的问题，应该把抽取过程分为两部分：执行响应时间和数据集获取时间

一旦测试了整个抽取作业，则将估计的样本作业放到生产环境中去执行，以防止在较小的环境中运行可能发生的结果失真情况。

转换过程所需的时间往往是最短的，尽可能利用ETL工具而避免使用数据库存储过程来完成转换。大多数ETL处理都是在I/O上耗时的，如果转换耗时没有比抽取和装载少很多，则很有可能是转换逻辑有严重的瓶颈。

计算装载时间时需要考虑的最重要的两个因素是索引和日志，ETL过程的目标是从源交易系统选出数据、转换并装载到数据仓库。ETL系统本质上按水平或垂直方式组织，在水平方式组织中，抽取、清洗、规格化和提交作业从头开始运行直到完成，与其它主要的数据流没有依赖关系或很少的关系；在垂直ETL系统组织中，多个ETL作业关联在一起，每一个作业中的并行步骤各自完成运行，但要等待其它作业以到达同一执行点。在ETL系统中使用水平方式还是垂直方式来组织依赖于环境中的两个主要因素：

需要多少个ETL作业并行处理详细的数据依赖关系
最终用户修改部分数据的必要性

生产中ETL失败的原因和处理方法主要包括：

网络失败：将ETL引擎和目标数据库放在同一台机器
数据库失败：数据库采用高可用架构
磁盘失败：设置集结区并做RAID
内存失败：开启swap
数据质量失败：停止作业并修复源数据
未公告的系统升级：清除数据从头开始处理

处理历史数据的基本原则如下：

分隔过程：使用日期或范围或自然键把过程分隔成更小的可管理单元
利用恢复点：为了安全，在每个主要的中间处理之后将数据写入文本文件
并行装载：包括数据和ETL的每个组件都应该并行运行
维护元数据：操作元数据是检测失败发生时ETL每个部件状态的关键

清除历史数据

在设计数据库系统时，会创建一个矩阵表格来跟踪插入、修改、删除和选择数据的处理过程，通常被叫做CRUD矩阵表。CRUD矩阵表保证了每一个实体都有一个以上四种方式之一的数据操作过程。当开发应用软件时，矩阵表中的D时非常常见的，意思是这里的数据可以被修改和读取，但不能执行常规的处理来删除它。当没有开发常规的处理来清除历史数据时，通常会导致两件事：会在系统上运行后台脚本来删除历史数据，或者记录不确定地留在系统中，这两种方案都不适用于数据仓库。随着每个主体域计划完成，必须有响应的清除处理过程，清除策略和元数据必须和初始化实施一起被收集和发布。数据仓库的存档工作由DBA来完成，而数仓中永久性地删除数据则必须由ETL团队完成，所删除数据的业务规则必须由经过充分测试和质量保证的ETL过程来执行。

监控ETL系统

依赖于数仓的业务数据按照事先设定的频率刷新，如果任务失败会导致数据仓库的可靠性和可信赖性被怀疑。因此，数据仓库如果没有有效的和持续的数据反馈就不能成功。ETL作业必须被监控和评估才能确保有效的运行。ETL指标是专门用来描述实际数据的真实移动和管理情况的，它们是典型性能指标之下的指标，即在ETL本身过程中。ETL效率的度量大多数都用来表示处理数据所用的实际时间，ETL系统的目标除了创建高质量的信息外，还有在分配的装载窗口内装载数据仓库。在装载性能方面，ETL的一些相关度量如下：

耗时：耗时指的是ETL过程从开始到结束所用的时间，是其它计算的最直接的基础数据
每秒处理的记录数：一般为每秒装载的记录数，当源数据比目标数据要大的时候则等于每秒读取的记录数
每秒读取的记录数：从源系统用SQL读取数据结果的记录数再除以所用时间
每秒写入记录数：转换之后提交到目标表的记录数，再除以所用时间
吞吐量：吞吐量指每秒处理的记录数乘以每条记录的字节数

在一个处理流程中，当一个处理部件的吞吐量不能处理前一个部件的输出时，就会产生瓶颈。处理过程中提供的直接只是可能是瓶颈的度量包括：

CPU利用率
内存分析
服务冲突

理解优化ETL之前要了解所采用的关系型数据库的技术，包括：DML、各日志的作用、索引的作用、约束关系和外键等。在数据集结区中的数据库安全比在数据仓库展现数据库里要容易实现。一般除了ETL引擎，其它用户无法读写数据集结区。通常需要创建一个具有下列权限的数据仓库管理角色：

select、insert、update和delete所有对象的权限
truncate table权限
使用批装载权限
删除和创建索引权限

对于高度敏感的数据应该在抽取之前由源系统进行加密。在开发环境中，每个开发用户都授予DWETL角色权限，此外，任何开发人员都可以添加、删除、或修改文件系统中指定目录中的文件，除了ETL开发团队之外，任何人都不能访问数据集结区。在生产环境中，除了ETL引擎具有DWETL权限外，其它开发人员只能具有只读权限。

对数据进行短期归档和恢复确保数据物理安全，按保护在线数据的方式保护存档数据。数仓中需要长期归档恢复保存的数据包括：