使用 AWS Glue DataBrew 清理您的 Excel 和 CSV 文件，而无需编写代码 大

无需编写代码，利用 AWS Glue DataBrew 清理 Excel 和 CSV 文件

关键要点

在管理组织内部的数据时，数据的复杂性提升，特别是当数据来自外部供应商时，情况更是如此。AWS Glue DataBrew 是一个处理数据质量和预处理的理想工具，它通过内置的转换和与 AWS Glue 数据目录及 Amazon S3 的集成，帮助您有效清理和预处理数据。这篇文章将介绍如何利用 DataBrew 处理并清理这些数据。

引言

在组织内部管理数据是一项复杂的任务。处理来自外部的数据信息则更加繁琐。由于组织从多个外部供应商那里接收数据，常常会遇到各种格式的问题，通常是 Excel 或 CSV 文件，每个供应商都用各自独特的数据布局和结构。本文将探讨一种通过利用 AWS Glue DataBrew 功能来简化这一过程的解决方案。

DataBrew 是一款出色的数据质量和预处理工具，可以使用其内置的转换和配方，以及与 AWS Glue 数据目录和 Amazon S3 的集成，进行数据预处理、清理，并将其发送至下游进行分析处理。

在本文中，我们将演示以下内容：

从文件的顶部提取非事务性元数据并与事务性数据合并将多行数据合并为单行数据从字符串或文本中提取唯一标识符

解决方案概述

设想您是一名在组织内部工作的数据分析师。销售领导团队要求提供一个关于组织从各个供应商处获得的净销售额的汇总视图。遗憾的是，这些信息在数据库中并不可用。销售数据来自各个供应商，格式可能类似于以下例子。

然而，由于有数百个供应商，手动提取顶部信息显然不可行。您的目标是将数据清理和扁平化，变成以下输出布局。

为实现这一目标，您可以使用 DataBrew 中的预构建转换，迅速将数据调整为所需布局。

前提条件

在开始之前，您需要满足以下前提条件：

拥有一个 AWS 账户。拥有一个具有 Amazon S3 和 DataBrew 权限的 AWS 身份与访问管理 (IAM) 角色。有关更多信息，请参考 设置 AWS 身份与访问管理 (IAM) 权限。

连接数据集

首先，我们需要将 输入数据集 上传至 Amazon S3。为该项目创建一个 S3 存储桶，并创建一个文件夹以上传原始输入数据。输出数据将在稍后的步骤中存储在另一个文件夹中。

接下来，我们需要连接 DataBrew 到 CSV 文件。我们创建一个称为数据集的项目，它是指向我们将使用的数据源的对象。请在左侧菜单中导航到“数据集”。

确保 Column header values field 设置为 Add default header。由于输入 CSV 格式不规则，因此第一行不会包含所需的列值。

创建项目

要创建一个新项目，请完成以下步骤：

在 DataBrew 控制台中，选择导航窗格中的 Projects。选择 Create project。为 Project name 输入 FoodMartSalesAllUpProject。对于 Attached recipe，选择 Create new recipe。为 Recipe name 输入 FoodMartSalesAllUpProjectrecipe。

选择 Select a dataset，选择 My datasets。选择 FoodMartSalesAllUp 数据集。

在 Permissions 下，对于 Role name，选择您作为前提条件创建的 IAM 角色或创建一个新角色。选择 Create project。

在项目打开后，将创建一个交互式会话，您可以在其中对数据样本进行转换编写。

从文件内容提取非事务性元数据并与事务性数据合并

在这一部分，我们考虑数据的前几行包含元数据，接下来是事务性数据。我们将演示如何从文档顶部提取相关数据，并将其与事务性数据合并为一个扁平表。

从标题提取元数据并移除无效行

完成以下步骤以从标题中提取元数据：

选择 Conditions，然后选择 IF。

对于 Matching conditions，选择 Match all conditions。对于 Source，选择 Value of 和 Column1。至于 Logical condition，选择 Is exactly。在 Enter a value 中，选择 Enter custom value 并输入 RESELLER NAME。对于 Flag result value as 选择 Custom value。在 Value if true 中，选择 Select source column，并将 Value of 设置为 Column2。在 Value if false 中，选择 Enter custom value 并输入 INVALID。选择 Apply。

