> ## 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 миллиарда строк климатических данных за последние 120 лет # NOAA Global Historical Climatology Network Этот набор данных содержит погодные измерения за последние 120 лет. Каждая строка — это измерение для определенного момента времени на определенной станции. Точнее, согласно [источнику этих данных](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn): > GHCN-Daily — это набор данных, содержащий ежедневные наблюдения по всей суше земного шара. Он включает измерения с наземных станций по всему миру, причем около двух третей из них относятся только к осадкам (Menne et al., 2012). GHCN-Daily представляет собой свод климатических записей из многочисленных источников, которые были объединены и подвергнуты единому набору проверок качества (Durre et al., 2010). Архив включает следующие метеорологические показатели: * Ежедневная максимальная температура * Ежедневная минимальная температура * Температура на момент наблюдения * Осадки (т. е. дождь, растаявший снег) * Снегопад * Глубина снежного покрова * Другие показатели, где доступны В разделах ниже кратко описаны шаги, которые потребовались для загрузки этого набора данных в ClickHouse. Если вы хотите подробнее узнать о каждом этапе, рекомендуем ознакомиться с нашей статьей в блоге ["Exploring massive, real-world data sets: 100+ Years of Weather Records in ClickHouse"](https://clickhouse.com/blog/real-world-data-noaa-climate-data).
## Загрузка данных
* [Заранее подготовленная версия](#pre-prepared-data) данных для ClickHouse, очищенная, реструктурированная и обогащённая. Эти данные охватывают период с 1900 по 2022 год. * [Скачать исходные данные](#original-data) и преобразовать их в формат, необходимый для ClickHouse. Пользователи, которые хотят добавить собственные столбцы, могут выбрать этот подход.
### Заранее подготовленные данные
Точнее, были удалены строки, которые прошли все проверки качества NOAA. Данные также были преобразованы из формата «одно измерение на строку» в формат «одна строка на идентификатор станции и дату», то есть. ```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 ``` С такими данными проще работать в запросах, а итоговая таблица получается менее разреженной. Кроме того, данные были дополнительно обогащены значениями широты и долготы. Эти данные доступны по следующему пути в S3. Либо загрузите данные в локальную файловую систему (и выполните вставку с помощью клиента ClickHouse), либо вставьте их напрямую в ClickHouse (см. [Вставка из S3](#inserting-from-s3)). Чтобы загрузить: ```bash theme={null} wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet ```
### Исходные данные
Ниже описаны шаги по загрузке и преобразованию исходных данных для подготовки к загрузке в ClickHouse.
#### Скачивание
Чтобы скачать исходные данные: ```bash theme={null} for i in {1900..2023}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done ```
#### Сэмплирование данных
```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 │ ᴺᵁᴸᴸ │ └─────────────┴──────────┴──────┴─────┴──────┴──────┴────┴──────┘ ``` Подытожим [документацию по формату](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn): Подытожим документацию по формату и по порядку перечислим столбцы: * 11-символьный идентификационный код станции. В нём закодирована некоторая полезная информация * YEAR/MONTH/DAY = 8-символьная дата в формате YYYYMMDD (например, 19860529 = 29 мая 1986 г.) * ELEMENT = 4-символьный индикатор типа элемента. По сути, это тип измерения. Хотя доступно множество измерений, мы выбираем следующие: * PRCP - Осадки (десятые доли мм) * SNOW - Снегопад (мм) * SNWD - Глубина снежного покрова (мм) * TMAX - Максимальная температура (десятые доли градуса C) * TAVG - Средняя температура (десятые доли градуса C) * TMIN - Минимальная температура (десятые доли градуса C) * PSUN - Доля возможного солнечного сияния за день (проценты) * AWND - Средняя скорость ветра за день (десятые доли метра в секунду) * WSFG - Максимальная скорость порыва ветра (десятые доли метра в секунду) * WT\*\* = Тип погоды, где \*\* задаёт тип погоды. Полный список типов погоды приведён здесь. * DATA VALUE = 5-символьное значение данных для ELEMENT, то есть значение измерения. * M-FLAG = 1-символьный флаг измерения. У него 10 возможных значений. Некоторые из них указывают на сомнительную точность данных. Мы принимаем данные, где указано значение "P" — отсутствие данных, предположительно равное нулю, так как это относится только к измерениям PRCP, SNOW и SNWD. * Q-FLAG — это флаг качества измерения с 14 возможными значениями. Нас интересуют только данные с пустым значением, то есть те, которые не не прошли ни одну из проверок контроля качества. * S-FLAG — это флаг источника наблюдения. Для нашего анализа он не представляет ценности и игнорируется. * OBS-TIME = 4-символьное время наблюдения в формате часы-минуты (то есть 0700 = 7:00 утра). Обычно отсутствует в старых данных. Для наших целей мы его игнорируем. Одно измерение в каждой строке привело бы к разреженной структуре таблицы в ClickHouse. Нам нужно преобразовать данные так, чтобы на каждое время и каждую станцию приходилась одна строка, а измерения были представлены в виде столбцов. Сначала мы ограничим набор данных теми строками, в которых нет проблем, то есть где `qFlag` равен пустой строке.
#### Очистка данных
Используя [ClickHouse local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local), мы можем отфильтровать строки с нужными измерениями, которые соответствуют нашим требованиям к качеству: ```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 ``` При объёме более 2,6 миллиарда строк это не самый быстрый запрос, поскольку он требует парсинга всех файлов. На нашей 8-ядерной машине это занимает около 160 секунд.
### Преобразование данных
Хотя структуру «одно измерение на строку» можно использовать с ClickHouse, в дальнейшем она лишь неоправданно усложнит запросы. В идеале нужна одна строка для каждого идентификатора станции и даты, где каждый тип измерения и соответствующее ему значение представлены в виде отдельного столбца, то есть ```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 ``` Используя ClickHouse local и простой `GROUP BY`, мы можем заново привести данные к такой структуре. Чтобы ограничить расход памяти, мы обрабатываем по одному файлу за раз. ```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 ``` Этот запрос создаёт файл `noaa.csv` размером 50 ГБ.
### Обогащение данных
В данных нет информации о местоположении, кроме идентификатора станции, который содержит код страны в качестве префикса. В идеале с каждой станцией должны быть связаны широта и долгота. Для этого NOAA предоставляет сведения о каждой станции в отдельном файле [ghcnd-stations.txt](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file). Этот файл содержит [несколько столбцов](https://github.com/awslabs/open-data-docs/tree/main/docs/noaa/noaa-ghcn#format-of-ghcnd-stationstxt-file), из которых для нашего дальнейшего анализа полезны пять: id, latitude, longitude, elevation и 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]*)'" ``` Выполнение этого запроса занимает несколько минут, в результате чего создается файл `noaa_enriched.parquet` размером 6,4 ГБ.
## Создание таблицы
Создайте таблицу MergeTree в ClickHouse с помощью клиента 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); ```
## Вставка данных в ClickHouse
### Вставка из локального файла
Данные можно вставить из локального файла следующим образом (с помощью клиента ClickHouse): ```sql theme={null} INSERT INTO noaa FROM INFILE '/noaa_enriched.parquet' ``` где `` — это полный путь к локальному файлу на диске. О том, как ускорить эту загрузку, см. [здесь](https://clickhouse.com/blog/real-world-data-noaa-climate-data#load-the-data).
### Вставка из S3
```sql theme={null} INSERT INTO noaa SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet') ``` О том, как ускорить этот процесс, читайте в нашей статье в блоге о [тонкой настройке загрузки больших объёмов данных](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part2).
## Запросы с выборкой
### Максимальная температура за всё время
```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.) ``` Что, как ни странно, согласуется с [задокументированным рекордом](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) по состоянию на 2023 год.
### Лучшие горнолыжные курорты
Используя [список горнолыжных курортов](https://gist.githubusercontent.com/gingerwizard/dd022f754fd128fdaf270e58fa052e35/raw/622e03c37460f17ef72907afe554cb1c07f91f23/ski_resort_stats.csv) в Соединённых Штатах и данные об их местоположении, мы выполняем JOIN с 1000 метеостанций, на которых в любом месяце за последние 5 лет выпало больше всего снега. Затем сортируем результаты этого JOIN по [geoDistance](/ru/reference/functions/regular-functions/geo/coordinates#geodistance) и оставляем только те, где расстояние меньше 20 км; после этого выбираем лучший результат для каждого курорта и сортируем курорты по общему количеству снега. Обратите внимание, что мы также ограничиваем выборку курортами, расположенными выше 1800 м, как грубым показателем хороших условий для катания. ```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. ```
## Благодарности
Мы хотели бы отметить вклад Global Historical Climatology Network в подготовку, очистку и распространение этих данных. Мы признательны за проделанную работу. 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. \[укажите используемое подмножество после десятичной точки, например, Version 3.25]. NOAA National Centers for Environmental Information. [http://doi.org/10.7289/V5D21VHZ](http://doi.org/10.7289/V5D21VHZ) \[17/08/2020]