在 BigQuery 中利用远程模型分析电影海报

在 BigQuery 中利用远程模型分析电影海报

关于此 Codelab

上次更新时间：3月 10, 2025

Alicia Williams 编写

1. 简介

在本实验中，您将学习如何将 BigQuery 机器学习推理功能与远程模型（Gemini 模型）结合使用，直接在 BigQuery 数据仓库中分析电影海报图片，并根据海报生成电影摘要。

上图：您要分析的电影海报图片示例。

BigQuery 是一个 AI 就绪型全托管式数据分析平台，可帮助您充分发掘数据的价值，并支持多引擎、多格式和多云。其主要功能之一是 BigQuery Machine Learning 推理，该功能允许您使用 GoogleSQL 查询来创建和运行机器学习 (ML) 模型。

Gemini 是 Google 开发的一系列生成式 AI 模型，专为多模态应用场景而设计。

使用 GoogleSQL 查询运行机器学习模型

对大型数据集执行机器学习或人工智能 (AI) 任务通常涉及大量编程工作，并需要具备机器学习框架方面的知识。因此，各公司只有一小部分专家能负责开发解决方案。借助 BigQuery 机器学习推理功能，SQL 专业人员可以使用现有 SQL 工具和技能来构建模型，并通过大语言模型 (LLM) 和 Cloud AI API 来生成结果。

前提条件

• 对 Google Cloud 控制台有基本的了解
• 有 BigQuery 使用经验为佳

学习内容

• 如何配置环境和账号以使用 API
• 如何在 BigQuery 中创建 Cloud 资源连接
• 如何在 BigQuery 中为电影海报图片创建数据集和对象表
• 如何在 BigQuery 中创建 Gemini 远程模型
• 如何提示 Gemini 模型为每张海报提供影片摘要
• 如何为每张海报对应的影片生成文本嵌入
• 如何使用 BigQuery VECTOR_SEARCH 将电影海报图片与数据集中高度相关的影片进行匹配

所需条件

• 已启用结算功能的 Google Cloud 账号和 Google Cloud 项目
• 网络浏览器，例如 Chrome

2. 设置和要求

自定进度的环境设置

1. 登录 Google Cloud 控制台，然后创建一个新项目或重复使用现有项目。如果您还没有 Gmail 或 Google Workspace 账号，则必须创建一个。

• 项目名称是此项目参与者的显示名称。它是 Google API 尚未使用的字符串。您可以随时对其进行更新。
• 项目 ID 在所有 Google Cloud 项目中是唯一的，并且是不可变的（一经设置便无法更改）。Cloud 控制台会自动生成一个唯一字符串；通常情况下，您无需关注该字符串。在大多数 Codelab 中，您都需要引用项目 ID（通常用 PROJECT_ID 标识）。如果您不喜欢生成的 ID，可以再随机生成一个 ID。或者，您也可以尝试自己的项目 ID，看看是否可用。完成此步骤后便无法更改该 ID，并且此 ID 在项目期间会一直保留。
• 此外，还有第三个值，即部分 API 使用的项目编号，供您参考。如需详细了解所有这三个值，请参阅文档。

注意：项目 ID 具有全局唯一性，您选择它后，其他任何人都不能再使用。您是此 ID 的唯一用户。即使项目被删除，相应 ID 也无法再次使用

注意：如果您使用的是 Gmail 账号，可以将默认位置设置为无组织。如果您使用的是 Google Workspace 账号，请选择对贵组织来说合理的位置。

2. 接下来，您需要在 Cloud 控制台中启用结算功能，以便使用 Cloud 资源/API。运行此 Codelab 应该不会产生太多的费用（如果有的话）。若要关闭资源以避免产生超出本教程范围的结算费用，您可以删除自己创建的资源或删除项目。Google Cloud 新用户符合参与 300 美元免费试用计划的条件。

启动 Cloud Shell

虽然可以通过笔记本电脑对 Google Cloud 进行远程操作，但在此 Codelab 中，您将使用 Google Cloud Shell，这是一个在云端运行的命令行环境。

