NOAA Global Historical Climatology Network

Este conjunto de dados contém medições meteorológicas dos últimos 120 anos. Cada linha é uma medição referente a um momento específico no tempo e a uma estação. Mais precisamente, e de acordo com a origem destes dados:

O GHCN-Daily é um conjunto de dados que contém observações diárias de áreas terrestres em todo o mundo. Ele inclui medições baseadas em estações terrestres do mundo todo, cerca de dois terços das quais são apenas de precipitação (Menne et al., 2012). O GHCN-Daily é uma compilação de registros climáticos de inúmeras fontes, que foram combinadas e submetidas a um conjunto comum de verificações de garantia de qualidade (Durre et al., 2010). O acervo inclui os seguintes elementos meteorológicos:

Temperatura máxima diária
- Temperatura mínima diária
- Temperatura no momento da observação
- Precipitação (isto é, chuva, neve derretida)
- Queda de neve
- profundidade da neve
- Outros elementos, quando disponíveis

As seções abaixo apresentam uma breve visão geral das etapas envolvidas para trazer este conjunto de dados para o ClickHouse. Se você tiver interesse em ler sobre cada etapa em mais detalhes, recomendamos dar uma olhada em nossa postagem no blog intitulada “Explorando conjuntos de dados massivos do mundo real: mais de 100 anos de registros meteorológicos no ClickHouse”.

Baixando os dados

Uma versão pré-processada dos dados para o ClickHouse, que foi limpa, reestruturada e enriquecida. Esses dados abrangem o período de 1900 a 2022.
Baixe os dados originais e converta-os para o formato exigido pelo ClickHouse. Usuários que desejam adicionar suas próprias colunas podem preferir essa abordagem.

Dados pré-preparados

Mais especificamente, foram removidas as linhas que não falharam em nenhuma das verificações de garantia de qualidade da NOAA. Os dados também foram reestruturados de uma medição por linha para uma linha por ID da estação e data, ou seja.

"station_id","date","tempAvg","tempMax","tempMin","precipitation","snowfall","snowDepth","percentDailySun","averageWindSpeed","maxWindSpeed","weatherType"
"AEM00041194","2022-07-30",347,0,308,0,0,0,0,0,0,0
"AEM00041194","2022-07-31",371,413,329,0,0,0,0,0,0,0
"AEM00041194","2022-08-01",384,427,357,0,0,0,0,0,0,0
"AEM00041194","2022-08-02",381,424,352,0,0,0,0,0,0,0

Isso é mais simples de consultar e garante que a tabela resultante seja menos esparsa. Por fim, os dados também foram enriquecidos com latitude e longitude. Esses dados estão disponíveis na seguinte localização no S3. Baixe os dados para seu sistema de arquivos local (e faça a inserção usando o ClickHouse client) ou insira-os diretamente no ClickHouse (consulte Inserção a partir do S3). Para baixar:

wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet

Dados originais

A seguir, são detalhadas as etapas para baixar e transformar os dados originais em preparação para a carga no ClickHouse.

Baixar

Para baixar os dados originais:

for i in {1900..2023}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done

Amostragem de dados

$ clickhouse-local --query "SELECT * FROM '2021.csv.gz' LIMIT 10" --format PrettyCompact
┌─c1──────────┬───────c2─┬─c3───┬──c4─┬─c5───┬─c6───┬─c7─┬───c8─┐
│ AE000041196 │ 20210101 │ TMAX │ 278 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AE000041196 │ 20210101 │ PRCP │   0 │ D    │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AE000041196 │ 20210101 │ TAVG │ 214 │ H    │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ TMAX │ 266 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ TMIN │ 178 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ PRCP │   0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ TAVG │ 217 │ H    │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AEM00041217 │ 20210101 │ TMAX │ 262 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AEM00041217 │ 20210101 │ TMIN │ 155 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
│ AEM00041217 │ 20210101 │ TAVG │ 202 │ H    │ ᴺᵁᴸᴸ │ S  │ ᴺᵁᴸᴸ │
└─────────────┴──────────┴──────┴─────┴──────┴──────┴────┴──────┘

Resumindo a documentação do formato: Resumindo a documentação do formato e, em seguida, as colunas:

