全面解析ETL：数据仓库架构中的关键处理过程

isNotNullX

已于 2024-08-20 17:03:15 修改

阅读量1.9k

点赞数 32

文章标签：数据仓库 etl 架构

于 2024-08-16 11:59:21 首次发布

本文链接：https://blog.csdn.net/oobubblex/article/details/141256050

版权

ETL一词是Extract、Transform、Load三个英文单词的首字母缩写，中文意为抽取、转换、装载。

抽取——从操作型数据源获取数据。
转换——转换数据，使之转变为适用于查询和分析的形式和结构。
装载——将转换后的数据导入到最终的目标数据仓库。

ETL是数据仓库建立中的核心处理环节，占据了项目工作量的半数以上。其主要任务是将来自多个异构源系统的数据整合到一个集中位置，以便于数据分析。由于源系统的数据通常不兼容，ETL系统需要将这些异构数据转换为同构格式，才能进行有效的程序化分析。没有ETL，处理这些异构数据将变得非常困难。

更多详细内容，推荐下载：

https://s.fanruan.com/e87g8

分享专业高效的数仓建设案例，提供完整数仓搭建方案

一、数据仓库架构中的ETL

可以把数据仓库架构理解成构成数据仓库的组件及其之间的关系，那么就有了下面的数据仓库架构图。

图中显示的整个数据仓库环境包括操作型系统和数据仓库系统两大部分。操作型系统的数据由各种形式的业务数据组成，这其中可能包含关系数据库、TXT或CSV文件、HTML或XML文档，还可能存在外部系统的数据，比如网络爬虫抓取来的互联网数据等。数据可能是结构化、半结构化或非结构化的。这些数据经过ETL过程进入数据仓库系统。

这里把ETL分成了抽取和转换装载两个部分。抽取过程负责从操作型系统获取数据，该过程一般不做数据聚合和汇总，但是会按照主题进行集成，物理上是将操作型系统的数据全量或增量复制到数据仓库系统的RDS中。Hadoop生态圈中的主要数据抽取工具是Sqoop。Sqoop被设计成支持在关系数据库和Hadoop之间传输数据。

转换装载过程将数据进行清洗、过滤、汇总、统一格式化等一系列转换操作，使数据转为适合查询的格式，然后装载进数据仓库系统的TDS中。传统数据仓库的基本模式是用一些过程将操作型系统的数据抽取到文件，然后另一些过程将这些文件转化成MySQL或Oracle这样的关系数据库的记录。最后，第三部分过程负责把数据导入进数据仓库。

RDS（Raw Data Stores）是原始数据存储的意思。将原始数据保存到数据仓库里是个不错的想法。ETL过程的bug或系统中的其它错误是不可避免的，保留原始数据使得追踪并修改这些错误成为可能。有时数据仓库的用户会有查询细节数据的需求，这些细节数据的粒度与操作型系统的相同。有了RDS，这种需求就很容易实现，用户可以查询RDS里的数据而不必影响业务系统的正常运行。这里的RDS实际上是起到了操作型数据存储（Operational Data Store，ODS）的作用。

TDS（Transformed Data Stores）意为转换后的数据存储，是真正的数据仓库中的数据。大量用户会在经过转换的数据集上处理他们的日常查询。如果前面的工作做得好，这些数据的构建方式将保证最重要的和最频繁的查询能够快速执行。

这里的原始数据存储和转换后的数据存储是逻辑概念，它们可能物理存储在一起，也可能分开。当原始数据存储和转换后的数据存储物理上分开时，它们不必使用同样的软硬件。传统数据仓库中，原始数据存储通常是本地文件系统，数据被组织进相应的目录中，这些目录是基于数据从哪里抽取或何时抽取建立（例如以日期作为文件或目录名称的一部分）。转换后的数据存储一般使用某种关系数据库。在Hadoop生中，可以这两类数据逻辑上分开，物理上通过在Hive中建立两个不同的数据库来实现，最终所有数据都被分布存储到HDFS上。

自动化调度组件的作用是自动定期重复执行ETL过程。不同角色的数据仓库用户对数据的更新频率要求也会有所不同，例如财务主管需要每月的营收汇总报告，而销售人员想看到每天的产品销售数据。作为通用需求，所有数据仓库系统都应该能够建立周期性自动执行的工作流作业。ETL过程自动化是数据仓库成功的重要衡量标准。传统数据仓库一般利用操作系统自带的调度功能（如Linux的cron或Windows的计划任务）实现作业自动执行。Hadoop生态圈中有一个叫做Oozie的工具，它是一个Hadoop的工作流调度系统，可以使用它将ETL过程封装进工作流自动执行。