在 Google Cloud 控制台 中，点击右上角工具栏中的 Cloud Shell 图标：

预配和连接到环境应该只需要片刻时间。完成后，您应该会看到如下内容：

这个虚拟机已加载了您需要的所有开发工具。它提供了一个持久的 5 GB 主目录，并且在 Google Cloud 中运行，大大增强了网络性能和身份验证功能。您在此 Codelab 中的所有工作都可以在浏览器中完成。您无需安装任何程序。

3. 准备工作

若要在 BigQuery 中使用 Gemini 模型，您需要完成一些设置步骤，包括启用 API、创建 Cloud 资源连接，以及向 Cloud 资源连接的服务账号授予特定权限。这些步骤每个项目只需执行一次，后续几个部分将对此进行介绍。

启用 API

请注意，您启用的某些资源将产生一定的费用，包括 BigQuery、BigQuery ML 和 Vertex AI。在正常情况下，本教程中所用资源在一次运行时的费用不会超过 5 美元。我们建议您查看结算信息，并确保您可接受该练习。

在 Cloud Shell 中，确保项目 ID 已设置：

gcloud config set project [YOUR-PROJECT-ID]

设置环境变量 PROJECT_ID：

PROJECT_ID=$(gcloud config get-value project)

配置要用于 Vertex AI 模型的默认区域。详细了解 Vertex AI 的可用位置。在此示例中，我们使用的是 us-central1 区域。

gcloud config set compute/region us-central1

设置环境变量 REGION：

REGION=$(gcloud config get-value compute/region)

启用所有必要的服务：

gcloud services enable bigqueryconnection.googleapis.com \
                       aiplatform.googleapis.com

运行上述所有命令后的预期输出：

student@cloudshell:~ (test-project-001-402417)$ gcloud config set project test-project-001-402417
Updated property [core/project].
student@cloudshell:~ (test-project-001-402417)$ PROJECT_ID=$(gcloud config get-value project)
Your active configuration is: [cloudshell-14650]
student@cloudshell:~ (test-project-001-402417)$ 
student@cloudshell:~ (test-project-001-402417)$ gcloud services enable bigqueryconnection.googleapis.com \
                       aiplatform.googleapis.com
Operation "operations/acat.p2-4470404856-1f44ebd8-894e-4356-bea7-b84165a57442" finished successfully.

4. 创建 Cloud 资源连接

在此任务中，您将创建一个 Cloud 资源连接，以便 BigQuery 访问 Cloud Storage 中的图片文件并调用 Vertex AI。

1. 在 Google Cloud 控制台中，点击导航菜单 ( ) 下的 BigQuery。

2. 如需创建连接，请点击 + 添加，然后点击与外部数据源的连接。

3. 在“连接类型”列表中，选择 Vertex AI 远程模型、远程函数和 BigLake（Cloud 资源）。
4. 在“连接 ID”字段中，输入 gemini_conn 作为连接的名称。
5. 在位置类型部分，选择多区域，然后从下拉菜单中选择美国（多区域）。
6. 对于其他设置，请使用默认值。

7. 点击创建连接。
8. 点击转到连接。
9. 将“连接信息”窗格中的服务账号 ID 复制到一个文本文件中，以便在下个任务中使用。您还会看到，在 BigQuery 探索器中，该连接已添加到您项目下的“外部连接”部分。

5. 向连接的服务账号授予 IAM 权限

在此项任务中，您将通过角色为 Cloud 资源连接的服务账号授予 IAM 权限，使其能够访问 Vertex AI 服务。

1. 在 Google Cloud 控制台中，点击导航菜单下的 IAM 和管理。
2. 点击授予访问权限。
3. 在新的主账号字段中，输入您之前复制的服务账号 ID。
4. 在“选择角色”字段中，输入 Vertex AI，然后选择 Vertex AI User 角色。

5. 点击保存。这样该服务账号 ID 就拥有了 Vertex AI User 角色。

