使用 SQL 通过 BQML 进行电影评分预测

1. 简介

在此 Codelab 中，我们将通过 BigQuery ML 自定义模型创建一个电影得分预测模型（仅使用 SQL），并在 VertexAI 中部署该模型。您会了解到以下内容：

  1. 如何使用 Cloud Shell 创建 BigQuery 数据集并从文件加载数据
  2. 如何使用 BigQuery ML 进行监督式学习，为电影得分预测创建多类别回归预测模型，而无需编写太多代码但仅使用 SQL 查询

构建内容

您需要创建一个

  • 一个包含表和模型组件的 BigQuery 数据集
  • 使用 SQL 查询预测电影评分的 BigQuery ML 模型
  • 在 Vertex AI Model Registry 中部署模型

要求

  • 一个浏览器，例如 Chrome 或 Firefox
  • 启用了结算功能的 Google Cloud 项目

2. 创建项目

  1. 在 Google Cloud Console 的项目选择器页面上，选择或创建一个 Google Cloud 项目。
  2. 确保您的 Cloud 项目已启用结算功能。了解如何检查项目是否已启用结算功能。
  3. 前往 BigQuery 以启用 API。您还可以在浏览器中输入以下网址，直接打开 BigQuery 网页界面：https://console.cloud.google.com/bigquery

3. 激活 Cloud Shell

  1. 您将使用 Cloud Shell，这是一个在 Google Cloud 中运行的命令行环境，其中预装了 bq：在 Cloud 控制台中，点击右上角的“激活 Cloud Shell”：6757b2fb50ddcc2d.png
  2. 在连接到 Cloud Shell 后，您应该会看到自己已通过身份验证，并且项目已设置为您的项目 ID。在 Cloud Shell 中运行以下命令，以确认您已通过身份验证：
gcloud auth list
  1. 在 Cloud Shell 中运行以下命令，以确认 gcloud 命令了解您的项目
gcloud config list project
  1. 如果项目未设置，请使用以下命令进行设置：
gcloud config set project <PROJECT_ID>

如需了解 gcloud 命令和用法，请参阅文档。

4. 准备训练数据

这是所有数据相关项目、产品和应用中的重要步骤，除了准备最佳数据集并使其为机器学习项目做好准备的技术之外，还需要丰富的领域专业知识。在此 Codelab 中，我们将使用准备好的 data 文件。

5. 创建和加载数据集

BigQuery 数据集是表的集合。数据集中的所有表都存储在相同的数据位置。您还可以附加自定义访问权限控制，限制对数据集及其表的访问权限。

  1. 在 Cloud Shell 中，使用 bq mk 命令创建一个名为“movies”的数据集。
bq mk --location=<<LOCATION>> movies

将 location 设置为区域 (asia-south1)。

  1. 确保您已准备好数据文件 (.csv)。在 Cloud Shell 中执行以下命令，以克隆代码库并导航到项目：
git clone <<repository link>>

cd movie-score
  1. 使用 bq load 命令可以将 CSV 文件加载到 BigQuery 表中（请注意，您也可以直接从 BigQuery 界面上传）：
bq load --source_format=CSV --skip_leading_rows=1 movies.movies_score \
./movies_bq_src.csv \ Id:numeric,name:string,rating:string,genre:string,year:numeric,released:string,score:string,director:string,writer:string,star:string,country:string,budget:numeric,company:string,runtime:numeric,data_cat:string

选项说明： –source_format=CSV - 在解析数据文件时使用 CSV 数据格式。–skip_ leads_rows=1 - 跳过 CSV 文件中的第一行，因为它是标题行。Movies.movies - 第一个位置参数，用于定义应将数据加载到哪个表中。./movies.csv（第二个位置参数）定义要加载的文件。除本地文件外，bq load 命令还可以使用 gs://my_bucket/path/to/file URI 从 Cloud Storage 中加载文件。架构，可以在 JSON 架构文件中定义，也可以定义为英文逗号分隔列表（我使用的是以英文逗号分隔的列表）。太棒了！现在，csv 数据已加载到 movies.movies 表中。

  1. 使用以下 3 种方式之一进行查询：

我们可以通过三种方式与 BigQuery 交互，我们将尝试其中两种：a.BigQuery 网页界面 b.bq 命令 c.API

SELECT name, rating, genre, runtime FROM movies.movies_score limit 3;

