使用 TRANSFORM 子句执行特征工程

---

本教程将介绍如何使用 CREATE MODEL 语句的 TRANSFORM 子句在创建和训练模型的同时执行特征工程。您可以使用 TRANSFORM 子句指定一个或多个预处理函数，以转换用于训练模型的输入数据。当您将模型与 ML.EVALUATE 和 ML.PREDICT 函数搭配使用时，系统会自动应用您对模型应用的预处理。

本教程使用公开的 bigquery-public-data.ml_datasets.penguin 数据集。

目标

本教程将指导您完成以下任务：

• 使用 CREATE MODEL 语句创建线性回归模型，以预测服务调用类型。在 CREATE MODEL 语句中，使用 ML.QUANTILE_BUCKETIZE 和 ML.FEATURE_CROSS 函数对数据进行预处理。
• 使用 ML.EVALUATE 函数评估模型。
• 使用 ML.PREDICT 函数从模型获取预测结果。

费用

本教程使用 Google Cloud的收费组件，包括：

• BigQuery
• BigQuery ML

如需详细了解 BigQuery 费用，请参阅 BigQuery 价格页面。

准备工作

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

6. 新项目会自动启用 BigQuery。如需在现有项目中激活 BigQuery，请前往

   Enable the BigQuery API.

   Enable the API

创建数据集

创建 BigQuery 数据集以存储您的机器学习模型：

1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

   转到 BigQuery 页面

2. 在探索器窗格中，点击您的项目名称。

3. 点击 查看操作 > 创建数据集。

   

4. 在创建数据集页面上，执行以下操作：

   • 在数据集 ID 部分，输入 bqml_tutorial。

   • 在位置类型部分，选择多区域，然后选择 US (multiple regions in United States)（美国[美国的多个区域]）。

     公共数据集存储在 US 多区域中。为简单起见，请将数据集存储在同一位置。

   • 保持其余默认设置不变，然后点击创建数据集。

     

创建模型

创建一个线性回归模型来预测企鹅体重，并使用 penguins 示例表格对其进行训练。

OPTIONS(model_type='linear_reg', input_label_cols=['body_mass_g']) 子句表示您正在创建线性回归模型。线性回归模型根据输入特征的线性组合生成连续值。body_mass_g 列为输入标签列。对于线性回归模型来说，标签列必须为实值（列值必须是实数）。

此查询的 TRANSFORM 子句使用 SELECT 语句中的以下列：

• body_mass_g：在训练中使用，且不做任何更改。
• culmen_depth_mm：在训练中使用，且不做任何更改。
• flipper_length_mm：在训练中使用，且不做任何更改。
• bucketized_culmen_length：生成自 culmen_length_mm，方法是使用 ML.QUANTILE_BUCKETIZE() 分析函数根据分位数对 culmen_length_mm 进行分桶。
• culmen_length_mm：原始 culmen_length_mm 值，会被转换为 STRING 值并用于训练。
• species_sex：通过使用 ML.FEATURE_CROSS 函数组合 species 和 sex 来生成。

您无需在 TRANSFORM 子句中使用训练表中的所有列。

WHERE 子句 - WHERE body_mass_g IS NOT NULL AND RAND() < 0.2 - 不包括企鹅体重为 NULL 的行，并使用 RAND 函数抽取数据的随机示例。

请按照以下步骤创建模型：

1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

   转到 BigQuery

2. 在查询编辑器中，粘贴以下查询，然后点击运行：

   CREATE OR REPLACE MODEL `bqml_tutorial.penguin_transform`
     TRANSFORM(
       body_mass_g,
       culmen_depth_mm,
       flipper_length_mm,
       ML.QUANTILE_BUCKETIZE(culmen_length_mm, 10) OVER () AS bucketized_culmen_length,
       CAST(culmen_length_mm AS string) AS culmen_length_mm,
       ML.FEATURE_CROSS(STRUCT(species, sex)) AS species_sex)
     OPTIONS (
       model_type = 'linear_reg',
       input_label_cols = ['body_mass_g'])
   AS
   SELECT
     *
   FROM
     `bigquery-public-data.ml_datasets.penguins`
   WHERE
     body_mass_g IS NOT NULL
     AND RAND() < 0.2;

   查询大约需要 15 分钟才能完成，之后 penguin_transform 模型会显示在探索器窗格中。由于查询使用 CREATE MODEL 语句来创建模型，因此您看不到查询结果。

评估模型

使用 ML.EVALUATE 函数评估模型的性能。ML.EVALUATE 函数根据训练数据中的实际企鹅体重，评估模型返回的预测企鹅体重。

