在 BigQuery 使用遠端模型分析電影海報

在 BigQuery 使用遠端模型分析電影海報

程式碼研究室簡介

上次更新時間：3月 10, 2025

作者：Alicia Williams

1. 簡介

在本實驗室中，您將瞭解如何使用 BigQuery 機器學習搭配遠端模型 ( Gemini 模型) 進行推論，分析電影海報圖片，並直接在 BigQuery 資料倉儲中根據海報產生電影摘要。

上圖：您要分析的電影海報圖片範例。

BigQuery 是內建 AI 的全代管資料分析平台，專為多引擎、跨格式和多雲端的環境而設計，可充分發揮資料的價值。其中一個重要功能是利用機器學習 (ML) 技術執行推論作業，讓您能使用 GoogleSQL 查詢建立及執行機器學習模型。

Gemini 是由 Google 開發的一系列生成式 AI 模型，專為多模態用途而設計。

使用 GoogleSQL 查詢執行機器學習模型

一般來說，要對大型資料集執行機器學習或人工智慧 (AI) 技術，需要進行大量程式設計，並具備機器學習框架的知識。這會將解決方案開發限制在少數公司專家內，透過 BigQuery 機器學習執行推論作業，SQL 從業人員就能運用現有的 SQL 工具和技能建構模型，並透過 LLM 和 Cloud AI API 產生結果。

必要條件

• 對 Google Cloud 控制台有基本瞭解
• 具備 BigQuery 使用經驗者優先

課程內容

• 如何設定環境和帳戶以使用 API
• 如何在 BigQuery 中建立雲端資源連線
• 如何在 BigQuery 中為電影海報圖片建立資料集和物件表
• 如何在 BigQuery 中建立 Gemini 遠端模型
• 如何提示 Gemini 模型為每張海報提供電影摘要
• 如何為每張海報中代表的電影產生文字嵌入
• 如何使用 BigQuery VECTOR_SEARCH，將電影海報圖片與資料集中的相關電影配對

軟硬體需求

• 已啟用計費功能的 Google Cloud 帳戶和 Google Cloud 專案
• 網路瀏覽器，例如 Chrome

2. 設定和需求

自助式環境設定

1. 登入 Google Cloud 控制台，然後建立新專案或重複使用現有專案。如果您還沒有 Gmail 或 Google Workspace 帳戶，請務必建立帳戶。

• 「Project name」是這個專案參與者的顯示名稱。這是 Google API 不會使用的字元字串。您隨時可以更新。
• 專案 ID 在所有 Google Cloud 專案中都是不重複的值，且無法變更 (設定後即無法變更)。Cloud 控制台會自動產生專屬字串，您通常不需要特別留意。在大多數程式碼研究室中，您都需要參照專案 ID (通常會以 PROJECT_ID 表示)。如果您不喜歡系統產生的 ID，可以隨機產生另一個 ID。或者，您也可以自行嘗試，看看是否可用。在這個步驟完成後就無法變更，且會在專案期間維持不變。
• 提醒您，有些 API 會使用第三個值「專案編號」。如要進一步瞭解這三個值，請參閱說明文件。

注意：專案 ID 在全球範圍內是唯一的，選取後就無法由其他人使用。你是該 ID 的唯一使用者。即使刪除專案，也無法再次使用該 ID

注意：如果你使用 Gmail 帳戶，可以將預設位置設為「無機構」。如果你使用 Google Workspace 帳戶，請選擇貴機構適用的位置。

2. 接下來，您需要在 Cloud 控制台中啟用帳單功能，才能使用 Cloud 資源/API。執行這個程式碼研究室不會產生太多費用，甚至可能完全不產生費用。如要關閉資源，避免在本教學課程結束後繼續產生費用，您可以刪除建立的資源或專案。Google Cloud 新使用者可享有 $300 美元的免費試用期。

啟動 Cloud Shell

雖然 Google Cloud 可透過筆記型電腦遠端操作，但在本程式碼研究室中，您將使用 Google Cloud Shell，這是在雲端運作的指令列環境。

在 Google Cloud 控制台中，按一下右上方工具列的 Cloud Shell 圖示：

佈建並連線至環境的作業需要一些時間才能完成。完成後，畫面應如下所示：

這個虛擬機器會載入您需要的所有開發工具。提供永久的 5 GB 主目錄，而且在 Google Cloud 中運作，可大幅提升網路效能和驗證功能。您可以在瀏覽器中完成本程式碼研究室的所有工作。您不需要安裝任何東西。

3. 事前準備

在 BigQuery 中使用 Gemini 模型需要完成幾個設定步驟，包括啟用 API、建立雲端資源連線，以及向雲端資源連線的服務帳戶授予特定權限。這些步驟每個專案只需執行一次，後續幾個章節會說明相關內容。

啟用 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 資源連線

在這項工作中，您將建立雲端資源連線，讓 BigQuery 存取 Cloud Storage 中的圖片檔案，並呼叫 Vertex AI。

