使用 BigQuery ML 预测出生体重

本教程旨在帮助数据分析师了解 BigQuery ML。通过 BigQuery ML,用户可以利用 SQL 查询在 BigQuery 中创建和执行机器学习模型。其目标是让 SQL 专业人员能够利用现有的工具构建模型,从而实现机器学习的普及,并通过消除数据移动需求来提高开发速度。

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

目标

在本教程中,您将需要:

  • 使用 BigQuery ML 通过 CREATE MODEL 语句创建二元线性回归模型
  • 使用 ML.EVALUATE 函数评估机器学习模型
  • ML.PREDICT 函数,用于通过机器学习模型进行预测

费用

本教程使用 Cloud Platform 的可计费组件,包括:

  • BigQuery
  • BigQuery ML

如需了解有关 BigQuery 费用的更多信息,请参阅 BigQuery 价格页面。

如需了解有关 BigQuery ML 费用的更多信息,请参阅 BigQuery ML 价格页面。

准备工作

  1. 登录您的 Google 帐号。

    如果您还没有 Google 帐号,请注册新帐号

  2. 选择或创建 Google Cloud Platform 项目。

    转到“管理资源”页面

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

    了解如何启用结算功能

  4. 系统会自动为新项目启用 BigQuery。要在现有的项目中启用 BigQuery,请转至 启用BigQuery API。

    启用 API

第一步:创建数据集

第一步是创建 BigQuery 数据集用于存储您的机器学习模型。要创建数据集,请执行以下操作:

  1. 转到 GCP Console 中的 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 在导航面板的资源部分,点击您的项目名称。

  3. 在右侧的详细信息面板中,点击创建数据集

    创建数据集

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

    • 对于数据集 ID,输入 bqml_tutorial
    • 选择美国 (US) 作为数据位置。目前,公共数据集存储在 US 多区域位置。为简单起见,您应将数据集放在同一位置。

      创建数据集页面

  5. 保留所有其他默认设置不变,然后点击创建数据集

第二步:创建模型

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

#standardSQL
CREATE MODEL `bqml_tutorial.natality_model`
OPTIONS
  (model_type='linear_reg',
    input_label_cols=['weight_pounds']) AS
SELECT
  weight_pounds,
  is_male,
  gestation_weeks,
  mother_age,
  CAST(mother_race AS string) AS mother_race
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 列为输入标签列。对于线性回归模型来说,标签列必须为实值(列值必须是实数)。

此查询的 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。这十分重要,因为将种族作为类别而不是整数(具有顺序和范围)具有更重大的意义。

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 查询来创建和训练模型,请执行以下操作:

  1. 在 BigQuery 网页界面中,点击编写新查询按钮。

  2. 查询编辑器文本区域中输入以下标准 SQL 查询。

    #standardSQL
    CREATE MODEL `bqml_tutorial.natality_model`
    OPTIONS
      (model_type='linear_reg',
        input_label_cols=['weight_pounds']) AS
    SELECT
      weight_pounds,
      is_male,
      gestation_weeks,
      mother_age,
      CAST(mother_race AS string) AS mother_race
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      weight_pounds IS NOT NULL
      AND RAND() < 0.001
    
  3. 点击运行

    查询大约需要 30 秒才能完成,之后您的模型 (natality_model) 将出现在 BigQuery 网页界面的导航面板中。由于查询使用 CREATE MODEL 语句来创建表,因此您看不到查询结果。

(可选)第三步:获取训练统计信息

如需查看模型训练的结果,您可以使用 ML.TRAINING_INFO 函数,也可以在 BigQuery 网页界面中查看该统计信息。在本教程中,您使用 BigQuery 网页界面。

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

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

