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:

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”.

Descarga de los datos

Una versión ya preparada de los datos para ClickHouse, que ha sido depurada, reestructurada y enriquecida. Estos datos abarcan de 1900 a 2022.
Descargue los datos originales 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.

"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). Para descargar:

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:

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

Muestreo de datos

$ 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: 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, podemos filtrar las filas que representan las mediciones de interés y cumplen nuestros requisitos de calidad:

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:

"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.

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. Este archivo tiene varias columnas, de las cuales cinco son útiles para nuestro análisis posterior: id, latitude, longitude, elevation y name.

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 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).

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):

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

donde <path> representa la ruta completa del archivo local en disco. Consulta aquí cómo acelerar esta carga.

Insertar desde S3

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.

Consultas de ejemplo

Temperatura máxima histórica

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.)

En consonancia, de forma tranquilizadora, con el registro documentado de Furnace Creek hasta 2023.

Mejores estaciones de esquí

Usando una lista de estaciones de esquí 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 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.

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 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 [17/08/2020]

​Descarga de los datos

​Datos preparados previamente

​Datos originales

​Descarga

​Muestreo de datos

​Limpiar los datos

​Reestructurar los datos

​Enriquecimiento de los datos

​Crear una tabla

​Insertar en ClickHouse

​Insertar desde un archivo local

​Insertar desde S3

​Consultas de ejemplo

​Temperatura máxima histórica

​Mejores estaciones de esquí

​Créditos