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

投稿者: | 2020年5月12日

こんにちは。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年の日別の気象データ表

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

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で検索します。世界各国割り当てが決まっているので、気になる場合は国際地点番号で検索すればわかると思います。

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

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

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

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学習のお役に立てば幸いです。
なお、気象観測の方法やその予測情報の発信は、気象業務法により制限されていますのでご注意ください。