注意：此演示会分析位于公共 Cloud Storage 存储分区中的数据集。分析非公开存储分区中的数据集时，服务账号还需要拥有该存储分区的“Storage Object Viewer”角色。

6. 在 BigQuery 中为电影海报图片创建数据集和对象表

在此任务中，您将为项目创建一个数据集，并在其中创建一个对象表来存储海报图片。

本教程中使用的电影海报图片数据集存储在一个公共 Google Cloud Storage 存储分区中：gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters

创建数据集

您将创建数据集来存储本教程中使用的数据库对象，包括表和模型。

1. 在 Google Cloud 控制台中，依次选择导航菜单 ( ) 和 BigQuery。
2. 在探索器面板中，选择项目名称旁边的查看操作 ( )，然后选择创建数据集。
3. 在创建数据集窗格中，输入以下信息：

• 数据集 ID：gemini_demo
• 位置类型：选择多区域
• 多区域：选择美国

4. 对于其他字段，保留默认值。

5. 点击创建数据集。

这将创建 gemini_demo 数据集，并在 BigQuery 探索器中您的项目下列出该数据集。

创建对象表

BigQuery 不仅存储结构化数据，还可以通过对象表访问非结构化数据（例如海报图片）。

您可以通过指向 Cloud Storage 存储分区来创建对象表，并且生成的对象表中会为存储分区中的每个对象创建一行，其中包含相应对象的存储路径和元数据。

要创建对象表，可以使用 SQL 查询。

1. 点击 + 以创建新的 SQL 查询。
2. 在查询编辑器中，粘贴下面的查询。

CREATE OR REPLACE EXTERNAL TABLE
  `gemini_demo.movie_posters`