数据目录有时也被称为元数据存储，它可以提供一份数据仓库中数据的清单。用户通过它应该可以快速解决这些问题：什么类型的数据被存储在哪里，数据集的构建有何区别，数据最后的访问或更新时间等。此外还可以通过数据目录感知数据是如何被操作和转换的。一个好的数据目录是让用户体验到系统易用性的关键。Hadoop生态圈中主要的数据目录工具是HCatalog，它是Hadoop上的一个表和存储管理层。

查询引擎组件负责实际执行用户查询。传统数据仓库中，它可能是存储转换后数据的Oracle、MySQL等关系数据库系统内置的查询引擎，还可能是以固定时间间隔向其导入数据的OLAP立方体，如Essbase cube。Hadoop生态圈中的主要SQL查询引擎有基于MapReduce的Hive、基于RDD的SparkSQL和基于MPP的Impala等。

二、数据抽取

抽取操作是ETL处理的第一步，关键在于从源系统获取数据，确保后续转换和装载步骤顺利进行。数据仓库的源系统通常是事务处理应用，如销售订单录入系统，这些系统包含了所有相关记录。设计和建立数据抽取过程通常耗时且复杂，源系统可能缺乏文档且结构复杂。数据抽取需要周期性进行，以保持数据的及时性，且必须不影响源系统的性能或可用性。选择抽取方法时需根据源系统和目标数据仓库的需求进行，通常不允许在源系统中增加额外的逻辑或负担。接下来，将从逻辑和物理两方面介绍数据抽取方法。

（1）逻辑抽取

有两种逻辑抽取类型：全量抽取和增量抽取。

a.全量抽取

源系统的数据全部被抽取。因为这种抽取类型影响源系统上当前所有有效的数据，所以不需要跟踪自上次成功抽取以来的数据变化。源系统只需要原样提供现有的数据而不需要附加的逻辑信息（比如时间戳等）。一个全表导出的数据文件或者一个查询源表所有数据的SQL语句，都是全量抽取的例子。

b.增量抽取

只抽取某个事件发生的特定时间点之后的数据。通过该事件发生的时间顺序能够反映数据的历史变化，它可能是最后一次成功抽取，也可能是一个复杂的业务事件，如最后一次财务结算等。必须能够标识出特定时间点之后所有的数据变化。这些发生变化的数据可以由源系统自身来提供，例如能够反映数据最后发生变化的时间戳列，或者是一个原始事务处理之外的，只用于跟踪数据变化的变更日志表。大多数情况下，使用后者意味着需要在源系统上增加数据抽取逻辑。

在许多数据仓库中，抽取过程不含任何变化数据捕获技术。取而代之的是，把源系统中的整个表抽取到数据仓库过渡区（Staging Area），然后用这个表的数据和上次从源系统抽取得到的表数据作比对，从而找出发生变化的数据。虽然这种方法不会对源系统造成很大的影响，但显然需要考虑给数据仓库处理增加的负担，尤其是当数据量很大的时候。

（2）物理抽取

依赖于选择的逻辑抽取方法，还有能够对源系统所做的操作和所受的限制，存在两种物理数据抽取机制：直接从源系统联机抽取或者间接从一个脱机结构抽取数据。这个脱机结构有可能已经存在，也可能得需要由抽取程序生成。

a.联机抽取

数据直接从源系统抽取。抽取进程或者直连源系统数据库访问它们的数据表，或者连接到一个存储快照日志或变更记录的中间层系统（如MySQL数据库的binlog）。注意这个中间层系统并不需要必须和源系统物理分离。

b.脱机抽取

数据不从源系统直接抽取，而是从一个源系统以外的过渡区抽取。过渡区可能已经存在（例如数据库备份文件、关系数据库系统的重做日志、归档日志等），或者抽取程序自己建立。应该考虑以下的存储结构：

数据库备份文件。一般需要数据还原操作才能使用。
备用数据库。如Oracle的DataGuard和MySQL的数据复制等技术。
平面文件。数据定义成普通格式，关于源对象的附加信息（列名、数据类型等等）需要另外处理。
导出文件。关系数据库大都自带数据导出功能，如Oracle的exp/expdp程序和MySQL的mysqldump程序，都可以用于生成数据文件。
重做日志和归档日志。每种数据库系统都有自己的日志格式和解析工具。

（3）变化数据捕获

抽取处理需要重点考虑增量抽取，也被称为变化数据捕获（Change Data Capture，CDC）。假设一个数据仓库系统，在每天夜里的业务低峰时间从操作型源系统抽取数据，那么增量抽取只需要过去24小时内发生变化的数据。变化数据捕获也是建立准实时数据仓库的关键技术。

