将混乱转化为利润：了解 ETL 流程

阻止公司向业务用户提供重要决策信息的最严重错误之一是缺乏来自一个或多个数据源的可靠数据，这些数据收集在一个位置，有组织并准备好使用。

想象一下这种情况——您是一家手机壳公司的 IT 经理，该公司的零售店遍布美国。 您的董事会正在努力根据销售情况做出决策，因为信息可用，但位置和格式不同。 一位董事要求您生成一个仪表板，其中包含来自所有销售点的销售信息，以便在下次董事会会议上展示。

您已经知道，由于格式和结构不同，整合信息几乎是不可能的。 一些零售亭仍在使用 Oracle 数据库中的专有系统。 更大的商店正在使用新的 Salesforce 系统。 在系统过渡期间开始运营的最新信息亭已共享用于计算销售额的电子表格。

您将如何将来自不同位置、格式和结构的所有数据整理到一个独特的数据库中，以便为仪表板生成做好访问准备？

ETL 基础知识

ETL 代表提取、转换和加载。 ETL 是一组流程，旨在将这种复杂的数据存储转变为有组织、可靠且可复制的流程，以帮助您的公司利用已有的数据产生更多的销售额。

在我们的案例中，我们将从 Oracle 数据库（大多数信息亭）、Salesforce（商店）和电子表格（较新的信息亭）接收数据，提取数据，在必要时进行转换，然后加载到单个数据仓库数据库中通过报告工具访问并用于生成仪表板和记分卡。

让我们深入了解 ETL 的三个步骤来详细说明该过程。

萃取

提取是从单个或多个来源接收数据的过程。 源可以具有不同的格式和结构，例如文档、电子表格、CSV 文件、平面文件、关系数据库（如 Oracle、MySQL、SQL Server）、非关系数据库等。

有两种主要的提取类型：完全提取和部分提取。

• 完全提取用于初始提取或当数据量和提取时间可接受时。
• 当不需要再次提取所有数据，或者数据量大到无法完全提取时，建议进行部分提取。 在部分提取中，只会提取更新的或新的数据。

除了这些方面，在选择完全或部分提取时还需要考虑其他一些因素，我想描述其中之一：数据可用性和完整性。

这意味着只考虑提取已完成的事务，从而消除可能导致完整性错误的数据。 例如，通过 10 个问题识别工程师知识的在线测试。 如果工程师正在测试中并且回答了几个问题但尚未完成，则提取过程无法从未完成的测试中读取问题。 它可能会导致完整性错误。

转型

提取数据后，我们可以开始转换过程：根据业务规则和技术标准清理、操作和转换数据，以保持可接受的数据质量水平。

根据许多因素，可能需要使用暂存区。 暂存区是一个中间存储空间，用于临时存储从要转换的数据源中提取的数据。

在一些项目中，通常是那些数据量很少的项目，没有必要使用暂存区，但大多数项目都使用它。

在转换阶段执行了一系列任务：

• 选择：选择提取数据的标准。 可以在提取阶段、转化阶段或两个阶段进行选择。
• 集成：从提取阶段到暂存区的数据组合。 这种组合意味着基于提取的数据在暂存区域中添加、删除和更新数据。
• Joins：用于连接提取的数据，类似于SQL join（内连接、左连接、右连接、全连接等）
• 清理或擦洗：删除不一致或无效的数据，或有错误的数据，以提高数据质量。 使用多个数据源会增加出现需要清理的数据问题的机会，例如：
  • 参照完整性（不存在类别的客户）
  • 缺失值（没有 ID 的客户）
  • 唯一性（多人拥有相同的 SSN）
  • 拼写错误（Sun Diego, Cannada, L.Angeles）
  • 矛盾的价值观（Alex dob 04.27.1974，Alex dob 04.14.2000）
  • 和许多其他人
• 总结：总结数据集以供后用
• 汇总：分组汇总和汇总的数据
• 合并：来自多个来源或结构的数据合并到一组数据中

以下是一些常见的转换类型：

• 删除重复数据
• 拆分和合并
• 转换（日期、时间、数字掩码、测量值）
• 编码（男转男）
• 计算（item_value = unit_Price * 数量）
• 密钥生成

正在加载

最后但同样重要的是，ETL 中的最后一个过程是将数据加载到目的地。 加载是将转换后的数据（无论是否来自暂存区域）插入存储库（通常是数据仓库数据库）的行为。

加载数据主要分为三种类型：完整或初始、增量和刷新。

• 完整或初始意味着提取和转换数据的完整加载。 暂存区中的所有数据都将加载到最终目的地，为业务用户做好准备。
• 增量加载是将转换后的数据与最终目标中的数据进行比较并仅加载新数据的过程。 增量负载可以与刷新负载结合使用，如下所述。
• 刷新加载是更新最终目标中的数据以反映原始源中所做更改的过程。 刷新可以是完整的或增量的。

总之，每家公司，无论其规模大小，都可以使用 ETL 流程来整合已经存在的信息，并为决策生成更丰富的信息，将以前无法使用的数据转化为新的收入来源。

