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

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

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

目标

在本教程中,您将需要:

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

费用

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

  • BigQuery
  • BigQuery ML
  • Datalab

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

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

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

准备工作

  1. 登录您的 Google Cloud 帐号。如果您是 Google Cloud 新手,请创建一个帐号来评估我们的产品在实际场景中的表现。新客户还可获享 $300 赠金,用于运行、测试和部署工作负载。
  2. 在 Google Cloud Console 的项目选择器页面上,选择或创建一个 Google Cloud 项目。

    转到“项目选择器”

  3. 确保您的 Cloud 项目已启用结算功能。 了解如何确认您的项目是否已启用结算功能

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

    启用 API

第一步:设置 Datalab

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

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

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

  1. 转到 Cloud Console。

    Cloud Console

  2. 点击激活 Cloud Shell

    激活 Cloud Shell 图标

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

    gcloud config set compute/zone us-central1-a
    
  4. 输入以下命令,创建用于运行 Datalab 容器的 Compute Engine 虚拟机(名为 mltutorial)。此命令还会创建与虚拟机的 SSH 连接,并将远程 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,然后点击更改并预览。此操作会在新的标签页中打开 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. 依次点击运行 > 从此单元运行。查询结果会显示在代码块下方。看到以下消息即表示安装完成:

    Successfully installed ...

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

    单元菜单

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

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

    from google.cloud import bigquery
    client = bigquery.Client()
    # We use a unique dataset ID for this example to avoid collisions with
    # other invocations of this tutorial.  In practice, you could leverage
    # a persistent dataset and not create/destroy it with each invocation.
    dataset_id = "bqml_tutorial_{}".format(str(uuid.uuid4().hex))
  16. 依次点击运行 > 从此单元运行。此命令没有任何输出。

第二步:创建数据集

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

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

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

    dataset = bigquery.Dataset(client.dataset(dataset_id))
    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. Table ID 字段中,输入 cume_games
    4. 点击确定
  5. 点击运行查询。这会创建一个查询作业,并将查询结果写入您指定的表中。

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

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

生成训练数据

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

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

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

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

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

    打开训练数据查询

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

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

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

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

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

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

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

第四步:创建模型

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

要运行 CREATE MODEL 查询以创建模型,请执行以下操作:

  1. 切换到您的 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

sql = """
    CREATE OR REPLACE MODEL `{0}.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
        `{0}.wide_games`
    WHERE
        # remove the game to predict
        game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

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

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

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

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

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

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

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

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

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

Jupyter

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

Python

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `{}.ncaa_model`)
""".format(dataset_id)
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

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

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

  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

sql = """
    WITH game_to_predict AS (
        SELECT
            *
        FROM
            `{0}.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 `{0}.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
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

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

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

    ML.PREDICT 输出

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

清除数据

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

  • 删除您在教程中创建的项目。
  • 或者您可以保留项目但删除 Datalab 虚拟机。

删除 Datalab 虚拟机

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

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

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

    转到“虚拟机实例”页面

  2. 勾选 mltutorial 实例,然后点击删除

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

删除您的项目

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

  1. 在 Cloud Console 中,转到管理资源页面。

    转到“管理资源”

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

后续步骤