こんにちは。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年の日別の気象データ表
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で検索します。世界各国割り当てが決まっているので、気になる場合は国際地点番号で検索すればわかると思います。
1 2 3 4 5 6 |
SELECT * FROM `bigquery-public-data.ghcn_d.ghcnd_stations` WHERE wmoid between 47200 AND 47998 |
これで日本の観測地点一覧が取得できました。日本のどこが観測地点か分からない場合は、この出力内容から観測地点を探せばいいと思います。
今回は東京のデータが欲しいのでnameで東京を絞って番号を確認します。
※name=TOKYOだと検索できなかったっ為LIKE条件で検索してます。
1 2 3 4 5 6 7 |
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で取得できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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 |
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学習のお役に立てば幸いです。
なお、気象観測の方法やその予測情報の発信は、気象業務法により制限されていますのでご注意ください。