在 BigQuery 代码实验中使用 AI 代理准备数据

1. 简介

想象一下，您无需成为编码专家，即可更快、更高效地准备好数据以供分析。借助 BigQuery 数据准备，这一切都能实现。这项强大的功能可简化数据注入、转换和清理流程，让组织中的所有数据从业者都能轻松完成数据准备工作。

准备好挖掘产品数据中隐藏的秘密了吗？

前提条件

• 对 Google Cloud 控制台有基本的了解
• 对 SQL 有基本的了解

学习内容

• 如何使用时尚和美容行业的实际示例，通过 BigQuery 数据准备将原始数据清理和转换为可据以采取行动的商业智能。
• 如何为清理后的数据运行和安排数据准备

所需条件

• Google Cloud 账号和 Google Cloud 项目
• 网络浏览器，例如 Chrome

2. 基本设置和要求

自定进度的环境设置

1. 登录 Google Cloud 控制台，然后创建一个新项目或重复使用现有项目。如果您还没有 Gmail 或 Google Workspace 账号，则必须创建一个。

• 项目名称是此项目参与者的显示名称。它是 Google API 尚未使用的字符串。您可以随时对其进行更新。
• 项目 ID 在所有 Google Cloud 项目中是唯一的，并且是不可变的（一经设置便无法更改）。Cloud 控制台会自动生成一个唯一字符串；通常情况下，您无需关注该字符串。在大多数 Codelab 中，您都需要引用项目 ID（通常用 PROJECT_ID 标识）。如果您不喜欢生成的 ID，可以再随机生成一个 ID。或者，您也可以尝试自己的项目 ID，看看是否可用。完成此步骤后便无法更改该 ID，并且此 ID 在项目期间会一直保留。
• 此外，还有第三个值，即部分 API 使用的项目编号，供您参考。如需详细了解所有这三个值，请参阅文档。

注意：项目 ID 具有全局唯一性，您选择它后，其他任何人都不能再使用。您是此 ID 的唯一用户。即使项目被删除，相应 ID 也无法再次使用

注意：如果您使用的是 Gmail 账号，可以将默认位置设置为无组织。如果您使用的是 Google Workspace 账号，请选择对贵组织来说合理的位置。

2. 接下来，您需要在 Cloud 控制台中启用结算功能，以便使用 Cloud 资源/API。运行此 Codelab 应该不会产生太多的费用（如果有的话）。若要关闭资源以避免产生超出本教程范围的结算费用，您可以删除自己创建的资源或删除项目。Google Cloud 新用户符合参与 300 美元免费试用计划的条件。

3. 准备工作

启用 API

如需使用 Gemini in BigQuery，您必须启用 Gemini for Google Cloud API。通常，拥有 serviceusage.services.enable IAM 权限的服务管理员或项目所有者会执行此步骤。

1. 如需启用 Gemini for Google Cloud API，请前往 Google Cloud Marketplace 中的 Gemini for Google Cloud 页面。前往 Gemini for Google Cloud
2. 在项目选择器中，选择项目。
3. 点击启用。页面会更新并显示状态为已启用。现在，Gemini in BigQuery 已在所选 Google Cloud 云项目中向所有拥有所需 IAM 权限的用户提供。

设置角色和权限以开发数据准备

1. 在“IAM 和管理”中，选择“IAM”

2. 选择相应用户，然后点击铅笔图标以“修改正文”

如需使用 BigQuery 数据准备，您需要拥有以下角色和权限：

• BigQuery Data Editor (roles/bigquery.dataEditor)
• Service Usage Consumer (roles/serviceusage.serviceUsageConsumer)

4. 在 BigQuery Analytics Hub 中查找并订阅“bq data preparation demo”产品详情

在本教程中，我们将使用 bq data preparation demo 数据集。它是 BigQuery Analytics Hub 中的关联数据集，我们将从中读取数据。

数据准备绝不会写回源数据，我们会要求您定义要写入的目标表。在此练习中，我们将使用的表只有 1,000 行，以最大限度地降低费用，但数据准备工作将在 BigQuery 上运行，并可随之扩缩。

请按照以下步骤查找并订阅关联的数据集：

