g
本記事はNI+C TeamGCP Advent Calendar 2023 4日目の記事となります。
若干タイトル詐欺ですがBigQueryくんに天気を聞いてみました。
目標はシンプルです｡
BigQueryくんだけを使って天候情報を導き出すことです｡
なにはともあれデータです。
今回利用するのは以下のBigQuery Public Dataset です｡
はい、すでにこの時点で気づくかと思いますがMLによる予測ではないです。
予測されたデータを使います｡
CREATE OR REPLACE TABLE blog.osm_prefectures AS
SELECT
*
FROM `bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
WHERE
("is_in:country", "Japan") IN (SELECT (KEY, value) FROM UNNEST(all_tags))
AND ("admin_level", "4") IN (SELECT (KEY, value) FROM UNNEST(all_tags))天気のデータはNOAAのデータを使用します。
予報日であるcreation_timeがパーティションカラムなので必ず指定しましょう。
指定しないと数百TBのクエリを実行してしまう可能性があります。-- 予報日付
DECLARE weather_date DATE DEFAULT "2023-12-01";
WITH osm_prefectures AS(SELECT * FROM `<PROJECT_ID>.blog.osm_prefectures` )
, join_noaa AS(
SELECT
osm.geometry
, (SELECT value FROM UNNEST(osm.all_tags) WHERE key = 'name') as prefecture
, noaa.*
FROM
osm_prefectures osm
,`bigquery-public-data.noaa_global_forecast_system.NOAA_GFS0P25` noaa
WHERE
noaa.creation_time = weather_date
AND ST_COVEREDBY(noaa.geography, osm.geometry)
), calc_precipitation AS(
SELECT
ANY_VALUE(geometry) as geometry
, prefecture
, DATE(time) as date
, hours
, AVG(temperature_2m_above_ground) avg_temperature
, AVG(total_precipitation_surface) / 6 as avg_precipitation -- 降水量は6時間毎の累計のため1時間あたりに直すために6で除算
, AVG(total_cloud_cover_entire_atmosphere) avg_cloud_cover
FROM
join_noaa
JOIN UNNEST(forecast) AS forecast
WHERE DATE(time) = weather_date AND hours = 12
GROUP BY 2,3,4
)
SELECT
*
, CASE
WHEN avg_precipitation > 1 THEN "雨"
WHEN avg_cloud_cover <= 10 THEN "快晴"
WHEN avg_cloud_cover > 10 AND avg_cloud_cover <= 80 THEN "晴れ"
ELSE "曇り"
END as weather
FROM calc_precipitation
ORDER BY 2, 3
上記クエリでは該当都道府県に含まれる予報データ(降水量と雲の割合)から天気を決定します｡
気をつける点としては降水量であるtotal_precipitation_surfaceは6時間毎の累計降水量となります｡
WINDOW関数を使って1時間あたりの降水量に直すと良いですが今回は(アドベントカレンダーの締め切り的に)6~12時の累計値をシンプルに6で割るだけにします。
BigQueryからGISデータを可視化する場合はBigQuery Geo Vizがあります｡
「データを探索」からアクセス出来るツールでおなじみかと思います。
BigQuery Geo Vizでそこそこのデータ量を処理しようとするとなかなか重くなりがちなのでノートブックを使うのも良いでしょう｡
Colab Enterpriseも登場したのでBigQueryのWeb UI上で表示することが可能となります｡
タイトル詐欺でしたがBigQueryとパブリックデータだけで天気がわかりました。
今回は47都道府県の天気としていましたが実際のビジネスでは店舗や配送経路など様々なデータに掛け合わせることが出来るでしょう｡
可視化についてはBigQuery Geo Vizはコンソールからシームレスに移動できるため、まずは可視化をというレベルに最適です｡
パフォーマンスや独自の可視化を行いたい場合はColab Enterpriseをはじめとしたノートブックが選択肢となるでしょう。
BigQueryStudioにより一貫したある操作が可能になりましたのでぜひ利用してみてはいかがでしょうか。
Google Cloud、Google Workspaceに関する お問い合わせはこちら