数据仓库中的 ETL (提取、转换和加载) 流程
David Carter 什么是 ETL？
ETL 是一个从不同的源系统提取数据，然后转换数据（例如应用计算、连接等），最后将数据加载到数据仓库系统的过程。ETL 的全称是提取、转换和加载。
人们很容易认为创建数据仓库只是将数据从多个源提取并加载到数据仓库的数据库中。这与事实相差甚远，需要一个复杂的 ETL 流程。ETL 流程需要包括开发人员、分析师、测试人员、高管在内的各种利益相关者的积极投入，并且在技术上具有挑战性。
为了保持其作为决策者工具的价值，数据仓库系统需要随着业务的变化而变化。ETL 是数据仓库系统的周期性活动（每日、每周、每月），需要敏捷、自动化且文档齐全。
为什么需要 ETL？
在组织中采用 ETL 的原因有很多
- 它帮助公司分析其业务数据，以做出关键的业务决策。
- 事务数据库无法回答复杂业务问题，而 ETL 可以回答，例如。
- 数据仓库提供了一个通用的数据存储库
- ETL 提供了一种将数据从各种源移动到数据仓库的方法。
- 随着数据源的变化，数据仓库会自动更新。
- 设计良好且文档齐全的 ETL 系统几乎是数据仓库项目成功的关键。
- 允许验证数据转换、聚合和计算规则。
- ETL 流程允许在源系统和目标系统之间进行样本数据比较。
- ETL 流程可以执行复杂的转换，并需要额外的区域来存储数据。
- ETL 有助于将数据迁移到数据仓库。转换为各种格式和类型，以遵循一个一致的系统。
- ETL 是一个预定义的访问和操作源数据到目标数据库的过程。
- 数据仓库中的 ETL 为业务提供了深厚的历史背景。
- 它有助于提高生产力，因为它可以在无需技术技能的情况下进行编码和重用。
数据仓库中的 ETL 流程
ETL 是一个 3 步流程
步骤 1) 提取
在 ETL 架构的这一步中，数据从源系统提取到暂存区域。暂存区域中的任何转换都可以进行，这样可以避免对源系统的性能造成降级。此外，如果损坏的数据直接从源复制到数据仓库数据库，则回滚将是一个挑战。暂存区域为数据移动到数据仓库之前验证提取的数据提供了机会。
数据仓库需要集成具有不同
DBMS、硬件、操作系统和通信协议的系统。源可能包括主帧等遗留应用程序、定制应用程序、ATM 等接触点设备、呼叫交换机、文本文件、电子表格、ERP、来自供应商、合作伙伴等的数据。
因此，在物理提取和加载数据之前，需要一个逻辑数据映射。此数据映射描述了源数据和目标数据之间的关系。
三种数据提取方法
- 完整提取
- 部分提取 - 无更新通知。
- 部分提取 - 有更新通知
无论使用哪种方法，提取都不应影响源系统的性能和响应时间。这些源系统是活动的生产数据库。任何减速或锁定都可能影响公司的底线。
提取过程中会进行一些验证
- 将记录与源数据进行对账
- 确保没有加载垃圾/不需要的数据
- 数据类型检查
- 删除所有类型的重复/碎片数据
- 检查所有键是否都到位
步骤 2) 转换
从源服务器提取的数据是原始的，无法以其原始形式使用。因此，它需要被清理、映射和转换。事实上，这是 ETL 流程增加价值并更改数据以生成有见地的 BI 报告的关键步骤。
这是 ETL 的重要概念之一，您可以在提取的数据上应用一组函数。不需要任何转换的数据称为直接移动或直通数据。
在转换步骤中，您可以对数据执行自定义操作。例如，如果用户想要数据库中不存在的销售收入总和。或者，如果表中的名字和姓氏在不同的列中。在加载之前可以将其连接起来。
以下是数据完整性问题
- 同一个人的拼写不同，例如 Jon、John 等。
- 公司名称有多种表示方式，例如 Google、Google Inc.。
- 使用不同的名称，例如 Cleaveland、Cleveland。
- 可能存在不同应用程序为同一客户生成不同账号的情况。
- 在某些数据中，必需的文件会留空
- POS 处收集的无效产品，因为手动输入可能导致错误。
在此阶段进行验证
- 过滤 – 只选择某些列进行加载
- 使用规则和查找表进行数据标准化
- 字符集转换和编码处理
- 单位转换，例如日期时间转换、货币转换、数值转换等。
- 数据阈值验证检查。例如，年龄不能超过两位数。
- 数据流从暂存区域到中间表的验证。
- 必需字段不应留空。
- 清理（例如，将 NULL 映射为 0，或将 Gender Male 映射为“M”，将 Female 映射为“F”等）
- 将一列拆分为多列，并将多列合并为一列。
- 转置行和列，
- 使用查找进行数据合并
- 使用任何复杂的数据验证（例如，如果一行中的前两列为空，则自动拒绝该行进行处理）
步骤 3) 加载
将数据加载到目标数据仓库数据库是 ETL 流程的最后一步。在典型的数据仓库中，需要在大约短时间内（夜晚）加载大量数据。因此，加载过程应针对性能进行优化。
如果加载失败，应配置恢复机制，使其能够从失败点重新启动，而不会丢失数据完整性。数据仓库管理员需要根据当前服务器性能来监控、恢复、取消加载。
加载类型
- 初始加载 — 填充所有数据仓库表
- 增量加载 — 按需定期应用进行的更改。
- 全量刷新 — 擦除一个或多个表的内容，然后用新数据重新加载。
加载验证
- 确保关键字段数据不缺失或为空。
- 根据目标表测试建模视图。
- 检查组合值和计算的度量。
- 维度表和历史表中的数据检查。
- 检查加载到事实表和维度表上的 BI 报告。
ETL 工具
市面上有许多ETL 工具。这里列出一些最突出的：
1. MarkLogic
MarkLogic 是一种数据仓库解决方案，它通过一系列企业功能使数据集成更轻松、更快捷。它可以查询不同类型的数据，如文档、关系和元数据。
https://www.marklogic.com/product/getting-started/
2. Oracle
Oracle 是行业领先的数据库。它为本地部署和云端提供了广泛的数据仓库解决方案选择。它通过提高运营效率来帮助优化客户体验。
https://www.oracle.com/index.html
3. Amazon RedShift
Amazon RedShift 是数据仓库工具。它是一种简单且经济高效的工具，可以使用标准的SQL和现有的 BI 工具来分析所有类型的数据。它还允许对 PB 级结构化数据运行复杂的查询。
https://aws.amazon.com/redshift/?nc2=h_m1
这是有用的数据仓库工具的完整列表。
ETL 流程最佳实践
以下是 ETL 流程步骤的最佳实践
切勿尝试清理所有数据
每个组织都希望拥有干净的所有数据，但大多数组织要么不准备付费，要么不准备等待。清理所有数据将花费太长时间，因此最好不要尝试清理所有数据。
切勿清理任何东西
始终计划清理一些内容，因为构建数据仓库的最大原因是提供更干净、更可靠的数据。
确定清理数据的成本
在清理所有脏数据之前，重要的是要确定每项脏数据元素的清理成本。
为了加快查询处理速度，请使用辅助视图和索引
为了降低存储成本，请将汇总数据存储到磁盘磁带中。此外，还需要权衡要存储的数据量及其详细使用情况。权衡数据粒度级别以降低存储成本。
摘要
- ETL 代表提取、转换和加载。
- ETL 提供了一种将数据从各种源移动到数据仓库的方法。
- 在第一步提取中，数据从源系统提取到暂存区域。
- 在转换步骤中，从源提取的数据被清理和转换。
- 将数据加载到目标数据仓库是 ETL 流程的最后一步。
