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

1. 簡介

在本程式碼研究室中，我們將使用 BigQuery SQL 查詢和 Vertex AI PaLM API，建構電影成功評分預測和建議應用程式。用於執行文字生成的模型為 text-bison，並以 BigQuery 中的遠端函式形式代管。

使用的服務包括：

BigQuery ML
Vertex AI PaLM API
Cloud Shell

建構項目

您將建立

包含模型的 BigQuery 資料集
BigQuery ML 模型，可根據電影的 GENRE 和 RUNTIME 屬性預測電影的成功分數
以遠端函式形式代管 Vertex AI PaLM API 的 BigQuery 模型
外部連線，用於建立 BigQuery 和 Vertex AI 之間的連線

2. 需求條件

Chrome 或 Firefox 瀏覽器
已啟用計費功能的 Google Cloud 專案

事前準備

在 Google Cloud 控制台的專案選擇器頁面中，選取或建立 Google Cloud 專案
確認 Cloud 專案已啟用計費功能。瞭解如何檢查專案是否已啟用計費功能
確認所有必要 API (BigQuery API、Vertex AI API、BigQuery Connection API) 均已啟用
您將使用 Cloud Shell，這是 Google Cloud 中執行的指令列環境，已預先載入 bq。如需 gcloud 指令和用法，請參閱說明文件

在 Cloud 控制台，按一下右上角的「啟用 Cloud Shell」：

如果未設定專案，請使用下列指令來設定：

gcloud config set project <YOUR_PROJECT_ID>

在瀏覽器中輸入下列網址，直接前往 BigQuery 控制台：https://console.cloud.google.com/bigquery

3. 準備資料

在本例中，我們將使用從 movielens 來源衍生的電影資料集。

建立資料集：

BigQuery 資料集是資料表的集合，資料集中的所有資料表都儲存在同一個資料位置。您也可以附加自訂存取權控管，限制資料集及其資料表的存取權。

在 Cloud Shell 中，使用 bq mk 指令建立名為「movie_insights」的資料集

bq mk --location=us-central1 movie_insights

將原始碼檔案複製到 Cloud Shell 電腦：

git clone https://github.com/AbiramiSukumaran/movie_score_genai_insights

前往 Cloud Shell 機器中建立的新專案目錄：

cd movie_score_genai_insights

使用 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

您可以查詢樣本，檢查資料集是否已建立 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';

查詢詳細資料：

CREATE MODEL 陳述式會使用 SELECT 陳述式中的訓練資料訓練模型。
OPTIONS 子句會指定模型類型和訓練選項。這裡的 LOGISTIC_REG 選項會指定邏輯迴歸模型類型。您不需要指定二元邏輯迴歸模型或多元類別邏輯迴歸模型；BigQuery ML 可根據標籤資料欄中不重複值的數量，判斷要使用何者來進行訓練。
data_split_method=‘NO_SPLIT' 會強制 BQML 根據查詢條件 (data_cat = ‘TRAIN') 訓練資料，此外，建議您在此選項中使用「AUTO_SPLIT」，讓架構 (或本例中的服務) 隨機分割訓練/測試資料。
input_label_cols 選項會指定 SELECT 陳述式中的哪個資料欄要當做標籤資料欄。這裡的標籤資料欄為「score」，因此模型將根據每個資料列中呈現的其他值，學習「score」的 10 個值中哪一個最有可能。
「auto_class_weights=TRUE」選項會平衡訓練資料中類別標籤的權重。預設情況下，訓練資料並未加權。如果訓練資料中的標籤不平衡，則模型學習到的權重可能不均，導致最熱門的標籤類別預測比例過高。
SELECT 陳述式會查詢我們載入 CSV 資料的資料表。WHERE 子句會篩選輸入資料表中的資料列，以便在這個步驟中只選取 TRAIN 資料集。
下列建構函式為選用，因此 BigQuery ML 可以明確將其註冊至 Vertex AI Model Registry。如要進一步瞭解這項功能，請參閱這篇網誌. model_registry='vertex_ai', vertex_ai_model_version_aliases=['logistic_reg', 'experimental']

建立完成後，BigQuery SQL 工作區的「SCHEMA」部分會顯示以下內容：

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

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

主要指標一覽：

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

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'
     )
  );

結果如下所示：

模型結果會顯示電影的預測分數，範圍為 1 到 10 分 (分類)。您可能想知道為何每部電影都有好幾列預測結果。這是因為模型已傳回可能的預測標籤，以及每個標籤的出現機率 (依遞減順序排列)。

分析預測結果和模型：

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

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

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

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

讓我們向 LLM (大型語言模型) 詢問影響電影評分高於 5 分的因素摘要，並使用 Vertex AI 的 text-bison (最新) 模型，透過 Generative AI 傳回電影資料集的洞察資訊，過程中只會使用 SQL 查詢。

我們建立的 movie_score 資料表也會是這個步驟的輸入內容。
系統會建立外部連線，在 BigQuery ML 和 Vertex 服務之間建立存取權。
BigQuery GENERATE_TEXT 建構函式會用於從 Vertex AI 遠端叫用 PaLM API。

7. 建立外部連線

如果尚未啟用 BQ Connection API，請啟用並記下連線設定詳細資料中的服務帳戶 ID：

在 BigQuery Explorer 窗格 (位於 BigQuery 控制台左側) 中，按一下「+新增」按鈕，然後在列出的熱門來源中，按一下「連線至外部資料來源」
選取「BigLake and remote functions」(BigLake 和遠端函式) 做為連線類型，提供「Region」(區域) 做為位置類型，並提供「us-central1 (Iowa)」(us-central1 (愛荷華州)) 做為值，以及「bq_llm_connection」做為連線 ID

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

授予權限

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

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

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 是指回覆中的字數

查詢回應如下所示：

如您所見，回應是巢狀結構，且未經格式化。

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));