BigQueryの公開天気データを使えそうな形に整形する

BigQueryの天気の公開データセットを使えそうな形に整形して
スケジュールクエリで更新かけるときのメモ。

#参考にした記事
https://cloudplatform-jp.googleblog.com/2016/10/ghcn-bigquery.html
https://qiita.com/satoru_mag/items/a72fd35b37f3742293e8

#Bigqueryにテーブル作成

2015年から2020年のデータで作成するサンプル

create table <tablename> as (

with tenki 
  as 
  ( 
    select 
      ghcnd.date, 
      stations.name, 
      stations.latitude,
      stations.longitude,
      max(case ghcnd.element when 'PRCP' then ghcnd_1day_ago.value / 10 else null end) as prcp, --降雨量(ミリメートル)
      max(case ghcnd.element when 'SNOW' then ghcnd_1day_ago.value / 10 else null end) as snow, --降雪量(ミリメートル) 
      max(case ghcnd.element when 'SNWD' then ghcnd_1day_ago.value / 10 else null end) as snwd, --積雪量(ミリメートル) 
      max(case ghcnd.element when 'TMAX' then ghcnd.value / 10 else null end) as tmax, --最高気温 
      max(case ghcnd.element when 'TMIN' then ghcnd.value / 10 else null end) as tmin, --最低気温 
      max(case ghcnd.element when 'TAVG' then ghcnd.value / 10 else null end) as tavg, --平均気温 
      max(case when SUBSTR(ghcnd.element, 0, 2) = 'WT' then 'True' else NULL end) as haswx --影響力の強い気象現象の有無 
    from 
      ( 
        select 
          id,
          date,
          element,
          value,
        from 
          `bigquery-public-data.ghcn_d.ghcnd_*` 
        where 
          _TABLE_SUFFIX between '2015' and '2020'
      ) as ghcnd
    left outer join
      ( 
        select 
          id,
          date_add(date, interval -1 day) as date,
          element,
          value,
        from 
          `bigquery-public-data.ghcn_d.ghcnd_*` 
        where 
          _TABLE_SUFFIX between '2015' and '2020'
      ) as ghcnd_1day_ago
     on
      ghcnd.id = ghcnd_1day_ago.id and
      ghcnd.date = ghcnd_1day_ago.date and
      ghcnd.element = ghcnd_1day_ago.element
    inner join 
      `bigquery-public-data.ghcn_d.ghcnd_stations` as stations 
     on 
      ghcnd.id = stations.id and 
      substr(stations.id, 1, 2) = 'JA' --日本だけに絞る
    group by 
      1,2,3,4
   ) 
   
select 
  a.date, 
  a.name, 
  a.latitude,
  a.longitude,
  coalesce(a.prcp, 0) as prcp, 
  coalesce(a.snow, 0) as snow, 
  coalesce(a.snwd, 0) as snwd, 
  coalesce(a.tmax, 0) as tmax, 
  coalesce(a.tmin, 0) as tmin, 
  coalesce(a.tavg, 0) as tavg, 
  coalesce(a.haswx, 'False') as haswx 
from 
  tenki a 
)

#BigQueryのスケジュールクエリに登録

運用するSQLをコンソールに貼り付ける

当年のデータの中から更新があったものだけ投入するようにする

日次バッチ用SQLサンプル

insert into 
  <tablename>

with tenki 
  as 
  ( 
    select 
      ghcnd.date, 
      stations.name, 
      stations.latitude,
      stations.longitude,
      max(case ghcnd.element when 'PRCP' then ghcnd_1day_ago.value / 10 else null end) as prcp, --降雨量 
      max(case ghcnd.element when 'SNOW' then ghcnd_1day_ago.value / 10 else null end) as snow, --降雪量 
      max(case ghcnd.element when 'SNWD' then ghcnd_1day_ago.value / 10 else null end) as snwd, --積雪量 
      max(case ghcnd.element when 'TMAX' then ghcnd.value / 10 else null end) as tmax, --最高気温 
      max(case ghcnd.element when 'TMIN' then ghcnd.value / 10 else null end) as tmin, --最低気温 
      max(case ghcnd.element when 'TAVG' then ghcnd.value / 10 else null end) as tavg, --平均気温 
      max(case when SUBSTR(ghcnd.element, 0, 2) = 'WT' then 'True' else NULL end) as haswx --影響力の強い気象現象の有無 
    from 
      ( 
        select 
          id,
          date,
          element,
          value,
        from 
          `bigquery-public-data.ghcn_d.ghcnd_*` 
        where 
          _TABLE_SUFFIX = FORMAT_DATE("%Y", CURRENT_DATE) 
      ) as ghcnd
    left outer join
      ( 
        select 
          id,
          date_add(date, interval -1 day) as date,
          element,
          value,
        from 
          `bigquery-public-data.ghcn_d.ghcnd_*` 
        where 
          _TABLE_SUFFIX = FORMAT_DATE("%Y", CURRENT_DATE)
      ) as ghcnd_1day_ago
     on
      ghcnd.id = ghcnd_1day_ago.id and
      ghcnd.date = ghcnd_1day_ago.date and
      ghcnd.element = ghcnd_1day_ago.element
    inner join 
      `bigquery-public-data.ghcn_d.ghcnd_stations` as stations 
     on 
      ghcnd.id = stations.id and 
      substr(stations.id, 1, 2) = 'JA' --日本だけに絞る
    group by 
      1,2,3,4
  ) 
  
select 
  a.date, 
  a.name, 
  a.latitude,
  a.longitude,
  coalesce(a.prcp, 0) as prcp, 
  coalesce(a.snow, 0) as snow, 
  coalesce(a.snwd, 0) as snwd, 
  coalesce(a.tmax, 0) as tmax, 
  coalesce(a.tmin, 0) as tmin, 
  coalesce(a.tavg, 0) as tavg, 
  coalesce(a.haswx, 'False') as haswx 
from 
  tenki a   
left outer join 
  <tablename> b 
 on 
  a.date = b.date and 
  a.name = b.name 
where 
  b.date is null

##スケジュール登録する

必要事項を入力してスケジュールボタンを押す

##スケジュールされたクエリを確認する
左側のメニューのスケジュールされたクエリを押す

こんな感じで追加される

#気象庁のデータとの比較
　ピンクが気象庁
　緑がBQの公開データ

##月別

大体合ってそう

##日別

日別で見るとちょいちょい差が出る。。
これはGHCN（Global Historical Climatology Network）データと気象庁に入電した月気候気象通報（CLIMAT報）のデータの違いによるものなのであろうが、
ほぼほぼ近いので大きな傾向を見ることに支障はないかと思われる

#注意点
・大体1週間に一回くらいのデータ更新頻度と思われる

・時差らしきもの
　　降水系は一日ずれてるが気温系はずれてない？っぽい。（のでSQLで降水系は１日ずらす）

・データ欠落
　　最高気温と最低気温はちょいちょい抜けてる。。orz