通过 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 数据集
  • 一个根据电影的 GENRE 和 RUNTIME 属性预测电影成功分数的 BigQuery ML 模型
  • 将 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 属性预测电影的成功分数。我们将使用带有“LOGISTIC_REG”选项的 CREATE MODEL 语句以创建和训练逻辑回归模型。

在 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&#39;会强制 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 工作区的 SCHEMA 部分中：

2e43087f914aa466

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

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

7f2dc168bac0ac1a

关键指标一览表：

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

5. 使用模型预测电影配乐

预测时间！！！！以下查询会预测数据集中归类为“TEST”的每部影片的得分数据。

在 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

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

分析预测结果和模型：

您可以针对预测结果执行两个很棒的分析步骤，以了解结果：

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

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

6. 从数据到生成式 AI

我们使用 Vertex AI 的 text-bison（最新）模型的生成式 AI，仅使用 SQL 查询，通过 LLM（大语言模型）总结影响电影得分高于 5 的因素，提供关于电影数据集的分析洞见

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

7. 创建外部连接

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

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

8a87802ab0846a6

  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.content 表示生成的文本结果 b。安全属性表示内置内容过滤器，在 Vertex AI Palm API 中启用可调整的阈值，以避免大语言模型收到任何意外或不可预见的响应 - 如果响应违反安全阈值，则会被屏蔽

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

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

movie_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

11. 清理

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

12. 恭喜

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