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

1. 简介

在本 Codelab 中，我们将仅使用 SQL 通过 BigQuery ML 自定义模型创建电影评分预测模型，并在 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 中，我们将使用已准备好的数据文件。

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 交互，我们将尝试其中两种方式：BigQuery 网页界面 b. bq 命令 c. API

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

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

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”字段（值为 TRAIN、TEST 和 PREDICT 之一）在表中将电影数据（CSV 格式）拆分为 3 类。拆分数据集以进行测试和训练是模型的一个重要方面。如需详细了解如何理解数据集拆分，请参阅文档。

创建逻辑回归模型

我们可以使用包含“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' 会强制 BQML 根据查询条件（data_cat = ‘TRAIN'）对数据进行训练，另请注意，最好在此选项中使用“AUTO_SPLIT”，以允许框架（在本例中为服务）对训练/测试分块进行随机分区
  4. input_label_cols 选项指定 SELECT 语句中的哪个列将用作标签列。在这里，标签列是“score”，因此模型将根据每行中的其他值来学习得出“score”的 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 得分、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;

模型创建后，您可以执行 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';

在 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 创建电影评分预测模型，并将该模型部署在 Vertex AI Model Registry 中，而这一切都只使用了 SQL。