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

1. 簡介

在本程式碼研究室中，我們將僅使用 SQL 建立 BigQuery ML 自訂模型，並在 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. 準備訓練資料

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

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. 您可以透過下列 3 種方式查詢：

與 BigQuery 互動的方式有三種，我們將試用其中兩種：a. 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，目的是簡化作業，並從多個資料庫取得更接近實際電影評分的結果。請注意，這僅是模型實作的範例，不一定適用於這個用途。實作這項功能的另一種方式是使用這個邏輯迴歸模型預測電影的結果是「良好」/「不良」，而不是預測分數。

選取訓練資料

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

建立邏輯迴歸模型

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

在 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' 會強制 BigQuery ML 根據查詢條件 (data_cat = ‘TRAIN') 訓練資料，此外，建議您在此選項中使用 ‘AUTO_SPLIT'，讓框架 (或本例中的服務) 隨機分割訓練/測試資料
  4. input_label_cols 選項會指定 SELECT 陳述式中的哪個資料欄要當做標籤資料欄。這裡的標籤資料欄為「score」，因此模型將根據每個資料列中呈現的其他值，學習「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 分數、對數損失和 ROC AUC，這些指標對於評估模型效能非常重要。您也可以呼叫 ML.EVALUATE，不必提供輸入資料。ML.EVALUATE 會擷取訓練期間得出的評估指標，這些指標是使用自動保留的評估資料集計算而得。

重要指標一覽：

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

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

建立模型後，您可以按照自訂 BQML 模型中的說明，執行 EVALUATE、PREDICT 和 EXPLAIN_PREDICT 步驟。如要進一步瞭解 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 Auto ML 模型
  4. 將 BQML 模型部署至 Vertex AI Model Registry

10. 清理

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

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

11. 恭喜

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