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

將「location」設為區域 (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__rows=1 - 略過 CSV 檔案的第一行，因為這是標題列。Movies.movies - 第一個位置引數 - 定義應載入資料的資料表。./movies.csv (第二個位置引數)：定義要載入的檔案。除了本機檔案之外，bq 載入指令還能使用 gs://my_bucket/path/to/file URI 載入 Cloud Storage 中的檔案。結構定義：可在 JSON 結構定義檔或半形逗號分隔的清單中定義 (我使用以半形逗號分隔的清單)。太棒了！我們的 CSV 資料現已載入到 table movies.movies 中。

  1. 您可透過以下 3 種方式的其中一種查詢：

我們可以透過三種方式與 BigQuery 互動，而我們會嘗試以下兩種做法：a.BigQuery 網路 UI b.bq 指令 c.API

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

我已使用 BigQuery Web 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 以保持簡單，並從數個資料庫取得接近實際電影評分的結果。請注意，此模型應視為實作模型的樣本，「不一定」適合此用途的建議模型。另一個實作此效果的方法之一，是利用此邏輯迴歸模型預測電影成「良好 / BAD」的結果，而不是預測分數。

選取訓練資料

我們已經使用「data_cat」欄位將電影資料 (CSV 檔案) 分割成表格的 3 個類別具有 3 個值之一：TRAIN、TEST 和 PREDICT。針對測試和訓練目的分割資料集是模型的重要層面。如要進一步瞭解資料集分割，請參閱說明文件。

建立邏輯迴歸模型

使用 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&#39;強制 BQML 根據查詢條件 (data_cat = ‘TRAIN') 使用資料進行訓練。另外請注意，最好使用「AUTO_SPLIT」這個選項可讓架構 (或服務) 隨機進行訓練/測試分割的分區
  4. input_label_cols 選項會指定要使用 SELECT 陳述式中的哪個資料欄做為標籤欄。這裡的標籤欄是「分數」，模型會根據各列出現的其他值，學習在 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-score、log_loss、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

模型結果會顯示電影的 SCORE 預測值，範圍為 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';

包含 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 進行部署。