使用 TRANSFORM 子句执行特征工程

本教程旨在向数据分析师介绍 BigQuery ML。通过 BigQuery ML，用户可以利用 SQL 查询在 BigQuery 中创建和执行机器学习模型。本教程使用 TRANSFORM 子句介绍了特征工程。利用 TRANSFORM 子句，您可以在创建模型的过程中指定所有预处理，然后在机器学习的预测和评估阶段自动应用预处理。

在本教程中，您将利用 natality 示例表创建模型，该模型根据婴儿的性别、怀孕的时长以及已分桶的母亲相关人口统计信息来预测婴儿的出生体重。natality 示例表包含 40 年中美国的每条出生信息。

目标

在本教程中，您将需要：

BigQuery ML，用于通过 CREATE MODEL 语句和 TRANSFORM 子句来创建线性回归模型
ML.FEATURE_CROSS 和 ML.QUANTILE_BUCKETIZE 预处理函数
ML.EVALUATE 函数，用于评估机器学习模型
ML.PREDICT 函数，用于通过机器学习模型进行预测

费用

本教程使用 Google Cloud 的如下计费组件：

BigQuery
BigQuery ML

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

准备工作

登录您的 Google Cloud 账号。如果您是 Google Cloud 新手，请创建一个账号来评估我们的产品在实际场景中的表现。新客户还可获享 $300 赠金，用于运行、测试和部署工作负载。

在 Google Cloud Console 中的项目选择器页面上，选择或创建一个 Google Cloud 项目。

转到“项目选择器”

确保您的 Google Cloud 项目已启用结算功能。

在 Google Cloud Console 中的项目选择器页面上，选择或创建一个 Google Cloud 项目。

转到“项目选择器”

确保您的 Google Cloud 项目已启用结算功能。

新项目会自动启用 BigQuery。如需在现有项目中激活 BigQuery，请转到
启用 BigQuery API。
启用 API

第一步：创建数据集

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

在 Google Cloud 控制台中，转到 BigQuery 页面。

转到 BigQuery 页面
在探索器窗格中，点击您的项目名称。
点击 查看操作 > 创建数据集。
在创建数据集页面上，执行以下操作：
- 在数据集 ID 部分，输入 bqml_tutorial。
- 在位置类型部分，选择多区域，然后选择 US (multiple regions in United States)（美国[美国的多个区域]）。
  
  公共数据集存储在 US 多区域中。为简单起见，请将数据集存储在同一位置。
- 保持其余默认设置不变，然后点击创建数据集。

第二步：创建模型

接下来，利用适用于 BigQuery 的出生率示例表创建线性回归模型。以下 GoogleSQL 查询用于创建您用来预测孩子出生体重的模型。

#standardSQL
CREATE MODEL `bqml_tutorial.natality_model`
TRANSFORM(weight_pounds,
    is_male,
    gestation_weeks,
    ML.QUANTILE_BUCKETIZE(mother_age,
      5) OVER() AS bucketized_mother_age,
    CAST(mother_race AS string) AS mother_race,
    ML.FEATURE_CROSS(STRUCT(is_male,
        CAST(mother_race AS STRING) AS mother_race)) is_male_mother_race)
OPTIONS
  (model_type='linear_reg',
    input_label_cols=['weight_pounds']) AS
SELECT
  *
FROM
  `bigquery-public-data.samples.natality`
WHERE
  weight_pounds IS NOT NULL
  AND RAND() < 0.001

除了创建模型外，运行 CREATE MODEL 命令还可训练您创建的模型。

查询详情

CREATE MODEL 子句用于创建和训练名为 bqml_tutorial.natality_model 的模型。

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

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

weight_pounds：孩子的体重，以磅为单位 (FLOAT64)。
is_male：孩子的性别。如果孩子为男性，则为 TRUE；如果为女性，则为 FALSE (BOOL)。
gestation_weeks：怀孕周数 (INT64)。
mother_age：母亲分娩时的年龄 (INT64)。
mother_race：母亲所属种族 (INT64)。此整数值与表架构中的 child_race 值相同。为了强制 BigQuery ML 将 mother_race 作为非数值特征处理，让每个不同的值表示不同的类别，查询会将 mother_race 转换为 STRING。这十分重要，因为将种族作为类别而不是整数（具有顺序和范围）具有更丰富的意义。

通过 TRANSFORM 子句，对原始特征进行预处理，以供训练使用。生成的列包括：

weight_pounds：按原样传递，且不做任何更改。
is_male：传递，以供训练使用。
gestation_weeks：传递，以供训练使用。
bucketized_mother_age：生成自 mother_age，方法是使用 ML.QUANTILE_BUCKETIZE() 分析函数根据分位数对 mother_age 进行分桶。
mother_race：原始 mother_race 的字符串格式。
is_male_mother_race：通过使用 ML.FEATURE_CROSS 函数组合 is_male 和 mother_race 来生成。