1. 访问 Analytics Hub：在 Google Cloud 控制台中，前往 BigQuery。
2. 在 BigQuery 导航菜单中，选择“治理”下的“Analytics Hub”。

3. 搜索商品详情：在 Analytics Hub 界面中，点击搜索商品详情。
4. 在搜索栏中输入 bq data preparation demo，然后按 Enter 键。

5. 订阅房源：从搜索结果中选择 bq data preparation demo 房源。
6. 在房源详情页面上，点击订阅按钮。
7. 查看所有确认对话框，并根据需要更新项目/数据集。默认值应该是合适的。

8. 在 BigQuery 中访问数据集：成功订阅后，相应列表中的数据集将与您的 BigQuery 项目相关联。

返回到 BigQuery Studio。

5. 探索数据并启动数据准备

1. 找到数据集和表：在“探索器”面板中，选择您的项目，然后找到 bq data preparation demo 列表中的数据集。选择 stg_product 表。
2. 在数据准备中打开：点击表名称旁边的三个竖点，然后选择 Open in Data Preparation。

如果系统提示您Enable data preparation，请点击相应按钮以启用该功能，然后按照提示操作。

这将在数据准备界面中打开该表，以便您开始转换数据。

从下方的数据预览中可以看出，我们需要解决一些数据问题，包括：

• 价格列同时包含金额和币种，因此难以进行分析。
• 商品列混合了商品名称和类别（以竖线符号 | 分隔）。

Gemini 会立即分析您的数据并建议多种转换。在此示例中，我们看到多条建议。在接下来的步骤中，我们将应用所需的权限。

6. 处理价格列

我们先来处理价格列。如我们所见，它包含币种和金额。我们的目标是将这些数据拆分为两个不同的列：币种和金额。

Gemini 已针对“价格”列确定了多项建议。

注意：如果您找不到包含以下说明的建议卡片，请按以下步骤创建一个：

1. 选择“添加步骤”>“转换”
2. 在 Description 字段中输入您要查找的说明。

1. 找到类似如下内容的建议：

说明：“此表达式会从指定字段中移除开头的‘USD ’”

REGEXP_REPLACE(Price,` `r'^USD\s',` `r'')

2. 选择“预览”

3. 选择“应用”

接下来，对于 Price 列，我们来将数据类型从 STRING 转换为 NUMERIC。

1. 找到类似如下内容的建议：

说明：“将列‘价格’从字符串类型转换为 float64 类型”

SAFE_CAST(Price AS float64)

2. 选择“应用”。

现在，您应该会在步数列表中看到 3 个已应用的步数。

注意：如果您找不到上述建议，请使用上述提示中记录的 Add Step 工作流程来添加和应用这些建议。

7. 处理“商品”列

商品列同时包含商品名称和类别，两者之间以竖线 (|) 分隔。

虽然我们可以再次使用自然语言，但我们不妨探索一下 Gemini 的另一项强大功能。

清理商品名称

1. 选择商品条目的类别部分（包括 | 字符），然后将其删除。

Gemini 会智能识别此模式，并建议将转换应用于整个列。

2. 选择“修改”。

Gemini 的建议非常准确：它会移除“|”字符后面的所有内容，从而有效地分离出商品名称。

但这次我们不想覆盖原始数据。

3. 在目标列下拉菜单中，选择“创建新列”。
4. 将名称设置为 ProductName。

4. 预览更改，确保一切正常。
5. 应用转换。

提取商品类别

我们将使用自然语言指示 Gemini 提取“产品”列中竖线 (|) 后面的字词。提取的此值将覆盖现有列（名为“商品”）中的值。

1. 点击 Add Step 以添加新的转换步骤。

2. 从下拉菜单中选择 Transformation
3. 在自然语言提示字段中，输入“提取‘商品’列中竖线 (|) 后面的字词”，然后按回车键生成 SQL。

4. 将“目标列”保留为“商品”。
5. 点击“应用”。

转换应生成以下结果。

8. 联接以丰富数据

通常，您需要使用来自其他来源的信息来丰富数据。在我们的示例中，我们将商品数据与来自第三方表的扩展商品属性 stg_extended_product 联接起来。此表格包含品牌和发布日期等详细信息。

