NOAA Global Historical Climatology Network

Этот набор данных содержит погодные измерения за последние 120 лет. Каждая строка — это измерение для определенного момента времени на определенной станции. Точнее, согласно источнику этих данных:

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

Загрузка данных

Заранее подготовленная версия данных для ClickHouse, очищенная, реструктурированная и обогащённая. Эти данные охватывают период с 1900 по 2022 год.
Скачать исходные данные и преобразовать их в формат, необходимый для ClickHouse. Пользователи, которые хотят добавить собственные столбцы, могут выбрать этот подход.

Заранее подготовленные данные

Точнее, были удалены строки, которые прошли все проверки качества NOAA. Данные также были преобразованы из формата «одно измерение на строку» в формат «одна строка на идентификатор станции и дату», то есть.

"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). Чтобы загрузить:

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

Исходные данные

Ниже описаны шаги по загрузке и преобразованию исходных данных для подготовки к загрузке в ClickHouse.

Скачивание

Чтобы скачать исходные данные:

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

Сэмплирование данных

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

Подытожим документацию по формату: Подытожим документацию по формату и по порядку перечислим столбцы:

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, мы можем отфильтровать строки с нужными измерениями, которые соответствуют нашим требованиям к качеству:

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, в дальнейшем она лишь неоправданно усложнит запросы. В идеале нужна одна строка для каждого идентификатора станции и даты, где каждый тип измерения и соответствующее ему значение представлены в виде отдельного столбца, то есть

"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, мы можем заново привести данные к такой структуре. Чтобы ограничить расход памяти, мы обрабатываем по одному файлу за раз.

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. Этот файл содержит несколько столбцов, из которых для нашего дальнейшего анализа полезны пять: id, latitude, longitude, elevation и 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]*)'" 

Выполнение этого запроса занимает несколько минут, в результате чего создается файл noaa_enriched.parquet размером 6,4 ГБ.

Создание таблицы

Создайте таблицу MergeTree в ClickHouse с помощью клиента 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);

Вставка данных в ClickHouse

Вставка из локального файла

Данные можно вставить из локального файла следующим образом (с помощью клиента ClickHouse):

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

где <path> — это полный путь к локальному файлу на диске. О том, как ускорить эту загрузку, см. здесь.

Вставка из S3

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

О том, как ускорить этот процесс, читайте в нашей статье в блоге о тонкой настройке загрузки больших объёмов данных.

Запросы с выборкой

Максимальная температура за всё время

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

Что, как ни странно, согласуется с задокументированным рекордом в Furnace Creek по состоянию на 2023 год.

Лучшие горнолыжные курорты

Используя список горнолыжных курортов в Соединённых Штатах и данные об их местоположении, мы выполняем JOIN с 1000 метеостанций, на которых в любом месяце за последние 5 лет выпало больше всего снега. Затем сортируем результаты этого JOIN по geoDistance и оставляем только те, где расстояние меньше 20 км; после этого выбираем лучший результат для каждого курорта и сортируем курорты по общему количеству снега. Обратите внимание, что мы также ограничиваем выборку курортами, расположенными выше 1800 м, как грубым показателем хороших условий для катания.

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.

Благодарности

Мы хотели бы отметить вклад 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 [17/08/2020]

​Загрузка данных

​Заранее подготовленные данные

​Исходные данные

​Скачивание

​Сэмплирование данных

​Очистка данных

​Преобразование данных

​Обогащение данных

​Создание таблицы

​Вставка данных в ClickHouse

​Вставка из локального файла

​Вставка из S3

​Запросы с выборкой

​Максимальная температура за всё время

​Лучшие горнолыжные курорты

​Благодарности