BigQueryに聞く天気予報

2023.12.03 2023.12.04 XIMIX 中根

g bq_weather_000 日本情報通信の中根です。

本記事はNI+C TeamGCP Advent Calendar 2023 4日目の記事となります。
若干タイトル詐欺ですがBigQueryくんに天気を聞いてみました。

目標

目標はシンプルです｡
BigQueryくんだけを使って天候情報を導き出すことです｡

準備

なにはともあれデータです。
今回利用するのは以下のBigQuery Public Dataset です｡

はい、すでにこの時点で気づくかと思いますがMLによる予測ではないです。
予測されたデータを使います｡

Step.1 予測する地点のデータを用意する

まずは天気を確認する範囲として47都道府県とします｡

政府統計ポータルサイトのe-Statから地図データは取得出来るのでこちらを使うことが多いのですが今回はBigQuery縛り(？)なのでOpenStreetMapより抽出します｡

OpenStreetMapは自由に利用、編集ができるフリーの地理データベースです｡
コミュニティ運営されておりユーザーベースのWikiもあります。

BigQueryのパブリックデータとして昔からありますが今回は47都道府県を以下のSQLで抽出します｡

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))

OpenStreatMapのデータはそこそこ大きいので事前に必要な範囲のみを抽出しましょう。

上記のクエリで日本の都道府県のポリゴンを抽出した結果14MBぐらいになります｡

Step.2 天気のデータを抽出し予測地点に掛け合わせる

天気のデータは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で割るだけにします。

Step.3 データの可視化

BigQueryからGISデータを可視化する場合はBigQuery Geo Vizがあります｡
「データを探索」からアクセス出来るツールでおなじみかと思います。

bq_weather_001

BigQuery Geo Vizでそこそこのデータ量を処理しようとするとなかなか重くなりがちなのでノートブックを使うのも良いでしょう｡
Colab Enterpriseも登場したのでBigQueryのWeb UI上で表示することが可能となります｡

まとめ

タイトル詐欺でしたがBigQueryとパブリックデータだけで天気がわかりました。
今回は47都道府県の天気としていましたが実際のビジネスでは店舗や配送経路など様々なデータに掛け合わせることが出来るでしょう｡

可視化についてはBigQuery Geo Vizはコンソールからシームレスに移動できるため、まずは可視化をというレベルに最適です｡
パフォーマンスや独自の可視化を行いたい場合はColab Enterpriseをはじめとしたノートブックが選択肢となるでしょう。
BigQueryStudioにより一貫したある操作が可能になりましたのでぜひ利用してみてはいかがでしょうか。