はじめに

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

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

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

前提

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

BigQuery上には、最初breast_cancerというテーブルがあるだけですが、学習してモデルを作成すると「モデル」というフォルダのような物ができて、その下にモデルファイルが置かれます。

1. 学習

CREATE MODEL文で作ります。
リファレンス：The CREATE MODEL statement | BigQuery ML | Google Cloud

モデル作成

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']と目的変数に指定しています。
チュートリアルによるとlabelという変数名にすると勝手に目的変数になるとのこと。INPUT_LABEL_COLSを使うか、labelにするか、どっちの方がわかりやすいかは微妙だけど、たくさんのソースを書くなら統一はした方がよいです。
あと、数値型以外の列はhot encodingしてくれたりするみたい。詳細は割愛しますが、やってくれるなら便利なケース多いはず。

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

2. 学習結果の確認

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

2.1. 学習パラメータ

今回はほぼデフォルトだけど、全部(というかもっと)変更可能です。詳細はCREATE MODEL文のリファレンスで。

2.2. 評価指標

評価指標も確認できます。詳細は割愛。
ロジスティック回帰の２値分類の場合は、後で閾値を変えられるので、インタラクティブにスクロールバーみたいのを動かして確認できる。（けどちょっと使いづらい）

3. 未知のデータに対する評価

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

リファレンス：The ML.EVALUATE function | BigQuery ML | Google Cloud

評価

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

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

予測

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

この結果、下記の結果を得ます。

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

おわりに

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

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

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

BigQueryで機械学習(BQML)