纽约出租车数据
纽约出租车数据包含自2009年以来自纽约市发起的超过30亿次出租车和租车(Uber, Lyft等)旅行。可以通过几种方式获取该数据集:
- 直接从 S3 或 GCS 将数据插入 ClickHouse Cloud
- 下载准备好的分区
创建表 trips
首先创建一个出租车乘车记录的表:
从对象存储直接加载数据
让我们获取一小部分数据以熟悉它。这些数据存储在对象存储中的 TSV 文件中,可以通过 s3
表函数轻松地流入 ClickHouse Cloud。
相同的数据同时存储在 S3 和 GCS 中;请选择任一标签。
- GCS
- S3
以下命令会将三个文件从 GCS 存储桶流入 trips
表中({0..2}
语法是对值 0,1 和 2 的通配符):
以下命令会将三个文件从 S3 存储桶流入 trips
表中({0..2}
语法是对值 0,1 和 2 的通配符):
示例查询
让我们看看插入了多少行:
每个 TSV 文件大约有 100 万行,三个文件共有 3,000,317 行。让我们看看几行数据:
请注意,有关于接送日期、地理坐标、费用详情、纽约社区等列:
让我们运行几个查询。这个查询显示接送次数最多的前10个社区:
结果是:
这个查询显示根据乘客人数的平均收费:
这是乘客人数与旅行距离之间的相关性:
结果的第一部分是:
下载准备好的分区
以下步骤提供了有关原始数据集的信息,以及将准备好的分区加载到自管理的 ClickHouse 服务器环境中的方法。
请访问 https://github.com/toddwschneider/nyc-taxi-data 和 http://tech.marksblogg.com/billion-nyc-taxi-rides-redshift.html,获取数据集的描述和下载说明。
下载将导致约227GB未压缩的数据,以 CSV 文件格式存储。通过 1 Gbit 的连接下载大约需要一个小时(从 s3.amazonaws.com 进行并行下载可以恢复至少一半的 1 Gbit 通道)。 某些文件可能无法完全下载。检查文件大小并重新下载任何看起来可疑的文件。
如果您将运行下面描述的查询,则必须使用完整的表名 datasets.trips_mergetree
。
单台服务器上的结果
Q1:
0.490 秒。
Q2:
1.224 秒。
Q3:
2.104 秒。
Q4:
3.593 秒。
使用的服务器配置如下:
两个 Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz,总计 16 个物理核心,128 GiB RAM,8x6 TB 硬盘,硬件 RAID-5
执行时间为三次运行的最佳时间。但从第二次运行开始,查询从文件系统缓存中读取数据。不再进行进一步的缓存:每次运行都会读取和处理数据。
在三台服务器上创建表:
在每台服务器上:
在源服务器上:
以下查询会重新分配数据:
这需要 2454 秒。
在三台服务器上:
Q1: 0.212 秒。 Q2: 0.438 秒。 Q3: 0.733 秒。 Q4: 1.241 秒。
这没有惊讶,因为查询的时间是线性扩展的。
我们也有 140 台服务器集群的结果:
Q1: 0.028 秒。 Q2: 0.043 秒。 Q3: 0.051 秒。 Q4: 0.072 秒。
在这种情况下,查询处理时间主要由网络延迟决定。 我们使用位于不同数据中心的客户端运行查询,这增加了大约 20 毫秒的延迟。
摘要
服务器 | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|
1, E5-2650v2 | 0.490 | 1.224 | 2.104 | 3.593 |
3, E5-2650v2 | 0.212 | 0.438 | 0.733 | 1.241 |
1, AWS c5n.4xlarge | 0.249 | 1.279 | 1.738 | 3.527 |
1, AWS c5n.9xlarge | 0.130 | 0.584 | 0.777 | 1.811 |
3, AWS c5n.9xlarge | 0.057 | 0.231 | 0.285 | 0.641 |
140, E5-2650v2 | 0.028 | 0.043 | 0.051 | 0.072 |