Um código de identificação da estação com 11 caracteres. Ele codifica algumas informações úteis
YEAR/MONTH/DAY = data de 8 caracteres no formato YYYYMMDD (ex.: 19860529 = 29 de maio de 1986)
ELEMENT = indicador de 4 caracteres do tipo de elemento. Na prática, o tipo de medição. Embora haja muitas medições disponíveis, selecionamos as seguintes:
- PRCP - Precipitação (décimos de mm)
- SNOW - queda de neve (mm)
- SNWD - Profundidade da neve (mm)
- TMAX - Temperatura máxima (décimos de grau C)
- TAVG - Temperatura média (décimos de grau C)
- TMIN - Temperatura mínima (décimos de grau C)
- PSUN - Percentual diário de insolação possível (percentual)
- AWND - Velocidade média diária do vento (décimos de metro por segundo)
- WSFG - Velocidade máxima de rajada de vento (décimos de metro por segundo)
- WT** = Tipo de tempo, em que ** define o tipo de tempo. Lista completa dos tipos de tempo aqui.
- DATA VALUE = valor de dados de 5 caracteres para ELEMENT, ou seja, o valor da medição.
- M-FLAG = Flag de medição com 1 caractere. Ela tem 10 valores possíveis. Alguns desses valores indicam precisão questionável dos dados. Aceitamos dados em que ela está definida como “P” - identificado como ausência presumida como zero, pois isso só é relevante para as medições PRCP, SNOW e SNWD.
Q-FLAG é a flag de qualidade da medição, com 14 valores possíveis. Estamos interessados apenas em dados com valor vazio, ou seja, que não falharam em nenhuma verificação de garantia de qualidade.
S-FLAG é a flag de origem da observação. Não é útil para nossa análise e é ignorada.
OBS-TIME = horário da observação com 4 caracteres no formato hora-minuto (ou seja, 0700 = 7:00 da manhã). Normalmente não está presente em dados mais antigos. Nós o ignoramos para os nossos propósitos.

Uma medição por linha resultaria em uma estrutura de tabela esparsa no ClickHouse. Devemos transformar isso em uma linha por horário e estação, com as medições como colunas. Primeiro, limitamos o conjunto de dados àquelas linhas sem problemas, ou seja, em que qFlag é igual a uma string vazia.

Limpar os dados

Usando o ClickHouse local, podemos filtrar as linhas que correspondem às medições de interesse e atendem aos nossos requisitos de qualidade:

clickhouse local --query "SELECT count() 
FROM file('*.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String') WHERE qFlag = '' AND (measurement IN ('PRCP', 'SNOW', 'SNWD', 'TMAX', 'TAVG', 'TMIN', 'PSUN', 'AWND', 'WSFG') OR startsWith(measurement, 'WT'))"

2679264563

Com mais de 2,6 bilhões de linhas, esta não é uma consulta rápida, já que exige analisar todos os arquivos. Em nossa máquina de 8 núcleos, isso leva cerca de 160 segundos.

Pivotar os dados

Embora a estrutura de uma medição por linha possa ser usada com o ClickHouse, ela complicará desnecessariamente as consultas futuras. O ideal é ter uma linha por ID da estação e data, em que cada tipo de medição e seu valor associado sejam uma coluna, ou seja.

"station_id","date","tempAvg","tempMax","tempMin","precipitation","snowfall","snowDepth","percentDailySun","averageWindSpeed","maxWindSpeed","weatherType"
"AEM00041194","2022-07-30",347,0,308,0,0,0,0,0,0,0
"AEM00041194","2022-07-31",371,413,329,0,0,0,0,0,0,0
"AEM00041194","2022-08-01",384,427,357,0,0,0,0,0,0,0
"AEM00041194","2022-08-02",381,424,352,0,0,0,0,0,0,0

Usando o ClickHouse local e um GROUP BY simples, podemos repivotar os dados para esta estrutura. Para limitar o uso de memória, fazemos isso um arquivo por vez.

for i in {1900..2022}
do
clickhouse-local --query "SELECT station_id,
       toDate32(date) as date,
       anyIf(value, measurement = 'TAVG') as tempAvg,
       anyIf(value, measurement = 'TMAX') as tempMax,
       anyIf(value, measurement = 'TMIN') as tempMin,
       anyIf(value, measurement = 'PRCP') as precipitation,
       anyIf(value, measurement = 'SNOW') as snowfall,
       anyIf(value, measurement = 'SNWD') as snowDepth,
       anyIf(value, measurement = 'PSUN') as percentDailySun,
       anyIf(value, measurement = 'AWND') as averageWindSpeed,
       anyIf(value, measurement = 'WSFG') as maxWindSpeed,
       toUInt8OrZero(replaceOne(anyIf(measurement, startsWith(measurement, 'WT') AND value = 1), 'WT', '')) as weatherType
FROM file('$i.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String')
 WHERE qFlag = '' AND (measurement IN ('PRCP', 'SNOW', 'SNWD', 'TMAX', 'TAVG', 'TMIN', 'PSUN', 'AWND', 'WSFG') OR startsWith(measurement, 'WT'))
GROUP BY station_id, date
ORDER BY station_id, date FORMAT CSV" >> "noaa.csv";
done

Esta consulta gera um único arquivo de 50 GB, noaa.csv.

