Clickhouse实践之地理信息数据处理

Clickhouse实践之地理信息数据处理

在地理信息世界，找到一个小的数据集是很容易的，例如excel文件的客户端位置，一个geojson带有数千个星巴克咖啡店的坐标，这些案例足够处理，甚至浏览器都能够加载、处理和可视化.

但是当我们讨论到核心的地理信息处理挑战时, 我们面对海量处理问题. 例如, raster 数据来自卫星影像 (地质持续监控), 一个大的地籍shapefiles 带有数百万个小的polygons, 或者 .csv files 通过 IoT 物联网生成(他们是实际的 GPS 追踪，有大量的挂载数据).

有大量的 CSVs 在 S3 云存储中，保存类 GPS数据. 以NYC Taxi 数据作为例子, 包含了所有NYC 出租车的数据. 这是大数据界的基准测试数据; 你应该看看Mark Litwintschik写的帖子，质量很好.

因此，通常情况下, 当你分析地理信息数据时，你应该停止使用PostgreSQL 和 PostGIS. 用SQL查询地理信息的数据不能与其他工具很好匹配. 另一方面,  PostgreSQL对大数据量的处理确实不佳. 让我们稍微解释一下… 你能够在PostgreSQL中加载大量的数据，同时 建立BRIN 索引, 你能够选择一小部分数据. 但是当你聚合大量数据时. PostgreSQL不是最佳选择，这就是为什么产生了 citusdb, greenplum or Amazon redshift 这样的方案，使用列式存储，并行处理，下推操作来提升性能. 因此我们使用支持 PostGIS的PostgreSQL ，另一方面，我们使用速度足够快，但部分支持地理空间信息处理的分布式数据库来支持大数据量处理 (mapd, clickhouse, vertica or druiddb) .

让我们回到NYC Taxi 数据集上，当你想要知道某特定区域的位点的平均数量, PostgreSQL 使用 “ages”. 让我们详解在PostgreSQL(9.6)上的一些查询:

[local] postgres@nyc-taxi-data=# select count(1) from trips;
  count
──────────
78424550
(1 row)
Time: 25347.000 ms

如果你想要找到在时代广场方圆300公里内有多少打车发生:

[local] postgres@nyc-taxi-data=# select count(1) from trips where st_dwithin(the_geom, 'SRID=4326;POINT(-73.985195 40.75896)', 180*300/(6371*1000*pi()));               

count
────────
797801
(1 row)
Time: 76552.877 ms

(我们使用角来计算一个平方的距离，如果是300公里我们使用公式distance = arc*radious)

76 秒看起来耗时较长，肯定有一些方法来优化，调优PostgreSQL 或者使用并行查询, 但是我们不能获得很大的性能提升. 因此，即时我们使用了32核的高端服务器，那么对于性能提升也是有限的，整个数据集放在内存中，理论上我们可以达到76s/32 ~ 2.3s.

有一些其他的数据库或框架更适合处理大数据量，例如 spark, presto, vertica, mapd 等. 但是当测试完 Clickhouse的性能以后, 我们决定选择Clickhouse作为大数据处理平台.

我们加载 NYC taxi 数据到Clickhouse中. Clickhouse 并没有任何的geospatial 工具包, 但是你能够使用基本的欧几里何的数学公式.

:) select count(1) from a2 where (pow(pickup_longitude - -73.985195, 2) +  pow(pickup_latitude - 40.75896, 2)) < pow(180*300/(6371*1000*pi()), 2);

SELECT count(1)
FROM trips
WHERE (pow(pickup_longitude - -73.985195, 2) + pow(pickup_latitude - 40.75896, 2)) < pow((180 * 300) / ((6371 * 1000) * pi()), 2)

┌─count(1)─┐
│  613491 │
└──────────┘
rows in set. Elapsed: 0.308 sec. Processed 45.45 million rows, 818.13 MB (147.63 million rows/s., 2.66 GB/s.)

308ms

我们正在处理45.5M 行 (基本执行全表扫描) ，速度超快. 如果我们使用索引来查询接近时代广场的行 (如PostgreSQL ) ，速度将会更快. 好消息是，Clickhouse 支持索引supports . 坏消息是它不支持 points 或 2D 数据的索引. 有一些方法可以线性化 2D 空间到标量, 因此传统的数据库索引可以工作. Hilbert 曲线和geohash是更为通用的方式.在我们得案例案例中我们决定使用 quadkey.

Quadkey是基本的方式在一个整型中来编码瓦片坐标，对于每个区域，我们使用比特位来编码1/4方格，每个方格有每个位置. 因此，我们能够以64位整数来编码任意的经纬坐标对儿，精度为1.9 cm (瓦片大小为31). 我们能够使用Hilbert 的geohash ，但是quadkey 更适合这种方式来可视化数据(使用quadtree).生成速度快，与通用数据库索引无缝结合.

通过创建一个带有quadkey 列的表，pickup_latitude和pickup_longitude 作为索引，我们能够使用 quadkey来查询，带上特定的where条件:

CREATE table test (
    lat Nullable(Float64),
    lon Nullable(Float64),
    datetime Nullable(DateTime),
    value Nullable(Float64),
    date_index Date,
    quadkey UInt64
)
ENGINE = MergeTree(date_index, quadkey, 8192)

运行此查询, 使用quadkey 索引:

:) select count(1) from a2 where quadkey between 1013670044871163904 and 1013670049166131200  and (pow(pickup_longitude - -73.985195, 2) +  pow(pickup_latitude - 40.75896, 2)) < pow(180*300/(6371*1000*pi()), 2)
SELECT count(1)
FROM a2
WHERE ((quadkey >= 1013670044871163904) AND (quadkey <= 1013670049166131200)) AND ((pow(pickup_longitude - -73.985195, 2) + pow(pickup_latitude - 40.75896, 2)) < pow((180 * 300) / ((6371 * 1000) * pi()), 2))
┌─count(1)─┐
│  613491 │
└──────────┘
1 rows in set. Elapsed: 0.032 sec. Processed 2.41 million rows, 56.07 MB (75.88 million rows/s., 1.77 GB/s.)

处理的数据量相同，但这次Clickhouse 处理了2.41M 行, 获得了10x提升.