BigQuery MLを使って線形回帰を試してみる

はじめに

とてつもなくイマサラですが、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の中から解説します。

1.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しているだけですね。

2.外側

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つの構成だと思っています。
まず外枠ですが、

1.外枠

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しているだけです。
順番に見ていきます。

2.予測されたデータ

  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を返しています。

3.実際のデータ

  SELECT STA, Date, MinTemp, MaxTemp FROM `using_bqml.weather_at_ww2`

実際のデータは単純にデータをSELECTしています。
このあとのJOINの条件で絞れるので、ここではWHERE句などでデータを絞っていません。