ClickHouse 2022 年 7 月新闻：面向铁路爱好者的地理查询

Christoph Wurm

2022 年 7 月 14 日

衷心欢迎大家。一年一度的这个时候，我们希望我们的阿姆斯特丹办事处有空调。再说，那意味着它将是超现代的，位于城郊，而不是古老迷人的， overlooking 一条中央运河。好吧，人生不可能事事如意，就像你不可能拥有一个满足 CAP 定理所有三个保证的分布式数据库一样。

继续阅读以了解我们的即将到来的活动（希望在伦敦或慕尼黑与您见面）、ClickHouse 22.6 中的新功能以及地理查询的一些乐趣。

顺便说一句，如果您是在我们的网站上阅读本文，您是否知道您也可以在收件箱中收到每个月的电子邮件？注册这里.

即将到来的活动

将这些日期标记在您的日历上

ClickHouse v22.7 版本网络研讨会
何时？星期四，7 月 21 日，太平洋标准时间上午 9 点 / 格林威治标准时间下午 5 点
如何加入？注册这里.
[现场] ClickHouse 伦敦聚会
加入我们在 Cloudflare 伦敦办事处，度过一个充满演讲的夜晚。Cloudflare 将分享有关大规模模式管理的技巧，以及他们如何让 100 多名工程师能够修改 ClickHouse 模式。听取分析初创公司 Clippd 如何使用 ClickHouse，还有更多关于如何将 ClickHouse 用于财务数据、优化 ClickHouse 以用于 ARM 以及使用 ClickHouse 可视化数据的演讲。
何时？星期三，7 月 20 日，格林威治标准时间下午 6 点
如何？注册这里.
[现场] ClickHouse 硅谷聚会
我们非常高兴能在 Barracuda 办事处举行我们的下一个 ClickHouse 现场聚会！请加入我们，度过一个充满演讲、美食和讨论的夜晚。将会有来自 ClickHouse 用户的演讲，ClickHouse 团队将分享我们最新的更新，并随时为您解答问题！
何时？星期三，7 月 20 日，太平洋标准时间下午 6 点
如何？注册这里.
[现场] ClickHouse 慕尼黑聚会
我们将齐聚慕尼黑的 Metoda 办事处，听取 Metoda、Akamai 和 ClickHouse 的演讲。许多 ClickHouse 工程师住在慕尼黑附近，所以我们会有很多专家在场，准备好您的问题！
何时？星期三，7 月 27 日，中欧时间下午 6 点
如何？注册这里.

ClickHouse v22.6

我们定期发布的 6 月版本中有什么

ARM 作为一等公民我们继续改善在 ARM 上运行的 ClickHouse 用户的体验。今年，我们推出了官方 Docker 镜像，现在我们已经完成了大量的性能优化。此外，ClickHouse 现在显示 ARM 上的堆栈跟踪，这对于调试很有用。
使用动态值搜索您现在可以使用非常量参数进行 LIKE 和 MATCH。例如，要查找使用其姓名字符串作为电子邮件地址一部分的客户，请使用 SELECT * FROM customers WHERE match(email, firstName)。
外部函数的参数您现在可以将参数传递给外部用户定义函数。
显示服务器证书使用 SELECT showCertificate() 查看服务器证书。它更符合您在客户端/浏览器中看到的！
ZooKeeper 写入您现在可以插入 system.zookeeper 表，直接操作 ZooKeeper 或 ClickHouse Keeper 中的数据。你应该吗？让我们说“这取决于”和“只有在您完全确定自己在做什么的情况下”。

查看版本网络研讨会幻灯片、录制，并请升级（除非您想保留在 LTS 版本上）。

本月查询：面向铁路爱好者的地理查询

假设您为一家跨国咖啡连锁店工作，该公司已扩展到世界各地，您希望找出哪些地方的商店可能有点多。并且假设您拥有关于所有商店的所有数据，包括它们在 ClickHouse 中的地理坐标。您有一个表，每行是一个商店及其纬度和经度。您将如何编写一个查询来查找彼此最接近的商店？

现在，我们不知道是否有公开的咖啡店位置数据集，但有一个数据集，包含英格兰和威尔士的所有公共交通站点。正式名称为“国家公共交通接入节点”（您是否曾向热心的路人询问过离您最近的接入节点的路线？），当您下载 CSV 文件时，更合理的名称是“Stops.csv”。

要将其加载到 ClickHouse 中，请运行

CREATE TABLE stops ENGINE = Memory AS
SELECT * FROM file('Stops.csv', 'CSVWithNames')
SETTINGS format_csv_allow_single_quotes = 0

