利用 BigQuery ML 预测篮球比赛结果

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

在本教程中,您将使用示例 BigQuery 公共数据集:NCAA 篮球比赛数据创建用于预测三分球命中率的模型。

目标

在本教程中,您将学习以下内容:

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

费用

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

  • Google BigQuery
  • BigQuery ML
  • Cloud Datalab

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

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

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

准备工作

  1. 登录您的 Google 帐号。

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

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

    转到“管理资源”页面

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

    了解如何启用结算功能

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

    启用 API

受众

本教程是面向数据科学家的高级教程。

数据科学家可以使用 BigQuery 标准 SQL 和 BigQuery ML 来实现以下目的:

  • 探索、分析和直观呈现数据
  • 清理要在统计模型中使用的数据集
  • 创建统计信息和机器学习模型
  • 创建、训练、评估和改善机器学习算法和模型
  • 探索数据仓库(如 BigQuery)中的数据

数据科学家使用多种工具,包括:

  • BigQuery 命令行工具(利用标准 SQL 查询数据)
  • 统计软件,如 RStudio 和 Matlab
  • Cloud Datalab(Jupyter 笔记本)
  • AI 平台
  • 编程语言,如 Python 和 Java
  • 机器学习库,如 MLlib 和 TensorFlow
  • 可视化工具,如 Cloud Datalab 和 Google Data Studio

由于本教程面向数据科学家,与机器学习概念相关的许多细节并未在此进行解释。要查看使用 BigQuery 网页界面的更详细入门教程,请转至面向数据分析师的 BigQuery ML 使用入门

要查看对此处所介绍概念的说明,请参阅以下部分:

第一步:设置 Cloud Datalab

在本教程中,您将使用 Google Cloud Shell 设置 Cloud Datalab。Cloud Datalab 提供基于 Jupyter 的笔记本环境,用于处理 BigQuery ML 和 BigQuery。

在设置 Cloud Datalab 之前,必须启用 Compute Engine API 和 Cloud Source Repositories API。如需了解如何启用这些 API,请参阅启用和停用服务

要在 Compute Engine 虚拟机上设置 Google Cloud Datalab,请执行以下操作:

  1. 转到 GCP Console。

    GCP Console

  2. 点击激活 Cloud Shell

    激活 Cloud Shell 图标

  3. 在 Cloud Shell 窗口中,输入以下命令来配置 gcloud 工具,以便将 us-central1-a 作为 Cloud SDK 的默认地区。该地区将包含您的 Cloud Datalab 实例。

    gcloud config set compute/zone us-central1-a
    
  4. 输入以下命令,以创建用于运行 Cloud Datalab 容器的 Compute Engine 虚拟机(名为 mltutorial)。此命令还会创建与虚拟机的 SSH 连接,并将远程 Cloud Datalab 网页界面映射到 localhost 端口 8081。如果系统提示,请输入与默认 Compute Engine 地区对应的数字。

    datalab create mltutorial
    

    看到 The connection to Datalab is now open and will remain until this command is killed 消息即表明您已连接到实例。建立 SSH 连接后,命令行界面可能不再生成输出。

  5. 在 Cloud Shell 窗口中,点击网页预览图标 网络预览图标

  6. 选择更改端口

  7. 更改预览端口对话框中,输入 8081,然后点击更改并预览。此操作会在新的标签中打开 Cloud Datalab。

    gcloud compute ssh --quiet --zone "us-central1-a" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@mltutorial"
    
  8. 点击笔记本以创建新笔记本。笔记本将在新标签页中打开。

  9. 在标题栏中,点击未命名的笔记本,更改笔记本的名称。

  10. 重命名笔记本对话框中,输入 BigQuery ML tutorial,然后点击确定

  11. 在第一个代码单元中,输入下列内容以更新为 BigQuery Python 客户端库的最新版本。

    !pip install --upgrade google-cloud-bigquery
    
  12. 依次点击运行 > 从此单元运行 (Run from this cell)。查询结果会显示在代码块下方。看到以下消息即表示安装完成:

    Successfully installed ...

  13. 代码产生了大量输出。要隐藏输出,请点击单元菜单,然后选择收起以收起单元。

    单元菜单

  14. 点击添加代码以创建新的代码单元。

  15. 输入以下代码以导入 BigQuery Python 客户端库并初始化客户端。BigQuery 客户端用于通过 BigQuery API 收发消息。

    from google.cloud import bigquery
    client = bigquery.Client()
  16. 依次点击运行 > 从此单元运行。此命令没有任何输出。

第二步:创建数据集

接下来,您要创建一个 BigQuery 数据集来存储机器学习模型。要创建数据集,请执行以下操作:

  1. 点击添加代码以创建新的代码单元。

  2. 输入以下命令以创建数据集。

    dataset = bigquery.Dataset(client.dataset('bqml_tutorial'))
    dataset.location = 'US'
    client.create_dataset(dataset)
  3. 依次点击运行 > 从此单元运行。命令输出应类似于以下内容:

    Dataset '[project_ID]:bqml_tutorial' successfully created.