测试

测试是 ETL 最重要的阶段之一，但也是最容易被忽视的阶段之一。

转换来自不同来源和结构的数据并将其加载到数据仓库中非常复杂，并且会产生错误。 上面的转换阶段描述了最常见的错误。

数据准确性是成功的关键，而不准确性则是灾难的根源。 因此，ETL 专业人员的使命是在整个过程中保证数据的完整性。 在每个阶段之后，必须进行测试。 无论是从单一来源还是从多个来源提取数据，都必须检查数据以确保没有错误。

任何转换后都必须这样做。 例如，在转换阶段汇总数据时，必须检查数据以保证没有数据丢失并且总和是正确的。

将转换后的数据加载到数据仓库后，必须再次应用测试过程。 加载的数据需要与转换后的数据进行比较，然后再与提取的数据进行比较。

在我们的手机案例公司示例中，我们使用三种不同的来源（专有的 Oracle 数据库、Salesforce 和电子表格）和不同的格式。 测试阶段可以使用来自原始来源的样本数据，并将它们与暂存区域中的数据进行比较，以保证提取正确无误。

样本数据——在这种情况下可以是来自三个不同位置（商店、旧信息亭、新信息亭）的销售信息——将与原始数据源进行比较。 必须分析差异（如果有）以查看它们是否可以接受或是否存在错误。

如果发现错误，则必须对其进行修复，如果需要修复错误，则需要做出一些决定：是否应该修改原始数据？ 有可能这样做吗？ 如果错误无法在原始来源中修复，是否可以通过一些转换来修复？

在某些情况下，必须消除有错误的数据并触发警报以通知负责人。

一些测试示例：

• 数据需要验证
• 数据质量
• 表现
• 数据规则
• 数据建模

日志记录

记录 ETL 过程是确保您拥有可维护且易于修复的系统的关键保证。

具有正确日志记录过程的 ETL 对于保持整个 ETL 操作处于持续改进状态非常重要，有助于团队管理数据源、数据格式、转换、目标等方面的错误和问题。

强大的日志流程可帮助团队节省时间，使他们能够更快、更轻松地识别问题，并且首席工程师需要更少的时间来直接查明问题。 有时，在提取大量数据的过程中会出现错误，如果没有日志，很难识别问题——有时几乎是不可能的。 没有日志，整个过程必须重新运行。 使用日志，团队可以快速识别导致问题的文件和行，并仅修复该数据。

我能想象日志不那么重要的唯一情况是非常小的非自动化系统，其中流程手动运行并且可以手动监控少量数据。

日志提高了自动化程度。 自动运行的具有大量数据的 ETL 流程需要日志系统。 如果计划和执行得当，构建日志系统所付出的所有努力将以更快的错误识别、更可靠的数据以及在日志文件中发现的改进点的形式获得回报。

创建日志系统主要分为三个步骤：生成、归档和分析。

• 生成是记录 ETL 管道执行过程中发生的事情的过程：该过程何时启动、正在提取哪个文件或表、保存在暂存区域中的数据、错误消息等。 必须记录所有可以帮助工程师的重要信息。 警告：注意不要产生太多的信息，只会消耗时间和空间，不会有帮助。
• 存档日志数据意味着保留过去执行的跟踪记录以搜索过去的场景，以便识别错误或与当前场景进行比较以寻求改进。 检查历史中特定点的相关性以保存是很重要的——很久以前的数据，其中结构多次更改，不值得保留。
• 分析。 日志分析至关重要。 存储大量未经分析的数据是没有意义的。 生成和存储数据只需要时间和金钱。 日志分析很重要，不仅可以帮助搜索错误，还可以识别改进点并提高整体数据质量。

表现

ETL 流程可以处理大量数据，并且可能会花费大量成本——无论是在设置它们所花费的时间方面，还是在处理数据所需的计算资源方面。 在规划集成时，工程师必须牢记使用所有数据的必要性。 最好的办法是使用最少的数据量来实现目标，而不是花费时间和金钱来迁移无用的数据。 此外，请记住，数据量往往会随着时间的推移而增加，因此请尝试考虑您未来的需求。

设计日志系统需要付出多少努力？

它取决于许多不同的因素，例如要处理的数据的数量和频率。 可以手动管理数据很少的小型系统，而无需投资高级日志系统。

拥有大量数据、许多不同流程、不同数据源和格式以及复杂管道的公司必须投资创建日志系统。 正如我们已经提到的，一个好的日志系统可以节省大量的时间和金钱。

对于更明显的方法，无论公司规模、数据量和集成频率如何，ETL 流程都必须是有利可图的。 对时间、金钱和技术资源的投资必须产生回报——正如经济学家米尔顿弗里德曼所说：“没有免费的午餐。”

总之，ETL 流程可以帮助公司利用他们已经拥有但没有以正确方式使用的数据来增加利润。 ETL 管道可以集成不同的系统、包含重要信息的电子表格以及分布在不同部门和分支机构的其他数据部分，从而使组织能够充分利用其数据。