1. 在 Google Cloud 控制台的「導覽選單」 ( ) 中，按一下「BigQuery」。

2. 為了建立連線，請依序點選「+ 新增」和「連線至外部資料來源」。

3. 在「連線類型」清單中，選取「Vertex AI 遠端模型、遠端函式和 BigLake (Cloud 資源)」。
4. 在「連線 ID」欄位為連線輸入 gemini_conn。
5. 「位置類型」請選取「多區域」，然後從下拉式選單中選取「US」這個多區域。
6. 其他設定均保留預設值。

7. 點選「建立連線」。
8. 點選「前往連線」。
9. 在「連線資訊」窗格，將服務帳戶 ID 複製到文字檔案，方便在下一項工作中使用。在 BigQuery Explorer，您也會看到該連線已新增至專案的「外部連線」專區。

5. 將 IAM 權限授予連線的服務帳戶

在這項工作，您會為 Cloud 資源連線的服務帳戶指定角色，讓帳戶具備 IAM 權限，以便使用 Vertex AI 服務。

1. 前往 Google Cloud 控制台，依序點選「導覽選單」和「IAM 與管理」，
2. 點選「授予存取權」。
3. 在「新增主體」欄位，輸入先前複製的服務帳戶 ID。
4. 在「請選擇角色」欄位輸入「Vertex AI」，然後選取「Vertex AI 使用者」角色。

5. 按一下 [儲存]。現在服務帳戶 ID 具備 Vertex AI 使用者角色。

注意：這個示範會分析位於公開 Cloud Storage 值區的資料集。分析非公開值區中的資料集時，服務帳戶也必須具備該儲存值區的「Storage 物件檢視者」角色。

6. 在 BigQuery 為電影海報圖片建立資料集和物件資料表

在這項工作中，您會為專案建立資料集，並在其中建立物件資料表來儲存海報圖片。

本教學課程中使用的電影海報圖片資料集會儲存在公開的 Google Cloud Storage 值區中：gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters

建立資料集

您將建立資料集，用於儲存本教學課程中使用的資料庫物件，包括資料表和模型。

1. 前往 Google Cloud 控制台，依序選取「導覽選單」圖示 和「BigQuery」。
2. 在「Explorer」面板中，依序選取專案名稱旁的「查看動作」圖示 ( ) 和「建立資料集」。
3. 在「建立資料集」窗格，輸入以下資訊：

• 資料集 ID：gemini_demo
• 位置類型：選取「多區域」
• 多區域：選取「美國」

4. 其他欄位均保留預設值。

5. 點選「建立資料集」。

系統就會建立 gemini_demo 資料集，並在 BigQuery Explorer 中列於專案下方。

建立物件資料表

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. 在 Explorer 點選 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. 在 Explorer 中，按一下 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 位置) 和 JSON 結果，其中包含 Gemini 1.5 Flash 模型生成的電影名稱和上映年份。

您可以使用下方查詢，以更方便人類閱讀的方式擷取資料：這個查詢會使用 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 模型提供電影摘要

假設您想進一步瞭解這些電影，例如每部電影的文字摘要，這類內容生成用途非常適合 Gemini 1.5 Flash 等 LLM 模型。

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.movie_posters_results_embeddings 資料表中的每個資料列，在 gemini_demo.imdb_movies_embeddings 資料表中尋找最相近的鄰近值。系統會使用餘弦距離指標來找出最鄰近的項目，這個指標用來表示兩個嵌入的相似度。

這個查詢可用於在 Gemini 1.5 Flash 在電影海報中識別的每部電影中，找出 IMDB 資料集中最相似的電影。舉例來說，您可以使用這項查詢，在 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 公開資料集的另一個資料表，以 join 的方式結合每部最相似電影的平均評分和投票數。

10. 恭喜

恭喜您完成程式碼研究室！您已成功在 BigQuery 中為海報圖片建立物件資料表、建立遠端 Gemini 模型、使用模型提示 Gemini 模型分析圖片和提供電影摘要、為電影名稱產生文字嵌入，並使用這些嵌入內容，將電影海報圖片與 IMDB 資料集中的相關電影名稱比對。

涵蓋內容

• 如何設定環境和帳戶以使用 API
• 如何在 BigQuery 中建立雲端資源連線
• 如何在 BigQuery 中為電影海報圖片建立資料集和物件表
• 如何在 BigQuery 中建立 Gemini 遠端模型
• 如何提示 Gemini 模型為每張海報提供電影摘要
• 如何為每張海報中代表的電影產生文字嵌入
• 如何使用 BigQuery VECTOR_SEARCH，將電影海報圖片與資料集中的相關電影配對

後續步驟 / 瞭解詳情

• BigQuery ML 簡介
• 網誌文章：使用 BigQuery ML 推論引擎擴大應用機器學習技術

11. 問卷調查

您要如何使用這個教學課程？

只閱讀內容閱讀內容並完成練習