第三步:生成训练和功能数据

接下来,您要生成培训和功能数据。首先,您通过查询 NCAA 篮球数据公共数据集中的 bigquery-public-data.ncaa_basketball.mbb_teams_games_sr 表来生成模型的输入功能。生成输入功能后,您就可生成训练数据。

生成输入功能(功能工程)

输入功能利用不同时间窗口纳入主队和客队之前比赛统计信息的平均和标准差。所用时间窗口为当前比赛之前的 10、5 和 1 场比赛。team-id 列(适用于主队和客队)和 season 列也用作独热特征,以便为模型提供平均统计信息。

考虑到用于生成输入特征的查询的长度,您将使用经典版 BigQuery 网页界面中已保存的查询来创建表。

要生成输入特征数据,请执行以下操作:

  1. 点击下方的按钮,打开输入数据查询。

    打开功能输入查询

    在时间允许的情况下,检验查询。

  2. 在查询窗口下方,点击显示选项

  3. 目标表部分,点击选择表

  4. 选择目标表对话框中,执行以下操作:

    1. 对于项目,选择您的项目。
    2. 对于数据集,选择 bqml_tutorial
    3. 表 ID 字段中,输入 cume_games
    4. 点击确定
  5. 点击运行查询。这会创建一个查询作业,并将查询结果写入您指定的表中。

    或者,如果在运行查询之前忘记指定目标表,您可以将临时表复制到永久表,只需点击结果窗口中的保存为表按钮即可。

  6. 查询将需要几秒钟才能完成。查询运行后,您的表 (cume_games) 会出现在 BigQuery 网页界面的导航面板中。

生成培训数据

生成输入功能后,您可以生成训练数据。训练数据要写入 bqml_tutorial.wide_games 表。生成训练数据时,请排除用于预测结果的某场比赛。

在此查询中,您可以自连接通过运行上一查询创建的 cume_games 表。这样做的目的是生成当前比赛之前主队和客队的统计信息。

考虑到用于生成训练数据的查询的长度,您使用经典版 BigQuery 网页界面中已保存的查询来创建表。

要生成您的训练数据,请执行以下操作:

  1. 点击下方的按钮,打开输入数据查询。

    打开训练数据查询

    在时间允许的情况下,检验查询。

  2. 在查询窗口下方,点击显示选项

  3. 目标表部分,点击选择表

  4. 选择目标表对话框中,执行以下操作:

    1. 对于项目,选择您的项目。
    2. 对于数据集,选择 bqml_tutorial
    3. 表 ID 字段中,输入 wide_games
    4. 点击确定
  5. 点击运行查询。这会创建一个查询作业,并将查询结果写入您指定的表中。

    或者,如果在运行查询之前忘记指定目标表,您可以将临时表复制到永久表,只需点击结果窗口中的保存为表按钮即可。

  6. 查询将需要几秒钟才能完成。查询运行后,您的表 (wide_games) 会出现在 BigQuery 网页界面的导航面板中。

第四步:创建模型

接下来,您要创建一个线性回归模型。该模型用于根据先前的游戏统计信息,预测组合的三分球命中率。标准 SQL 查询利用 CREATE MODEL 语句来创建模型。虽然 CREATE MODEL 语句可以创建和训练模型,但在本教程中,您要单独训练模型。

要运行 CREATE MODEL 查询以创建模型:

  1. 切换到您的 Cloud Datalab 笔记本。

  2. 点击添加代码以创建新的代码单元。

  3. BigQuery Python 客户端库提供了一个魔法命令,可让您使用最少的代码运行查询。要从客户端库加载魔法命令,请输入以下代码。

    %load_ext google.cloud.bigquery
    
  4. 依次点击运行 > 从此单元运行。此命令没有任何输出。

  5. 点击添加代码以创建新的代码单元。

  6. BigQuery 客户端库提供了一个单元魔法 %%bigquery,它可运行 SQL 查询并以 Pandas DataFrame 形式返回结果。在单元中输入以下标准 SQL 查询。对于客户端库,不需要使用 #standardSQL 前缀。标准 SQL 为默认查询语法。

Jupyter

%%bigquery
CREATE OR REPLACE MODEL bqml_tutorial.ncaa_model OPTIONS(
  model_type='linear_reg', max_iteration=50) AS
SELECT
  * EXCEPT(
    game_id, season, scheduled_date,
    total_three_points_made,
    total_three_points_att),
  total_three_points_att as label
FROM
  bqml_tutorial.wide_games
WHERE
  # remove the game to predict
  game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'

Python

在尝试此示例之前,请先按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