1. 点击 Add Step
2. 选择Join
3. 浏览到 stg_extended_product 表。

Gemini in BigQuery 自动为我们选择了 productid 联接键，并限定了左侧和右侧，因为键名称相同。

注意：请确保说明字段显示“按 productid 加入”。如果它包含其他联接键，请将说明字段覆盖为“按 productid 联接”，然后选择说明字段中的生成按钮，以重新生成联接表达式，并添加以下条件 L.

productid

= R.

productid。

4. （可选）选择“预览”可预览结果。
5. 点击 Apply。

清理扩展属性

虽然联接成功，但扩展属性数据需要进行一些清理。LaunchDate 列的日期格式不一致，而 Brand 列包含一些缺失值。

我们先来处理 LaunchDate 列。

在创建任何转换之前，请查看 Gemini 的建议。

1. 点击 LaunchDate 列名称。您应该会看到一些生成的建议，类似于下图中的建议。

2. 如果您看到包含以下 SQL 的建议，请应用该建议，然后跳过后续步骤。

COALESCE(SAFE.PARSE_DATE('%Y-%m-%d',
LaunchDate),SAFE.PARSE_DATE('%Y/%m/%d', LaunchDate))

3. 如果您没有看到与上述 SQL 匹配的建议，请点击 Add Step。
4. 选择 Transformation。
5. 在 SQL 字段中，输入以下内容：

COALESCE(SAFE.PARSE_DATE('%Y-%m-%d',
LaunchDate),SAFE.PARSE_DATE('%Y/%m/%d', LaunchDate))

6. 将 Target Columns 设置为 LaunchDate。
7. 点击 Apply。

LaunchDate 列现在具有一致的日期格式。

9. 添加目标表

现在，我们的数据集已清理完毕，可以加载到数据仓库中的维度表中了。

1. 点击 ADD STEP。
2. 选择 Destination。
3. 填写必需的参数：数据集：bq_data_preparation_demo 表：DimProduct
4. 点击 Save。

现在，我们已经使用过“数据”和“架构”标签页。除此之外，BigQuery 数据准备还提供“图表”视图，以直观方式显示流水线中的转换步骤序列。

注意：您可以点击标题中的数据准备名称来查看图表。

10. 奖励 A：处理“制造商”列并创建错误表

我们还在 Manufacturer 列中发现了空值。对于这些记录，我们希望实现数据质量检查，并将它们移至错误表中以供进一步审核。

创建错误表

1. 点击 stg_product data preparation 标题旁边的 More 按钮。
2. 在 Setting 部分下，选择 Error Table。
3. 选中 Enable error table 框，然后按如下方式配置设置：

• 数据集：选择 bq_data_preparation_demo
• 表格：输入 err_dataprep
• 在 Define duration for keeping errors 下，选择 30 days (default)

4. 点击 Save。

在“制造商”列中设置验证

1. 选择“制造商”列。
2. Gemini 可能已识别出相关转换。找到仅保留“制造商”字段不为空的行的建议。它将包含类似于以下的 SQL：

Manufacturer IS NOT NULL

2. 点击此建议中的“修改”按钮以查看该建议。

3. 如果未选中“验证失败的行会转到错误表”选项，请选中该选项
4. 点击 Apply。

您可以随时点击“已应用的步骤”按钮，查看、修改或删除已应用的转换。

清理冗余的 ProductID_1 列

现在可以删除 ProductID_1 列，该列与联接表中的 ProductID 重复。

1. 前往 Schema 标签页
2. 点击 ProductID_1 列旁边的 3 个点。
3. 点击 Drop。

现在，我们已准备好运行数据准备作业并验证整个流水线。对结果感到满意后，我们可以安排作业自动运行。

4. 在离开数据准备视图之前，请保存准备工作。在 stg_product data preparation 标题旁边，您应该会看到一个 Save 按钮。点击相应按钮即可保存。

11. 清理环境

• 删除 stg_product data preparation
• 删除 bq data preparation demo 数据集

12. 恭喜

恭喜您完成此 Codelab。

所学内容

• 设置数据准备
• 打开表格并浏览数据准备
• 拆分包含数值和单位描述符数据的列
• 标准化日期格式
• 运行数据准备