首先，我们必须以我们所需的方式获取数据。许多站点有多行，每行代表一个入口、公交站、出租车停靠点、站台等。要将此缩减为每个站点一行，让我们过滤掉伦敦地铁站台（有一个 211 页的模式指南，以防您想知道我们是如何知道要过滤什么的）

SELECT * FROM stops
WHERE (StopType = 'PLT') AND (CommonName LIKE '%Underground%')

每个站点仍然会有多行，但它们似乎完全相同，因此我们只需将它们分组为一行

SELECT
       CommonName AS Name,
       any(Longitude) AS Lon,
       any(Latitude) AS Lat
FROM stops
WHERE (StopType = 'PLT') AND (CommonName LIKE '%Underground%')
GROUP BY CommonName

我们如何找到彼此最接近的站点？像这样

SELECT Name,Lat, Lon,
    lagInFrame(Name) OVER (Rows BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PrevName,
    lagInFrame(Lat) OVER (Rows BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PrevLat,
    lagInFrame(Lon) OVER (Rows BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PrevLon,
    geoDistance(Lon, Lat, PrevLon, PrevLat) AS Distance
FROM (
    SELECT CommonName AS Name, any(Longitude) AS Lon, any(Latitude) AS Lat
    FROM stops
    WHERE (StopType = 'PLT') AND (Name LIKE '%Underground%')
    GROUP BY CommonName
    ORDER BY Lat * Lon ASC
)
ORDER BY Distance ASC

彼此靠近的站点将具有几乎完全相同的纬度和经度，因此两者的乘积将几乎完全相同。根据 Lat * Lon 对结果集进行排序，会导致每个站点与其最接近的站点相邻排序。然后，我们使用窗口函数找到前一行（即离当前站点最近的站点），计算两点之间的距离并按该距离排序。

原来，最近的两个车站根本不是两个不同的车站！“希思罗机场 1-2-3 号航站楼地铁站”和“希思罗机场 2 & 3 号航站楼地铁站”实际上是同一个车站，相距仅 4 米。然而，下一个结果才是我们正在寻找的：女王路和贝斯沃特是位于城市街区两端的两个独立车站。谷歌地图显示，步行从一个车站到另一个车站需要 2 分钟。为什么会有两个车站如此靠近？因为它们服务不同的地铁线路：女王路是中央线的车站，贝斯沃特则由环线和区线服务。如果你有机会在伦敦访问这两个车站，避免前者，选择后者。你会感谢我的，尤其是在夏天。

阅读角落

我们一直在读什么

如何在处理时间优化复杂查询 Instana 撰写了他们如何在 ClickHouse 中使用物化视图和标记传入数据来加速重复的复杂查询。
在集群到集群上下文中使用 Quickwit 和 ClickHouse 进行全文搜索 Contentsquare 正在使用开源搜索引擎 Quickwit（用 Rust 编写）将 ClickHouse 中的分析查询与全文搜索功能结合起来。
ClickHouse 可扩展性和功能用于构建数据密集型应用程序 Cube 详细解释了如何使用 ClickHouse 来支撑分析仪表板，以及 Cube 如何在其无头 BI 平台中与之交互。
DENIC 使用 ClickHouse 将查询时间缩短 10 倍：DENIC 管理所有 .de 域名的注册。了解他们如何为其内部分析平台选择 ClickHouse。
QuickCheck 如何使用 ClickHouse 为未开户者提供银行服务：QuickCheck 将分析查询从 PostgreSQL 转移到 ClickHouse，查询时间从“永远”变为“瞬间”！
2022 年 6 月 8 日在阿姆斯特丹与 ClickHouse 团队会面：感谢所有亲自来到阿姆斯特丹参加我们活动的人。在这里了解活动记录。
多年来 ClickHouse 的基准测试：在 ClickHouse，我们痴迷于基准测试！在这里了解我们如何测试每个 ClickHouse 版本。
使用 ClickHouse Kafka 引擎从 Kafka 主题收集半结构化数据：在客座文章中，Superology 撰写了如何将协议缓冲区从 Kafka 导入 ClickHouse。
ClickHouse + Cumul.io：Cumul.io 撰写了如何将他们的面向客户的分析仪表板技术与 ClickHouse 结合使用。
ClickHouse + Deepnote：宣布协作数据笔记本 Deepnote 和 ClickHouse 之间的全新集成！
新的 ClickHouse 采用者：欢迎加密货币和 NFT 可视化探索者 Santiment、安全数据湖 Dassana、JSON 数据可视化平台 GraphJSON 以及注重隐私的标签管理器 Scale8。快来将自己也添加到列表中吧！