sql = """
    CREATE OR REPLACE MODEL `bqml_tutorial.ncaa_model`
    OPTIONS (
        model_type='linear_reg',
        max_iteration=50 ) AS
    SELECT
        * EXCEPT (
            game_id, season, scheduled_date,
            total_three_points_made,
            total_three_points_att),
        total_three_points_att as label
    FROM
        `bqml_tutorial.wide_games`
    WHERE
        # remove the game to predict
        game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 依次点击运行 > 从此单元运行 (Run from this cell)。

  2. 查询需要几分钟才能完成。第一次迭代完成后,您的模型 (ncaa_model) 将出现在 BigQuery 网页界面的导航面板中。由于查询使用 CREATE MODEL 语句,因此您看不到查询结果。输出为空字符串。

第五步:获取训练统计信息

要查看模型训练结果,您可以使用 ML.TRAINING_INFO 函数,也可以在 BigQuery 网页界面中查看统计信息。BigQuery 经典版网页界面中尚未提供此功能。在本教程中,您将使用 ML.TRAINING_INFO 函数。

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

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

要查看运行 CREATE MODEL 查询时生成的模型训练统计信息:

  1. 点击添加代码以创建新的代码单元。

  2. 在单元中输入以下标准 SQL 查询。

Jupyter

%%bigquery
SELECT
  *
FROM
  ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)

Python

在尝试此示例之前,请先按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 依次点击运行 > 从此单元运行

  2. 查询完成后,结果会显示在查询下方。结果应类似于以下内容:

    ML.TRAINING_INFO 输出

    训练数据损失列表示训练数据集迭代后计算得出的损失指标。由于您执行了线性回归,因此该列为均方误差评估数据损失列是根据保留数据集计算得出的相同损失指标(从训练中收回用于验证模型的数据)。

    如需详细了解 ML.TRAINING_INFO 函数,请参阅 BigQuery ML 语法参考

第六步:评估模型

创建模型后,您可通过 ML.EVALUATE 函数评估模型的性能。

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

  1. 点击添加代码以创建新的代码单元。

  2. 在单元中输入以下标准 SQL 查询。

Jupyter

%%bigquery
WITH eval_table AS (
SELECT
  *,
  total_three_points_att AS label
FROM
  `bqml_tutorial.wide_games` )
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
    TABLE eval_table)

Python

在尝试此示例之前,请先按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

sql = """
    WITH eval_table AS (
        SELECT
            *,
            total_three_points_att AS label
        FROM
            `bqml_tutorial.wide_games` )
    SELECT
        *
    FROM
        ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
            TABLE eval_table)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 依次点击运行 > 从此单元运行 (Run from this cell)。

  2. 查询完成后,结果会显示在查询下方。结果应类似于以下内容:

    ML.EVALUATE 输出

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

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

评估结果中的一项重要指标为平均绝对误差。平均绝对误差是预测标签和实际标签之间的平均差。在此示例中约为 7。

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

您现已对模型进行了评估,下一步是用 ML.PREDICT 函数预测 2018 年 NCAA 决赛中三分球的总命中率:密歇根队对战维拉诺瓦队。

要运行使用 ML.PREDICT 函数的查询:

  1. 点击添加代码以创建新的代码单元。

  2. 在单元中输入以下标准 SQL 查询。

Jupyter

%%bigquery
  WITH game_to_predict AS (
  SELECT
    *
  FROM
    `bqml_tutorial.wide_games`
  WHERE
    game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
SELECT
  truth.game_id AS game_id,
  total_three_points_att,
  predicted_total_three_points_att
FROM (
  SELECT
    game_id,
    predicted_label AS predicted_total_three_points_att
  FROM
    ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
      table game_to_predict) ) AS predict
JOIN (
  SELECT
    game_id,
    total_three_points_att AS total_three_points_att
  FROM
    game_to_predict) AS truth
ON
  predict.game_id = truth.game_id

Python

在尝试此示例之前,请先按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

sql = """
    WITH game_to_predict AS (
        SELECT
            *
        FROM
            `bqml_tutorial.wide_games`
        WHERE
            game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
    SELECT
        truth.game_id AS game_id,
        total_three_points_att,
        predicted_total_three_points_att
    FROM (
        SELECT
            game_id,
            predicted_label AS predicted_total_three_points_att
        FROM
            ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
            table game_to_predict) ) AS predict
    JOIN (
        SELECT
            game_id,
            total_three_points_att AS total_three_points_att
        FROM
            game_to_predict) AS truth
    ON
        predict.game_id = truth.game_id
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 依次点击运行 > 从此单元运行 (Run from this cell)。

  2. 查询完成后,结果会显示在查询下方。结果应类似于以下内容。由于培训模型具有不确定性,因此您的结果可能会有所不同。

    ML.PREDICT 输出

total_three_points_att 值是决赛的实际命中数 - 50。该模型的预测值为 43.41。

清理

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

  • 您可以删除自己创建的项目。
  • 或者您可以保留项目但删除 Cloud Datalab 虚拟机。

删除 Cloud Datalab 虚拟机

删除项目会移除 Cloud Datalab 虚拟机。如果您不想删除 Cloud Platform 项目,则可删除 Cloud Datalab 虚拟机。

要删除 Cloud Datalab 虚拟机,请执行以下操作:

  1. 打开“Compute Engine 虚拟机实例”页面。

    转到“虚拟机实例”页面

  2. 检查 mltutorial 实例,然后点击删除

  3. 当系统提示时,点击删除

删除您的项目

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

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

    转到“项目”页面

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

后续步骤

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

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面