はじめに
とてつもなくイマサラですが、GoogleのBigQueryを使って線形回帰を試してみたので、そのメモです。
BQMLでできること
BQMLでは線形回帰とロジスティック回帰が実行できます。
BQMLではMLの中でも特に統計学的なものが実現できるということのようです。
記録
やったこと
なんでもよかったのですが、とりあえずkaggle上で第2次世界大戦の気象データの気温を予測するためのデータセットがあったので、それを利用してみます。
はじめに断らせていただきますが、この問題を解くためのTips的なものではなく単にBigQueryMLを使ってみようというだけのものなので、データサイエンスと言われるようなことは一切やれていません。
今後はやりたいですが・・
とりあえずデータの中身を見てみる
Kaggleのページを見るとかなりたくさんのデータがあることがわかります。
ここではとりあえず、Summary of Weather.csvの最低気温だけを利用して気温を予測してみることにします。
| # | 列名 | 意味 | 何に利用するか |
|---|---|---|---|
| 1 | STA | 気温を取った地点番号 | #2と併せて識別のため |
| 2 | Date | 気温を取った日時 | #1と併せて識別のため |
| 3 | MaxTemp | 最高気温 | 予測モデルの作成に利用 |
| 4 | MinTemp | 最低気温 | 予測モデルの作成に利用 |
データの前処理
ダウンロードしたデータをそのままBQML上にロードしてもよいのですが、私の環境ではBQのテーブル作成の機能であるスキーマの自動取得がうまく動作してくれなかったので、大量の列をテーブル作成時に定義しなくてはならず少し面倒です。
そのため、事前に必要な列のみ抽出したcsvを作成します。
$ cut -d ',' -f 1,2,5,6,7 Summary\ of\ Weather.csv > weather.csv
ちょっと余計にMeanTemp(平均気温)も残してみましたが、他にも必要な列があればその列番号を-fの後ろの数字の羅列に追加してあげてください。
BQML上にデータセット/テーブルを作成する
GCPのコンソールを開き以下の様に選択してデータセットを作成します。
表示された画面では以下のように入力します。
個々で入力した情報をもとにデータセットが作成されます。
完了したら、作成したデータセットの中にテーブルを作成します。以下のように選択します。
表示された画面で以下のように入力してください。
作成が完了すると以下のようにスキーマを確認することができます。
プレビュータブを選択することで、データの中身も確認できますので、念の為確認してください。
実際にモデルを作成する。
モデルは上部のクエリエディタにSQLを入力することで実現します。
それでは早速作成します。
ちなみにデータですが、今回は適当に1940/1/1から1943/1/1までのデータを学習用に使ってみています。
CREATE OR REPLACE MODEL `using_bqml.model01`
OPTIONS(model_type='linear_reg', labels = ['MaxTemp']) AS
SELECT
* EXCEPT(Date, STA)
FROM (
SELECT
Date,
STA,
MaxTemp,
MinTemp
FROM `using_bqml.weather_at_ww2`
WHERE Date BETWEEN CAST('1940-01-01' AS date) AND CAST('1943-01-01' as date)
)
SQLの解説は後述しますが、これでとりあえずモデルが作成できます。
入力したら、実行をクリックしてください。完了すると以下のようになります。
予測してみる
それではこのモデルを使って、1943/1/2から1945/1/1までのデータを予測してみます。
SELECT
actual.STA,
actual.Date,
actual.MinTemp,
actual.MaxTemp,
predicted.predicted_MaxTemp
FROM (
SELECT STA, Date,MinTemp, predicted_MaxTemp
from ml.predict(model `using_bqml.model01`, (
SELECT STA, Date, MinTemp
FROM `using_bqml.weather_at_ww2` WHERE Date BETWEEN CAST('1943-01-02' AS date) AND CAST('1945-01-01' as date)
))
) AS predicted
JOIN (
SELECT STA, Date, MinTemp, MaxTemp FROM `using_bqml.weather_at_ww2`
) AS actual
ON predicted.STA = actual.STA AND predicted.Date = actual.Date
ORDER BY predicted.Date;
SQLの解説は後述しますが、これにより実際のデータにある値と予測した値が表示されます。
パッと見た感じ精度はいまいちですが、最低気温だとこんなものっていう感じですかね。
ともあれ、BQMLを使ってデータの予測が行えました！！
結論
BQMLを使うと簡単に線形回帰のモデルが作成できる。
(ここでは取り上げていないが、ロジスティック回帰も簡単に行える)
おまけの解説
モデルを作成するSQL
CREATE OR REPLACE MODEL `using_bqml.model01`
OPTIONS(model_type='linear_reg', labels = ['MaxTemp']) AS
SELECT
* EXCEPT(Date, STA)
FROM (
SELECT
Date,
STA,
MaxTemp,
MinTemp
FROM `using_bqml.weather_at_ww2`
WHERE Date BETWEEN CAST('1940-01-01' AS date) AND CAST('1943-01-01' as date)
)
SQLは内側から読んでいくとわかりやすい(と思う)ので、まずはFROMの中から解説します。
SELECT
Date,
STA,
MaxTemp,
MinTemp
FROM `using_bqml.weather_at_ww2`
WHERE Date BETWEEN CAST('1940-01-01' AS date) AND CAST('1943-01-01' as date)
これは非常に単純で1940/1/1 - 1943/1/1までのデータをSELECTしているだけですね。
CREATE OR REPLACE MODEL `using_bqml.model01`
OPTIONS(model_type='linear_reg', labels = ['MaxTemp']) AS
SELECT
* EXCEPT(Date, STA)
FROM (
1で解説した部分
)
1で取得したデータからモデルを作成しています。
CREATE OR REPLACE MODEL `using_bqml.model01`
OPTIONS(model_type='linear_reg', labels = ['MaxTemp']) AS
で AS以下に続くデータをもとにモデルを作成します。
OPTIONSの中でモデルの種別(model_type)や最終的に出力としたい値(MaxTemp)を指定します。
モデルを作成するSQL
SELECT
actual.STA,
actual.Date,
actual.MinTemp,
actual.MaxTemp,
predicted.predicted_MaxTemp
FROM (
SELECT STA, Date,MinTemp, predicted_MaxTemp
from ml.predict(model `using_bqml.model01`, (
SELECT STA, Date, MinTemp
FROM `using_bqml.weather_at_ww2` WHERE Date BETWEEN CAST('1943-01-02' AS date) AND CAST('1945-01-01' as date)
))
) AS predicted
JOIN (
SELECT STA, Date, MinTemp, MaxTemp FROM `using_bqml.weather_at_ww2`
) AS actual
ON predicted.STA = actual.STA AND predicted.Date = actual.Date
ORDER BY predicted.Date;
大きく分けて、JOINの中、真ん中あたりのFROMの中、外枠の3つの構成だと思っています。
まず外枠ですが、
SELECT
actual.STA,
actual.Date,
actual.MinTemp,
actual.MaxTemp,
predicted.predicted_MaxTemp
FROM (
2.予測されたデータ
) AS predicted
JOIN (
3.実際のデータ
) AS actual
ON predicted.STA = actual.STA AND predicted.Date = actual.Date
ORDER BY predicted.Date;
というような構成です。
2.予測されたデータと3.実際のデータをSTAとDateに基づいてJOINしているだけです。
順番に見ていきます。
SELECT STA, Date,MinTemp, predicted_MaxTemp
from ml.predict(model `using_bqml.model01`, (
SELECT STA, Date, MinTemp
FROM `using_bqml.weather_at_ww2` WHERE Date BETWEEN CAST('1943-01-02' AS date)
この中にさらにFROMがあり、ここで予測を行っています。
ml.predictはモデルを指定し、必要なデータを入力することで結果を返してくれます。
ここの結果として、STA, Date, MinTemp, predicted_MaxTempを返しています。
SELECT STA, Date, MinTemp, MaxTemp FROM `using_bqml.weather_at_ww2`
実際のデータは単純にデータをSELECTしています。
このあとのJOINの条件で絞れるので、ここではWHERE句などでデータを絞っていません。