> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > 2.500 millones de filas de datos climáticos de los últimos 120 años # NOAA Global Historical Climatology Network Este conjunto de datos contiene mediciones meteorológicas de los últimos 120 años. Cada fila corresponde a una medición para un momento determinado y una estación. Más concretamente, y según el [origen de estos datos](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn): > GHCN-Daily es un conjunto de datos que contiene observaciones diarias de zonas terrestres de todo el mundo. Incluye mediciones de estaciones terrestres de todo el planeta, aproximadamente dos tercios de las cuales corresponden únicamente a mediciones de precipitación (Menne et al., 2012). GHCN-Daily es una recopilación de registros climáticos de numerosas fuentes que se fusionaron y se sometieron a un conjunto común de revisiones de control de calidad (Durre et al., 2010). El archivo incluye los siguientes elementos meteorológicos: * Temperatura máxima diaria * Temperatura mínima diaria * Temperatura en el momento de la observación * Precipitación (es decir, lluvia, nieve derretida) * Caída de nieve * Espesor de la nieve * Otros elementos, cuando están disponibles Las secciones siguientes ofrecen un breve resumen de los pasos necesarios para incorporar este conjunto de datos a ClickHouse. Si te interesa leer sobre cada paso con más detalle, te recomendamos consultar nuestra entrada del blog titulada ["Exploring massive, real-world data sets: 100+ Years of Weather Records in ClickHouse"](https://clickhouse.com/blog/real-world-data-noaa-climate-data).
## Descarga de los datos
* Una [versión ya preparada](#pre-prepared-data) de los datos para ClickHouse, que ha sido depurada, reestructurada y enriquecida. Estos datos abarcan de 1900 a 2022. * [Descargue los datos originales](#original-data) y conviértalos al formato requerido por ClickHouse. Los usuarios que deseen añadir sus propias columnas pueden preferir este enfoque.
### Datos preparados previamente
Más concretamente, se han eliminado las filas que no habían fallado ninguna comprobación de control de calidad de NOAA. Los datos también se han reestructurado de una medición por línea a una fila por identificador de estación y fecha, es decir. ```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 ``` Esto facilita las consultas y garantiza que la tabla resultante sea menos dispersa. Por último, los datos también se han enriquecido con latitud y longitud. Estos datos están disponibles en la siguiente ubicación de S3. Descargue los datos en su sistema de archivos local (e insértelos con el cliente de ClickHouse) o insértelos directamente en ClickHouse (consulte [Inserting from S3](#inserting-from-s3)). Para descargar: ```bash theme={null} wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet ```
### Datos originales
A continuación, se detallan los pasos para descargar y transformar los datos originales antes de cargarlos en ClickHouse.
#### Descarga
Para descargar los datos originales: ```bash theme={null} for i in {1900..2023}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done ```
#### Muestreo de datos
```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 │ ᴺᵁᴸᴸ │ └─────────────┴──────────┴──────┴─────┴──────┴──────┴────┴──────┘ ``` En resumen, la [documentación del formato](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn): En resumen, la documentación del formato y, a continuación, las columnas: * Un código de identificación de estación de 11 caracteres. Este código contiene en sí mismo información útil. * YEAR/MONTH/DAY = fecha de 8 caracteres en formato YYYYMMDD (p. ej., 19860529 = 29 de mayo de 1986) * ELEMENT = indicador de 4 caracteres del tipo de elemento. En la práctica, es el tipo de medición. Aunque hay muchas mediciones disponibles, seleccionamos las siguientes: * PRCP - Precipitación (décimas de mm) * SNOW - Nevada (mm) * SNWD - Espesor de nieve (mm) * TMAX - Temperatura máxima (décimas de grado C) * TAVG - Temperatura media (décimas de grado C) * TMIN - Temperatura mínima (décimas de grado C) * PSUN - Porcentaje diario de insolación posible (porcentaje) * AWND - Velocidad media diaria del viento (décimas de metros por segundo) * WSFG - Velocidad máxima de ráfaga de viento (décimas de metros por segundo) * WT\*\* = Tipo de tiempo, donde \*\* define el tipo de tiempo. La lista completa de tipos de tiempo está aquí. * DATA VALUE = valor de datos de 5 caracteres para ELEMENT; es decir, el valor de la medición. * M-FLAG = indicador de medición de 1 carácter. Tiene 10 valores posibles. Algunos de estos valores indican que la precisión de los datos es cuestionable. Aceptamos datos donde este está establecido en "P" — identificado como ausente y presumiblemente cero —, ya que esto solo es relevante para las mediciones PRCP, SNOW y SNWD. * Q-FLAG es el indicador de calidad de la medición con 14 valores posibles. Solo nos interesan los datos con un valor vacío; es decir, que no fallaron ninguna comprobación de aseguramiento de la calidad. * S-FLAG es el indicador de origen de la observación. No es útil para nuestro análisis y se ignora. * OBS-TIME = hora de observación de 4 caracteres en formato hora-minuto (es decir, 0700 = 7:00 a. m.). Normalmente no está presente en los datos más antiguos. Lo ignoramos para nuestros fines. Una medición por línea daría lugar a una estructura de tabla dispersa en ClickHouse. Debemos transformarla para tener una fila por instante y estación, con las mediciones como columnas. Primero, limitamos el conjunto de datos a aquellas filas sin problemas; es decir, donde `qFlag` es igual a una cadena vacía.
#### Limpiar los datos
Con [ClickHouse local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local), podemos filtrar las filas que representan las mediciones de interés y cumplen nuestros requisitos de calidad: ```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 ``` Con más de 2.600 millones de filas, esta no es una consulta rápida, ya que requiere parsear todos los archivos. En nuestra máquina de 8 núcleos, tarda unos 160 segundos.
### Reestructurar los datos
Aunque la estructura de una medición por línea se puede usar con ClickHouse, complicará innecesariamente las consultas posteriores. Lo ideal es tener una fila por id de estación y fecha, donde cada tipo de medición y su valor asociado correspondan a una columna, es decir: ```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 ClickHouse local y un simple `GROUP BY`, podemos reestructurar nuestros datos para que adopten esta estructura. Para limitar la sobrecarga de memoria, lo hacemos archivo por archivo. ```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 genera un solo archivo de 50 GB: `noaa.csv`.
### Enriquecimiento de los datos
Los datos no incluyen ninguna indicación de ubicación aparte de un identificador de estación, que incorpora un prefijo con el código del país. Idealmente, cada estación tendría una latitud y una longitud asociadas. Para ello, NOAA proporciona convenientemente los detalles de cada estación en un archivo independiente, [ghcnd-stations.txt](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file). Este archivo tiene [varias columnas](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file), de las cuales cinco son útiles para nuestro análisis posterior: id, latitude, longitude, elevation y name. ```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 tarda unos minutos en ejecutarse y genera un archivo de 6.4 GB, `noaa_enriched.parquet`.
## Crear una tabla
Crea una tabla MergeTree en ClickHouse (desde el cliente de ClickHouse). ```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); ```
## Insertar en ClickHouse
### Insertar desde un archivo local
Se pueden insertar datos desde un archivo local de la siguiente manera (desde cliente de ClickHouse): ```sql theme={null} INSERT INTO noaa FROM INFILE '/noaa_enriched.parquet' ``` donde `` representa la ruta completa del archivo local en disco. Consulta [aquí](https://clickhouse.com/blog/real-world-data-noaa-climate-data#load-the-data) cómo acelerar esta carga.
### Insertar desde 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 cómo acelerar este proceso, consulta nuestra entrada del blog sobre [cómo optimizar grandes cargas de datos](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part2).
## Consultas de ejemplo
### Temperatura máxima histórica
```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.) ``` En consonancia, de forma tranquilizadora, con el [registro documentado](https://en.wikipedia.org/wiki/List_of_weather_records#Highest_temperatures_ever_recorded) de [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) hasta 2023.
### Mejores estaciones de esquí
Usando una [lista de estaciones de esquí](https://gist.githubusercontent.com/gingerwizard/dd022f754fd128fdaf270e58fa052e35/raw/622e03c37460f17ef72907afe554cb1c07f91f23/ski_resort_stats.csv) de Estados Unidos y sus respectivas ubicaciones, las cruzamos con las 1000 estaciones meteorológicas con más nieve registrada en cualquier mes de los últimos 5 años. Al ordenar este join por [geoDistance](/es/reference/functions/regular-functions/geo/coordinates#geodistance) y limitar los resultados a aquellos en los que la distancia es inferior a 20 km, seleccionamos el resultado principal de cada estación de esquí y lo ordenamos por nieve total. Ten en cuenta que también limitamos las estaciones de esquí a aquellas situadas por encima de los 1800 m, como indicador general de buenas condiciones para esquiar. ```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 filas en el conjunto. Tiempo transcurrido: 0.750 seg. Procesadas 689.10 millones de filas, 3.20 GB (918.20 millones de filas/s., 4.26 GB/s.) Uso máximo de memoria: 67.66 MiB. ```
## Créditos
Queremos reconocer la labor de la Global Historical Climatology Network en la preparación, depuración y distribución de estos datos. Agradecemos su esfuerzo. 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), Versión 3. \[indicar el subconjunto utilizado después del decimal, por ejemplo, Versión 3.25]. NOAA National Centers for Environmental Information. [http://doi.org/10.7289/V5D21VHZ](http://doi.org/10.7289/V5D21VHZ) \[17/08/2020]