我使用 BigQuery Web SQL 工作区运行查询。SQL 工作区如下所示：

109a0b2c7155e9b3

使用 bq 命令：

bq query --use_legacy_sql=false \
SELECT name, rating, genre, runtime FROM movies.movies_score limit 3;

6. 预测电影分级（1 到 10 级）

BigQuery ML 支持使用逻辑回归模型类型进行监督式学习。您可以使用二元逻辑回归模型类型来预测某个值是否属于两个类别之一：或者，您可以使用多类别回归模型类型来预测某个值是否属于多个类别之一。这类问题称为分类问题，因为它们会尝试将数据分为两个或更多类别。

关于选择模型的简要说明：这是此处选择的实验性模型，基于对结果的评估结果，我最初对几个模型运行了评估，最后使用 LOGISTIC REG 进行了测试，以确保其简单性，并从多个数据库获得更接近实际电影评分的结果。请注意，这应视为实现模型的示例，不一定是此用例的推荐模型。另一种实现方法是使用此逻辑回归模型将影片的结果预测为“良好”/“不佳”，而不是预测分数。

选择训练数据

我们已经使用字段“data_cat”在表中将电影数据（CSV 格式）拆分为 3 个类别其中包含以下 3 个值之一：TRAIN、TEST 和 PREDICT。拆分数据集以进行测试和训练，是模型的一个重要方面。如需详细了解数据集拆分，请参阅此文档。

创建逻辑回归模型

我们可以使用带有“LOGISTIC_REG”选项的 CREATE MODEL 语句以创建和训练逻辑回归模型。

在 BigQuery 网页界面 SQL 工作区中运行以下查询：

CREATE OR REPLACE MODEL
  `movies.movies_score_model`
