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

# はじめに
とてつもなくイマサラですが、GoogleのBigQueryを使って線形回帰を試してみたので、そのメモです。

# BQMLでできること
BQMLでは線形回帰とロジスティック回帰が実行できます。
BQMLではMLの中でも特に統計学的なものが実現できるということのようです。

# 記録
## やったこと
なんでもよかったのですが、とりあえずkaggle上で[第2次世界大戦の気象データ](https://www.kaggle.com/smid80/weatherww2)の気温を予測するためのデータセットがあったので、それを利用してみます。

はじめに断らせていただきますが、この問題を解くためのTips的なものではなく単にBigQueryMLを使ってみようというだけのものなので、データサイエンスと言われるようなことは一切やれていません。
今後はやりたいですが・・

## とりあえずデータの中身を見てみる

[Kaggleのページ](https://www.kaggle.com/smid80/weatherww2)を見るとかなりたくさんのデータがあることがわかります。
ここではとりあえず、Summary of Weather.csvの最低気温だけを利用して気温を予測してみることにします。

|#|列名|意味|何に利用するか|
|---|---|---|---|
|1|STA|気温を取った地点番号|#2と併せて識別のため|
|2|Date|気温を取った日時|#1と併せて識別のため|
|3|MaxTemp|最高気温|予測モデルの作成に利用|
|4|MinTemp|最低気温|予測モデルの作成に利用|

## データの前処理

ダウンロードしたデータをそのままBQML上にロードしてもよいのですが、私の環境ではBQのテーブル作成の機能であるスキーマの自動取得がうまく動作してくれなかったので、大量の列をテーブル作成時に定義しなくてはならず少し面倒です。
そのため、事前に必要な列のみ抽出したcsvを作成します。

```bash
$ cut -d ',' -f 1,2,5,6,7 Summary\ of\ Weather.csv > weather.csv
```

ちょっと余計にMeanTemp(平均気温)も残してみましたが、他にも必要な列があればその列番号を-fの後ろの数字の羅列に追加してあげてください。

## BQML上にデータセット/テーブルを作成する

GCPのコンソールを開き以下の様に選択してデータセットを作成します。
![01_BQML_console.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/d0d1da6f-888c-d9f3-95b8-0a8f9770a088.png)

表示された画面では以下のように入力します。
個々で入力した情報をもとにデータセットが作成されます。
![02_BQML_create_dataset.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/af34f140-ead8-2e4c-e4d9-f4c0351a97c6.png)

完了したら、作成したデータセットの中にテーブルを作成します。以下のように選択します。
![03_BQML_console_create_table.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/be271f16-c45a-2044-2aae-f718452f05f4.png)

表示された画面で以下のように入力してください。
![04_BQML_createtable01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/c25151aa-c479-18bb-0915-f7370762ab5b.png)
![05_BQML_createtable02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/b4451a40-d62c-f09d-9549-0000156f4b44.png)

作成が完了すると以下のようにスキーマを確認することができます。
プレビュータブを選択することで、データの中身も確認できますので、念の為確認してください。
![06_BQML_checkscheme.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/2fecb5b6-127c-c95a-63f2-84335dea4e34.png)

## 実際にモデルを作成する。
モデルは上部のクエリエディタにSQLを入力することで実現します。
![01_BQML_queryeditor.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/23a44a74-fec4-10f3-8604-a70e93d9d8a4.png)

それでは早速作成します。
ちなみにデータですが、今回は適当に1940/1/1から1943/1/1までのデータを学習用に使ってみています。

```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の解説は後述しますが、これでとりあえずモデルが作成できます。
入力したら、実行をクリックしてください。完了すると以下のようになります。

![02_BQML_createdmodel.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/fa603263-647d-a3e2-dc78-96222d686286.png)

## 予測してみる
それではこのモデルを使って、1943/1/2から1945/1/1までのデータを予測してみます。

```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;
```

SQLの解説は後述しますが、これにより実際のデータにある値と予測した値が表示されます。
![03_BQML_predict.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99704/c45749ce-6459-f03b-7b5c-6c111576b06a.png)


パッと見た感じ精度はいまいちですが、最低気温だとこんなものっていう感じですかね。
ともあれ、BQMLを使ってデータの予測が行えました！！

## 結論
BQMLを使うと簡単に線形回帰のモデルが作成できる。
(ここでは取り上げていないが、ロジスティック回帰も簡単に行える)

# おまけの解説
## モデルを作成するSQL
```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の中から解説します。

```sql: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しているだけですね。

```sql:2.外側
CREATE OR REPLACE MODEL `using_bqml.model01`
OPTIONS(model_type='linear_reg', labels = ['MaxTemp']) AS
SELECT
  * EXCEPT(Date, STA)
FROM (
  1で解説した部分
)
```

1で取得したデータからモデルを作成しています。

```sql
CREATE OR REPLACE MODEL `using_bqml.model01`
OPTIONS(model_type='linear_reg', labels = ['MaxTemp']) AS
```
で AS以下に続くデータをもとにモデルを作成します。
OPTIONSの中でモデルの種別(model_type)や最終的に出力としたい値(MaxTemp)を指定します。

## モデルを作成するSQL
```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つの構成だと思っています。
まず外枠ですが、

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

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

```sql:3.実際のデータ
  SELECT STA, Date, MinTemp, MaxTemp FROM `using_bqml.weather_at_ww2`
```

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