使用 SQL 搭配 BQML 進行電影分級預測

1. 簡介

在本程式碼研究室中，我們將使用 BigQuery ML 自訂模型，僅透過 SQL 建立電影評分預測模型，並在 Vertex AI 中部署模型。您將學會：

  1. 如何使用 Cloud Shell 建立 BigQuery 資料集，並從檔案載入資料
  2. 如何使用 BigQuery ML 進行監督式學習，建立多類別迴歸預測模型，用於預測電影評分，而不需要撰寫太多程式碼，只需使用 SQL 查詢即可

建構項目

您將建立

  • 包含資料表和模型元件的 BigQuery 資料集
  • 使用 SQL 查詢預測電影評分的 BigQuery ML 模型
  • 在 Vertex AI Model Registry 中部署模型

規定

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

2. 建立專案

  1. 在 Google Cloud 控制台的專案選取器頁面中，選取或建立 Google Cloud 專案。
  2. 確認 Cloud 專案已啟用計費功能。瞭解如何檢查專案是否已啟用計費功能。
  3. 前往 BigQuery 啟用 API。您也可以在瀏覽器中輸入下列網址，直接開啟 BigQuery 網頁版 UI：https://console.cloud.google.com/bigquery

3. 啟用 Cloud Shell

  1. 您將使用 Cloud Shell，這是在 Google Cloud 中運作的指令列環境，並預先載入 bq：在 Cloud 控制台中，按一下右上角的「啟用 Cloud Shell」：6757b2fb50ddcc2d.png
  2. 連線至 Cloud Shell 後，您應會發現自己通過驗證，且專案已設為您的專案 ID。在 Cloud Shell 中執行下列指令，確認您已通過驗證：
gcloud auth list
  1. 在 Cloud Shell 中執行下列指令，確認 gcloud 指令知道您的專案
gcloud config list project
  1. 如果未設定專案，請使用下列指令進行設定：
gcloud config set project <PROJECT_ID>

如要瞭解 gcloud 指令和用法，請參閱說明文件。

4. 準備訓練資料

這是所有資料相關專案、產品和應用程式的重要步驟，除了技術之外，還需要大量的專業知識，才能準備最佳資料集，並為機器學習專案做好準備。為了完成本程式碼研究室的內容，我們將使用已準備好的資料檔案。

5. 建立及載入資料集

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

  1. 在 Cloud Shell 中使用 bq mk 指令，建立名為「movies」的資料集。
bq mk --location=<<LOCATION>> movies

將「位置」設為區域 (asia-south1)。

  1. 請務必準備好資料檔案 (.csv)。在 Cloud Shell 中執行下列指令，複製存放區並前往專案：
git clone <<repository link>>

cd movie-score
  1. 使用 bq load 指令將 CSV 檔案載入 BigQuery 資料表 (請注意，您也可以直接從 BigQuery UI 上傳資料)：
bq load --source_format=CSV --skip_leading_rows=1 movies.movies_score \
./movies_bq_src.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

選項說明：–source_format=CSV - 會在剖析資料檔案時使用 CSV 資料格式。–skip_leading_rows=1 - 略過 CSV 檔案中的第一列，因為這是標題列。Movies.movies - 第一個位置引數 - 定義資料應載入至哪個資料表。./movies.csv 是第二個位置引數，用於定義要載入的檔案。除了本機檔案外，bq load 指令也可以使用 gs://my_bucket/path/to/file URI 從 Cloud Storage 載入檔案。結構定義，可在 JSON 結構定義檔案中定義，或以逗號分隔的清單定義 (我使用的是逗號分隔清單)。太好了！我們的 CSV 資料現在已載入至資料表 movies.movies。

  1. 請使用下列任一方式進行查詢：

我們可以透過三種方式與 BigQuery 互動，我們將嘗試其中兩種：BigQuery 網頁版 UI b. bq 指令 c。API

SELECT name, rating, genre, runtime FROM movies.movies_score limit 3;

