BigQueryMLで太陽光発電量を予測して、DataPortalに表示する

前回記事の続きとなります。

以下の記事にて、BigQueryにデータを保存しました。
[・DataprepでCloud Storage→BigQueryへデータをクレンジングして格納する]
(https://qiita.com/kccs_api4/items/ec47a2890400624f140c)

保存したデータからモデルを作成し、データポータルへ表示します。
各サービスの役割をまとめます。

|サービス|役割|備考|
|:---|:---|:---|
|CloudStorage|Inputデータの格納先 (過去気象予報、発電実績)|前回記事参照|
|Dataprep|BigQueryへデータをクレンジングして格納する。|前回記事参照|
|BigQuery|モデルを作って発電予測を行う。|本記事参照|
|DataPortal(DataStudio)|予測値と実測値を可視化して、比較する。|本記事参照|

機械学習にBigQuery MLを利用する意図としては、BigQueryのSQLを利用して大きなデータを扱えるメリットを活かすことです。

BigQuery MLについては、GCP公式ページをご参照下さい。
[・BigQuery ML ]
(https://cloud.google.com/bigquery-ml/docs/introduction "BigQuery ML とは ")
>BigQuery ML では、既存の SQL ツールやスキルで機械学習を使用できるため、データ アナリストも機械学習を簡単に利用できます。アナリストは、BigQuery ML を使用して BigQuery に ML モデルを構築し、評価できます。スプレッドシートなどのアプリケーションに少量のデータをエクスポートする必要はありません。また、データ サイエンス チームの限られたリソースを待つ必要もありません。

BigQuery MLによるモデルの作り方は、GCP公式ページをご参照下さい。
[・The CREATE MODEL statement ]
(https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create)

前回の記事にて、以下のテーブルができております。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/afff126b-e3e9-c78f-145b-094ceec83356.png)


**説明変数：日射量**
**目的変数：発電電力量**
**学習データ：5月~7月末のデータ**
**テストデータ：8月のデータ**

として実装します。

```SQL
CREATE OR REPLACE MODEL `qiita.pv_forecast_model`
OPTIONS(
	MODEL_TYPE='linear_reg',      --線形回帰のため、linear_regを指定
	INPUT_LABEL_COLS=['sum_kwh']  --目的変数
) AS
SELECT
 weather_forecast.Insolation AS insolation,
 pv_data.sum_kwh AS sum_kwh
FROM (
    SELECT
     timestamp,
     ROUND(SUM(kwh),2) AS sum_kwh
    FROM 
     `qiita.PV_FORECAST`
    GROUP BY timestamp
) pv_data
INNER JOIN `qiita.WEATHER_FORECAST_RESULT` weather_forecast
ON pv_data.timestamp = weather_forecast.timestamp
WHERE pv_data.timestamp BETWEEN CAST('2021-05-01' as date) and CAST('2021-07-31' as date) --期間を指定
```

このSQLを発行すると、モデルが作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/9996a7cf-258a-8c36-0aae-49fd6b160006.png)

作成されたモデルについての評価を見ることができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/6d5212e8-978a-8b45-07bd-d9d7153dcce5.png)


このモデルを利用して、テストデータの発電電力量を予測します。
以下のSQLで説明変数(insolation 日射量)をモデルに与えて、SQLを発行します。
**BigQuery MLはSQLが書ける人なら、SQLで予測モデルを作る事ができることが面白く、優れている点です。**
**また、BigQeuryが得意な大量データの早い処理の恩恵も受けることができます。**

```SQL
with weather as (
select
  weather_forecast.Insolation as insolation, --説明変数:日射量
  weather_forecast.timestamp -- 時間 
from
  `qiita.WEATHER_FORECAST_RESULT` weather_forecast
where
  weather_forecast.timestamp between cast('2021-08-01' as date) and cast('2021-08-31' as date) -- 範囲を指定
),
pv_forecast as (
  Select timestamp, sum(kwh) as sum_kwh from -- 学習結果の評価用に、実際の発電電力量を取得
    `qiita.PV_FORECAST`
  group by timestamp
)
select predicted_sum_kwh, sum_kwh, insolation, a.timestamp from
ml.predict(model `qiita.pv_forecast_model`, table weather) a
INNER JOIN pv_forecast b
ON a.timestamp = b.timestamp
order by a.timestamp
```

出力された予測値と、実測値を比較してみます。
「データを探索」→「データポータルで調べる」を選択して、データポータルに表示します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/132281e2-f4ba-62c5-cccf-0ef0d19ebfe7.png)

データポータルに移動します。
予測したSQLはデータソースに保存されています。
初期は表形式で表示されますが、整えて保存してあげれば、必要な時に参照したい形で表示できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/f59c4c75-a665-d9a9-1015-c0e45448c485.png)

予測と実測を表示したいので、2つ線のある折れ線グラフを選択します。
グラフの値に
・predicted_sum_kwh(予測値)
・sum_kwh(実績値)
を指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/f915de53-5b12-0a05-e74f-946792f08487.png)

予測値が実測値をとらえられていて、差分が見るようになっています。
改良の余地はあるかもあしれませんが、大きな誤差は無く、可視化することができました。

KCCS APIの過去気象予報、発電実績を利用して、Google Cloudを利用してデータクレンジングから、機械学習、見える化まで実現する事ができました。