使用 ClickHouse 进行云观鸟是什么体验？-优快云博客

本文链接：https://blog.youkuaiyun.com/ClickHouseDB/article/details/148688689

本文字数：6760；估计阅读时间：17 分钟

作者：Alexey Milovidov

本文在公众号【ClickHouseInc】首发

在活动的最后一天，我才第一次听说“观鸟”这个词，是在偶然间发现了康奈尔鸟类学实验室的 eBird 项目。这个项目提供了一个全球鸟类观察记录的数据集，包含 15 亿条观测记录，并且每月更新。

我马上把这个数据集导入了自己的可视化工具中。其实，adsb.exposed 网站已经托管了一个包含超过 1300 亿条记录的航空交通数据集，以及一个 Foursquare 地点信息的数据集。虽然天上的鸟可能比飞机多，但这个鸟类观察数据集的体量仅为航空数据的百分之一，因此非常容易加载和分析。

数据集

这个数据集可以通过一个 58 GB 大小的 zip 压缩包获取，压缩包里包含了一个高达 440 GB 的 CSV 文件。

下载数据集

wget 'https://hosted-datasets.gbif.org/eBird/2023-eBird-dwca-1.0.zip'

使用 clickhouse-local 工具分析本地或外部数据集非常方便。clickhouse-local 是一个轻量级命令行工具，但具备完整的 ClickHouse 引擎功能。

下载 clickhouse-local

curl https://clickhouse.com/ | sh
sudo ./clickhouse install # or run it as ./clickhouse without installation

让我们来预览一下数据集的结构

ClickHouse 支持在不解压的前提下直接处理压缩包中的文件。

DESCRIBE file('2023-eBird-dwca-1.0.zip :: *.csv');

    ┌─name──────────────┬─type──────────────┐
 1. │ basisofrecord     │ Nullable(String)  │
 2. │ institutioncode   │ Nullable(String)  │
 3. │ collectioncode    │ Nullable(String)  │
 4. │ catalognumber     │ Nullable(String)  │
 5. │ occurrenceid      │ Nullable(String)  │
 6. │ recordedby        │ Nullable(String)  │
 7. │ year              │ Nullable(Int64)   │
 8. │ month             │ Nullable(Int64)   │
 9. │ day               │ Nullable(Int64)   │
10. │ publishingcountry │ Nullable(String)  │
11. │ country           │ Nullable(String)  │
12. │ stateprovince     │ Nullable(String)  │
13. │ county            │ Nullable(String)  │
14. │ decimallatitude   │ Nullable(Float64) │
15. │ decimallongitude  │ Nullable(Float64) │
16. │ locality          │ Nullable(String)  │
17. │ kingdom           │ Nullable(String)  │
18. │ phylum            │ Nullable(String)  │
19. │ class             │ Nullable(String)  │
20. │ order             │ Nullable(String)  │
21. │ family            │ Nullable(String)  │
22. │ genus             │ Nullable(String)  │
23. │ specificepithet   │ Nullable(String)  │
24. │ scientificname    │ Nullable(String)  │
25. │ vernacularname    │ Nullable(String)  │
26. │ taxonremarks      │ Nullable(String)  │
27. │ taxonconceptid    │ Nullable(String)  │
28. │ individualcount   │ Nullable(Int64)   │
    └───────────────────┴───────────────────┘

使用 DESCRIBE 查询，可以自动推断出数据的结构。file 表函数可用于处理本地文件，而使用 :: 符号可以访问压缩文件中的内容。文件名支持丰富的通配符模式，比如 *, ?, **, {0..9}, {abc,def} 等。对于 CSV 文件，ClickHouse 能自动识别是否包含表头，并在存在表头时直接使用，同时还能自动识别每一列的数据类型。借助这些便捷功能，ClickHouse 极大地简化了数据处理流程。

预览数据

SELECT * FROM file('2023-eBird-dwca-1.0.zip :: *.csv') LIMIT 1;

Row 1:
──────
basisofrecord:     HumanObservation
institutioncode:   CLO
collectioncode:    EBIRD_ARG
catalognumber:     OBS602415301
occurrenceid:      URN:catalog:CLO:EBIRD_ARG:OBS602415301
recordedby:        obsr904254
year:              1989
month:             4
day:               23
publishingcountry: AR
country:           Argentina
stateprovince:     Salta
county:            Anta
decimallatitude:   -24.7
decimallongitude:  -64.63333
locality:          PN El Rey
kingdom:           Animalia
phylum:            Chordata
class:             Aves
order:             Pelecaniformes
family:            Threskiornithidae
genus:             Theristicus
specificepithet:   caudatus
scientificname:    Theristicus caudatus
vernacularname:    Buff-necked Ibis
taxonremarks:      ᴺᵁᴸᴸ
taxonconceptid:    avibase-5E393799
individualcount:   ᴺᵁᴸᴸ

1 row in set. Elapsed: 0.008 sec.

看，这是在阿根廷拍到的一只朱鹭！【https://adsb.exposed/?dataset=Birds&zoom=19&lat=-17.9143&lng=-63.1646&query=35198a853912e85c5a96e9767ebe7e0c&box=-17.0568,-65.6250,-19.8356,-62.2266】

加载数据到 ClickHouse

虽然使用 clickhouse-local 就能完成数据分析，但为了搭建交互式网站，我们选择将数据加载进 clickhouse-server。值得一提的是，clickhouse-local 和 clickhouse-server 本质上是同一个可执行文件，主要区别在于后者具备监听连接的能力。

表结构设计

我创建了如下结构的表：