我已使用 BigQuery 網頁版 SQL 工作區執行查詢。SQL 工作區如下所示：

109a0b2c7155e9b3.png

使用 bq 指令：

bq query --use_legacy_sql=false \
SELECT name, rating, genre, runtime FROM movies.movies_score limit 3;

6. 預測電影評分 (1 到 10 分)

BigQuery ML 透過邏輯迴歸類型的模型支援監督式學習。您可以使用二元邏輯迴歸類型的模型，來預測某值是否屬於兩個類別之一，也可以使用多元類別迴歸類型的模型，來預測某值是否屬於多個類別之一。前述兩項作業的目的都在於將資料分入兩個以上的類別，處理的就是所謂的分類問題。

關於模型的選擇，在此提供一點簡短說明：我們是根據最初執行的幾個模型的評估結果，選擇了這個實驗模型，最後決定採用 LOGISTIC REG，以簡化程序並讓結果更接近多個資料庫中的實際電影評分。請注意，這只是實作模型的範例，不一定是這個用途的建議模型。另一種實作方式是使用這個邏輯迴歸模型，預測電影的結果是「好」或「差」，而不是預測分數。

選取訓練資料

我們已使用包含 3 個值 (TRAIN、TEST 和 PREDICT) 之一的「data_cat」欄，將電影資料 (CSV 格式) 拆分為 3 個類別。將資料集分割成用於測試和訓練的資料集，是模型的重要環節。如要進一步瞭解資料集分割作業，請參閱說明文件。

建立邏輯迴歸模型

我們可以使用 CREATE MODEL 陳述式搭配「LOGISTIC_REG」選項，建立並訓練邏輯迴歸模型。

在 BigQuery 網頁版 UI SQL 工作區中執行下列查詢：

CREATE OR REPLACE MODEL
  `movies.movies_score_model`