OPTIONS
  ( model_type='LOGISTIC_REG',
    auto_class_weights=TRUE,
   data_split_method='NO_SPLIT',
    input_label_cols=[‘score']
  ) AS
SELECT
  * EXCEPT(id, data_cat)
FROM
  ‘movies.movies_score'
WHERE
  data_cat = 'TRAIN';

查询详情：

  1. CREATE MODEL 语句使用 SELECT 语句中的训练数据来训练模型
  2. OPTIONS 子句指定模型类型和训练选项。在这里，LOGISTIC_REG 选项指定逻辑回归模型类型。无需指定二元逻辑回归模型与多类别逻辑回归模型：BigQuery ML 可根据标签列中唯一值的数量确定要训练的模型
  3. data_split_method=‘NO_SPLIT&#39;会强制 BQML 根据查询条件使用数据进行训练 (data_cat = ‘TRAIN’)，另请注意，最好使用“AUTO_SPLIT”此选项可让框架（在本例中为服务）对训练/测试拆分进行随机化处理
  4. input_label_cols 选项指定 SELECT 语句中的哪个列用作标签列。在这里，标签列是得分，因此模型将根据每行中的其他值来学习 10 个得分值中的哪个值最有可能
  5. “auto_class_weights=TRUE”选项平衡训练数据中的类别标签。默认情况下，训练数据未加权。如果训练数据中的标签不平衡，则模型可能会更频繁地学习预测最热门的标签类别
  6. SELECT 语句会查询我们加载了 CSV 数据的表。WHERE 子句过滤输入表中的行，以便在此步骤中仅选择 TRAIN 数据集

创建完成后，以下内容将显示在 BigQuery SQL 工作区的 SCHEMA 部分中：

标签

93efd0c1a7883690

特点

8c539338df1a9652

评估您的逻辑回归模型

创建模型后，您可以使用 ML.EVALUATE 函数评估模型的性能。ML.EVALUATE 函数根据实际数据评估预测值。用于评估模型的查询如下所示：

SELECT
  *
FROM
  ML.EVALUATE (MODEL movies.movies_score_model,
    (
    SELECT
      *
    FROM
      movies.movies_score
    WHERE
      data_cat= ‘TEST'
    )
  )

ML.EVALUATE 函数将获取上一步中训练的模型和 SELECT 子查询返回的评估数据。此函数会返回有关模型的一行统计信息。

b54b0ebd6514a498.png

由于您执行了逻辑回归，因此结果包含您在上方屏幕截图中看到的指标，即精确率、召回率、准确率、f1-score、log_loss 和 roc_auc，这些指标对于评估模型的性能至关重要。您也可以在不提供输入数据的情况下调用 ML.EVALUATE。ML.EVALUATE 会检索在训练期间派生的评估指标，该指标使用自动预留的评估数据集。

关键指标一览表：

精确率 - 正例识别项中实际正确的比例占多大比例？精确率 = 真正例 /（真正例 + 假正例）召回率 - 正确识别的实际正例的比例是多少？召回率 = 真正例 /（真正例 + 假负例）准确率 - 用于评估分类模型的指标，是我们模型实际做出正确预测的比例。准确率 = 正确预测的数量 / 预测总数

使用 ML.PREDICT 预测电影评分

以下查询会预测数据集的 PREDICT 部分中每部电影的得分。

SELECT
  *
FROM
  ML.PREDICT (MODEL movies.movies_score_model,
    (
    SELECT
      *
    FROM
      movies.movies_score
    WHERE
      data_cat= ‘PREDICT'
     )
  )

结果摘要如下：

1efb91967acc1f0c

模型结果显示了影片的预测 SCORE，分值范围为 1 到 10（分类）。您肯定想知道，为什么每部电影都有多个预测行。这是因为模型返回了可能的预测标签，以及每个标签的出现概率（以降序排列）。

分析预测结果和模型

此外，您还可以执行两个很棒的分析步骤作为预测结果的一部分：

  1. 如需了解模型生成这些预测结果的原因，您可以使用 ML.EXPLAIN_PREDICT 函数
  2. 要知道哪些特征对于确定一般收入等级来说最为重要，可以使用 ML.GLOBAL_EXPLAIN 函数

如需详细了解这些步骤，请参阅文档。

7. 使用 BigQuery AutoML 预测电影评分

如果您希望让 BigQuery 服务为您创建模型并在模型生成结果生成之前进行放松，那么 AutoML 就是一个很好的选择。AUTOML 功能可直接在 BQML 中使用（model_type=AUTOML_CLASSIFIER 或 AUTOML_REGRESSOR），并且如果数据已在 BigQuery 中，则调用将更加容易。

CREATE OR REPLACE MODEL movies.auto_movies
       OPTIONS(model_type='AUTOML_CLASSIFIER',
               input_label_cols=['score'],
               budget_hours=1.0)
AS 
SELECT
  * EXCEPT(id, data_cat)
FROM
  movies.movies_score;

创建模型后，您可以执行 EVALUATE、PREDICT 和 EXPLAIN_PREDICT 的步骤，就像我们在自定义 BQML 模型中讨论的那样。如需详细了解 BigQuery AutoML，请参阅文档。

8. 一键将 BQML 模型部署到 Vertex AI Model Registry

您现在可以在 Vertex AI Model Registry 中看到您的所有机器学习模型，这使得您的组织能够更轻松地管理和部署模型。这包括使用 BigQuery ML、AutoML 构建的模型和自定义训练模型。具体方法如下：

CREATE OR REPLACE MODEL
  `movies.movies_score_model`
OPTIONS
  ( model_type='LOGISTIC_REG',
    auto_class_weights=TRUE,
   data_split_method='NO_SPLIT',
   model_registry='vertex_ai',
  vertex_ai_model_version_aliases=['logistic_reg', 'experimental'],
    input_label_cols=[‘score']
  ) AS
SELECT
  * EXCEPT(id, data_cat)
FROM
  ‘movies.movies_score'
WHERE
  data_cat = 'TRAIN';

添加 model_registry="vertex_ai"选项，以便 BigQuery ML 可以选择明确要注册到 Vertex AI Model Registry 中的模型。如需了解详情，请参阅此博客。

9. 总结

在不到 30 分钟的时间里，我们获得了以下结果：

  1. 将 BigQuery 设置为分析项目的数据库
  2. 仅使用 BigQuery 数据 SQL 而不使用其他编码创建自定义预测模型
  3. 仅使用 SQL 查询创建了 BQ Auto 机器学习模型
  4. 已将 BQML 模型部署到 Vertex AI Model Registry

10. 清理

为避免系统因本博文中使用的资源向您的 Google Cloud 账号收取费用，请按以下步骤操作：

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

11. 恭喜

恭喜！您已成功使用 BQML 自定义和 AutoML 创建了电影得分预测模型，并在 Vertex AI Model Registry 中部署了模型，并且仅使用 SQL。