CREATE TABLE birds_mercator
(
    basisofrecord LowCardinality(String),
    institutioncode LowCardinality(String),
    collectioncode LowCardinality(String),
    catalognumber String,
    occurrenceid String,
    recordedby String,
    year UInt16 EPHEMERAL,
    month UInt8 EPHEMERAL,
    day UInt8 EPHEMERAL,
    publishingcountry LowCardinality(String),
    country LowCardinality(String),
    stateprovince LowCardinality(String),
    county LowCardinality(String),
    decimallatitude Float32,
    decimallongitude Float32,
    locality LowCardinality(String),
    kingdom LowCardinality(String),
    phylum LowCardinality(String),
    class LowCardinality(String),
    order LowCardinality(String),
    family LowCardinality(String),
    genus LowCardinality(String),
    specificepithet LowCardinality(String),
    scientificname LowCardinality(String),
    vernacularname LowCardinality(String),
    taxonremarks LowCardinality(String),
    taxonconceptid LowCardinality(String),
    individualcount UInt32,

    date Date32 MATERIALIZED makeDate32(year, month, day),
    mercator_x UInt32 MATERIALIZED 0xFFFFFFFF * ((decimallongitude + 180) / 360),
    mercator_y UInt32 MATERIALIZED 0xFFFFFFFF * ((1 / 2) - ((log(tan(((decimallatitude + 90) / 360) * pi())) / 2) / pi())),
    INDEX idx_x mercator_x TYPE minmax,
    INDEX idx_y mercator_y TYPE minmax
)
ORDER BY mortonEncode(mercator_x, mercator_y)

整体结构大体沿用了从 CSV 文件中推断出的字段定义。

我去除了 Nullable 类型，因为这个字段在本场景中并不必要。使用空字符串代替 NULL 更加高效，也符合最佳实践。

同时，我将 year、month 和 day 字段改为 EPHEMERAL（临时）列，并新增了一个 date 字段：
date Date MATERIALIZED makeDate(year, month, day)
EPHEMERAL 列不会存储在表中，但可以在 INSERT 时参与计算表达式，用于数据插入时的转换。而 MATERIALIZED 列则相反，它们不能在 INSERT 中赋值，而是始终由表达式自动生成。

我还分析了多个字段的唯一值数量，将部分 String 类型字段替换为 LowCardinality(String)，以启用字典编码。例如 country 字段只包含 253 个不同值。

此外，我添加了两个物化列 mercator_x 和 mercator_y，用于将地理坐标转换为 Web Mercator 投影下的平面坐标。这两个坐标由 UInt32 表示，有利于地图瓦片分割。我还基于这两个字段使用空间填充曲线设定了排序顺序，并增加了两个 minmax 索引，以提升查询性能。ClickHouse 在构建实时地图应用方面具备全面能力。

数据加载

接下来，我使用如下查询加载数据：

ch --progress --query "
    SELECT * FROM file('2023-eBird-dwca-1.0.zip :: eod.csv')
    WHERE decimallatitude BETWEEN -89 AND 89
      AND decimallongitude BETWEEN -180 AND 180
    FORMAT Native" \
| clickhouse-client --host ... --query "INSERT INTO birds_mercator (basisofrecord, institutioncode, collectioncode, catalognumber, occurrenceid, recordedby, year, month, day, publishingcountry, country, stateprovince, county, decimallatitude, decimallongitude, locality, kingdom, phylum, class, order, family, genus, specificepithet, scientificname, vernacularname, taxonremarks, taxonconceptid, individualcount) FORMAT Native"

我通过 clickhouse-local（安装后可使用 ch 命令调用）过滤掉了不符合投影要求的经纬度值，并将数据转换为 Native 格式，这是 ClickHouse 最适合插入的格式。转换后的数据通过管道传给 clickhouse-client，最终写入部署在 ClickHouse Cloud 中的服务。

在 ClickHouse 中，整个数据表仅占用 16.8 GB 的空间，远远小于原始 58 GB 的 zip 文件，这得益于 ClickHouse 出色的压缩能力。每条观测数据平均仅需 11 字节空间。

SELECT name, total_rows, total_bytes FROM system.tables WHERE name = 'birds_mercator'

┌─name───────────┬─total_rows─┬─total_bytes─┐
│ birds_mercator │ 1512208407 │ 16847994349 │
└────────────────┴────────────┴─────────────┘

数据可视化

我通过简单的配置调整将这个数据集添加进系统【https://github.com/ClickHouse/adsb.exposed/pull/42】，现在可以立即开始探索！

例如，可以根据不同的鸟类种类来为地图上色：

我们可以只绘制海鸟的分布情况：

在巴塔哥尼亚附近的南大西洋区域，观察鸟类的迁徙路径非常有趣：

在新西兰地区同样也非常值得一看：

接下来我们来筛选企鹅的记录：

如果我们在筛选条件中加入 AND family = 'Apterygidae'，就能看到各种不同种类的几维鸟：

看到这只鸟，绝对不虚此行！

这只乌鸦只分布在亚洲：

与其他工具对比

eBird 本身提供了地图可视化功能【https://secure.birds.cornell.edu/cassso/login?service=https%3A%2F%2Febird.org%2Flogin%2Fcas%3Fportal%3Debird&locale=zh_CN】，但交互性较弱，无法深入分析数据，也不支持直接运行 SQL 查询。

总结

ClickHouse 是处理大规模地理数据分析的理想选择。eBird 数据集拥有 15 亿条记录，而 ADS-B 数据集已有 1300 亿条记录，并且仍在持续增长。ClickHouse 客户处理的数据集甚至超过数十万亿条。ClickHouse 让海量数据真正“飞”起来！

参考资料：

eBird. 2021. eBird: An online database of bird distribution and abundance [web application]. eBird, Cornell Lab of Ornithology, Ithaca, New York.
地址：https://www.ebird.org（访问时间：2025 年 6 月 1 日）

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com