ClickHouse 神助攻：纽约城市公共交通管理（MTA）数据应用挑战赛

本文字数：13198；估计阅读时间：33 分钟

作者：The PME Team

本文在公众号【ClickHouseInc】首发

我们一向对开放数据挑战充满热情，所以当发现 MTA（城市交通管理局）在其官网发起了这样的挑战时，便跃跃欲试，积极参与。我们专注于闸机数据集，以分析纽约市的地铁使用情况，并在我们新的数据游乐场中免费开放此数据供用户查询。

MTA 负责纽约市的公共交通系统，涵盖地铁、公交和通勤铁路，每天服务数百万乘客。MTA 开放数据挑战是为期一个月的竞赛，面向开发者和数据爱好者，鼓励他们利用 MTA 的数据集创造性地开发项目，比如网页应用、数据可视化或报告。参赛作品需使用 data.ny.gov 上至少一个数据集，评审标准包括创意、实用性、执行效果及透明度。

MTA 提供了 176 个数据集供参赛者使用，大部分数据集较小，通常只有几百行。尽管这些数据资源极具价值，但其体量较小，难以充分发挥 ClickHouse 的优势。

ClickHouse 是专为大规模数据分析设计的 OLAP 数据库，因此我们选择了体量最大的闸机数据集进行探索。这个数据集汇总了多年来纽约市地铁站闸机的进出记录，约有 1 亿行数据，便于分析城市人流的流动情况。尽管该数据集看似简单，但为了清理并使之具备实际应用价值，我们付出了远超预期的努力。

该数据集涵盖 2014 至 2022 年的数据。针对最近几年的数据，有一个经过清理的版本可供使用，我们也加载了此版本并提供了示例查询。不过，为了完整呈现所有数据，我们在本文中将重点关注历史数据。

本文将详细介绍加载和清理这些数据的步骤，帮助大家在自己的 ClickHouse 实例中复现这些操作。这将展示 ClickHouse 在数据工程方面的关键功能，许多操作和查询同样适用于其他数据集。

对于只关心最终数据集的用户，我们已将其加载到新的 ClickHouse playground，您可以在其中尝试超过 220 个查询和 35 个数据集！如需贡献新的查询和数据集，欢迎访问我们的 demo 仓库。

本文的所有步骤都可以使用 clickhouse-local 复现。clickhouse-local 是 ClickHouse 的轻量版，非常适合开发人员在无需安装完整数据库的情况下，通过 SQL 快速处理本地和远程文件。

数据初步探索与加载

为了简化加载，我们将闸机数据（以 TSV 文件格式）存储在一个公共存储桶中。通过简单的 S3 查询就可以查看列信息，ClickHouse 会自动推断列的类型。

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv')
SETTINGS describe_compact_output = 1

┌─name───────────────────────────────────────────────────────┬─type────────────────────┐
│ C/A                                                    	 │ Nullable(String)    	   │
│ Unit                                                   	 │ Nullable(String)    	   │
│ SCP                                                    	 │ Nullable(String)    	   │
│ Station                                                	 │ Nullable(String)    	   │
│ Line Name                                              	 │ Nullable(String)    	   │
│ Division                                               	 │ Nullable(String)    	   │
│ Date                                                   	 │ Nullable(DateTime64(9)) │
│ Time                                                   	 │ Nullable(String)    	   │
│ Description                                            	 │ Nullable(String)    	   │
│ Entries                                                	 │ Nullable(Int64)     	   │
│ Exits                                                  	 │ Nullable(Int64)     	   │
└────────────────────────────────────────────────────────────┴─────────────────────────┘


11 rows in set. Elapsed: 0.309 sec.

抽样数据并查看数据集描述后可以发现，每行数据记录了某一时间点的闸机进出人次计数。描述显示这些计数的数据是周期性报告的，统计的是上一个时间段内的情况。以下是直接在 S3 中执行的查询示例：

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv')
LIMIT 1
FORMAT Vertical

Row 1:
──────
C/A:                                                        A002
Unit:                                                       R051
SCP:                                                        02-00-00
Station:                                                    LEXINGTON AVE
Line Name:                                                  NQR456
Division:                                                   BMT
Date:                                                       2014-12-31 00:00:00.000000000
Time:                                                       23:00:00
Descript