Global Historical Climatology Network de la NOAA

2,5 milliards de lignes de données climatiques sur les 120 dernières années

Ce jeu de données contient des mesures météorologiques sur les 120 dernières années. Chaque ligne correspond à une mesure effectuée à un instant donné pour une station.

Plus précisément, selon l’origine de ces données :

GHCN-Daily est un jeu de données qui contient des observations quotidiennes sur les zones terrestres du globe. Il contient des mesures par station provenant de stations terrestres du monde entier, dont environ les deux tiers concernent uniquement les précipitations (Menne et al., 2012). GHCN-Daily est une compilation d’archives climatiques issues de nombreuses sources, qui ont été fusionnées puis soumises à un ensemble commun de contrôles d’assurance qualité (Durre et al., 2010). Les archives comprennent les éléments météorologiques suivants :

• Température maximale quotidienne
  • Température minimale quotidienne
  • Température au moment de l’observation
  • Précipitations (c.-à-d. pluie, neige fondue)
  • Chutes de neige
  • Hauteur de neige
  • Autres éléments, lorsqu’ils sont disponibles

Les sections ci-dessous présentent brièvement les étapes ayant permis d’intégrer ce jeu de données dans ClickHouse. Si vous souhaitez en savoir plus sur chacune de ces étapes, nous vous recommandons de consulter notre article de blog intitulé “Exploring massive, real-world data sets: 100+ Years of Weather Records in ClickHouse”.

​

Téléchargement des données

• Une version préparée à l’avance des données pour ClickHouse, déjà nettoyées, restructurées et enrichies. Ces données couvrent les années 1900 à 2022.
• Télécharger les données d’origine et les convertir dans le format requis par ClickHouse. Les utilisateurs qui souhaitent ajouter leurs propres colonnes peuvent privilégier cette approche.

​

Données préparées à l’avance

Plus précisément, les lignes n’ayant échoué à aucun contrôle d’assurance qualité de la NOAA ont été supprimées. Les données ont également été restructurées, passant d’une mesure par ligne à une ligne par identifiant de station et par date, c.-à-d.

"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

Ces données sont plus faciles à interroger et garantissent que la table obtenue est moins clairsemée. Enfin, elles ont également été enrichies avec la latitude et la longitude.

Ces données sont disponibles à l’emplacement S3 suivant. Téléchargez-les soit sur votre système de fichiers local (puis insérez-les à l’aide du client ClickHouse), soit directement dans ClickHouse (voir Insertion depuis S3).

Pour télécharger :

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

​

Données d’origine

Voici les étapes pour télécharger et transformer les données d’origine avant de les charger dans ClickHouse.

​

Téléchargement

Pour télécharger les données d’origine :

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

​

Échantillonnage des données

$ 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  │ ᴺᵁᴸᴸ │
└─────────────┴──────────┴──────┴─────┴──────┴──────┴────┴──────┘

Résumé de la documentation du format :

Résumé de la documentation du format et des colonnes, dans l’ordre :

• Un code d’identification de station sur 11 caractères. Il encode à lui seul certaines informations utiles.
• YEAR/MONTH/DAY = date sur 8 caractères au format YYYYMMDD (p. ex. 19860529 = 29 mai 1986)
• ELEMENT = indicateur sur 4 caractères du type d’élément, c’est-à-dire du type de mesure. Bien que de nombreuses mesures soient disponibles, nous sélectionnons les suivantes :
  • PRCP - Précipitations (dixièmes de mm)
  • SNOW - Chute de neige (mm)
  • SNWD - Hauteur de neige (mm)
  • TMAX - Température maximale (dixièmes de degré C)
  • TAVG - Température moyenne (dixièmes de degré C)
  • TMIN - Température minimale (dixièmes de degré C)
  • PSUN - Pourcentage quotidien d’ensoleillement possible (pourcentage)
  • AWND - Vitesse moyenne quotidienne du vent (dixièmes de mètre par seconde)
  • WSFG - Vitesse maximale des rafales de vent (dixièmes de mètre par seconde)
  • WT** = Type de temps, où ** définit le type de temps. Liste complète des types de temps ici.
  • DATA VALUE = valeur de données sur 5 caractères pour ELEMENT, c.-à-d. la valeur de la mesure.
  • M-FLAG = indicateur de mesure sur 1 caractère. Il a 10 valeurs possibles. Certaines indiquent une précision des données discutable. Nous acceptons les données pour lesquelles cette valeur est définie sur “P”, c’est-à-dire manquant présumé nul, car cela ne concerne que les mesures PRCP, SNOW et SNWD.
