BigQueryで機械学習(BQML)

# はじめに
BigQuery MLを使うと、BigQueryのテーブルさえあれば機械学習の学習も予測ができます。
この記事では、下記について書きます。データの前処理と予測した結果をテーブルに入れるとかは細かく書きません。またバージョンアップのスピードが速い機能なので、この記事やほかの記事も読む際には、書かれた日にちにご注意ください。この記事は、2022年5月27日時点です。

[1. 学習](#1-学習)
[2. 学習結果の確認](#2-学習結果の確認)
[3. 未知のデータに対する評価](#3-未知のデータに対する評価)
[4. 未知のデータに対する予測](#4-未知のデータに対する予測)

基本的に、↓こちらのGoogleのクイックスタート（チュートリアル）のトレースです。

https://cloud.google.com/bigquery-ml/docs/create-machine-learning-model

## 前提
データは[KaggleのBreast Cancer](https://www.kaggle.com/datasets/uciml/breast-cancer-wisconsin-data)を使います。※ 列名にスペースが入ってたので「_」に置換してあります。
ロジスティック回帰で、malignant（M:悪性）かbenign（B:良性）を予測します。B:M=63%:37% の不均衡データです。が、そういうことは一切気にせず、BQMLの書き方に注力して説明します。
適当に上から80%くらいにあった `id=911202` より前を学習データ、以上を未知のデータとして使いました。なのでB:Mの割合もちょっと違うかもしれません。

BigQuery上には、最初`breast_cancer`というテーブルがあるだけですが、学習してモデルを作成すると「モデル」というフォルダのような物ができて、その下にモデルファイルが置かれます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/722610/b5d00e88-b9ee-012b-bc28-2dda4a171373.png)



# 1. 学習

CREATE MODEL文で作ります。
リファレンス：[The CREATE MODEL statement  |  BigQuery ML  |  Google Cloud](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create?hl=ja)

```sql:モデル作成
CREATE OR REPLACE MODEL ds_test.model_breast_cancer
Options(
  INPUT_LABEL_COLS=['is_malignant'],
  MODEL_TYPE='LOGISTIC_REG'
)
AS 
SELECT
  (case when diagnosis='M' then 1 else 0 end) as is_malignant,
  radius_mean,
  texture_mean,
  perimeter_mean,
  area_mean,
  smoothness_mean,
  compactness_mean,
  concavity_mean,
  concave_points_mean,
  symmetry_mean,
  fractal_dimension_mean,
  radius_se,
  texture_se,
  perimeter_se,
  area_se,
  smoothness_se,
  compactness_se,
  concavity_se,
  concave_points_se,
  symmetry_se,
  fractal_dimension_se,
  radius_worst,
  texture_worst,
  perimeter_worst,
  area_worst,
  smoothness_worst,
  compactness_worst,
  concavity_worst,
  concave_points_worst,
  symmetry_worst,
  fractal_dimension_worst
FROM `bqmltest-351505.ds_test.breast_cancer`
where id<911202
```
`CREATE TABLE AS SELECT ～`のような書き方の、CREATE "MODEL"文です。
SELECTしている最初のdiagnosisが目的変数ですが、文字列で'M'か'B'なので、case文で0 or 1に変換して、それを`INPUT_LABEL_COLS=['is_malignant']`と目的変数に指定しています。
[チュートリアル](https://cloud.google.com/bigquery-ml/docs/create-machine-learning-model)によると`label`という変数名にすると勝手に目的変数になるとのこと。INPUT_LABEL_COLSを使うか、labelにするか、どっちの方がわかりやすいかは微妙だけど、たくさんのソースを書くなら統一はした方がよいです。
あと、数値型以外の列はhot encodingしてくれたりするみたい。詳細は割愛しますが、やってくれるなら便利なケース多いはず。

この結果、`ds_test.model_breast_cancer`にモデルファイルが作成されます。



# 2. 学習結果の確認

ブラウザ上でモデルを選択すると見られます。クエリでも取得できるので、MLOpsとかの関係で結果を保存したいときとかは、そちらを使います。

## 2.1. 学習パラメータ
今回はほぼデフォルトだけど、全部(というかもっと)変更可能です。詳細は[CREATE MODEL文のリファレンス](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create?hl=ja)で。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/722610/de0c93b9-441f-3346-b9a9-25c7bb94ae9e.png)


## 2.2. 評価指標
評価指標も確認できます。詳細は割愛。
ロジスティック回帰の２値分類の場合は、後で閾値を変えられるので、インタラクティブにスクロールバーみたいのを動かして確認できる。（けどちょっと使いづらい）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/722610/6941455b-148d-be14-2c5f-eb2502fed768.png)

# 3. 未知のデータに対する評価
`ML.EVALUATE()`を使います。形としては、`SELECT * FROM ML.EVALUATE()`で、`EVALUATE()`の引数でモデルファイルと評価で使用する説明変数＋目的変数を示すSELECTをします。

リファレンス：[The ML.EVALUATE function  |  BigQuery ML  |  Google Cloud](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-evaluate)

```sql: 評価
select *
from ML.EVALUATE(MODEL `ds_test.model_breast_cancer`,(
SELECT
  (case when diagnosis='M' then 1 else 0 end) as is_malignant,
  radius_mean,
  texture_mean,
  （・・・略・・・）,
  symmetry_worst,
  fractal_dimension_worst
FROM `bqmltest-351505.ds_test.breast_cancer`
where id>=911202
))
```
この処理により、１行だけの結果を得ます。

| precision | recall | accuracy | f1_score | log_loss | roc_auc |
|---|---|---|---|---|---|
| 0.93684210526315792 | 0.98888888888888893 | 0.97318007662835249 | 0.96216216216216222 | 0.075070317791874522 | 0.9988421578421578 |

この例だと精度が高すぎてなんとも言えないけど、まぁ見れます。

# 4. 未知のデータに対する予測

評価とほぼ同じで、`ML.PREDICT()`という関数を使っている点と、目的変数を使わない点だけ違います。（あえてコメントアウトして残してあります。）目的変数は、`ML.PREDICT()`が作ってくれるからです。
リファレンス：[The ML.PREDICT function  |  BigQuery ML  |  Google Cloud](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-predict)

今回は使っていませんが、`ML.PREDICT()`に、`STRUCT`オプションを指定すると、２値分類のロジスティック回帰の閾値を設定できます。詳細はリファレンスを参考のこと。（EVALUATEも同様）

```sql: 予測
select *
from ML.PREDICT(MODEL `ds_test.model_breast_cancer`,(
SELECT
  # (case when diagnosis='M' then 1 else 0 end) as is_malignant,
  radius_mean,
  texture_mean,
  （・・・略・・・）,
  symmetry_worst,
  fractal_dimension_worst
FROM `bqmltest-351505.ds_test.breast_cancer`
where id>=911202
))
```
この結果、下記の結果を得ます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/722610/81c398c4-fc8a-c334-d3e4-5adb13654986.png)

結果は、`predicted_is_malignant`に0 or 1と、`predictated_is_malignant_probs`にJSON型でラベルとその確率が入ってます。詳細を使いたい場合はこの値を使う。

# おわりに
BigQueryだけで予測ができます。しかも別のサービス(GCEとか)を使わなくてよくて、とてもお手軽です。

■いいところ
まず入力で使うデータは全部、おなじみのSELECT文なので、前処理として加工したければできます。
次に予測結果を得るのもSELECT文なので、別テーブルへINSERTさせたり、CREATE TABLE AS SELECTとかでも使えます。GROUP BYをつけたりすることもできる。

■気を付けるところ
料金には注意です。
機械学習は一般的に、全件x全列読むような検索が多く、また繰り返しもするので、問い合わせ量がエグいです。業務で使うためには、少しずつ試して試算して、学習頻度を検討する必要があります。