> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > 2,5 milliards de lignes de données climatiques sur les 120 dernières années # Global Historical Climatology Network de la NOAA 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](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn) : > 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"](https://clickhouse.com/blog/real-world-data-noaa-climate-data).
## Téléchargement des données
* Une [version préparée à l’avance](#pre-prepared-data) 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](#original-data) 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. ```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 ``` 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](#inserting-from-s3)). Pour télécharger : ```bash theme={null} 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 : ```bash theme={null} for i in {1900..2023}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done ```
#### Échantillonnage des données
```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 │ ᴺᵁᴸᴸ │ └─────────────┴──────────┴──────┴─────┴──────┴──────┴────┴──────┘ ``` Résumé de la [documentation du format](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn) : 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](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local), nous pouvons filtrer les lignes correspondant aux mesures qui nous intéressent et répondant à nos exigences de qualité : ```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 ``` 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. ```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 ``` À 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. ```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 ``` 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](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file). Ce fichier comporte [plusieurs colonnes](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file), dont cinq seront utiles pour notre analyse ultérieure : id, latitude, longitude, elevation et 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]*)'" ``` 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). ```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); ```
## 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) : ```sql theme={null} INSERT INTO noaa FROM INFILE '/noaa_enriched.parquet' ``` où `` représente le chemin complet du fichier local sur le disque. Voir [ici](https://clickhouse.com/blog/real-world-data-noaa-climate-data#load-the-data) pour savoir comment accélérer le chargement.
### Insertion à partir de S3
```sql theme={null} 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](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part2).
## Exemples de requêtes
### Température la plus élevée jamais enregistrée
```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.) ``` Conforme, de manière rassurante, au [record documenté](https://en.wikipedia.org/wiki/List_of_weather_records#Highest_temperatures_ever_recorded) à [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) en 2023.
### Meilleures stations de ski
À l’aide d’une [liste de stations de ski](https://gist.githubusercontent.com/gingerwizard/dd022f754fd128fdaf270e58fa052e35/raw/622e03c37460f17ef72907afe554cb1c07f91f23/ski_resort_stats.csv) 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](/fr/reference/functions/regular-functions/geo/coordinates#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. ```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 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](http://doi.org/10.7289/V5D21VHZ) \[17/08/2020]