通过 BigQuery SQL 和 Vertex AI 生成生成式数据分析

1. 简介

在此 Codelab 中，我们将使用 BigQuery SQL 查询和 Vertex AI PaLM API 构建一款电影成功率预测和推荐应用。用于执行文本生成的模型为 text-bison，并作为远程函数托管在 BigQuery 中。

使用的服务列表如下：

  1. BigQuery ML
  2. Vertex AI PaLM API
  3. Cloud Shell

构建内容

您将创建

  • 包含模型的 BigQuery 数据集
  • 一种 BigQuery ML 模型，可根据电影的 GENRE 和 RUNTIME 属性预测电影的成功得分
  • 将 Vertex AI PaLM API 作为远程函数托管的 BigQuery 模型
  • 用于在 BigQuery 和 Vertex AI 之间建立连接的外部连接

2. 要求

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

准备工作

  1. 在 Google Cloud 控制台的项目选择器页面上，选择或创建一个 Google Cloud 项目
  2. 确保您的 Cloud 项目已启用结算功能。了解如何检查项目是否已启用结算功能
  3. 确保所有必需的 API（BigQuery API、Vertex AI API、BigQuery Connection API）均已启用
  4. 您将使用 Cloud Shell，这是一个在 Google Cloud 中运行的命令行环境，它预加载了 bq。如需了解 gcloud 命令和用法，请参阅文档

在 Cloud 控制台中，点击右上角的“激活 Cloud Shell”：

51622c00acec2fa.png

如果项目未设置，请使用以下命令进行设置：

gcloud config set project <YOUR_PROJECT_ID>
  1. 在浏览器中输入以下网址，直接前往 BigQuery 控制台：https://console.cloud.google.com/bigquery

3. 准备数据

在此使用情形中，我们将使用源自 movielens 的 movies 数据集。

  1. 创建数据集：

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

在 Cloud Shell 中，使用 bq mk 命令创建名为“movie_insights”的数据集

bq mk --location=us-central1 movie_insights
  1. 将源文件克隆到 Cloud Shell 机器：
git clone https://github.com/AbiramiSukumaran/movie_score_genai_insights
  1. 前往在 Cloud Shell 机器中创建的新项目目录：
cd movie_score_genai_insights
  1. 使用 bq load 命令将 CSV 文件加载到 BigQuery 表中（请注意，您也可以直接通过 BigQuery 界面上传）：
bq load --source_format=CSV --skip_leading_rows=1 movie_insights.movie_score \
./movies_data.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
  1. 您可以查询一个示例，以检查数据集是否已创建表 movie_score 和数据：
bq query --use_legacy_sql=false \
SELECT name, rating, genre, runtime FROM movie_insights.movie_score limit 3;

4. 从数据到机器学习

我们来创建一个分类模型，根据 GENRE 和 RUNTIME 属性预测电影的成功得分。我们将使用 CREATE MODEL 语句和“LOGISTIC_REG”选项来创建和训练逻辑回归模型。

在 BigQuery 控制台的 SQL 工作区查询编辑器部分中运行以下查询：

CREATE OR REPLACE MODEL
  `movie_insights.model_rating_by_runtime_genre`
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 name, genre,runtime, score
FROM
  movie_insights.movie_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 数据集。
  7. 以下构造是可选的，因此 BigQuery ML 可以明确地将其注册到 Vertex AI Model Registry。如需详细了解此功能，请参阅这篇博文. model_registry='vertex_ai', vertex_ai_model_version_aliases=['logistic_reg', 'experimental']

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

2e43087f914aa466.png

创建模型后，您可以使用 ML.EVALUATE 函数评估模型的性能。ML.EVALUATE 函数根据实际数据评估预测值。

您还可以从“模型”页面查看模型的评估指标：

7f2dc168bac0ac1a.png

关键指标一览：

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

5. 使用模型预测电影得分

预测时间到了！以下查询可预测数据集中归类为“测试”数据的每部电影的得分。

在 BigQuery 控制台的 SQL 工作区查询编辑器部分中运行以下查询：

SELECT
  *
FROM
  ML.PREDICT (MODEL movie_insights.model_rating_by_runtime_genre,
    (
    SELECT
      *
    FROM
      movie_insights.movie_score
    WHERE
      data_cat= 'TEST'
     )
  );

结果如下所示：

c719844860ce7c27.png

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

分析预测结果和模型：

您可以执行两个出色的分析步骤，以便通过预测了解结果：

  1. 如需了解模型为何生成这些预测结果，您可以使用 ML.EXPLAIN_PREDICT 函数。
  2. 如需了解哪些特征在整体上对确定收入等级最为重要，您可以使用 ML.GLOBAL_EXPLAIN 函数。

您可以在该文档中详细了解这些步骤。

6. 数据到生成式 AI

我们来分析电影数据集，方法是向 LLM（大语言模型）询问影响电影得分超过 5 分的因素的摘要，其中生成式 AI 使用 Vertex AI 的 text-bison（最新）模型，并且仅使用 SQL 查询

  1. 我们创建的 movie_score 表也将作为此步骤的输入。
  2. 系统将创建外部连接，以建立 BigQuery ML 与 Vertex 服务之间的访问权限。
  3. BigQuery GENERATE_TEXT 构造函数将用于从 Vertex AI 远程调用 PaLM API。

7. 创建外部连接