Enriquecendo os dados

Os dados não trazem nenhuma indicação de localização além do ID da estação, que inclui um código de país como prefixo. O ideal seria que cada estação tivesse uma latitude e uma longitude associadas. Para isso, a NOAA disponibiliza convenientemente os detalhes de cada estação em um arquivo ghcnd-stations.txt separado. Esse arquivo tem várias colunas, das quais cinco são úteis para nossa análise futura: id, latitude, longitude, elevação e nome.

wget http://noaa-ghcn-pds.s3.amazonaws.com/ghcnd-stations.txt

clickhouse local --query "WITH stations AS (SELECT id, lat, lon, elevation, splitByString(' GSN ',name)[1] as name FROM file('ghcnd-stations.txt', Regexp, 'id String, lat Float64, lon Float64, elevation Float32, name String'))
SELECT station_id,
       date,
       tempAvg,
       tempMax,
       tempMin,
       precipitation,
       snowfall,
       snowDepth,
       percentDailySun,
       averageWindSpeed,
       maxWindSpeed,
       weatherType,
       tuple(lon, lat) as location,
       elevation,
       name
FROM file('noaa.csv', CSV,
          'station_id String, date Date32, tempAvg Int32, tempMax Int32, tempMin Int32, precipitation Int32, snowfall Int32, snowDepth Int32, percentDailySun Int8, averageWindSpeed Int32, maxWindSpeed Int32, weatherType UInt8') as noaa LEFT OUTER
         JOIN stations ON noaa.station_id = stations.id INTO OUTFILE 'noaa_enriched.parquet' FORMAT Parquet SETTINGS format_regexp='^(.{11})\s+(\-?\d{1,2}\.\d{4})\s+(\-?\d{1,3}\.\d{1,4})\s+(\-?\d*\.\d*)\s+(.*)\s+(?:[\d]*)'" 

Esta consulta leva alguns minutos para ser executada e gera um arquivo de 6,4 GB, noaa_enriched.parquet.

Criar tabela

Crie uma tabela MergeTree no ClickHouse (usando o clickhouse client).

CREATE TABLE noaa
(
   `station_id` LowCardinality(String),
   `date` Date32,
   `tempAvg` Int32 COMMENT 'Average temperature (tenths of a degrees C)',
   `tempMax` Int32 COMMENT 'Maximum temperature (tenths of degrees C)',
   `tempMin` Int32 COMMENT 'Minimum temperature (tenths of degrees C)',
   `precipitation` UInt32 COMMENT 'Precipitation (tenths of mm)',
   `snowfall` UInt32 COMMENT 'Snowfall (mm)',
   `snowDepth` UInt32 COMMENT 'Snow depth (mm)',
   `percentDailySun` UInt8 COMMENT 'Daily percent of possible sunshine (percent)',
   `averageWindSpeed` UInt32 COMMENT 'Average daily wind speed (tenths of meters per second)',
   `maxWindSpeed` UInt32 COMMENT 'Peak gust wind speed (tenths of meters per second)',
   `weatherType` Enum8('Normal' = 0, 'Fog' = 1, 'Heavy Fog' = 2, 'Thunder' = 3, 'Small Hail' = 4, 'Hail' = 5, 'Glaze' = 6, 'Dust/Ash' = 7, 'Smoke/Haze' = 8, 'Blowing/Drifting Snow' = 9, 'Tornado' = 10, 'High Winds' = 11, 'Blowing Spray' = 12, 'Mist' = 13, 'Drizzle' = 14, 'Freezing Drizzle' = 15, 'Rain' = 16, 'Freezing Rain' = 17, 'Snow' = 18, 'Unknown Precipitation' = 19, 'Ground Fog' = 21, 'Freezing Fog' = 22),
   `location` Point,
   `elevation` Float32,
   `name` LowCardinality(String)
) ENGINE = MergeTree() ORDER BY (station_id, date);

Inserção no ClickHouse

Inserindo a partir de um arquivo local

Os dados podem ser inseridos de um arquivo local da seguinte forma (no ClickHouse client):

INSERT INTO noaa FROM INFILE '<path>/noaa_enriched.parquet'

em que <path> representa o caminho completo para o arquivo local no disco. Veja aqui como acelerar esse processo de carregamento.

Inserindo a partir do S3

INSERT INTO noaa SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet')

Para saber como acelerar esse processo, veja nosso post no blog sobre como otimizar grandes cargas de dados.

Consultas de exemplo

Maior temperatura já registrada

SELECT
    tempMax / 10 AS maxTemp,
    location,
    name,
    date
FROM blogs.noaa
WHERE tempMax > 500
ORDER BY
    tempMax DESC,
    date ASC
LIMIT 5

