数据仓库中的 ETL（提取、转换和加载）流程

由： 大卫卡特 大卫卡特
Hours 更新

什么是ETL？

ETL 是从不同的源系统提取数据，然后转换数据（如应用计算、连接等），最后将数据加载到数据仓库系统的过程。ETL 的全称是提取、转换和加载。

人们很容易认为创建数据仓库只是从多个来源提取数据并加载到数据仓库的数据库中。这远非事实，需要复杂的 ETL 过程。ETL 过程需要来自各种利益相关者的积极投入，包括开发人员、分析师、测试人员、高管，并且在技术上具有挑战性。

为了保持其作为决策者工具的价值，数据仓库系统需要随着业务变化而变化。ETL 是数据仓库系统的一项重复活动（每日、每周、每月），需要灵活、自动化且记录良好。

为什么需要 ETL？

组织中采用 ETL 的原因有很多：

  • 它可以帮助公司分析其业务数据以做出关键的业务决策。
  • 事务数据库无法回答 ETL 示例可以回答的复杂业务问题。
  • 数据仓库提供了一个通用的数据存储库
  • ETL 提供了一种将数据从各种来源移动到数据仓库的方法。
  • 随着数据源的变化，数据仓库将自动更新。
  • 精心设计和记录的 ETL 系统对于数据仓库项目的成功几乎至关重要。
  • 允许验证数据转换、聚合和计算规则。
  • ETL 过程允许在源系统和目标系统之间进行比较样本数据。
  • ETL 过程可以执行复杂的转换，并且需要额外的区域来存储数据。
  • ETL 有助于将数据迁移到数据仓库。转换为各种格式和类型以遵循一个一致的系统。
  • ETL 是访问和操作源数据到目标数据库的预定义过程。
  • 数据仓库中的 ETL 为业务提供了深厚的历史背景。
  • 它有助于提高生产力，因为它不需要技术技能就可以编码和重复使用。

数据仓库中的 ETL 流程

ETL 是一个三步过程

ETL流程
ETL流程

步骤1）提取

在 ETL 架构的这一步中，数据从源系统提取到暂存区。转换（如果有）在暂存区进行，这样源系统的性能就不会下降。此外，如果损坏的数据直接从源复制到数据仓库数据库，回滚将是一个挑战。暂存区提供了一个机会，可以在提取的数据移入数据仓库之前对其进行验证。

数据仓库需要集成具有不同

DBMS、硬件、 Opera系统和通信协议。来源可能包括大型机等遗留应用程序、定制应用程序、ATM 等接触点设备、呼叫交换机、文本文件、电子表格、ERP、来自供应商和合作伙伴的数据等。

因此，在数据被物理提取和加载之前，需要一个逻辑数据图。该数据图描述了源数据和目标数据之间的关系。

三种数据提取方法：

  1. 完全提取
  2. 部分提取-无需更新通知。
  3. 部分提取-带有更新通知

无论使用哪种方法，提取都不应影响源系统的性能和响应时间。这些源系统是实时生产数据库。任何减速或锁定都可能影响公司的底线。

提取过程中会进行一些验证：

  • 使记录与源数据一致
  • 确保没有加载垃圾邮件/不需要的数据
  • 数据类型检查
  • 删除所有类型的重复/碎片数据
  • 检查所有钥匙是否到位

相关文章

步骤2）转型

从源服务器提取的数据是原始的，无法以原始形式使用。因此，需要对其进行清理、映射和转换。事实上，这是 ETL 流程增加价值并更改数据的关键步骤，以便生成富有洞察力的 BI 报告。

它是重要的 ETL 概念之一，您可以在提取的数据上应用一组函数。不需要任何转换的数据称为 直接移动 or 传递数据.

在转换步骤中，您可以对数据执行自定义操作。例如，如果用户想要数据库中没有的销售收入总额。或者如果表中的名字和姓氏位于不同的列中。可以在加载之前将它们连接起来。

数据集成问题
数据集成问题

