BigQueryで一般公開データセット(気象データ)を使用してみよう

こんにちは。GMO NIKKO エンジニアのN.I.です。

BigQueryを試していて試験で大量のデータがあるといいなぁと思うことがあります。
そんな時に役立つものとしてBigQueryの一般公開データセットという公開データがあり、このデータの使用方法を調べました。
地理情報、気象データなど商用サービスで使用出来そうなものから、エンジニアには役立ちそうなGitHubの公開されているソースコードなども一般公開データとして提供されているようです。
どんなデータが公開されているか調べる場合は、GCP Marketplaceのデータセットページが役立ちます。英語ですが、公開されているデータが確認できます。
今回はその中から気象データを取得する方法を紹介します。

気象データの概要説明

気象データはNOAA(アメリカ海洋大気庁)のGlobal Historical Climatology Network（GHCN）という、気象情報統合データ。
BigQueryでは、GHCN-D（毎日）とGHCN-M（毎月）の2つのGHCNデータセットを使用できる。
GHCN-Dailyは、世界中の観測所からの毎日の気象情報の統合データベースであり、180の国と地域の10万を超える観測所からの毎日の気象記録で構成される。
1763年以降の毎年のいくつかのデータが含まれる。(日本のデータは1931年～)
観測地点は年によって増減がある。(観測地点によって何年からデータが存在するかは実際にDBを確認しないと分からない)
このデータセットで使用可能なデータ変数の詳細については、NOAAのGHCN-D readmeを参照。
公開データセットはGoogle BigQueryでホストされており、BigQueryの1TB/月の無料枠処理に含まれている。つまり、各ユーザーは毎月1TBの無料のBigQuery処理を受け取り、このパブリックデータセットに対してクエリを実行するために使用できる。

この気象データを利用して2019年の東京の日別の最高気温、最低気温、降水量を取得してみましょう。
日別データは年別で分割されており、ghcnd_XXXX(西暦4桁)という表名で保存されています。2019年の日別のデータを取得する場合ghcnd_2019という名前の表に接続します。

Bigquery 2019年の日別の気象データ表

bigquery-public-data.ghcn_d.ghcnd_2019

1	bigquery-public-data.ghcn_d.ghcnd_2019

このテーブルには以下のようなデータが含まれます。

id	date	element	value	mflag	qflag	sflag	time
JA000047597	2019/1/1	TAVG	15	H		S
JA000047430	2019/1/1	SNWD	211			S
JA000047405	2019/1/1	PRCP	0	B		S
JA000047991	2019/1/1	TAVG	222	H		S
JA000047616	2019/1/1	TMAX	76			S
JA000047741	2019/1/1	SNWD	10			S
JA000047607	2019/1/1	PRCP	0			S

列の説明

id	観測地点の番号
date	日付
element	気象観測データの種類例 PRCP:降水量(1/10ミリ) SNOW:降雪(mm) SNWD:雪の深さ(mm) TMAX:最高温度(1/10度C) TMIN:最低気温(1/10度C)
value	気象観測データ
mflag	気象観測データの測定方法のフラグ例空白:普通の測定 B:2つの12時間合計から形成される降水量合計 D:4つの6時間の合計から形成される降水量の合計
qflag	気象観測データの品質フラグ例空白:品質保証チェックに失敗しなかった I:内部整合性チェックに失敗
sflag	気象観測データのソースフラグ例 U:Remote Automatic Weather Stations (RAWS)のデータ、S:Global Summary of the Day (NCDC DSI-9618)のデータ

※詳しくはNOAAのGHCN-D readmeを参照

この表には2019年の世界中の観測データが含まれている為、東京の観測地点の番号を確認し東京のデータを取得します。
観測地点番号(id)は、ghcnd_stations表で確認できます。
ghcnd_stations表の列説明

id	観測地点の番号
latitude	緯度
longitude	経度
elevation	標高
state	アメリカの郵便番号データアメリカ用データ)
name	地名
gsn_flag	GCOS Surface Network（GSN）フラグ。GSNの観測地点の場合は「GSN」
hcn_crn_flag	U.S. Historical Climatology Network stationHCNの観測地点の場合は「HCN」、U.S. Climate Reference Network or U.S. Regionalの観測地点の場合は「CRN」 (アメリカ用フラグ)
wmoid	国際地点番号

日本の観測地点を確認するには、国際地点番号(wmoid)で検索すると便利です。観測地点の日本の割り当てが決まっているので、この番号で検索すれば日本の観測地点が分かります。
日本には47200番～47998番が割り当てられています。この番号をSQLで検索します。世界各国割り当てが決まっているので、気になる場合は国際地点番号で検索すればわかると思います。

SELECT
  * 
FROM
  `bigquery-public-data.ghcn_d.ghcnd_stations` 
WHERE
  wmoid between 47200 AND 47998

SELECT

FROM

