野暮用でGoogle BigQueryを勉強し始めました。そしてBigQueryは機械学習をサポートしているというので早速少し使ってみました。「SQLと機械学習」と聞くとどこか奇妙な組み合わせのような響きですが、機械学習は大抵データフレームを使ってモデルを作り、そのモデルを使ってデータフレームを変更する（大抵は列を足す）という処理になので、そう考えればSQLで使うのは自然かもしれません。
ちょうどこちらも野暮用でバスケットボール選手の成績を使ったクラスタリングをしていたので、そのCSVファイルをBigQueryにアップロードしデータセットを作成、そのデータセットを使ってk-means法によるクラスタリングを試してみました。トレーニングはこんな感じで書けます。ちなみに簡略化の為にパラメータは2つのみ使用しています。
CREATE OR REPLACE MODEL `mydataset1.bleague_player_clustering` OPTIONS(model_type='kmeans', num_clusters=10) AS SELECT (PPG - PPG_AVG) / PPG_STD AS PPG_STDED, (RPG - RPG_AVG) / RPG_STD AS RPG_STDED FROM ( SELECT PPG, AVG(PPG) OVER() AS PPG_AVG, STDDEV(PPG) OVER() AS PPG_STD, RPG, AVG(RPG) OVER() RPG_AVG, STDDEV(RPG) OVER() RPG_STD FROM `mydataset1.bleague_players` )
下部のSELECT文で抽出されたデータがモデルのトレーニングに使われるという構図です。CREATE OR REPLACE MODELのOPTIONSでは、モデルのハイパーパラメータ等が指定し、ここでは分類するクラスター数を10と指定しました。mydataset1.bleague_player_clusteringというのがモデルの名前です。
Big Query MLの文法的はそれなりにシンプルだと思います。ですがSQLには大抵データの前処理に適した関数などが少なく、それをする為に複雑な副問い合わせなどが必要になりそうです。例えばk-means法だと各パラメータに標準化を施す必要があったりしますが、それだけの為に上述のような副問い合わせが必要になってしまいます。
作成したモデルを使い、実際にクラスタリングを行うためには以下のように記述します。
SELECT PLAYER, TEAM, CENTROID_ID FROM ML.PREDICT ( MODEL `myproject1-401904.mydataset1.bleague_player_clustering`, ( SELECT *, (PPG - PPG_AVG) / PPG_STD AS PPG_STDED, (RPG - RPG_AVG) / RPG_STD AS RPG_STDED FROM ( SELECT *, AVG(PPG) OVER() AS PPG_AVG, STDDEV(PPG) OVER() AS PPG_STD, AVG(RPG) OVER() RPG_AVG, STDDEV(RPG) OVER() RPG_STD FROM `mydataset1.bleague_players` ) ) )
下部が再びモデルに与えるデータで（今回の場合はトレーニングのデータと同じ）、それに伴いML.PREDICTが新たなカラムを追加し（例えばCENTROID_ID）、最上部でそれをSELECTしています。こちらも文法だけ言えばとてもシンプルです。
Googleのインフラを用いて動くこともあり、BigQuery MLにはそれなりの速度的なメリットはあるのではないかと推測します。ただし上述のように前処理をデータに施すときにも面倒が起きそうですし、トレーニングデータとテストデータを分けるといった、機械学習の前に行うあれこれがいちいち面倒になりそうです。トレーニングデータとテストデータを分ける場合、乱数の入ったひとつのカラムを用意し、その値でどの行を取得するのかを決定する感じでしょうか。
タイトルにもつけたのですが、Google BigQueryが便利に使えるシーンが今のところ分かりません。前処理の終わったデータをアップロードしてから使用してもよいかもしれませんが、速度的メリットを享受したい程の大量データだとそれも大変ですし、かといってバックエンドで何処かから流してきたデータを使うのであればやはり前処理は必須でしょうし、その辺の丁度いいシチュエーションが何なのか、頭の片隅に入れておいて考えたいと思います。