启用 BQ Connection API（如果尚未启用），并记下连接配置详细信息中的服务账号 ID：

  1. 点击 BigQuery 探索器窗格（位于 BigQuery 控制台左侧）中的“+ 添加”按钮，然后点击列出的热门来源中的“连接到外部数据源”
  2. 选择“BigLake 和远程函数”作为连接类型，提供“区域”作为位置类型，提供“us-central1 (Iowa)”作为值，并提供“bq_llm_connection”作为连接 ID

8a87802ab0846a6.png

  1. 创建连接后，请记下从连接配置详细信息中生成的服务账号

授予权限

在此步骤中，我们将向服务账号授予访问 Vertex AI 服务的权限：

打开 IAM，将您在创建外部连接后复制的服务账号添加为主账号，然后选择“Vertex AI User”角色

ff8e1d730879f972.png

8. 创建远程机器学习模型

创建表示托管式 Vertex AI 大语言模型的远程模型：

CREATE OR REPLACE MODEL
  movie_insights.llm_model REMOTE
WITH CONNECTION `us-central1.bq_llm_connection` OPTIONS (remote_service_type = 'CLOUD_AI_LARGE_LANGUAGE_MODEL_V1');

它会在数据集 movie_insights 中创建一个名为 llm_model 的模型，该模型利用 Vertex AI 的 CLOUD_AI_LARGE_LANGUAGE_MODEL_V1 API 作为远程函数。完成此操作需要几秒钟时间。

9. 使用机器学习模型生成文本

模型创建完毕后，您可以使用该模型生成、总结或分类文本。

SELECT
  ml_generate_text_result['predictions'][0]['content'] AS generated_text,
  ml_generate_text_result['predictions'][0]['safetyAttributes']
    AS safety_attributes,
  * EXCEPT (ml_generate_text_result)
FROM
  ML.GENERATE_TEXT(
    MODEL `movie_insights.llm_model`,
    (
 SELECT
      CONCAT('FROM THE FOLLOWING TEXT ABOUT MOVIES, WHAT DO YOU THINK ARE THE FACTORS INFLUENCING A MOVIE SCORE TO BE GREATER THAN 5?: ', movie_data) AS prompt
    FROM (
      SELECT
        REPLACE(STRING_AGG( CONCAT('A movie named ',name, ' from the country ', country, ' with a censor rating of ',rating, ' and a budget of ', budget, ' produced by ', company, ' with a runtime of about ', runtime, ' and in the genre ', genre, ' starring ', star, ' has had a success score of ', score, '') ), ',','. ') AS movie_data
      FROM (
        SELECT
          *
        FROM
          `movie_insights.movie_score`
        WHERE
          CAST(SCORE AS INT64) > 5
        LIMIT
          50) ) AS MOVIES
    ),
    STRUCT(
      0.2 AS temperature,
      100 AS max_output_tokens));

**说明：

ml_generate_text_result** 是文本生成模型以 JSON 格式返回的响应，其中包含内容和安全属性：a. 内容表示生成的文本结果 b。安全属性表示 Vertex AI Palm API 中启用的内置内容过滤条件，该过滤条件具有可调节的阈值，可避免大语言模型生成任何意外或不可预见的回答 - 如果回答违反安全阈值，则会被屏蔽

ML.GENERATE_TEXT 是您在 BigQuery 中用于访问 Vertex AI LLM 以执行文本生成任务的结构

CONCAT 会附加您的 PROMPT 语句和数据库记录

movie_insightsmovie_insights 是数据集名称，movie_score 是包含我们将在提示设计中使用的数据的表的名称

温度是用于控制回答随机性的提示参数 - 就相关性而言，温度越低越好

Max_output_tokens 是您希望回答中包含的字数

查询响应如下所示：

a3691afc0a97e724.png

如您所见，响应是嵌套的，且未格式化。

10. 展平查询结果

我们来展平结果，这样就不必在查询中显式解码 JSON：

SELECT
  *
FROM
  ML.GENERATE_TEXT( MODEL movie_insights.llm_model,
    (
    SELECT
      CONCAT('FROM THE FOLLOWING TEXT ABOUT MOVIES, WHAT DO YOU THINK ARE THE FACTORS INFLUENCING A MOVIE SCORE TO BE GREATER THAN 5?: ', movie_data) AS prompt
    FROM (
      SELECT
        REPLACE(STRING_AGG( CONCAT('A movie named ',name, ' from the country ', country, ' with a censor rating of ',rating, ' and a budget of ', budget, ' produced by ', company, ' with a runtime of about ', runtime, ' and in the genre ', genre, ' starring ', star, ' has had a success score of ', score, '') ), ',','. ') AS movie_data
      FROM (
        SELECT
          *
        FROM
          `movie_insights.movie_score`
        WHERE
          CAST(SCORE AS INT64) > 5
        LIMIT
          50) ) AS MOVIES),
    STRUCT( 0.2 AS temperature,
      100 AS max_output_tokens,
      TRUE AS flatten_json_output));

**说明：

Flatten_json_output** 表示布尔值，如果设置为 true，则返回从 JSON 响应中提取的易于理解的扁平文本。

查询响应如下所示：

1aaa0c514fccab59.png

11. 清理

为避免本博文中使用的资源导致您的 Google Cloud 账号产生费用，您可以前往 Vertex AI 端点页面，删除您在机器学习步骤中创建的 Vertex AI 端点。

12. 恭喜

恭喜！您已成功创建 BQML 模型，并仅使用 SQL 查询通过 Vertex AI API 对电影数据集执行基于 LLM 的分析。如需详细了解可用模型，请参阅 Vertex AI LLM 产品文档。