跳到主要内容
跳到主要内容
编辑此页

NOAA 全球历史气候学网络

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

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

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

  • 每日最高温度
  • 每日最低温度
  • 观测时的温度
  • 降水（即雨、融化的雪）
  • 降雪
  • 积雪深度
  • 其他可用要素

以下章节简要概述了将此数据集引入 ClickHouse 所涉及的步骤。如果您有兴趣更详细地了解每个步骤，我们建议您查看我们的博客文章，标题为 “探索海量的真实世界数据集：ClickHouse 中 100 多年的天气记录”。

下载数据​

  • 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 - 降雪量 (mm)
    • SNWD - 积雪深度 (mm)
    • 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、纬度、经度、海拔和名称。

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.

鸣谢​

我们要感谢全球历史气候学网络为准备、清洗和分发此数据所做的努力。我们感谢你们的努力。

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. [indicate subset used following decimal, e.g. Version 3.25]. NOAA National Centers for Environmental Information. http://doi.org/10.7289/V5D21VHZ [17/08/2020]