OPTIONS
  ( model_type='LOGISTIC_REG',
    auto_class_weights=TRUE,
   data_split_method='NO_SPLIT',
    input_label_cols=[‘score']
  ) AS
SELECT
  * EXCEPT(id, data_cat)
FROM
  ‘movies.movies_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，因此模型將根據每個資料列中呈現的其他值，學習分數的 10 個值中哪一個最有可能
  5. 「auto_class_weights=TRUE」選項會平衡訓練資料中類別標籤的權重。根據預設，訓練資料並未加權。如果訓練資料中的標籤不平衡，則模型學習到的權重可能不均，導致最熱門的標籤類別預測比例過高。
  6. SELECT 陳述式會查詢我們使用 CSV 資料載入的資料表。WHERE 子句會篩選輸入表格中的資料列，以便在這個步驟中只選取 TRAIN 資料集

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

標籤

93efd0c1a7883690.png

功能

8c539338df1a9652.png

評估邏輯迴歸模型

建立模型之後，請使用 ML.EVALUATE 函式評估模型的效能。ML.EVALUATE 函式會根據實際資料來評估預測值，用於評估模型的查詢如下所示：

SELECT
  *
FROM
  ML.EVALUATE (MODEL movies.movies_score_model,
    (
    SELECT
      *
    FROM
      movies.movies_score
    WHERE
      data_cat= ‘TEST'
    )
  )

ML.EVALUATE 函式會使用先前步驟中訓練的模型，以及 SELECT 子查詢傳回的評估資料。該函式會傳回模型相關統計資料的單一資料列。

b54b0ebd6514a498.png

由於您執行了邏輯迴歸，因此結果包含上圖所示的指標：精確度、回傳率、準確度、f1 分數、log_loss、roc_auc，這些指標對於評估模型效能至關重要。您也可以不提供輸入資料來呼叫 ML.EVALUATE。ML.EVALUATE 會擷取訓練期間產生的評估指標，這些指標會使用自動保留的評估資料集。

一覽重點指標：

精確度：正確辨識的比例為何？精確度 = 真陽性 / (真陽性 + 偽陽性) 喚回率 - 實際正類正確識別的比例為何？Recall = 真陽性 / (真陽性 + 偽陰性) 準確率 - 用於評估分類模型的指標，即模型實際預測正確的比例。準確率 = 正確預測次數 / 總預測次數

使用 ML.PREDICT 預測電影評分

以下查詢會預測資料集 PREDICT 部分中每部電影的分數。

SELECT
  *
FROM
  ML.PREDICT (MODEL movies.movies_score_model,
    (
    SELECT
      *
    FROM
      movies.movies_score
    WHERE
      data_cat= ‘PREDICT'
     )
  )

以下是結果的片段：

1efb91967acc1f0c.png

模型結果會顯示電影的預測分數，分數範圍為 1 到 10 (分類)。你可能會好奇，為何每部電影都有多個預測資料列。這是因為模型已依遞減順序傳回可能的預測標籤，以及每個標籤的發生機率。

分析預測結果和模型

此外，您還可以執行兩個實用的分析步驟，作為預測結果的一部分：

  1. 如要瞭解模型為何產生這些預測結果，您可以使用 ML.EXPLAIN_PREDICT 函式
  2. 如要瞭解哪些特徵對於一般收入等級的判斷最為重要，您可以使用 ML.GLOBAL_EXPLAIN 函式

如需這些步驟的詳細說明，請參閱說明文件。

7. 使用 BigQuery AutoML 預測電影評分

如果您想讓 BigQuery 服務為您建立模型，並在模型結果產生前放心休息，AutoML 就是您的最佳選擇。AUTOML 功能可直接在 BQML 中使用 (model_type=AUTOML_CLASSIFIER 或 AUTOML_REGRESSOR)，如果資料已在 BigQuery 中，則更容易呼叫。

CREATE OR REPLACE MODEL movies.auto_movies
       OPTIONS(model_type='AUTOML_CLASSIFIER',
               input_label_cols=['score'],
               budget_hours=1.0)
AS 
SELECT
  * EXCEPT(id, data_cat)
FROM
  movies.movies_score;

建立模型後，您可以執行 EVALUATE、PREDICT 和 EXPLAIN_PREDICT 步驟，這與我們在自訂 BQML 模型中討論的內容相同。如要進一步瞭解 BigQuery AutoML，請參閱說明文件。

8. 一鍵部署 BQML 模型至 Vertex AI Model Registry

您現在可以在 Vertex AI Model Registry 中查看所有機器學習模型，讓貴機構更輕鬆地管理及部署模型。這包括使用 BigQuery ML 建構的模型、AutoML 和自訂訓練模型。方法如下：

CREATE OR REPLACE MODEL
  `movies.movies_score_model`
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
  * EXCEPT(id, data_cat)
FROM
  ‘movies.movies_score'
WHERE
  data_cat = 'TRAIN';

在 CREATE MODEL 查詢中加入 model_registry="vertex_ai" 選項，讓 BigQuery ML 可以選擇要明確註冊至 Vertex AI Model Registry 的模型。如要進一步瞭解這項功能，請參閱這篇網誌文章。

9. 摘要

在不到 30 分鐘內，我們完成了以下事項：

  1. 將 BigQuery 設為數據分析專案的資料庫
  2. 只使用 BigQuery 資料 SQL 建立自訂預測模型，不需編寫其他程式碼
  3. 僅使用 SQL 查詢建立 BQ 自動機器學習模型
  4. 將 BQML 模型部署至 Vertex AI Model Registry

10. 清理

如要避免系統向您的 Google Cloud 帳戶收取這篇文章所用資源的費用，請按照下列步驟操作：

  1. 在 Google Cloud 控制台中前往「管理資源」頁面
  2. 在專案清單中選取要刪除的專案，然後按一下「刪除」
  3. 在對話方塊中輸入專案 ID，然後按一下「Shut down」(關閉) 即可刪除專案

11. 恭喜

恭喜！您已成功使用 BQML 自訂和 AutoML 建立電影評分預測模型，並在 Vertex AI Model Registry 中部署模型，而且這一切都只使用 SQL 即可完成。