此查询的嵌套 SELECT 语句和 FROM 子句与 CREATE MODEL 查询中的相同。由于您在创建模型时使用了 TRANSFORM 子句，因此无需在 ML.EVALUATE 函数中再次指定列和转换。该函数会自动从模型中检索这些值。

请按照以下步骤评估模型：

1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

   转到 BigQuery

2. 在查询编辑器中，粘贴以下查询，然后点击运行：

   SELECT
     *
   FROM
     ML.EVALUATE(
       MODEL `bqml_tutorial.penguin_transform`,
       (
         SELECT
           *
         FROM
           `bigquery-public-data.ml_datasets.penguins`
         WHERE
           body_mass_g IS NOT NULL
       ));

   结果应如下所示：

   +---------------------+--------------------+------------------------+-----------------------+--------------------+--------------------+
   | mean_absolute_error | mean_squared_error | mean_squared_log_error | median_absolute_error |      r2_score      | explained_variance |
   +---------------------+--------------------+------------------------+-----------------------+--------------------+--------------------+
   |   64.21134350607677 | 13016.433317859564 |   7.140935762696211E-4 |     15.31788461553515 | 0.9813042531507734 | 0.9813186268757634 |
   +---------------------+--------------------+------------------------+-----------------------+--------------------+--------------------+
   

   评估结果中的一项重要指标为 R² 得分。R² 得分为统计测量结果，用于确定线性回归预测是否接近实际数据。0 值表示该模型未能说明响应数据相对于平均值的可变性。1 值表示该模型说明了响应数据相对于平均值的所有可变性。

   如需详细了解 ML.EVALUATE 函数输出，请参阅回归模型。

   您也可以在不提供输入数据的情况下调用 ML.EVALUATE。它将使用在训练期间计算得出的评估指标。

使用模型预测企鹅体重

将模型与 ML.PREDICT 函数结合使用，预测雄企鹅的体重。

ML.PREDICT 函数会在 predicted_label_column_name 列（在本例中为 predicted_body_mass_g）中输出预测值。

使用 ML.PREDICT 函数时，您不必传入模型训练中使用的所有列。只有您在 TRANSFORM 子句中使用的列是必需的。与 ML.EVALUATE 类似，ML.PREDICT 函数会自动从模型中检索 TRANSFORM 列和转换。

请按照以下步骤从模型获取预测结果：

1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

   转到 BigQuery

2. 在查询编辑器中，粘贴以下查询，然后点击运行：

   SELECT
     predicted_body_mass_g
   FROM
     ML.PREDICT(
       MODEL `bqml_tutorial.penguin_transform`,
       (
         SELECT
           *
         FROM
           `bigquery-public-data.ml_datasets.penguins`
         WHERE
           sex = 'MALE'
       ));

   结果应如下所示：

   +-----------------------+
   | predicted_body_mass_g |
   +-----------------------+
   |    2810.2868541725757 |
   +-----------------------+
   |    3813.6574220842676 |
   +-----------------------+
   |     4098.844698262214 |
   +-----------------------+
   |     4256.587135004173 |
   +-----------------------+
   |     3008.393497302691 |
   +-----------------------+
   |     ...               |
   +-----------------------+
   

清理

为避免因本教程中使用的资源导致您的 Google Cloud 账号产生费用，请删除包含这些资源的项目，或者保留项目但删除各个资源。

• 删除您在教程中创建的项目。
• 或者，保留项目但删除数据集。

删除数据集

删除项目也将删除项目中的所有数据集和所有表。如果您希望重复使用该项目，则可以删除在本教程中创建的数据集：

1. 如有必要，请在Google Cloud 控制台中打开 BigQuery 页面。

   转到 BigQuery 页面

2. 在导航面板中，点击您创建的 bqml_tutorial 数据集。

3. 点击窗口右侧的删除数据集。此操作会删除相关数据集、表和所有数据。

4. 在删除数据集对话框中，输入您的数据集的名称 (bqml_tutorial)，然后点击删除以确认删除命令。

删除项目

要删除项目，请执行以下操作：

注意：删除项目会产生以下影响：

• 项目中的所有内容都会被删除。如果您将现有项目用于本文档中的任务，则删除该项目后，还将删除您已在该项目中完成的任何其他工作。
• 自定义项目 ID 丢失。创建此项目时，您可能创建了要在将来使用的自定义项目 ID。要保留使用该项目 ID 的网址（例如 appspot.com 网址），请删除项目内的所选资源，而不是删除整个项目。

如果您打算探索多个架构、教程或快速入门，则重复使用项目可以帮助您避免超出项目配额限制。

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

后续步骤

• 要详细了解机器学习，请参阅机器学习速成课程。
• 如需大致了解 BigQuery ML，请参阅 BigQuery ML 简介。
• 如需详细了解 Google Cloud 控制台，请参阅使用 Google Cloud 控制台。