要查看运行 CREATE MODEL 查询时生成的模型训练统计信息,请按以下步骤操作:

  1. 在 BigQuery 网页界面的资源部分,展开 [PROJECT_ID] > bqml_tutorial,然后点击 natality_model

  2. 点击模型统计信息标签。结果应如下所示:

    ML.TRAINING_INFO 输出

    训练数据丢失列表示在训练数据集上训练模型后计算得出的损失指标。由于您执行了线性回归,因此该列为均方误差评估数据丢失列是根据留出数据集(从训练中保留的用于验证模型的数据)计算得出的相同损失指标。“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
      weight_pounds,
      is_male,
      gestation_weeks,
      mother_age,
      CAST(mother_race AS STRING) AS mother_race
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      weight_pounds IS NOT NULL))

查询详情

最顶层的 SELECT 语句从模型中检索列。

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

此查询的嵌套 SELECT 语句和 FROM 子句与 CREATE MODEL 查询中的相同。

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

运行 ML.EVALUATE 查询

要运行 ML.EVALUATE 查询来评估模型,请执行以下操作:

  1. 在 BigQuery 网页界面中,点击编写新查询按钮。

  2. 查询编辑器文本区域中输入以下标准 SQL 查询。

    #standardSQL
    SELECT
      *
    FROM
      ML.EVALUATE(MODEL `bqml_tutorial.natality_model`,
        (
        SELECT
          weight_pounds,
          is_male,
          gestation_weeks,
          mother_age,
          CAST(mother_race AS STRING) AS mother_race
        FROM
          `bigquery-public-data.samples.natality`
        WHERE
          weight_pounds IS NOT NULL))
    
  3. (可选)要设置处理位置,请依次点击更多 > 查询设置。对于处理位置,请选择 US。此步骤是可选的,因为系统将根据数据集的位置自动检测处理位置。

    查询设置

  4. 点击运行

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

    ML.EVALUATE 输出

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

    • mean_absolute_error
    • mean_squared_error
    • mean_squared_log_error
    • median_absolute_error
    • r2_score
    • explained_variance

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

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

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

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

#standardSQL
SELECT
  predicted_weight_pounds
FROM
  ML.PREDICT(MODEL `bqml_tutorial.natality_model`,
    (
    SELECT
      is_male,
      gestation_weeks,
      mother_age,
      CAST(mother_race AS STRING) AS mother_race
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      state = "WY"))

查询详情

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

ML.PREDICT 函数利用模型 bqml_tutorial.natality_model 预测结果。

此查询的嵌套 SELECT 语句和 FROM 子句与 CREATE MODEL 查询中的相同。

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

运行 ML.PREDICT 查询

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

  1. 在 BigQuery 网页界面中,点击编写新查询按钮。

  2. 查询编辑器文本区域中输入以下标准 SQL 查询。

    #standardSQL
    SELECT
      predicted_weight_pounds
    FROM
      ML.PREDICT(MODEL `bqml_tutorial.natality_model`,
        (
        SELECT
          is_male,
          gestation_weeks,
          mother_age,
          CAST(mother_race AS STRING) AS mother_race
        FROM
          `bigquery-public-data.samples.natality`
        WHERE
          state = "WY"))
    
  3. (可选)要设置处理位置,请依次点击更多 > 查询设置。对于处理位置,请选择 US。此步骤是可选的,因为系统将根据数据集的位置自动检测处理位置。

    查询设置

  4. 点击运行

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

    ML.PREDICT 输出

清理

为避免因本教程中使用的资源而导致我们向您的 Google Cloud Platform 帐号收取费用,请执行以下操作:

  • 您可以删除已创建的项目。
  • 或者您可以保留项目但删除数据集。

删除数据集

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

  1. 如有必要,请打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 在导航窗格中,点击您创建的 bqml_tutorial 数据集。

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

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

删除项目

要删除项目,请执行以下操作:

  1. 在 GCP Console 中,转到“项目”页面。

    转到“项目”页面

  2. 在项目列表中,选择要删除的项目,然后点击删除
  3. 在对话框中输入项目 ID,然后点击关闭以删除项目。

后续步骤

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
BigQuery ML 文档