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

1. 简介

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

  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

将位置设置为某个区域 (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_leading_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.png

使用 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。拆分数据集以用于测试和训练是模型的一个重要方面。如果您需要详细了解数据集拆分，请参阅文档。

创建逻辑回归模型

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

在 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' 会强制 BQML 根据查询条件 (data_cat = ‘TRAIN') 训练数据，另请注意，最好在此选项中使用“AUTO_SPLIT”，以允许框架（或本例中的服务）随机划分训练/测试拆分
  4. input_label_cols 选项用于指定 SELECT 语句中的哪个列将用作标签列。在这里，标签列是“得分”，因此模型将根据每行中的其他值来学习得出“得分”的 10 个值中的哪一个值最有可能
  5. ‘auto_class_weights=TRUE' 选项平衡训练数据中的类别标签。默认情况下，训练数据未加权。如果训练数据中的标签不平衡，则模型可能会更频繁地学习预测最热门的标签类别
  6. SELECT 语句会查询我们使用 CSV 数据加载的表。WHERE 子句过滤输入表中的行，以便在此步骤中仅选择 TRAIN 数据集

创建完成后，BigQuery SQL 工作区的“架构”部分会显示以下内容：

标签

93efd0c1a7883690.png

特点

8c539338df1a9652.png

评估逻辑回归模型

创建模型后，您可以使用 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.png

模型结果显示了电影的预测得分（分类），范围为 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;

创建模型后，您可以按照我们在自定义 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';

在 CREATE MODEL 查询中添加 model_registry="vertex_ai" 选项，以便 BigQuery ML 可以选择要明确注册到 Vertex AI Model Registry 的模型。如需详细了解此功能，请参阅这篇博文。

9. 总结

在不到 30 分钟的时间内，我们：

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

10. 清理

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

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

11. 恭喜

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