• Q-FLAG est l’indicateur de qualité de la mesure, avec 14 valeurs possibles. Nous nous intéressons uniquement aux données dont la valeur est vide, c.-à-d. qui n’ont échoué à aucun contrôle d’assurance qualité.
• S-FLAG est l’indicateur de source de l’observation. Il n’est pas utile pour notre analyse et nous l’ignorons.
• OBS-TIME = heure d’observation sur 4 caractères au format heure-minute (c.-à-d. 0700 = 7:00 am). Généralement absente des données plus anciennes. Nous l’ignorons pour notre usage.

Une mesure par ligne produirait une structure de table sparse dans ClickHouse. Nous devons donc transformer cela en une row par heure et par station, avec les mesures comme columns. Dans un premier temps, nous limitons le dataset aux rows sans anomalie, c.-à-d. celles où qFlag est égal à une chaîne vide.

​

Nettoyer les données

À l’aide de ClickHouse local, nous pouvons filtrer les lignes correspondant aux mesures qui nous intéressent et répondant à nos exigences de qualité :

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

Avec plus de 2,6 milliards de lignes, cette requête n’est pas rapide, car elle nécessite de parser tous les fichiers. Sur notre machine à 8 cœurs, cela prend environ 160 secondes.

​

Réorganiser les données

Bien que la structure avec une mesure par ligne puisse être utilisée avec ClickHouse, elle compliquera inutilement les requêtes par la suite. Idéalement, il nous faut une ligne par identifiant de station et par date, où chaque type de mesure et la valeur associée correspondent à une colonne, c.-à-d.

"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

À l’aide de ClickHouse local et d’un simple GROUP BY, nous pouvons réorganiser nos données selon cette structure. Pour limiter la consommation mémoire supplémentaire, nous procédons fichier par fichier.

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

Cette requête produit un seul fichier de 50 Go, noaa.csv.

​

Enrichir les données

Les données ne fournissent aucune indication de localisation en dehors d’un identifiant de station, qui inclut un préfixe correspondant au code du pays. Dans l’idéal, chaque station serait associée à une latitude et une longitude. Pour cela, la NOAA met à disposition les informations détaillées de chaque station dans un fichier distinct, ghcnd-stations.txt. Ce fichier comporte plusieurs colonnes, dont cinq seront utiles pour notre analyse ultérieure : id, latitude, longitude, elevation et 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]*)'" 

Cette requête prend quelques minutes et produit un fichier de 6,4 Go, noaa_enriched.parquet.

​

Créer une table

Créez une table MergeTree dans ClickHouse (depuis le client 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);

​

Insérer des données dans ClickHouse

​

Insertion à partir d’un fichier local

Les données peuvent être insérées à partir d’un fichier local comme suit (via le client ClickHouse) :

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

où <path> représente le chemin complet du fichier local sur le disque.

Voir ici pour savoir comment accélérer le chargement.

​

Insertion à partir de S3

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

Pour savoir comment accélérer ce processus, consultez notre article de blog sur l’optimisation du chargement de gros volumes de données.

​

Exemples de requêtes

​

Température la plus élevée jamais enregistrée

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

Conforme, de manière rassurante, au record documenté à Furnace Creek en 2023.

​

Meilleures stations de ski

À l’aide d’une liste de stations de ski aux États-Unis et de leurs emplacements respectifs, nous effectuons une jointure avec les 1 000 stations météorologiques ayant enregistré les valeurs les plus élevées sur un mois donné au cours des 5 dernières années. En triant cette jointure par geoDistance et en limitant les résultats à ceux où la distance est inférieure à 20 km, nous sélectionnons le meilleur résultat pour chaque station, puis nous les trions par enneigement total. Notez que nous limitons également les stations de ski à celles situées à plus de 1 800 m d’altitude, comme indicateur général de bonnes conditions de ski.

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 rows in set. Elapsed: 0.750 sec. Processed 689.10 million rows, 3.20 GB (918.20 million rows/s., 4.26 GB/s.)
Peak memory usage: 67.66 MiB.

​

Crédits

Nous souhaitons saluer les efforts du Global Historical Climatology Network pour la préparation, le nettoyage et la diffusion de ces données. Nous vous en remercions.

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. [indiquer le sous-ensemble utilisé après la décimale, par exemple Version 3.25]. NOAA National Centers for Environmental Information. http://doi.org/10.7289/V5D21VHZ [17/08/2020]