WITH CONNECTION `us.gemini_conn`
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/*']
  );

3. 运行查询。这会将 movie_posters 对象表添加到 gemini_demo 数据集中，并加载每张电影海报图片的 URI（Cloud Storage 位置）。
4. 在“探索器”中，点击 movie_posters，查看架构和详细信息。请查询此表以查看特定记录。

7. 在 BigQuery 中创建 Gemini 远程模型

对象表创建完毕后，就可以开始使用它了。在此任务中，您将为 Gemini 1.5 Flash 创建一个远程模型，以便在 BigQuery 中使用该模型。

创建 Gemini 1.5 Flash 远程模型

1. 点击 + 以创建新的 SQL 查询。
2. 在查询编辑器中，粘贴下方的查询并运行。

CREATE OR REPLACE MODEL `gemini_demo.gemini_1_5_flash`
REMOTE WITH CONNECTION `us.gemini_conn`
OPTIONS (endpoint = 'gemini-1.5-flash')

这将创建 gemini_1_5_flash 模型，您会看到它已添加到“模型”部分的 gemini_demo 数据集中。

3. 在“探索器”中，点击 gemini_1_5_flash 模型，查看详细信息。

8. 提示 Gemini 模型为每张海报提供影片摘要

在此任务中，您将使用刚刚创建的 Gemini 远程模型来分析电影海报图片，并生成每部影片的摘要。

您可以使用 ML.GENERATE_TEXT 函数向模型发送请求，并在参数中引用模型。

使用 Gemini 1.5 Flash 模型分析图片

1. 使用以下 SQL 语句创建并运行一个新查询：

CREATE OR REPLACE TABLE
`gemini_demo.movie_posters_results` AS (
SELECT
    uri,
    ml_generate_text_llm_result
FROM
    ML.GENERATE_TEXT( MODEL `gemini_demo.gemini_1_5_flash`,
    TABLE `gemini_demo.movie_posters`,
    STRUCT( 0.2 AS temperature,
        'For the movie represented by this poster, what is the movie title and year of release? Answer in JSON format with two keys: title, year. title should be string, year should be integer. Do not use JSON decorators.' AS PROMPT,
        TRUE AS FLATTEN_JSON_OUTPUT)));

运行查询时，BigQuery 会针对对象表的每一行向 Gemini 模型提示，将图片与指定的静态提示组合在一起。这将会创建 movie_posters_results 表。

注意：ML.GENERATE_TEXT 函数用于针对对象表的每一行提示 Gemini 模型。此函数接受三个参数：

• 第一个参数是用于提示的 MODEL 的名称。在本例中，模型名为 gemini_demo.gemini_1_5_flash。
• 第二个参数指向引用图片的对象 TABLE。
• 第三个实参是用于指定参数的 STRUCT。在本例中，STRUCT 包含 PROMPT，该值将与对象表中每行的图片串联在一起，然后再发送到 Gemini 模型。它还包含 temperature 和 flatten_json_output 等可选参数，将其设置为 TRUE 可将输出展平为单个 JSON 对象。

2. 现在，我们来查看结果。使用以下 SQL 语句创建并运行一个新查询：

SELECT * FROM `gemini_demo.movie_posters_results`

这将显示每张电影海报的行，其中包括 URI（电影海报图片的 Cloud Storage 位置），以及包含 Gemini 1.5 Flash 模型中的影片名称和影片发行年份的 JSON 结果。

您可以使用下方的查询，以更便于人类阅读的方式检索这些结果。此查询使用 SQL 将这些响应中的电影名和发行年份提取到新列中。

3. 使用以下 SQL 语句创建并运行一个新查询：

CREATE OR REPLACE TABLE
  `gemini_demo.movie_posters_results_formatted` AS (
  SELECT
    uri,
    JSON_VALUE(ml_generate_text_llm_result, "$.title") AS title,
    JSON_VALUE(ml_generate_text_llm_result, "$.year") AS year
  FROM
    `gemini_demo.movie_posters_results` results )

这将会创建 movie_posters_result_formatted 表。

4. 您可以通过下方的查询来查询此表，以查看创建的行。

SELECT * FROM `gemini_demo.movie_posters_results_formatted`

请注意，URI 列结果保持不变，但 JSON 现已转换为每一行的 title 和 year 列。

提示 Gemini 1.5 Flash 模型提供影片摘要

如果您想详细了解这些电影，比如查看每个电影的文本摘要，该怎么办？这种内容生成应用场景非常适合 LLM 模型（例如 Gemini 1.5 Flash 模型）。

1. 您可以运行以下查询，让 Gemini 1.5 Flash 提供每张海报对应的影片摘要：

SELECT
  uri,
  title,
  year,
  prompt,
  ml_generate_text_llm_result
  FROM
 ML.GENERATE_TEXT( MODEL `gemini_demo.gemini_1_5_flash`,
   (
   SELECT
     CONCAT('Provide a short summary of movie titled ',title, ' from the year ',year,'.') AS prompt,
     uri,
     title,
     year
   FROM
     `gemini_demo.movie_posters_results_formatted`
   LIMIT
     20 ),
   STRUCT(0.2 AS temperature,
     TRUE AS FLATTEN_JSON_OUTPUT));     

请注意结果中的 ml_generate_text_llm_result 字段；其中包含电影的简短摘要。

注意：在此 ML.GENERATE_TEXT 函数用法中，您将针对 movie_posters_results_formatted 表的每一行向 Gemini 模型发送纯文本提示。该函数仍然接受与之前相同的三个参数：

• MODEL 以提示
• TABLE，这次是使用 SELECT 语句动态创建的
• STRUCT，现在不含 PROMPT，但包含其他可选参数

请注意，在此仅限文本提示场景中，PROMPT 会移至 TABLE 参数中的列，并且可以纳入数据中的字段，例如 title 和 year。此格式不同于您在上一节中构建多模态提示的方式。

9. 使用远程模型生成文本嵌入

现在，您可以将自己构建的结构化数据与数据仓库中的其他结构化数据联接起来。BigQuery 中提供的 IMDB 公共数据集包含大量有关电影的信息，包括观看者评分以及一些自由形式的用户评价示例。这些数据有助于您深入分析电影海报，了解观众对这些电影的看法。

如需联接数据，您需要使用键。在这种情况下，Gemini 模型生成的电影名可能与 IMDB 数据集中的电影名不完全匹配。

在此任务中，您将为这两个数据集中的电影名和年份生成文本嵌入，然后使用这些嵌入之间的距离将最接近的 IMDB 电影名与新创建的数据集中的电影海报名联接起来。

创建远程模型

如需生成文本嵌入，您需要创建一个指向 text-multilingual-embedding-002 端点的新远程模型。

1. 使用以下 SQL 语句创建并运行一个新查询：

CREATE OR REPLACE MODEL `gemini_demo.text_embedding`
REMOTE WITH CONNECTION `us.gemini_conn`
OPTIONS (endpoint = 'text-multilingual-embedding-002')

这将创建 text_embedding 模型，并显示在探索器中 gemini_demo 数据集的下方。

为与海报关联的影片名称和年份生成文本嵌入

现在，您将将此远程模型与 ML.GENERATE_EMBEDDING 函数搭配使用，为每张电影海报的标题和年份创建嵌入。

1. 使用以下 SQL 语句创建并运行一个新查询：

CREATE OR REPLACE TABLE
  `gemini_demo.movie_posters_results_embeddings` AS (
  SELECT
    *
  FROM
    ML.GENERATE_EMBEDDING(
    MODEL `gemini_demo.text_embedding`,
    (
    SELECT
      CONCAT('The movie titled ', title, ' from the year ', year,'.') AS content,
      title,
      year,
      uri
    FROM
      `gemini_demo.movie_posters_results_formatted` ),
    STRUCT(TRUE AS flatten_json_output)));

结果是，系统会创建 movie_poster_results_embeddings 表，其中包含为 gemini_demo.movie_posters_results_formatted 表的每一行串联的文本内容创建的嵌入。

注意：ML.GENERATE_EMBEDDING 函数用于生成嵌入。与 ML.GENERATE_TEXT 类似，此函数接受三个参数：

• 用于生成嵌入的 MODEL；这是您刚刚创建的名为 gemini_demo.text_embedding 的远程模型。
• 包含要嵌入的文本内容的 TABLE；再次使用 SELECT 语句动态创建，该语句将文本内容定义为将每部电影的 title 和 year 串联成单个字符串。
• STRUCT 和可选参数。

2. 您可以使用下面的新查询来查看查询结果：

SELECT * FROM `gemini_demo.movie_posters_results_embeddings`

您可以看到模型生成的每部影片的嵌入（由数字表示的向量）。

为 IMDB 数据集的子集生成文本嵌入

您将创建一个新的数据视图，其中仅包含公共 IMDB 数据集中于 1935 年之前发行的影片（海报图片中已知的影片时间段）。

1. 使用以下 SQL 语句创建并运行一个新查询：

CREATE OR REPLACE VIEW
  `gemini_demo.imdb_movies` AS (
  WITH
    reviews AS (
      SELECT
        reviews.movie_id AS movie_id,
        title.primary_title AS title,
        title.start_year AS year,
        reviews.review AS review
      FROM
        `bigquery-public-data.imdb.reviews` reviews
      LEFT JOIN
        `bigquery-public-data.imdb.title_basics` title
      ON
        reviews.movie_id = title.tconst)
  SELECT
    DISTINCT(movie_id),
    title,
    year
  FROM
    reviews
  WHERE
    year < 1935)

这将生成一个新视图，其中包含 bigquery-public-data.imdb.reviews 表中于 1935 年之前发行的所有影片的影片 ID、片名和发行年份列表。

2. 现在，您将使用与上一部分类似的流程为 IMDB 中的部分电影创建嵌入。使用以下 SQL 语句创建并运行一个新查询：

CREATE OR REPLACE TABLE
  `gemini_demo.imdb_movies_embeddings` AS (
  SELECT
    *
  FROM
    ML.GENERATE_EMBEDDING( MODEL `gemini_demo.text_embedding`,
      (
      SELECT
        CONCAT('The movie titled ', title, ' from the year ', year,'.') AS content,
        title,
        year,
        movie_id
      FROM
        `gemini_demo.imdb_movies` ),
      STRUCT(TRUE AS flatten_json_output) )
  WHERE
    ml_generate_embedding_status = '' );

查询的结果是一个表，其中包含 gemini_demo.imdb_movies 表的文本内容的嵌入。

使用 BigQuery VECTOR_SEARCH 将电影海报图片与 IMDB movie_id 进行匹配

现在，您可以使用 VECTOR_SEARCH 函数联接这两个表。

1. 使用以下 SQL 语句创建并运行一个新查询：

SELECT
  query.uri AS poster_uri,
  query.title AS poster_title,
  query.year AS poster_year,
  base.title AS imdb_title,
  base.year AS imdb_year,
  base.movie_id AS imdb_movie_id,
  distance
FROM
 VECTOR_SEARCH( TABLE `gemini_demo.imdb_movies_embeddings`,
   'ml_generate_embedding_result',
   TABLE `gemini_demo.movie_posters_results_embeddings`,
   'ml_generate_embedding_result',
   top_k => 1,
   distance_type => 'COSINE');

该查询使用 VECTOR_SEARCH 函数在 gemini_demo.imdb_movies_embeddings 表中查找 gemini_demo.movie_posters_results_embeddings 表中每一行的最近邻。最近邻是通过余弦距离指标找到的，该指标能够判断两个嵌入项的相似程度。

此查询可用于在 IMDB 数据集中查找与 Gemini 1.5 Flash 在电影海报中识别的每部影片最相似的影片。例如，您可以使用此查询，在 IMDB 公共数据集中查找与影片“Au Secours!”最为匹配的结果，该公共数据集通过英文片名“Help!”来指代该影片。

2. 创建并运行一个新查询，以便联接 IMDB 公共数据集中提供的有关影片评分的其他信息：

SELECT
  query.uri AS poster_uri,
  query.title AS poster_title,
  query.year AS poster_year,
  base.title AS imdb_title,
  base.year AS imdb_year,
  base.movie_id AS imdb_movie_id,
  distance,
  imdb.average_rating,
  imdb.num_votes
FROM
  VECTOR_SEARCH( TABLE `gemini_demo.imdb_movies_embeddings`,
    'ml_generate_embedding_result',
    TABLE `gemini_demo.movie_posters_results_embeddings`,
    'ml_generate_embedding_result',
    top_k => 1,
    distance_type => 'COSINE') DATA
LEFT JOIN
  `bigquery-public-data.imdb.title_ratings` imdb
ON
  base.movie_id = imdb.tconst
ORDER BY
  imdb.average_rating DESC

此查询与上一条查询类似。它仍然使用特殊数值表示法（向量嵌入）来查找与给定电影海报相似的影片。不过，它还会从 IMDB 公共数据集内的单独表中，联接每个最近邻影片的平均评分和投票数。

10. 恭喜

恭喜您完成此 Codelab。您已成功在 BigQuery 中为海报图片创建对象表，创建了远程 Gemini 模型，使用该模型提示 Gemini 模型分析图片并提供电影摘要，为电影名生成文本嵌入，并使用这些嵌入将电影海报图片与 IMDB 数据集中的相关电影名进行匹配。

所学内容

• 如何配置环境和账号以使用 API
• 如何在 BigQuery 中创建 Cloud 资源连接
• 如何在 BigQuery 中为电影海报图片创建数据集和对象表
• 如何在 BigQuery 中创建 Gemini 远程模型
• 如何提示 Gemini 模型为每张海报提供影片摘要
• 如何为每张海报对应的影片生成文本嵌入
• 如何使用 BigQuery VECTOR_SEARCH 将电影海报图片与数据集中高度相关的影片进行匹配

后续步骤 / 了解详情

• BigQuery ML 简介
• 利用 BigQuery 机器学习推理引擎扩展机器学习 - 博客

11. 调查问卷

您打算如何使用本教程？

仅通读阅读并完成练习