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