您的数据集现在应显示如下，Reseller Name 的值已提取到一个单独的列中。

接下来，您将删除无效行，并为行填充 Reseller Name 的值。

选择 Clean，然后选择 Custom values。

对于 Source column，选择 ResellerName。在 Specify values to remove 中，选择 Custom value。在 Values to remove 中，选择 Invalid。在 Apply transform to 中，选择 All rows。选择 Apply。选择 Missing，然后选择 Fill with most frequent value。

对于 Source column，选择 FirstTransactionDate。在 Missing value action 中，选择 Fill with most frequent value。在 Apply transform to 中，选择 All rows。选择 Apply。

您的数据集现在应如下所示，Reseller Name 的值已提取并存在于单独的列中。

重复本节中的相同步骤以提取其余元数据，包括 Reseller Email Address、Reseller ID 和 First Transaction Date。

提升列头并清理数据

若要提升列头，请完成以下步骤：

通过选择 Column， Move column 和 Start of the table 来重新排列列，将元数据列放置在数据集的左侧。

使用适当的名称重命名列。

现在，您可以清理一些列和行。

删除不必要的列，例如 Column7。

您还可以通过过滤掉没有交易日期值的记录来删除无效行。

一元机场vip官网选择 TransactionDate 列的 ABC 图标，选择 date。

对于 Handle invalid values，选择 Delete rows，然后选择 Apply。

数据集现在应提取完所有元数据，并提升了列标题。

将多行拆分为单行

下一个要解决的问题是同一行的事务在多行中分割。我们将提取需要的数据并将其合并为单行事务。例如，Reseller Margin 数据被拆分到两行中。

完成以下步骤，以便将 Reseller Margin 值与对应的交易放在同一行。首先，我们识别 Reseller Margin 行并将其存储在一个临时列中。

选择 Conditions，然后选择 IF。

对于 Matching conditions，选择 Match all conditions。对于 Source，选择 Value of 和 TransactionID。对于 Logical condition，选择 Contains。在 Enter a value 中，选择 Enter custom value 并输入 Reseller Margin。对于 Flag result value as 选择 Custom value。针对 Value if true，选择 Select source column 并将 Value of 设置为 TransactionAmount。对于 Value if false，选择 Enter custom value 并输入 Invalid。在 Destination column 中选择 ResellerMarginTemp。选择 Apply。

接下来，您需要将 Reseller Margin 值上移一行。

选择 Functions，然后选择 NEXT。

对于 Source column，选择 ResellerMarginTemp。对于 Number of rows，输入 1。对于 Destination column，选择 ResellerMargin。在 Apply transform to 中，选择 All rows。选择 Apply。

接下来，删除无效行。

选择 Missing，然后选择 Remove missing rows。

在 Source column 中选择 TransactionDate。在 Missing value action 中选择 Delete rows with missing values。在 Apply transform to 中选择 All rows。选择 Apply。

您的数据集现在应如下所示，Reseller Margin 值已提取至单独列中。

有了合适的结构，我们可以继续挖掘清理过的数据。

从字符串文本中提取唯一标识符

许多类型的数据包含存储在单元格中的重要信息。在这一部分，我们将探讨如何提取这些数据。在示例数据集中，BankTransferText 列包含我们供应商注册的银行账户号码以及交易货币的信息，包括 IBAN、SWIFT 代码和货币。

完成以下步骤以将 IBAN、SWIFT 代码和货币提取到单独的列中。首先，使用正则表达式提取文本中的 IBAN 号码。

选择 Extract，然后选择 Custom value or pattern。

在 Create column options 中，选择 Extract values。在 Source column 中，选择 BankTransferText。在 Extract options 中，选择 Custom value or pattern。在 Values to extract 中，输入 [azAZ][azAZ][09]{2}[AZ09]{130}。在 Destination column 中选择 IBAN。在 Apply transform to 中选择 All rows。选择 Apply。使用相似的步骤通过正则表达式提取 SWIFT