LoginSignup
1

More than 5 years have passed since last update.

BigQuery MLをさわってみた

Posted at

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

作業概要

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

1. 気象庁のHPから、過去の気象データをダウンロードする

過去の気象データ・ダウンロードから、以下の条件を指定してCSVファイルをダウンロードします。

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

2. データを加工してBigQueryへアップロードする

ダウンロードしたCSVファイルを以下のように加工します。

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

upload.png

3. BigQuery MLで学習する

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

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'

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

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'));
[
  {
    "precision": "0.7731958762886598",
    "recall": "0.9316770186335404",
    "accuracy": "0.751131221719457",
    "f1_score": "0.8450704225352113",
    "log_loss": "0.5526056113512635",
    "roc_auc": "0.671409"
  }
]

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

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;

result.png

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

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年は予測値になります。

union.png

感想

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1