使用 BigQuery SQL 和 Vertex AI 執行生成式深入分析資訊

1. 簡介

在本程式碼實驗室中，我們將使用 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 屬性預測電影的成功率
  • BigQuery 模型，將 Vertex AI PaLM API 做為遠端函式代管
  • 外部連線，用於建立 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 來源衍生的電影資料集。

  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 UI 上傳)：
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 Workspace」查詢編輯器部分，執行下列查詢：

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 陳述式中的哪個資料欄來當做標籤資料欄。這裡的標籤資料欄為「score」，因此模型將根據每個資料列中呈現的其他值，學習「score」的 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.png

建立模型後，請使用 ML.EVALUATE 函式評估模型效能。ML.EVALUATE 函式會根據實際資料來評估預測值。

您也可以在「MODEL」(模型) 頁面中查看模型的評估指標：

7f2dc168bac0ac1a.png

主要指標一覽：

精確度：實際正確的正向識別比例是多少？精確度 = 真陽性 / (真陽性 + 偽陽性) 喚回率 - 模型正確找出多少比例的實際正類？召回率 = 真陽性 / (真陽性 + 偽陰性) 準確率 - 用於評估分類模型的指標，是模型實際預測正確的比例。準確率 = 正確預測數 / 預測總數

5. 使用模型預測電影評分

預測時間到啦！以下查詢會預測資料集中歸類為「TEST」資料的每部電影分數。

在 BigQuery 控制台的「SQL Workspace」查詢編輯器部分，執行下列查詢：

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 分 (分類)。您可能想知道，為何每部電影都有好幾列預測資料。這是因為模型已傳回可能的預測標籤，以及每個標籤的出現機率 (依遞減順序排列)。

分析預測結果和模型：

您可以透過預測執行兩項出色的分析步驟，瞭解結果：

  1. 如要瞭解模型產生這些預測結果的原因，可以使用 ML.EXPLAIN_PREDICT 函式。
  2. 如要瞭解哪些特徵最重要，可決定一般收入水平，請使用 ML.GLOBAL_EXPLAIN 函式。

如要詳細瞭解這些步驟，請參閱說明文件。

6. 提供給生成式 AI 的資料

讓我們透過 Generative AI，使用 Vertex AI 的 text-bison (最新) 模型，僅透過 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 (Iowa)」做為值，以及「bq_llm_connection」做為連線 ID

8a87802ab0846a6.png

  1. 連線建立完畢後，請記下連線設定詳細資料中產生的服務帳戶

授予權限

在這個步驟中，我們會授予服務帳戶存取 Vertex AI 服務的權限：

開啟 IAM，將您在建立外部連線後複製的服務帳戶新增為主體，然後選取「Vertex AI 使用者」角色

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_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 產品說明文件。