Google スプレッドシート(コネクテッドシート)でBigQueryMLの機械学習結果を参照する

# Google スプレッドシートでBigQueryMLの機械学習結果を参照する
以前、Dataprep→BigQuery→BigQueryML→DataPortalと連携し機械学習を行い、参照する記事を掲載しました。
・[DataprepでCloud Storage→BigQueryへデータをクレンジングして格納する](https://qiita.com/kccs_api4/items/ec47a2890400624f140c)
・[BigQueryMLで太陽光発電量を予測して、DataPortalに表示する](https://qiita.com/kccs_api4/items/e3a1ca7d7e8364c2d451)

最近、GoogleのスプレッドシートにBigQueryの検索結果をアウトプットできるようになりました。
・[コネクテッド シートの使用](https://cloud.google.com/bigquery/docs/connected-sheets?hl=ja)

これにより、スプレッドシートで手軽に機械学習結果を参照でき、非常に便利な機能ですので、ご紹介いたします。

### 太陽光の発電電力量を予測する
詳細は以前の記事にありますが、
気象予報データと発電実績データを元に機械学習を行い、未来の太陽光発電電力量を予測します。
データモデルの作成までは以前の記事をご参照ください。

入力情報は以下を使います。
・[過去気象予報データ(KCCS API)](https://www.energy-cloud.jp/specification/#past-weather-forecast "KCCS API 過去気象予報データダウンロード機能")
・発電実績データ(弊社社員の家より)

### モデルとデータ
以前の記事で作った、[モデルとデータ](https://qiita.com/kccs_api4/items/e3a1ca7d7e8364c2d451)にアクセスします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/33b7d7c1-60d1-8d21-449c-aa98cf14a9ab.png)

### スプレッドシートでBigQueryMLにアクセスします。

スプレッドシートを開き「BigQueryに接続」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/3249308d-f255-5936-7916-a75245f59aa6.png)

BigQueryに配置されているプロジェクトを選びます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/1b02dcaa-908f-d517-b92d-6eda32622a48.png)

「カスタムクエリを作成」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/2c09ccc0-6cad-8412-7f88-955b37493738.png)

前述したデータにアクセスするためのSQLを記載します。

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

となりますので、SQLは以下になります。
```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
```

設定が完了すると、
作ったモデルで予測結果が表示されます。
そのままだと、参照用のシートとなり、スプレッドシートとして使えないため、「抽出」ボタンを押し、データをシートに出力します。
※BigQueryのデータはそのまま扱えません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/2d50abe0-cf35-edf4-6ff1-aa88f9f11734.png)


### スプレッドシートとして使ってみる
抽出後に普通のスプレッドシートとして利用可能になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/616d214c-dc0f-5ce4-5d35-95e693cb823e.png)

### グラフにしてみる
グラフにしたい場合は、元の参照用シートで「グラフを作成」で「新しいシート」で「作成」します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/d863b56c-29f9-d6ea-24b0-ae37625d34e8.png)

グラフのパラメータを指定すれば、DataPortalのようなグラフの表示も可能になります。
以下のグラフでは、
***predicted_sum_kwh=予測した発電電力量***
***sum_kwh=実際の発電電力量***
になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1525729/d3855b1f-1e65-76f3-18b8-9f2d9acd9619.png)

スプレッドシートで機械学習結果も参照できるようになると、ほぼ誰でも扱う事ができるようになります。
非常に便利かと思いますので、ご興味のある方は使ってみてください。