BigQuery MLをさわってみた

さらっとBigQuery MLをさわってみました

# 作業概要
1. 気象庁のHPから、過去の気象データをダウンロードする
2. データを加工してBigQueryへアップロードする
3. BigQuery MLで学習する

# 1. 気象庁のHPから、過去の気象データをダウンロードする
[過去の気象データ・ダウンロード](http://www.data.jma.go.jp/gmd/risk/obsdl/index.php#)から、以下の条件を指定してCSVファイルをダウンロードします。

- 地点
    - 東京
- 項目
    - 日平均気温
    - 日最高気温
    - 日最低気温
    - 日平均相対湿度
    - 日平均現地気圧
    - 天気概況（昼）
- 期間
    - 2015/1/1
    - 2018/8/10

# 2. データを加工してBigQueryへアップロードする
ダウンロードしたCSVファイルを以下のように加工します。

- 項目の並び
    1. 日付
    2. 日平均気温
    3. 日最高気温
    4. 日最低気温
    5. 日平均相対湿度
    6. 日平均現地気圧
    7. **翌日**の天気を1 または 2 に変換した値 (2は曇or雨or雪の文字列を含む場合、1はそれ以外)


```csv
date,temp_avg,temp_max,temp_min,humidity,pressure,weather
2015/1/1,3.7,8.2,0.7,41,1000.4,2
2015/1/2,2.7,7.9,-2.2,41,1007.4,1
2015/1/3,3.8,8.9,-1.1,42,1012,2
2015/1/4,4,9.3,-0.4,51,1010.4,1
2015/1/5,6.9,12.9,1.3,56,1014.6,2
2015/1/6,10.8,16,5.8,64,1000.6,2
・・・
2018/8/9,27.4,32.1,23.8,85,994.2,2
```

ファイルが加工できたら、BigQueryへアップロードします。

|データセット|テーブル|
|---|---|
|weather|tokyo_weather|

<img width="661" alt="upload.png" src="https://qiita-image-store.s3.amazonaws.com/0/192584/7f37dfa8-2178-df8d-de43-acafc8f6c3a4.png">

# 3. BigQuery MLで学習する

まずはロジスティック回帰のモデルを作成します。

```sql
CREATE MODEL weather.weather_model
OPTIONS(model_type='logistic_reg') AS
SELECT
  weather AS label,
  temp_avg,
  temp_max,
  humidity,
  pressure
FROM
  weather.tokyo_weather
WHERE
  date BETWEEN '2015-01-01' AND '2017-12-31'
```

出来たモデルを評価します。

```sql
SELECT
  *
FROM
  ML.EVALUATE(MODEL weather.weather_model, (
SELECT
  weather AS label,
  temp_avg,
  temp_max,
  humidity,
  pressure
FROM
  weather.tokyo_weather
WHERE
  date BETWEEN '2018-01-01' AND '2018-08-09'));
```

```json
[
  {
    "precision": "0.7731958762886598",
    "recall": "0.9316770186335404",
    "accuracy": "0.751131221719457",
    "f1_score": "0.8450704225352113",
    "log_loss": "0.5526056113512635",
    "roc_auc": "0.671409"
  }
]
```

モデルを使って予測してみます。

```sql
SELECT
  date,
  label,
  predicted_label
FROM
  ML.PREDICT(MODEL weather.weather_model, (
SELECT
  date,
  weather AS label,
  temp_avg,
  temp_max,
  humidity,
  pressure
FROM
  weather.tokyo_weather
WHERE
  date BETWEEN '2018-01-01' AND '2018-08-09'))
ORDER BY
  date;
```

<img width="334" alt="result.png" src="https://qiita-image-store.s3.amazonaws.com/0/192584/a1bee5d4-031b-6d87-093d-b922c2e90ffc.png">

実測値と予測値をシームレスにSelectしてみます。

```sql
SELECT *
FROM (
SELECT
  date,
  weather as tomorrow_weather
FROM
  weather.tokyo_weather
WHERE
  date BETWEEN '2015-01-01' AND '2017-12-31'
UNION ALL
SELECT
  date,
  predicted_label AS tomorrow_weather
FROM
  ML.PREDICT(MODEL weather.weather_model, (
SELECT
  date,
  temp_avg,
  temp_max,
  humidity,
  pressure
FROM
  weather.tokyo_weather
WHERE
  date BETWEEN '2018-01-01' AND '2018-08-09'))
)
WHERE
  date BETWEEN '2017-12-28' AND '2018-01-04'
ORDER BY
  date;
```

2017年は実測値、2018年は予測値になります。

<img width="343" alt="union.png" src="https://qiita-image-store.s3.amazonaws.com/0/192584/de4c81ab-d116-b41d-1a06-ed5be236600b.png">

# 感想

- SQLで機械学習とか、夢が広がります。。。
- 最後のSQLをViewにするところまでは出来たのですが、作成したViewをSelectするSQLを入力した時点で「An internal error occurred and the request could not be completed.」と怒られてしまいました。
- 現時点(2018/8/12)で線形回帰と２値のロジスティック回帰が利用出来ますが、今後利用できるアルゴリズムが増えるといいなぁ。