纽约出租车数据

纽约市出租车数据有以下两个方式获取：

• 从原始数据导入
• 下载处理好的数据

怎样导入原始数据

可以参考 https://github.com/toddwschneider/nyc-taxi-data 和 http://tech.marksblogg.com/billion-nyc-taxi-rides-redshift.html 中的关于数据集结构描述与数据下载指令说明。

数据集包含227GB的CSV文件。在1Gbig的带宽下，下载大约需要一个小时这大约需要一个小时的下载时间(从s3.amazonaws.com并行下载时间至少可以缩减一半)。 下载时注意损坏的文件。可以检查文件大小并重新下载损坏的文件。

有些文件中包含一些无效的行，您可以使用如下语句修复他们：

然后必须在PostgreSQL中对数据进行预处理。这将创建多边形中选择的点(将地图上的点与纽约市的行政区相匹配)，并使用连接将所有数据合并到一个非规范化的平面表中。为此，您需要安装支持PostGIS的PostgreSQL。

运行initialize_database.sh时要小心，并手动重新检查是否正确创建了所有表。

在PostgreSQL中处理每个月的数据大约需要20-30分钟，总共大约需要48小时。

您可以按如下方式检查下载的行数：

(根据Mark Litwintschik的系列博客报道数据略多余11亿行)

PostgreSQL处理这些数据大概需要370GB的磁盘空间。

从PostgreSQL中导出数据：

数据快照的创建速度约为每秒50MB。 在创建快照时，PostgreSQL以每秒约28MB的速度从磁盘读取数据。 这大约需要5个小时。 最终生成的TSV文件为590612904969 bytes。

在ClickHouse中创建临时表：

接下来,需要将字段转换为更正确的数据类型，并且在可能的情况下，消除NULL。

数据的读取速度为112-140 Mb/秒。 通过这种方式将数据加载到Log表中需要76分钟。 这个表中的数据需要使用142GB的磁盘空间.

（也可以直接使用COPY ... TO PROGRAM从Postgres中导入数据）

数据中所有与天气相关的字段(precipitation...average_wind_speed)都填充了NULL。 所以，我们将从最终数据集中删除它们

首先，我们使用单台服务器创建表，后面我们将在多台节点上创建这些表。

创建表结构并写入数据：

这需要3030秒，速度约为每秒428,000行。 要加快速度，可以使用Log引擎替换MergeTree引擎来创建表。 在这种情况下，下载速度超过200秒。

这个表需要使用126GB的磁盘空间。

除此之外，你还可以在MergeTree上运行OPTIMIZE查询来进行优化。但这不是必须的，因为即使在没有进行优化的情况下它的表现依然是很好的。

下载预处理好的分区数据

!!! info "信息" 如果要运行下面的SQL查询，必须使用完整的表名，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个物理内核，128GiB RAM，8X6TB HD，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秒。

在这种情况下，查询处理时间首先由网络延迟确定。

总结

服务器	Q1	Q2	Q3	Q4
1	0.490	1.224	2.104	3.593
3	0.212	0.438	0.733	1.241
140	0.028	0.043	0.051	0.072