当能够识别并获得最近发生变化的数据时，抽取及其后面的转换、装载操作显然都会变得更高效，因为要处理的数据量会小很多。遗憾的是，很多源系统很难识别出最近变化的数据，或者必须侵入源系统才能做到。变化数据捕获是数据抽取中典型的技术挑战。

常用的变化数据捕获方法有时间戳、快照、触发器和日志四种。相信熟悉数据库的用户对这些方法都不会陌生。时间戳方法需要源系统有相应的数据列表示最后的数据变化。快照方法可以使用数据库系统自带的机制实现，如Oracle的物化视图技术，也可以自己实现相关逻辑，但会比较复杂。触发器是关系数据库系统具有的特性，源表上建立的触发器会在对该表执行insert、update、delete等语句时被触发，触发器中的逻辑用于捕获数据的变化。日志可以使用应用日志或系统日志，这种方式对源系统不具有侵入性，但需要额外的日志解析工作。

三、数据转换

数据从操作型源系统获取后，需要进行多种转换操作。如统一数据类型、处理拼写错误、消除数据歧义、解析为标准格式等等。数据转换通常是最复杂的部分，也是ETL开发中用时最长的一步。数据转换的范围极广，从单纯的数据类型转化到极为复杂的数据清洗技术。

在数据转换阶段，为了能够最终将数据装载到数据仓库中，需要在已经抽取来的数据上应用一系列的规则和函数。有些数据可能不需要转换就能直接导入到数据仓库。

数据转换一个最重要的功能是清洗数据，目的是只有“合规”的数据才能进入目标数据仓库。这步操作在不同系统间交互和通信时尤其必要，例如，一个系统的字符集在另一个系统中可能是无效的。另一方面，由于某些业务和技术的需要，也需要进行多种数据转换，例如下面的情况：

只装载特定的数据列。例如，某列为空的数据不装载。
统一数据编码。例如，性别字段，有些系统使用的是1和0，有些是‘M’和‘F’，有些是‘男’和‘女’，统一成‘M’和‘F’。
自由值编码。例如，将‘Male’改成‘M’。
预计算。例如，产品单价 * 购买数量 = 金额。
基于某些规则重新排序以提高查询性能。
合并多个数据源的数据并去重。
预聚合。例如，汇总销售数据。
行列转置。
将一列转为多列。例如，某列存储的数据是以逗号作为分隔符的字符串，将其分割成多列的单个值。
合并重复列。
预连接。例如，查询多个关联表的数据。
数据验证。针对验证的结果采取不同的处理，通过验证的数据交给装载步骤，验证失败的数据或直接丢弃，或记录下来做进一步检查。

四、数据装载

ETL的最后步骤是将转换后的数据装载到目标数据仓库中，这一步骤需关注两个主要问题：装载效率和失败后的重试机制。

（1）提高装载效率

1. 系统资源：确保有足够的系统资源。数据仓库需要高性能的服务器，并且应独占资源，避免与其他系统共享。

2. 禁用约束和索引：在装载过程中，禁用数据库的约束（如唯一性、非空性）和索引，待装载完成后再重新启用并重建索引。这可以显著提高装载速度。

3. 参考完整性：一般不使用数据库的外键约束来保证数据的完整性，而是由ETL工具或程序来维护。

（2）处理装载失败

1. 记录失败点：装载失败时，记录失败点，以便在重新执行时只装载失败的部分。

2. 数据更新：如果装载后数据发生变化（如新增或更新数据），需要重新装载过程。可以通过删除后插入或使用`REPLACE INTO`、`MERGE INTO`等操作来避免重复新增。

最终，装载到数据仓库的数据将经过汇总、聚合处理，并交付给多维立方体、数据可视化、仪表盘等工具进行进一步分析。

五、ETL工具推荐

市面上常见的数据仓库ETL工具都有自己的功能、优势和支持的数据源。然而，帆软软件推出的FineDataLink以其实时同步和数据质量控制功能脱颖而出。它提供了完整的自助化数据调度与治理平台解决方案，在数据仓库ETL处理方面具有强大的功能和灵活的配置能力，可以帮助企业快速、高效地实现数据仓库ETL处理。

在数据仓库ETL处理流程中，FineDataLink的优点如下：

1.数据抽取：FineDataLink支持从多种数据源中抽取数据，包括关系型数据库、非关系型数据库、文件、消息队列等。用户可以通过简单的配置，选择需要抽取的数据源和表，并设置抽取规则。