ClickHouse Newsletter 2022 年 7 月刊：铁路爱好者的地理查询

热烈欢迎大家。又到了一年中的这个时候，我们希望我们在阿姆斯特丹的办公室能有空调。当然，那意味着它会是超现代化的，位于郊区，而不是古老而迷人，可以俯瞰市中心的运河。唉，人生不如意事十之八九，就像你不能拥有一个满足 CAP 定理所有三个保证的分布式数据库一样。

继续阅读，了解我们即将举行的活动（希望在伦敦或慕尼黑与您面对面交流）、ClickHouse 22.6 中的新增功能以及一些有趣的地理查询。

顺便说一句，如果您正在我们的网站上阅读此内容，您是否知道您也可以通过电子邮件在收件箱中收到每月的简报？在此处注册here。

即将举行的活动

在您的日历上标记这些

• ClickHouse v22.7 发布网络研讨会
  时间？ 7 月 21 日星期四上午 9 点（太平洋夏令时间）/下午 5 点（格林威治标准时间）
  如何加入？ 在此处注册。

• 【线下活动】ClickHouse 伦敦聚会
  加入我们在 Cloudflare 伦敦办公室举办的充满演讲的夜晚。Cloudflare 将分享有关大规模架构管理以及他们如何使 100 多名工程师能够修改 ClickHouse 架构的技巧。听取分析初创公司 Clippd 关于他们如何使用 ClickHouse 的介绍，还有更多关于将 ClickHouse 用于金融数据、优化 ARM 的 ClickHouse 以及使用 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 月份的常规月度版本中的内容

1. ARM 作为一等公民 我们继续改进在 ARM 上运行 ClickHouse 用户的体验。今年，我们推出了官方 Docker 镜像，现在我们进行了大量的性能优化。此外，ClickHouse 现在显示 ARM 上的堆栈跟踪，这对于调试很有用。
2. 使用动态值搜索 您现在可以使用非常量参数进行 LIKE 和 MATCH。例如，要查找使用其名字作为电子邮件地址一部分的客户，请使用 SELECT * FROM customers WHERE match(email, firstName)。
3. 外部函数的参数 您现在可以将参数传递给外部用户定义的函数。
4. 显示服务器证书 使用 SELECT showCertificate() 查看服务器证书。它更好地匹配您在客户端/浏览器中看到的内容！
5. 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 米，实际上或多或少是同一个车站。但是，下一个结果是我们正在寻找的：Queensway 和 Bayswater 是两个独立的车站，位于城市街区的另一端。谷歌地图显示从一个车站走到另一个车站需要 2 分钟。为什么有两个车站如此靠近？好吧，它们服务于不同的地铁线路：Queensway 是中央线上的一个站点，Bayswater 由环线和区域线服务。如果您曾经访问伦敦并在两者之间做出选择，请避开前者而选择后者。您会感谢我的，尤其是在夏天。

阅读角

我们一直在阅读的内容

1. 我们如何在处理时优化复杂查询 Instana 写了他们如何使用物化视图和传入数据的标记来加速 ClickHouse 中重复出现的复杂查询。

2. 在集群到集群上下文中将 Quickwit 和 ClickHouse 用于全文搜索 Contentsquare 正在使用开源搜索引擎 Quickwit（用 Rust 编写）将 ClickHouse 中的分析查询与全文搜索功能结合起来。

3. ClickHouse 的可扩展性和构建数据密集型应用程序的能力 Cube 详细解释了如何使用 ClickHouse 来支持分析仪表板，以及 Cube 如何在其无头 BI 平台中与之交互。

4. DENIC 通过 ClickHouse 将查询时间缩短了 10 倍：DENIC 管理 .de 域的所有注册。了解他们如何选择 ClickHouse 作为其内部分析平台。

5. QuickCheck 如何使用 ClickHouse 为没有银行账户的人提供银行服务：QuickCheck 将分析查询从 PostgreSQL 迁移到 ClickHouse，查询时间从“永远”变为“瞬间”！

6. 阿姆斯特丹 ClickHouse 团队聚会 – 2022 年 6 月 8 日：感谢所有亲临阿姆斯特丹现场的朋友。在此处观看录像。

7. ClickHouse 多年来的基准测试：在 ClickHouse，我们痴迷于基准测试！在此处阅读我们如何测试每个 ClickHouse 版本。

8. 使用 ClickHouse Kafka 引擎从 Kafka 主题收集半结构化数据：在客座文章中，Superology 介绍了如何将协议缓冲区从 Kafka 摄取到 ClickHouse 中。

9. ClickHouse + Cumul.io：Cumul.io 介绍了如何将他们的面向客户的分析仪表板技术与 ClickHouse 结合使用。

10. ClickHouse + Deepnote：宣布协作数据笔记本 Deepnote 和 ClickHouse 之间的新集成！

11. 新的 ClickHouse 采用者：欢迎加密货币和 NFT 可视化浏览器 Santiment、安全数据湖 Dassana、JSON 数据可视化平台 GraphJSON 和注重隐私的标签管理器 Scale8。也让自己被添加进来吧！

感谢您的阅读。下个月再见！

ClickHouse 团队

摄影：delfi de la Rua，来自 Unsplash