NOAA 全球历史气候网络

此数据集包含过去 120 年的天气测量数据。每一行都是某个时间点和气象站的测量数据。

更准确地说，根据此数据来源

GHCN-Daily 是一个数据集，包含全球陆地区域的每日观测数据。它包含来自全球陆基气象站的基于气象站的测量数据，其中约三分之二是仅用于降水测量（Menne 等人，2012）。GHCN-Daily 是来自众多来源的气候记录的组合，这些记录被合并在一起并接受了一套通用的质量保证审查（Durre 等人，2010）。该档案包含以下气象要素

- Daily maximum temperature
- Daily minimum temperature
- Temperature at the time of observation
- Precipitation (i.e., rain, melted snow)
- Snowfall
- Snow depth
- Other elements where available

下载数据

一个预处理版本供 ClickHouse 使用的数据，已进行清理、重构和丰富。这些数据涵盖了 1900 年到 2022 年。
下载原始数据并将其转换为 ClickHouse 所需的格式。希望添加自己列的用户可能希望探索这种方法。

预处理数据

更具体地说，已删除未通过 Noaa 任何质量保证检查的行。数据也已从每行一个测量值重构为每个气象站 ID 和日期一行，即

"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 = YYYYMMDD 格式的 8 个字符日期（例如 19860529 = 1986 年 5 月 29 日）
ELEMENT = 元素类型的 4 个字符指示符。有效地测量类型。虽然有许多可用的测量值，但我们选择以下测量值
- PRCP - 降水量（十分之一毫米）
- SNOW - 降雪量（毫米）
- SNWD - 雪深（毫米）
- TMAX - 最高温度（十分之一摄氏度）
- TAVG - 平均温度（十分之一摄氏度）
- TMIN - 最低温度（十分之一摄氏度）
- PSUN - 每日可能日照百分比（百分比）
- AWND - 平均每日风速（十分之一米/秒）
- WSFG - 峰值阵风风速（十分之一米/秒）
- WT = 天气类型，其中定义天气类型。此处列出了完整的天候类型。
DATA VALUE = ELEMENT 的 5 个字符数据值，即测量的值。
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

超过 26 亿行，这并不是一个快速的查询，因为它涉及解析所有文件。在我们的 8 核机器上，这大约需要 160 秒。

数据透视

虽然每行一个测量值的结构可以与 ClickHouse 一起使用，但它会不必要地使将来的查询复杂化。理想情况下，我们需要每个气象站 ID 和日期一行，其中每个测量类型和关联值都是一列，即

"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 的 50GB 单个文件。

丰富数据

除了包含前缀国家代码的气象站 ID 外，数据没有其他位置指示。理想情况下，每个气象站都应关联经纬度。为了实现这一点，NOAA 方便地提供了每个气象站的详细信息作为单独的ghcnd-stations.txt。此文件有几列，其中五列对我们的未来分析有用：id、纬度、经度、海拔和名称。

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]*)'" 

此查询需要几分钟才能运行，并生成一个 6.4 GB 的文件 noaa_enriched.parquet。

创建表

在 ClickHouse 中创建一个 MergeTree 表（来自 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.)

令人欣慰的是，这与截至 2023 年记录在案的熔炉溪数据保持一致。

最佳滑雪胜地

我们使用美国滑雪胜地列表及其各自的位置，并将它们与过去 5 年中任何月份降雪量最多的前 1000 个气象站进行关联。通过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.

鸣谢

我们谨向全球历史气候网络（GHCN）为准备、清理和分发这些数据所付出的努力表示感谢。感谢你们的辛勤付出。

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: 全球历史气候网络 - 每日（GHCN-每日），版本 3。（在小数点后标明使用的子集，例如版本 3.25）. NOAA 国家环境信息中心。 http://doi.org/10.7289/V5D21VHZ[17/08/2020]

NOAA 全球历史气候网络

下载数据​

预处理数据​

原始数据​

下载​

数据采样​

清理数据​

数据透视​

丰富数据​

创建表​

插入 ClickHouse​

从本地文件插入​

从 S3 插入​

示例查询​

有史以来最高温度​

最佳滑雪胜地​

鸣谢​