`bigquery-public-data.ghcn_d.ghcnd_stations`

WHERE

wmoid between 47200 AND 47998

これで日本の観測地点一覧が取得できました。日本のどこが観測地点か分からない場合は、この出力内容から観測地点を探せばいいと思います。
今回は東京のデータが欲しいのでnameで東京を絞って番号を確認します。
※name=TOKYOだと検索できなかったっ為LIKE条件で検索してます。

SELECT
  * 
FROM
  `bigquery-public-data.ghcn_d.ghcnd_stations` 
WHERE
  wmoid between 47200 AND 47998 
  AND name LIKE '%TOKYO%'

SELECT

FROM

`bigquery-public-data.ghcn_d.ghcnd_stations`

WHERE

wmoid between 47200 AND 47998

AND name LIKE '%TOKYO%'

id	latitude	longitude	elevation	state	name	gsn_flag	hcn_crn_flag	wmoid
JA000047662	35.683	139.767	36		TOKYO			47662

これで東京の番号が分かったので、2019年の東京の日別の最高気温、最低気温、降水量を取得してみましょう。
最高気温、最低気温、降水量は1/10にする必要があります。下記SQLで取得できます。

SELECT
  id
  , date
  , MAX(tmin) AS tmin
  , MAX(tmax) AS tmax
  , MAX(prcp) AS prcp
FROM
  ( 
    SELECT
      tokyoweather.id
      , tokyoweather.date
      , IF ( tokyoweather.element = 'TMIN', tokyoweather.value / 10, NULL ) AS tmin
      , IF ( tokyoweather.element = 'TMAX', tokyoweather.value / 10, NULL ) AS tmax 
      , IF ( tokyoweather.element = 'PRCP', tokyoweather.value / 10, NULL ) AS prcp
    FROM
     `bigquery-public-data.ghcn_d.ghcnd_2019` AS tokyoweather 
    WHERE
      id = 'JA000047662'
  ) 
GROUP BY
  id,date 
ORDER BY
  date

SELECT

, date

, MAX(tmin) AS tmin

, MAX(tmax) AS tmax

, MAX(prcp) AS prcp

FROM

(

SELECT

tokyoweather.id

, tokyoweather.date

, IF ( tokyoweather.element = 'TMIN', tokyoweather.value / 10, NULL ) AS tmin

, IF ( tokyoweather.element = 'TMAX', tokyoweather.value / 10, NULL ) AS tmax

, IF ( tokyoweather.element = 'PRCP', tokyoweather.value / 10, NULL ) AS prcp

FROM

`bigquery-public-data.ghcn_d.ghcnd_2019` AS tokyoweather

WHERE

id = 'JA000047662'

)

GROUP BY

id,date

ORDER BY

date

2019年東京最高気温、最低気温、降水量(1/1～1/20)

id	date	tmin	tmax	prcp
JA000047662	2019/1/1	0.9	10.7	0
JA000047662	2019/1/2		10.9	0
JA000047662	2019/1/3	-0.3	10.9	0
JA000047662	2019/1/4	0.2	9.8	0
JA000047662	2019/1/5	0.9	14	0
JA000047662	2019/1/6		8	0
JA000047662	2019/1/7	0.4	10.6	0
JA000047662	2019/1/8	0.7	9.8	0
JA000047662	2019/1/9		8	0
JA000047662	2019/1/10	-1.2		0
JA000047662	2019/1/11	-0.3	12.2	0
JA000047662	2019/1/12	2.7	6.2	0
JA000047662	2019/1/13		10.1	0
JA000047662	2019/1/14		9.8	0
JA000047662	2019/1/15	0.4	10.3	0
JA000047662	2019/1/16	1.8	11.3	0.5
JA000047662	2019/1/17	2	11.6	0
JA000047662	2019/1/18		10	0
JA000047662	2019/1/19	1.9	11.7	0
JA000047662	2019/1/20	1.8	12	0

データを取得してみるとデータに抜けがあり、データがNULLのところもありますが、気象データの取得が出来ました。

今回のご紹介はここまでです。
大量のデータが必要な場合は、取得する観測地点を増やしたり、テーブル名にワイルドカードが使えるのでbigquery-public-data.ghcn_d.ghcnd_2019の部分をbigquery-public-data.ghcn_d.ghcnd_201*にすれば、2010～2019年のデータ全部が取得出来たりします。
※どんな条件でも短時間で取得できますが、データ量で課金されるため大量データのSQLの実行は注意して行いましょう。
BigQuery学習のお役に立てば幸いです。
なお、気象観測の方法やその予測情報の発信は、気象業務法により制限されていますのでご注意ください。

ixs

おすすめ

GoogleのVertex AI PaLM Embedding APIを使って類似記事タイトルをレコメンドする

Duet AIのBigQueryサポート機能を試す

プレビュー機能: BigQuery のパーティショニングとクラスタリングの Recommender を覗いてみる