数据仓库快速入门教程4-ETL

什么是ETL？

ETL是Extract，Transform和Load的缩写。 在此过程中，ETL工具从不同的RDBMS源系统中提取数据，然后转换数据，如应用计算，连接等，然后将数据加载到数据仓库系统中。

ETL流程需要来自各种利益相关方（包括开发人员，分析师，测试人员，高级管理人员)的积极参与.为了保持其作为决策者工具的价值，数据仓库系统需要随业务变化而变化。 ETL是数据仓库系统的重复活动(每日，每周，每月)，需要灵活，自动化且有良好的文档。

你为什么需要ETL？

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

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

数据仓库中的ETL过程

ETL分为3个步骤

图片.png

提取

在此步骤中，将数据从源系统提取到暂存区域。 如果在暂存区域中完成转换，源系统的性能不受影响。 暂存区域也有助于验证。

数据仓库需要集成不同的系统

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

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

三种数据提取方法：

  • 完全提取
  • 部分提取 - 无更新通知。
  • 部分提取 - 带更新通知

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

在提取期间完成了一些验证：

  • 协调记录与源数据
  • 确保没有加载垃圾/不需要的数据
  • 数据类型检查
  • 删除所有类型的重复/碎片数据
  • 检查所有keys

转换

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

在此步骤中，您将对提取的数据应用一组函数。 不需要任何转换的数据称为直接移动或传递数据 。

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

图片.png

以下是数据完整性问题：

  1. Jon，John等同一个人的拼写不同
  2. 有多种方式可以表示Google，Google Inc.等公司名称。
  3. 使用不同的名字，如Cleaveland，Cleveland。
  4. 可能存在由同一客户的各种应用程序生成不同帐号的情况。
  5. 部分数据缺失
  6. 在POS处收集的产品无效，因为手动输入可能导致错误。

验证在此阶段完成

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

加载

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

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

加载类型：

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

加载验证

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

参考资料

ETL工具

市场上有许多数据仓库工具。 这里有一些最突出的：

1.MarkLogic：

MarkLogic使用一系列企业功能使数据集成更容易，更快捷。 此工具有助于执行非常复杂的搜索操作。 它可以查询不同类型的数据，如文档，关系和元数据。

http://developer.marklogic.com/products

甲骨文：

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

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

3.亚马逊RedShift：

它使用标准SQL和现有BI工具分析所有类型数据的简单且经济高效的工具。 它还允许使用查询优化技术运行针对数PB的结构化数据的复杂查询。

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

最佳实践ETL过程

切勿尝试清理所有数据：

每个组织都希望将所有数据都清理干净，但大多数组织都不愿意等待或不准备等待。 要清理它只需要太长时间，所以最好不要尝试清理所有数据。

永远不要清洗任何东西：

始终计划清理某些东西，因为构建数据仓库的最大原因是提供更清晰，更可靠的数据。

确定清理数据的成本：

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

要加快查询处理速度，请使用辅助视图和索引：

要降低存储成本，请将汇总数据存储到磁盘磁带中。 此外，还需要在要存储的数据量与其详细使用之间进行权衡。 在数据粒度级别进行权衡以降低存储成本。

小结

  • ETL是Extract，Transform和Load的缩写。
  • ETL提供了一种将数据从各种源移动到数据仓库的方法。
  • 在第一步提取中，将数据从源系统提取到暂存区域。
  • 在转换步骤中，从源中提取的数据被清理和转换。
  • 将数据加载到目标数据仓库是ETL过程的最后一步。

参考资料

links