Congestion forecast

[toshi0607]

Explanatory variables

• datetime
• weekday
• holiday
• event
• weather
• temperature

male and female

Predictor variable

sauna state

Ref

https://medium.com/google-cloud-jp/bigquery-ml%E3%81%AE%E7%B7%9A%E5%BD%A2%E5%9B%9E%E5%B8%B0%E3%81%A7%E9%9B%BB%E5%8A%9B%E9%9C%80%E8%A6%81%E4%BA%88%E6%B8%AC%E3%82%84%E3%81%A3%E3%81%A6%E3%81%BF%E3%81%9F-fd211a8a4ded

[toshi0607]

Test

Prediction with an existing feature (only datetime)

Data import from Sheets to BQ

https://cloud.google.com/bigquery/docs/loading-data
https://cloud.google.com/bigquery/external-data-drive

$ gcloud auth login --enable-gdrive-access

Create dataset
Follow the instruction to create table. Just input the sheets URL and decide a table name. Import scope is also selectable.

create model `sauna_prediction.congestion_model`
options(model_type='linear_reg') as
select
  string_field_2 as datetime
  ,cast (string_field_0 as INT64) as label
from `terraform-toshi0607.sauna_prediction.simple`
;

=> 'label' column should be numerical. Because fields are all string.
=> Bad int64 value: male because the header is not excluded

[toshi0607]

Import another sheet

• RFC 3399 is unable to parse by BQ.
• male and female are imported as INTEGER

create model `sauna_prediction.congestion_model`
options(model_type='linear_reg') as
select
  fts
  ,male as label
from `terraform-toshi0607.sauna_prediction.sauna_2020_1014_f`
;

このステートメントで新しいモデル terraform-toshi0607:sauna_prediction.congestion_model が作成されました。

[toshi0607]

Prediction

select * from ml.predict(model `sauna_prediction.congestion_model`, (select fts, male from `terraform-toshi0607.sauna_prediction.sauna_2020_1014_f`));

This is useless because