以下是数据 Integrity 问题：

  1. 同一个人的不同拼写，如 Jon、John 等。
  2. 有多种方式来表示公司名称，例如 Google、Google Inc.
  3. 使用不同的名称，如 Cleaveland、Cleveland。
  4. 可能会出现不同应用程序为同一客户生成不同账号的情况。
  5. 某些所需数据文件仍为空白
  6. 由于手动输入会导致错误，因此在 POS 处收集的无效产品。

在此阶段进行验证

  • 过滤 – 仅选择要加载的特定列
  • 使用规则和查找表进行数据标准化
  • 字符集转换和编码处理
  • 测量单位的转换，如日期时间转换、货币转换、数字转换等。
  • 数据阈值验证检查。例如，年龄不能超过两位数。
  • 从暂存区到中间表的数据流验证。
  • 必填字段不应留空。
  • 清理（​​例如，将 NULL 映射到 0 或将性别男性映射到“M”，将女性映射到“F”等）
  • 将一列拆分为多个列，并将多列合并为一列。
  • 转置行和列，
  • 使用查找合并数据
  • 使用任何复杂的数据验证（例如，如果一行中的前两列为空，则它会自动拒绝处理该行）

步骤3）加载

将数据加载到目标数据仓库数据库是 ETL 过程的最后一步。在典型的数据仓库中，需要在相对较短的时间内（夜间）加载大量数据。因此，加载过程应针对性能进行优化。

如果发生负载故障，应配置恢复机制以从故障点重新启动而不会丢失数据完整性。数据仓库管理员需要根据当前的服务器性能监控、恢复、取消负载。

加载类型：

  • 初始负载 — 填充所有数据仓库表
  • 增量负载 — 根据需要定期应用持续的变更。
  • 完全刷新 — 删除一个或多个表的内容并重新加载新数据。

负载验证

  • 确保关键字段数据不缺失也不为空。
  • 根据目标表测试建模视图。
  • 检查组合值和计算度量值。
  • 维度表和历史表中的数据检查。
  • 检查已加载的事实和维度表的 BI 报告。

ETL工具

有许多 ETL工具 市场上有售。以下是一些最突出的：

1. 马克逻辑：

MarkLogic 是一种数据仓库解决方案，它使用一系列企业功能使数据集成变得更容易、更快捷。它可以查询不同类型的数据，如文档、关系和元数据。

https://www.marklogic.com/product/getting-started/

2. Oracle:

Oracle 是业界领先的数据库。它为本地和云端提供了广泛的数据仓库解决方案选择。它有助于通过提高运营效率来优化客户体验。

https://www.oracle.com/index.html

3. Amazon 红色Shift:

Amazon Redshift 是数据仓库工具。它是一种简单且经济高效的工具，可以使用标准 SQL 和现有的 BI 工具。它还允许针对 PB 级结构化数据运行复杂查询。

https://aws.amazon.com/redshift/?nc2=h_m1

以下是有用的 数据仓库工具。

最佳实践 ETL 流程

以下是 ETL 流程步骤的最佳实践：

永远不要尝试清理所有数据：

每个组织都希望清理所有数据，但大多数组织不愿意花钱等待或不愿意等待。清理所有数据会花费太长时间，所以最好不要尝试清理所有数据。

切勿清洗任何东西：

总是计划清理一些东西，因为构建数据仓库的最大原因是提供更清洁、更可靠的数据。

确定清理数据的成本：

在清理所有脏数据之前，确定每个脏数据元素的清理成本非常重要。

为了加快查询处理，有辅助视图和索引：

为了降低存储成本，将汇总的数据存入磁盘磁带。此外，还需要在需要存储的数据量和其详细用途之间进行权衡。在数据粒度级别上进行权衡以降低存储成本。

总结

  • ETL 代表提取、转换和加载。
  • ETL 提供了一种将数据从各种来源转移到 数据仓库.
  • 在第一步提取中，数据从源系统提取到暂存区。
  • 在转换步骤中，从源提取的数据被清理和转换。
  • 将数据加载到目标数据仓库是 ETL 过程的最后一步。

你可能会喜欢：