查询的 SELECT 语句提供了可在 TRANSFORM 子句中使用的列。不过，您无需在 TRANSFORM 子句中使用所有列。因此，您可以在 TRANSFORM 子句内执行特征选择和预处理。

FROM 子句 - bigquery-public-data.samples.natality - 表示您要查询示例数据集中的出生率样本表。此数据集位于 bigquery-public-data 项目中。

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

运行 `CREATE MODEL` 查询

要运行 CREATE MODEL 查询来创建和训练模型，请执行以下操作：

在 Google Cloud 控制台中，点击编写新查询按钮。

在查询编辑器文本区域中输入以下 GoogleSQL 查询。

#standardSQL
CREATE MODEL `bqml_tutorial.natality_model`
TRANSFORM(weight_pounds,
    is_male,
    gestation_weeks,
    ML.QUANTILE_BUCKETIZE(mother_age,
      5) OVER() AS bucketized_mother_age,
    CAST(mother_race AS string) AS mother_race,
    ML.FEATURE_CROSS(STRUCT(is_male,
        CAST(mother_race AS STRING) AS mother_race)) is_male_mother_race)
OPTIONS
  (model_type='linear_reg',
    input_label_cols=['weight_pounds']) AS
SELECT
  *
FROM
  `bigquery-public-data.samples.natality`
WHERE
  weight_pounds IS NOT NULL
  AND RAND() < 0.001

点击运行。

查询大约需要 30 秒才能完成，之后您的模型 (natality_model) 会显示在导航面板中。由于查询使用 CREATE MODEL 语句来创建表，因此您看不到查询结果。

第三步（可选）：获取训练统计信息

如需查看模型训练的结果，您可以使用 ML.TRAINING_INFO 函数，也可以在 Google Cloud 控制台中查看统计信息。在本教程中，您将使用 Google Cloud 控制台。

机器学习算法通过检查多个示例并尝试找到实现损失最小化的模型来构建模型。该过程称为经验风险最小化。

损失是对不良预测的惩罚，是一个表明模型的预测在单个示例中不良程度的数字。如果模型的预测完全准确，则损失为零，否则损失会大于零。训练模型的目标是从所有示例中找到一组平均损失“较小”的权重和偏差。

要查看运行 CREATE MODEL 查询时生成的模型训练统计信息，请执行以下操作：

在 Google Cloud 控制台导航面板的资源部分中，展开 project-name > bqml_tutorial，然后点击 natality_model。
点击训练标签页，在查看方式部分，选择表选项。结果应如下所示：
```
+-----------+--------------------+----------------------+--------------------+
| Iteration | Training data loss | Evaluation data loss | Duration (seconds) |
+-----------+--------------------+----------------------+--------------------+
| 0         | 1.6640             | 1.7352               | 6.27               |
+-----------+--------------------+----------------------+--------------------+
```
训练数据丢失列表示在训练数据集上训练模型后计算得出的损失指标。由于您执行了线性回归，因此该列为均方误差。

评估数据损失列是根据留出数据集（从训练中收回用于验证模型的数据）计算得出的相同损失指标。用于训练的默认优化策略是“normal_equation”，因此只需要一次迭代就可以收敛到最终模型。

如需详细了解 optimize_strategy 选项，请参阅 CREATE MODEL 语句。

如需详细了解 ML.TRAINING_INFO 函数和“optimize_strategy”训练选项，请参阅 BigQuery ML 语法参考。

第四步：评估模型

创建模型后，您可以使用 ML.EVALUATE 函数评估分类器的性能。ML.EVALUATE 函数根据实际数据评估预测值。

用于评估模型的查询如下所示：

#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.natality_model`,
    (
    SELECT
      *
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      weight_pounds IS NOT NULL))

查询详情

上方的 SELECT 语句从模型中检索列。

FROM 子句使用 ML.EVALUATE 函数评估模型 bqml_tutorial.natality_model。

此查询的嵌套 SELECT 语句和 FROM 子句与 CREATE MODEL 查询中的相同。由于训练中使用了 TRANSFORM 子句，因此您无需指定特定列和转换。它们会自动恢复。

WHERE 子句 - WHERE weight_pounds IS NOT NULL - 不包括权重为 NULL 的行。

#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.natality_model`)

运行 `ML.EVALUATE` 查询

要运行 ML.EVALUATE 查询以评估模型，请完成以下步骤：

在 Google Cloud 控制台中，点击编写新查询按钮。

在查询编辑器文本区域中输入以下 GoogleSQL 查询。

