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

1. 簡介

在本程式碼研究室中，我們將使用 BigQuery SQL 查詢和 Vertex AI PaLM API 建構電影成功評分預測和處方藥應用程式。用於產生文字的模型是「text-bison」text-bison，並由 BigQuery 以遠端函式的形式託管。

目前使用的服務如下：

1. BigQuery ML
2. Vertex AI PaLM API
3. Cloud Shell

建構項目

您會自行建立

• 包含模型的 BigQuery 資料集
• BigQuery ML 模型會根據電影的 GENRE 和 RUNTIME 屬性預測電影的成功分數
• 將 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」：

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

gcloud config set project <YOUR_PROJECT_ID>

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

3. 準備資料

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

1. 建立資料集：

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

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

bq mk --location=us-central1 movie_insights

2. 將來源檔案複製到 Cloud Shell 機器：

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

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

cd movie_score_genai_insights

4. 請使用 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

5. 您可以查詢範例，檢查 image_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 工作區查詢編輯器區段中執行下列查詢：

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 陳述式中的哪個資料欄做為標籤欄。這裡的標籤欄是分數，因此模型會根據各列現有的其他值，學習在 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」區段中：

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

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

重要指標一覽：

精確度 - 有多少比例的陽性識別結果實際上正確？精確度 = 真陽性 / (真陽性 + 偽陽性) 喚回度 - 正確識別出實際正向的比例是多少？喚回度 = 真陽性 / (真陽性 + 偽陰性) 準確率 - 評估分類模型的指標，是指模型實際獲得正確準確率的比例 = 正確預測數量 / 預測總數

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

結果如下所示：

模型結果會顯示電影的 train_score (分類分)。想知道為什麼每部電影都有數個預測列。這是因為模型以遞減順序傳回可能的預測標籤，以及每個標籤出現的機率。

分析預測結果和模型：

您可以運用兩個實用的預測結果來解讀結果：

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

請參閱說明文件，進一步瞭解這些步驟。

6. 將資料轉移至生成式 AI

現在，我們來要求 LLM (大型語言模型) 將影響電影分數的因素摘要傳送至 5 以上，並透過 Vertex AI 僅使用 SQL 查詢來使用 Vertex AI 的文字 Bison (最新) 模型，針對電影資料集提供深入分析資訊

1. 我們建立的 movies_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

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

授予權限

在這個步驟中，我們會授予服務帳戶存取 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));

**說明：

Flatten_json_output** 代表布林值，如果設定 true，則會從 JSON 回應中擷取可理解的平面文字。

查詢回應如下所示：

11. 清除所用資源

如要避免系統向您的 Google Cloud 帳戶收取本文中所用資源的費用，請前往「Vertex AI 端點」頁面，刪除您在機器學習步驟中建立的 Vertex AI 端點。

12. 恭喜

恭喜！您已成功建立 BQML 模型，並透過 Vertex AI API 對電影資料集執行 LLM 分析，而且只使用 SQL 查詢。如要進一步瞭解可用的模型，請參閱 Vertex AI LLM 產品說明文件。