┌─maxTemp─┬─location──────────┬─name───────────────────────────────────────────┬───────date─┐
│    56.7 │ (-116.8667,36.45) │ CA GREENLAND RCH                               │ 1913-07-10 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)                                 │ 1949-08-20 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)                                 │ 1949-09-18 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)                                 │ 1952-07-17 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)                                 │ 1952-09-04 │
└─────────┴───────────────────┴────────────────────────────────────────────────┴────────────┘

5 rows in set. Elapsed: 0.514 sec. Processed 1.06 billion rows, 4.27 GB (2.06 billion rows/s., 8.29 GB/s.)

Em linha, de forma tranquilizadora, com o registro documentado em Furnace Creek em 2023.

Melhores estações de esqui

Usando uma lista de estações de esqui nos Estados Unidos e suas respectivas localizações, fazemos um join com as 1000 estações meteorológicas com maior volume de neve em qualquer mês nos últimos 5 anos. Ordenando esse join por geoDistance e restringindo os resultados àqueles em que a distância é menor que 20 km, selecionamos o melhor resultado para cada estação de esqui e o ordenamos pela neve total. Observe que também restringimos as estações de esqui àquelas acima de 1800 m, como um indicador geral de boas condições para a prática de esqui.

SELECT
   resort_name,
   total_snow / 1000 AS total_snow_m,
   resort_location,
   month_year
FROM
(
   WITH resorts AS
       (
           SELECT
               resort_name,
               state,
               (lon, lat) AS resort_location,
               'US' AS code
           FROM url('https://gist.githubusercontent.com/gingerwizard/dd022f754fd128fdaf270e58fa052e35/raw/622e03c37460f17ef72907afe554cb1c07f91f23/ski_resort_stats.csv', CSVWithNames)
       )
   SELECT
       resort_name,
       highest_snow.station_id,
       geoDistance(resort_location.1, resort_location.2, station_location.1, station_location.2) / 1000 AS distance_km,
       highest_snow.total_snow,
       resort_location,
       station_location,
       month_year
   FROM
   (
       SELECT
           sum(snowfall) AS total_snow,
           station_id,
           any(location) AS station_location,
           month_year,
           substring(station_id, 1, 2) AS code
       FROM noaa
       WHERE (date > '2017-01-01') AND (code = 'US') AND (elevation > 1800)
       GROUP BY
           station_id,
           toYYYYMM(date) AS month_year
       ORDER BY total_snow DESC
       LIMIT 1000
   ) AS highest_snow
   INNER JOIN resorts ON highest_snow.code = resorts.code
   WHERE distance_km < 20
   ORDER BY
       resort_name ASC,
       total_snow DESC
   LIMIT 1 BY
       resort_name,
       station_id
)
ORDER BY total_snow DESC
LIMIT 5

┌─resort_name──────────┬─total_snow_m─┬─resort_location─┬─month_year─┐
│ Sugar Bowl, CA       │        7.799 │ (-120.3,39.27)  │     201902 │
│ Donner Ski Ranch, CA │        7.799 │ (-120.34,39.31) │     201902 │
│ Boreal, CA           │        7.799 │ (-120.35,39.33) │     201902 │
│ Homewood, CA         │        4.926 │ (-120.17,39.08) │     201902 │
│ Alpine Meadows, CA   │        4.926 │ (-120.22,39.17) │     201902 │
└──────────────────────┴──────────────┴─────────────────┴────────────┘

5 linhas no conjunto. Tempo decorrido: 0.750 seg. Processadas 689.10 milhões de linhas, 3.20 GB (918.20 milhões de linhas/s., 4.26 GB/s.)
Pico de uso de memória: 67.66 MiB.

Créditos

Gostaríamos de reconhecer o trabalho da Global Historical Climatology Network na preparação, limpeza e distribuição destes dados. Agradecemos por esse esforço. Menne, M.J., I. Durre, B. Korzeniewski, S. McNeal, K. Thomas, X. Yin, S. Anthony, R. Ray, R.S. Vose, B.E.Gleason, and T.G. Houston, 2012: Global Historical Climatology Network - Daily (GHCN-Daily), Version 3. [indique o subconjunto usado após o decimal, por exemplo Version 3.25]. NOAA National Centers for Environmental Information. http://doi.org/10.7289/V5D21VHZ [17/08/2020]

​Baixando os dados

​Dados pré-preparados

​Dados originais

​Baixar

​Amostragem de dados

​Limpar os dados

​Pivotar os dados

​Enriquecendo os dados

​Criar tabela

​Inserção no ClickHouse

​Inserindo a partir de um arquivo local

​Inserindo a partir do S3

​Consultas de exemplo

​Maior temperatura já registrada

​Melhores estações de esqui

​Créditos