#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.natality_model`,
    (
    SELECT
      *
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      weight_pounds IS NOT NULL))

（可选）如需设置处理位置，请在更多下拉列表中点击查询设置。在处理位置部分，选择美国 (US)。此步骤是可选的，因为系统将根据数据集的位置自动检测处理位置。
点击运行。

查询完成后，点击查询文本区域下方的结果标签页。结果应如下所示：

+---------------------+--------------------+------------------------+---------------------+---------------------+----------------------+
| mean_absolute_error | mean_squared_error | mean_squared_log_error | mean_absolute_error | r2_score            | explained_variance   |
+---------------------+--------------------+------------------------+---------------------+---------------------+----------------------+
| 0.9566580179970666  | 1.6756289722442677 | 0.034241471462096516   | 0.7385590721661188  | 0.04650972930257946 | 0.046516832131241026 |
+---------------------+--------------------+------------------------+---------------------+---------------------+----------------------+

由于您执行了线性回归，因此结果包含以下列：

mean_absolute_error
mean_squared_error
mean_squared_log_error
median_absolute_error
r2_score
explained_variance

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

第五步：利用您的模型预测结果

您现已对模型进行了评估，下一步是用其预测结果。您可以使用您的模型来预测怀俄明州出生的所有婴儿的出生体重。

用于预测结果的查询如下所示：

#standardSQL
SELECT
  predicted_weight_pounds
FROM
  ML.PREDICT(MODEL `bqml_tutorial.natality_model`,
    (
    SELECT
      *
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      state = "WY"))

查询详情

最顶层的 SELECT 语句检索 predicted_weight_pounds 列。该列由 ML.PREDICT 函数生成。当您使用 ML.PREDICT 函数时，模型的输出列名称为 predicted_label_column_name。对于线性回归模型来说，predicted_label 为 label 的估计值。对于逻辑回归模型来说，predicted_label 为两个输入标签中的一个，具体取决于哪个标签的预测概率更高。

ML.PREDICT 函数用于通过模型 bqml_tutorial.natality_model 预测结果。

此查询的嵌套 SELECT 语句和 FROM 子句与 CREATE MODEL 查询中的相同。请注意，您不必像在训练中那样传入所有列，只需传入在 TRANSFORM 子句中使用的列即可。与 ML.EVALUATE 类似，TRANSFORM 内的转换会自动恢复。

WHERE 子句 - WHERE state = "WY" - 表示您将预测限制为怀俄明州。

运行 `ML.PREDICT` 查询

要运行利用模型预测结果的查询：

在 Google Cloud 控制台中，点击编写新查询按钮。

在查询编辑器文本区域中输入以下 GoogleSQL 查询。

#standardSQL
SELECT
  predicted_weight_pounds
FROM
  ML.PREDICT(MODEL `bqml_tutorial.natality_model`,
    (
    SELECT
      *
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      state = "WY"))

（可选）如需设置处理位置，请在更多下拉列表中点击查询设置。在处理位置部分，选择美国 (US)。此步骤是可选的，因为系统将根据数据集的位置自动检测处理位置。
点击运行。

查询完成后，点击查询文本区域下方的结果标签页。结果应如下所示：

+----------------------------+
| predicted_weight_pounds    |
+----------------------------+
|  7.735962399307027         |
+----------------------------+
|  7.728855793480761         |
+----------------------------+
|  7.383850250400428         |
+----------------------------+
| 7.4132677633242565         |
+----------------------------+
|  7.734971309702814         |
+----------------------------+

清除数据

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

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

删除数据集

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

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

前往 BigQuery 页面
在导航面板中，点击您创建的 bqml_tutorial 数据集。
点击窗口右侧的删除数据集。此操作会删除相关数据集、表和所有数据。
在删除数据集对话框中，输入您的数据集的名称 (bqml_tutorial)，然后点击删除以确认删除命令。

删除项目

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

在 Google Cloud 控制台中，进入管理资源页面。
转到“管理资源”
在项目列表中，选择要删除的项目，然后点击删除。
在对话框中输入项目 ID，然后点击关闭以删除项目。

后续步骤

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

使用 TRANSFORM 子句执行特征工程

目标

费用

准备工作

第一步：创建数据集

第二步：创建模型

查询详情

运行 CREATE MODEL 查询

第三步（可选）：获取训练统计信息

第四步：评估模型

查询详情

运行 ML.EVALUATE 查询

第五步：利用您的模型预测结果

查询详情

运行 ML.PREDICT 查询

清除数据

删除数据集

删除项目

后续步骤

运行 `CREATE MODEL` 查询

运行 `ML.EVALUATE` 查询

运行 `ML.PREDICT` 查询