NOAA 全球历史气候网络

过去 120 年的 25 亿行气候数据

该数据集包含过去 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 质量保证检查的行。数据还已从每行一条测量值重组为每个测站 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

该查询会生成一个 50GB 的文件 noaa.csv。

​

富化数据

这些数据除了带有国家代码前缀的测站 ID 外，没有提供任何位置信息。理想情况下，每个测站都应关联对应的纬度和经度。为此，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]*)'" 

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

​

创建表

使用 ClickHouse 客户端在 ClickHouse 中创建一个 MergeTree 表。

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 年 Furnace Creek 的有据可查的记录高度一致，着实令人放心。

​

最佳滑雪度假村

根据美国的滑雪度假村列表及其对应位置，我们将其与近 5 年中任一月份数值最高的前 1000 个气象站进行联接。按 geoDistance 对联接结果排序，并将结果限制为距离小于 20km 的记录后，我们为每个度假村选取排名最靠前的结果，再按总降雪量排序。请注意，我们还将度假村限定为海拔高于 1800m，以此作为滑雪条件较好的一个粗略指标。

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)。耗时：0.750 秒。已处理 6.891 亿行，3.20 GB（9.182 亿行/秒，4.26 GB/秒）。
峰值内存占用：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. [注明所使用的 subset，并标在小数点后，例如 Version 3.25]。NOAA National Centers for Environmental Information. http://doi.org/10.7289/V5D21VHZ [17/08/2020]