> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > 2,5 bilhões de linhas de dados climáticos dos últimos 120 anos # 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](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn): > 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"](https://clickhouse.com/blog/real-world-data-noaa-climate-data).
## Baixando os dados
* Uma [versão pré-processada](#pre-prepared-data) 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](#original-data) 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. ```csv theme={null} "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](#inserting-from-s3)). Para baixar: ```bash theme={null} 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: ```bash theme={null} for i in {1900..2023}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done ```
#### Amostragem de dados
```bash theme={null} $ 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](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn): 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](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local), podemos filtrar as linhas que correspondem às medições de interesse e atendem aos nossos requisitos de qualidade: ```bash theme={null} 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. ```csv theme={null} "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. ```bash theme={null} 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](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file) separado. Esse arquivo tem [várias colunas](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file), das quais cinco são úteis para nossa análise futura: id, latitude, longitude, elevação e nome. ```bash theme={null} wget http://noaa-ghcn-pds.s3.amazonaws.com/ghcnd-stations.txt ``` ```bash theme={null} 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). ```sql theme={null} 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): ```sql theme={null} INSERT INTO noaa FROM INFILE '/noaa_enriched.parquet' ``` em que `` representa o caminho completo para o arquivo local no disco. Veja [aqui](https://clickhouse.com/blog/real-world-data-noaa-climate-data#load-the-data) como acelerar esse processo de carregamento.
### Inserindo a partir do S3
```sql theme={null} 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](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part2).
## Consultas de exemplo
### Maior temperatura já registrada
```sql theme={null} SELECT tempMax / 10 AS maxTemp, location, name, date FROM blogs.noaa WHERE tempMax > 500 ORDER BY tempMax DESC, date ASC LIMIT 5 ``` ```response theme={null} ┌─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](https://en.wikipedia.org/wiki/List_of_weather_records#Highest_temperatures_ever_recorded) em [Furnace Creek](https://www.google.com/maps/place/36%C2%B027'00.0%22N+116%C2%B052'00.1%22W/@36.1329666,-116.1104099,8.95z/data=!4m5!3m4!1s0x0:0xf2ed901b860f4446!8m2!3d36.45!4d-116.8667) em 2023.
### Melhores estações de esqui
Usando uma [lista de estações de esqui](https://gist.githubusercontent.com/gingerwizard/dd022f754fd128fdaf270e58fa052e35/raw/622e03c37460f17ef72907afe554cb1c07f91f23/ski_resort_stats.csv) 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](/pt-BR/reference/functions/regular-functions/geo/coordinates#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. ```sql theme={null} 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 ``` ```response theme={null} ┌─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](http://doi.org/10.7289/V5D21VHZ) \[17/08/2020]