将真实世界的数据集加载到 ClickHouse 中通常需要经过一系列简单的步骤。这篇文章将是探索此类数据集的一系列文章中的第一部分 - 在本例中，是来自NOAA 全球历史气候网络的 10 亿行气候数据 - 逐步完成数据采样、准备、丰富和加载的典型过程，然后针对特定查询优化我们的架构。我们认为数据集的探索是改进 ClickHouse 的关键组成部分，不仅是为了发现边缘情况的问题，而且是为了识别将使用者的生活变得更轻松的功能：我们甚至使用GitHub 上的特定标签在主存储库中跟踪潜在的有趣机会。
这篇博文源于去年创建的一个问题，旨在探索 NOAA 天气数据集。此数据的不同版本以不同的格式存在，并且质量参差不齐。在这里，我们使用awslabs 发布的一个版本，该版本是来自多个来源的气候记录的组合，经过合并，并经过了一套通用的质量保证审查。在发出一些基本查询以确认历史天气记录之前，我们将重点放在清理和将数据加载到 ClickHouse 中，以及插入补充数据集的更高级功能。
对于我们的客户端机器，我们使用具有 16 个核心和 32GB RAM 的c5ad.4xlarge。ClickHouse 托管在具有 16 个核心和 64GB RAM 的 AWSm5d.4xlarge 实例上。我们还将这些数据加载到play.clickhouse.com，供用户进行实验，并在允许的情况下链接示例查询。
下载数据
随着 ClickHouse最近添加了对 1900 年日期的支持，我们可以下载 1900 年到 2022 年的数据。如果使用的是旧版本的 ClickHouse，请将范围限制在 1925 年 - 因此后续查询结果显然会有所不同。数据集以 csv 和压缩 gz 两种格式提供。由于 ClickHouse 可以原生读取 gz，因此我们更喜欢后者，因为它的大小要小得多 (12GB 对比 100GB)。
for i in {1900..2022}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done
下载此数据集大约需要 10 分钟，具体取决于您的连接。
数据采样
在上个世纪，人类极大地增加了他们关于天气的资料收集工作：代表 1900 年的文件有 460 万行，而 2022 年的文件则接近 3600 万行。通过对数据进行采样，我们可以看到它以每行一个测量的格式分布，即
zcat 2021.csv.gz | head
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 = 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-local，这是一个允许处理本地文件的强大工具，我们可以过滤代表我们感兴趣的测量并通过我们的质量要求的行，同时避免安装和运行 ClickHouse。
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'))"
2679255970
由于涉及解析所有文件，因此对于超过 26 亿行的查询来说，这并不是一个快速的查询。在我们的客户端机器上，这大约需要 160 秒。
注意：完整的数据集包含 2,956,750,089 行。通过排除具有质量问题的行，我们仅丢弃了 0.3% 的行。
准备数据
虽然每行一个测量的结构可以与 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，我们可以将我们的数据重新转换为这种结构。
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('./raw/*.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String')
WHERE qFlag = ''
GROUP BY station_id, date
ORDER BY station_id, date FORMAT CSVWithNames;" > noaa.csv
这是一个非常占用内存的查询。为了在较小的机器上执行此工作，我们可以请求聚合溢出到磁盘，方法是使用max_bytes_before_external_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 = ''
GROUP BY station_id, date
ORDER BY station_id, date FORMAT TSV" >> "noaa.tsv";
done
此查询将花费一些时间。为了加速，我们有几个选择：要么通过处理多个文件进行并行化，要么将数据加载到 ClickHouse 实例中并利用 INSERT SELECT 来根据需要重新定向数据。我们在“最终丰富”中探讨了后一种方法。
丰富数据
我们当前的数据除了站点 ID（包含国家代码前缀）外，没有其他位置信息。理想情况下，每个站点都应该关联经度和纬度。为了实现这一点，NOAA 方便地提供了每个站点的详细信息，作为一个单独的 ghcnd-stations.txt 文件。此文件包含 多个列，其中五个对我们未来的分析很有用：ID、纬度、经度、海拔和名称。
为了解析此文件，我们使用带有简单正则表达式组捕获每一列的 Regexp 格式。我们将它与我们新创建的 noaa.tsv 文件进行连接。
wget http://noaa-ghcn-pds.s3.amazonaws.com/ghcnd-stations.txt
clickhouse-local --query "WITH stations AS (SELECT id, lat, lon, elevation, 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.tsv', TSV,
'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 FORMAT TSV SETTINGS format_regexp='^(.{11})\s+(\-?\d{1,2}\.\d{4})\s+(\-?\d{1,3}\.\d{1,4})\s+(\-?\d*\.\d*)\s+(.*?)\s{2,}.*$'" > noaa_enriched.tsv
请注意，我们将经度和纬度捕获为一个点，表示为元组 location。我们连接后的数据大约 83GB。
创建我们的表
超过 10 亿行的规模，对于 ClickHouse 来说是一个相当小的数据集，单个节点和 MergeTree 表可以轻松处理。请注意，使用 ClickHouse Cloud 的用户 - 下面的 DDL 语句将透明地创建一个复制的合并树（甚至可以省略 ENGINE）。我们将在以后的博文中优化下面的模式，目前，我们将使用一个简单的定义。枚举允许我们捕获不同类型的事件。
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);
加载数据
之前对数据进行透视和清理的工作确保了加载现在变得非常简单。最简单的加载方法依赖于 INFILE 子句，它接受加载的本地文件的路径。我们在 clickhouse-client 中执行此操作，以确保我们收到执行时间和每秒行数等详细信息。
对于更有经验的 ClickHouse 用户来说，82GB 在 195 秒内加载可能看起来有点慢。我们可以通过多种方式加速此过程，最快的方法是并行化插入。这要求我们将文件拆分并为每个文件调用 clickhouse-client。完整的优化任务超出了本文的范围，但以下示例演示了如何将文件拆分为 16 个相等的部分，然后并行地在每个文件上调用客户端。INSERT INTO noaa(station_id, date, tempAvg, tempMax, tempMin, precipitation, snowfall, snowDepth, percentDailySun, averageWindSpeed, maxWindSpeed, weatherType, location, elevation, name) FROM INFILE '/data/blog/noaa_enriched.tsv' FORMAT TSV; 1076229294 rows in set. Elapsed: 195.762 sec.
// split the file into roughly 16 equal parts
time split -l 67264331 noaa_enriched_2.tsv noaa_split
real 6m36.569s
// insert each file via clickhouse-client using a 16 separate process for each client
time find . -type f -name 'noaa_split*' | xargs -P 16 -n 1 -I {} sh -c "clickhouse-client --query 'INSERT INTO noaa(station_id, date, tempAvg, tempMax, tempMin, precipitation, snowfall, snowDepth, percentDailySun, averageWindSpeed, maxWindSpeed, weatherType, location, elevation, name) FORMAT TSV' < '{}'"
real 2m15.047s
这个简单的更改，虽然可能没有必要，但已将我们的加载时间缩短到 135 秒或大约 620 MB/秒。对于更专业的读者来说，这与我们客户端磁盘在顺序读取工作负载下测量的最大读取性能一致，该性能是通过 fio 针对 c5ad.4xlarge 测量的 - 基于 此处 的步骤）。此处的任何收益都被文件拆分所需的时间抵消，因此只有当您的文件已分成多个部分时，这种方法才有意义。我们可以在我们的丰富步骤中生成多个文件，以便以后利用此优化。
一些简单的查询
数据加载完成后，我们很想看看它与 有据可查的历史天气记录 相比如何。虽然存在争议，但“根据 世界气象组织 (WMO) 的说法，有记录以来最高温度是 56.7 °C (134.1 °F)，发生在 1913 年 7 月 10 日的 炉溪 (格陵兰牧场)，加利福尼亚州，美国”。
让我们通过一个简单的查询来确认这一点 - 请注意，我们将第一个达到特定温度的位置归为第一名（数据集包含自 1913 年以来几个达到 56.7 度的位置）并过滤掉温度记录超过 50C 的位置
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.107 sec. Processed 1.08 billion rows, 4.35 GB (10.03 billion rows/s., 40.60 GB/s.)✎
令人欣慰的是，这与 记录在案的记录 一致，该记录位于 炉溪。
最终丰富
任何未来更复杂的天气分析可能都需要能够识别特定地理区域的天气事件。理论上，一个区域可以通过 Polygon 甚至 MultiPolygon（多边形的数组）来定义，这些多边形由经纬度坐标组成。可以使用 pointinpolygon 查询来检查天气事件是否属于某个多边形。我们将在以后的博文中为更有趣的多边形使用此功能，但目前，我们将考虑国家。
Datahub.io 提供了许多有用的较小数据集，包括世界所有国家/地区的多边形列表 以 geojson 格式分发。这使我们能够演示使用 url 函数导入函数并在插入时执行数据转换。
此单个 JSON 文件包含国家/地区的多边形，作为数组“features”的元素。为了获得每个国家/地区的一行及其相应的多边形，我们转向 arrayJoin 和 JSONExtractArrayRaw 函数。
SELECT arrayJoin(JSONExtractArrayRaw(json, 'features')) AS json FROM url('https://datahub.io/core/geo-countries/r/0.geojson', JSONAsString) LIMIT 1 FORMAT JSONEachRow {"json":"{\"type\":\"Feature\",\"properties\":{\"ADMIN\":\"Aruba\",\"ISO_A3\":\"ABW\"},\"geometry\":{\"type\":\"Polygon\",\"coordinates\":[[[-69.99693762899992,12.577582098000036],[-69.93639075399994,12.53172435100005],[-69.92467200399994,12.519232489000046],[-69.91576087099992,12.497015692000076],[-69.88019771999984,12.453558661000045],[-69.87682044199994,12.427394924000097],[-69.88809160099993,12.417669989000046],[-69.90880286399994,12.417792059000107],[-69.93053137899989,12.425970770000035],[-69.94513912699992,12.44037506700009],[-69.92467200399994,12.44037506700009],[-69.92467200399994,12.447211005000014],[-69.95856686099992,12.463202216000099],[-70.02765865799992,12.522935289000088],[-70.04808508999989,12.53115469000008],[-70.05809485599988,12.537176825000088],[-70.06240800699987,12.546820380000057],[-70.06037350199995,12.556952216000113],[-70.0510961579999,12.574042059000064],[-70.04873613199993,12.583726304000024],[-70.05264238199993,12.600002346000053],[-70.05964107999992,12.614243882000054],[-70.06110592399997,12.625392971000068],[-70.04873613199993,12.632147528000104],[-70.00715084499987,12.5855166690001],[-69.99693762899992,12.577582098000036]]]}}"}
这仍然使我们每个国家/地区都有一个 JSON 块。指示国家/地区及其相应 ISO 代码的字段分别是 ADMIN 和 ISO_A3。国家/地区可以是多边形（当它们是单个连续陆地区域时）或多边形（当它们具有复杂的陆地区域时），例如希腊及其岛屿。幸运的是，type 字段指示哪种情况。为了确保未来的查询得到简化，我们将使用一些简单的 条件 逻辑将所有多边形转换为多边形。我们在 INSERT SELECT 语句（我们在过去的帖子中使用过的一个功能）中使用此语句，以将国家/地区数据插入到表中。
CREATE TABLE countries ( `name` String, `coordinates` MultiPolygon ) ENGINE = MergeTree ORDER BY name INSERT INTO countries SELECT name, coordinates FROM ( SELECT JSONExtractString(JSONExtractString(json, 'properties'), 'ADMIN') AS name, JSONExtractString(JSONExtractRaw(json, 'geometry'), 'type') AS type, if(type = 'Polygon', [JSONExtract(JSONExtractRaw(JSONExtractRaw(json, 'geometry'), 'coordinates'), 'Polygon')], JSONExtract(JSONExtractRaw(JSONExtractRaw(json, 'geometry'), 'coordinates'), 'MultiPolygon')) AS coordinates FROM ( SELECT arrayJoin(JSONExtractArrayRaw(json, 'features')) AS json FROM url('https://datahub.io/core/geo-countries/r/0.geojson', JSONAsString) ) )
有了这个补充数据集，我们可以使用 pointInPolygon 函数来识别发生在葡萄牙多边形内的天气事件。请注意，我们必须使用 arrayExists 遍历多边形字段的多边形。葡萄牙本身有 17 个多边形来捕获其边界 - 结果是，每个天气事件都需要进行这么多次比较才能找到有记录以来最热的一天。
WITH ( SELECT coordinates FROM countries WHERE name = 'Portugal' ) AS pCoords SELECT tempMax, station_id, date, location FROM noaa WHERE arrayExists(cord -> pointInPolygon(location, cord), pCoords) ORDER BY tempMax DESC LIMIT 5 ┌─tempMax─┬─station_id──┬───────date─┬─location──────────┐ │ 458 │ PO000008549 │ 1944-07-30 │ (-8.4167,40.2) │ │ 454 │ PO000008562 │ 2003-08-01 │ (-7.8667,38.0167) │ │ 452 │ PO000008562 │ 1995-07-23 │ (-7.8667,38.0167) │ │ 445 │ POM00008558 │ 2003-08-01 │ (-7.9,38.533) │ │ 442 │ POM00008558 │ 2022-07-13 │ (-7.9,38.533) │ └─────────┴─────────────┴────────────┴───────────────────┘ 10 rows in set. Elapsed: 3388.576 sec. Processed 1.06 billion rows, 46.83 GB (314.06 thousand rows/s., 13.82 MB/s.
虽然响应与 葡萄牙的历史记录 一致，但此查询非常慢，需要近一个小时。对于更复杂的国家/地区（例如加拿大），此查询将不可行。
SELECT name, length(coordinates) AS num_coordinates FROM countries ORDER BY num_coordinates DESC LIMIT 5 ┌─name─────────────────────┬─num_coordinates─┐ │ Canada │ 410 │ │ United States of America │ 346 │ │ Indonesia │ 264 │ │ Russia │ 213 │ │ Antarctica │ 179 │ └──────────────────────────┴─────────────────┘
为了加速此查询，我们可以使用 多边形字典。这些允许用户有效地搜索包含指定点的多边形。我们主要使用默认值来定义我们的字典，使用国家/地区表作为其来源。
我们的查询随后变为CREATE DICTIONARY country_polygons ( `name` String, `coordinates` MultiPolygon ) PRIMARY KEY coordinates SOURCE(CLICKHOUSE(TABLE 'countries')) LIFETIME(MIN 0 MAX 0) LAYOUT(POLYGON(STORE_POLYGON_KEY_COLUMN 1))
这样好多了*！对于最糟糕的情况加拿大呢？SELECT tempMax / 10 AS maxTemp, station_id, date, location FROM noaa WHERE dictGet(country_polygons, 'name', location) = 'Portugal' ORDER BY tempMax DESC LIMIT 5✎Query id: bfb88bc1-4c1d-4808-bd2d-3a2406d387c3
┌─maxTemp─┬─station_id──┬───────date─┬─location──────────┐ │ 45.8 │ PO000008549 │ 1944-07-30 │ (-8.4167,40.2) │ │ 45.4 │ PO000008562 │ 2003-08-01 │ (-7.8667,38.0167) │ │ 45.2 │ PO000008562 │ 1995-07-23 │ (-7.8667,38.0167) │ │ 44.5 │ POM00008558 │ 2003-08-01 │ (-7.9,38.533) │ │ 44.2 │ POM00008558 │ 2022-07-13 │ (-7.9,38.533) │ └─────────┴─────────────┴────────────┴───────────────────┘
5 rows in set. Elapsed: 14.498 sec. Processed 1.06 billion rows, 46.83 GB (73.40 million rows/s., 3.23 GB/s.)
*请注意，第一次发出此查询时，字典将被加载到内存中 - 这大约需要 5 秒。后续查询应该保持一致。为了使用一个简单的查询加载字典，我们可以发出一个简单的 dictGet。例如：
SELECT dictGet(country_polygons, 'name', (-9.3704, 38.8027));
SELECT tempMax / 10 AS maxTemp, station_id, date, location FROM noaa WHERE dictGet(country_polygons, 'name', location) = 'Canada' ORDER BY tempMax DESC LIMIT 5 ┌─maxTemp─┬─station_id──┬───────date─┬─location───────┐ │ 47.3 │ CA001163780 │ 2021-06-30 │ (-120.45,50.7) │ │ 46.6 │ CA001163780 │ 2021-07-01 │ (-120.45,50.7) │ │ 46.2 │ CA001163842 │ 2021-06-30 │ (-120.45,50.7) │ │ 45.3 │ CA001163842 │ 2021-07-01 │ (-120.45,50.7) │ │ 45 │ CA004015160 │ 1937-07-05 │ (-103.4,49.4) │ └─────────┴─────────────┴────────────┴────────────────┘ 5 rows in set. Elapsed: 14.481 sec. Processed 1.06 billion rows, 46.83 GB (73.49 million rows/s., 3.23 GB/s.)✎
类似的性能并且独立于多边形的复杂性！也与 加拿大记录 一致 - 尽管位置和确切温度略有 偏差。
在实现此特定查询时，很明显，station_id 的前两个字母表示国家/地区 - 这是一堂了解数据的课程。下面显示了上述查询的更简单（更快）的等效查询。但是，多边形字典确实提供了更灵活的解决方案，因为它们允许我们匹配任意陆地区域。
最后，作为一个狂热的滑雪爱好者，我个人很好奇过去 5 年中美国最好的滑雪胜地在哪里。SELECT tempMax / 10 AS maxTemp, station_id, date, location FROM noaa WHERE substring(station_id, 1, 2) = 'CA' ORDER BY tempMax DESC LIMIT 5 ┌─maxTemp─┬─station_id──┬───────date─┬─location───────┐ │ 47.3 │ CA001163780 │ 2021-06-30 │ (-120.45,50.7) │ │ 46.6 │ CA001163780 │ 2021-07-01 │ (-120.45,50.7) │ │ 46.2 │ CA001163842 │ 2021-06-30 │ (-120.45,50.7) │ │ 45.3 │ CA001163842 │ 2021-07-01 │ (-120.45,50.7) │ │ 45 │ CA004015160 │ 1937-07-05 │ (-103.4,49.4) │ └─────────┴─────────────┴────────────┴────────────────┘ 5 rows in set. Elapsed: 3.000 sec. Processed 1.06 billion rows, 22.71 GB (354.76 million rows/s., 7.57 GB/s.)✎
使用 美国滑雪胜地 列表及其相应位置，我们将这些位置与过去 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/Ewiseman/b251e5eaf70ca52a4b9b10dce9e635a4/raw/9f0100fe14169a058c451380edca3bda24d3f673/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: 2.119 sec. Processed 989.55 million rows, 24.99 GB (467.07 million rows/s., 11.80 GB/s.)
在本系列的下一篇文章中，我们将更详细地研究此数据集，并尝试回答一些更有趣的问题，以及探索地理数据的可视化技术。
鸣谢
我们要感谢全球历史气候网络为准备、清理和分发这些数据所付出的努力。感谢你们的努力。
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-Daily)，版本 3。[在小数点后指示所用子集，例如版本 3.25]。NOAA 国家环境信息中心。 http://doi.org/10.7289/V5D21VHZ [2020 年 8 月 17 日]
