又过了一个月，这意味着又到了发布新版本的时候了！
ClickHouse 24.3 版本包含 **13 个新功能** 🎁 **16 个性能优化** 🛷 **65 个错误修复** 🐛
新贡献者
和往常一样，我们特别欢迎 24.4 版本的所有新贡献者！ClickHouse 的流行很大程度上要归功于社区的贡献。看到社区不断壮大，我们感到非常欣慰。
以下是新贡献者的姓名
Alexey Katsman, Anita Hammer, Arnaud Rocher, Chandre Van Der Westhuizen, Eduard Karacharov, Eliot Hautefeuille, Igor Markelov, Ilya Andreev, Jhonso7393, Joseph Redfern, Josh Rodriguez, Kirill, KrJin, Maciej Bak, Murat Khairulin, Paweł Kudzia, Tristan, dilet6298, loselarry
提示：如果您想知道我们如何生成此列表……点击此处。
您也可以查看演示文稿的幻灯片。
递归 CTE
由 Maksim Kita 贡献
SQL:1999 引入了递归通用表表达式 (CTE) 用于层次查询，使 SQL 成为一种图灵完备编程语言。
到目前为止，ClickHouse 通过利用层次字典支持层次查询。凭借我们新的查询分析和优化基础设施，现在默认启用，我们终于具备了引入期待已久的强大功能，例如递归 CTE。
ClickHouse 递归 CTE 具有标准 SQL:1999 语法，并通过了所有 PostgreSQL 针对递归 CTE 的测试。此外，ClickHouse 现在对递归 CTE 的支持比 PostgreSQL 更强大。在 CTE 的 UNION ALL 子句的底部部分，可以指定多个（任意复杂的）查询，可以多次引用 CTE 基表，等等。
递归 CTE 可以优雅而简单地解决层次问题。例如，它们可以轻松回答层次数据模型（例如树和图）的可达性问题。
具体来说，递归 CTE 可以计算关系的传递闭包。以伦敦地铁的线路连接作为二元关系示例，您可以想象所有直接连接的地铁站的集合：
Oxford Circus -> Bond Street、Bond Street -> Marble Arch、Marble Arch -> Lancaster Gate，等等。这些连接的传递闭包包括这些车站之间所有可能的连接，例如Oxford Circus -> Lancaster Gate、Oxford Circus -> Marble Arch，等等。
为了演示这一点，我们使用数据集的改编版本来模拟所有伦敦地铁连接，其中每个条目表示两个直接连接的车站。然后，我们可以使用递归 CTE 来轻松回答以下问题
我们使用中央线车站地图的截图来直观地展示这一点
我们创建一个 ClickHouse 表来存储伦敦地铁连接数据集
CREATE OR REPLACE TABLE Connections (
Station_1 String,
Station_2 String,
Line String,
PRIMARY KEY(Line, Station_1, Station_2)
);
细心的读者会发现，我们在上面的 DDL 语句中没有指定表引擎（这是可能的，因为 ClickHouse24.3），并在列定义中使用了 PRIMARY KEY 语法（这是可能的，因为 ClickHouse23.7）。有了它，不仅递归 CTE，而且 ClickHouse 表 DDL 语法也与 SQL 标准兼容。
通过利用url 表函数和自动模式推断，我们将数据集直接加载到我们的表中
INSERT INTO Connections
SELECT * FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/london_underground/london_connections.csv')
这是加载后的数据
SELECT
*
FROM Connections
WHERE Line = 'Central Line'
ORDER BY Station_1, Station_2
LIMIT 10;
┌─Station_1──────┬─Station_2────────┬─Line─────────┐
1. │ Bank │ Liverpool Street │ Central Line │
2. │ Bank │ St. Paul's │ Central Line │
3. │ Barkingside │ Fairlop │ Central Line │
4. │ Barkingside │ Newbury Park │ Central Line │
5. │ Bethnal Green │ Liverpool Street │ Central Line │
6. │ Bethnal Green │ Mile End │ Central Line │
7. │ Bond Street │ Marble Arch │ Central Line │
8. │ Bond Street │ Oxford Circus │ Central Line │
9. │ Buckhurst Hill │ Loughton │ Central Line │
10. │ Buckhurst Hill │ Woodford │ Central Line │
└────────────────┴──────────────────┴──────────────┘
现在，我们使用递归 CTE 来回答上面的问题
WITH RECURSIVE Reachable_Stations AS
(
SELECT Station_1, Station_2, Line, 1 AS stops
FROM Connections
WHERE Line = 'Central Line'
AND Station_1 = 'Oxford Circus'
UNION ALL
SELECT rs.Station_1, c.Station_2, c.Line, rs.stops + 1 AS stops
FROM Reachable_Stations AS rs, Connections AS c
WHERE rs.Line = c.Line
AND rs.Station_2 = c.Station_1
AND rs.stops < 5
)
SELECT DISTINCT (Station_1, Station_2, stops) AS connections
FROM Reachable_Stations
ORDER BY stops ASC;
这是结果
┌─connections────────────────────────────────┐
1. │ ('Oxford Circus','Bond Street',1) │
2. │ ('Oxford Circus','Tottenham Court Road',1) │
3. │ ('Oxford Circus','Marble Arch',2) │
4. │ ('Oxford Circus','Oxford Circus',2) │
5. │ ('Oxford Circus','Holborn',2) │
6. │ ('Oxford Circus','Bond Street',3) │
7. │ ('Oxford Circus','Lancaster Gate',3) │
8. │ ('Oxford Circus','Tottenham Court Road',3) │
9. │ ('Oxford Circus','Chancery Lane',3) │
10. │ ('Oxford Circus','Marble Arch',4) │
11. │ ('Oxford Circus','Oxford Circus',4) │
12. │ ('Oxford Circus','Queensway',4) │
13. │ ('Oxford Circus','Holborn',4) │
14. │ ('Oxford Circus','St. Paul\'s',4) │
15. │ ('Oxford Circus','Bond Street',5) │
16. │ ('Oxford Circus','Lancaster Gate',5) │
17. │ ('Oxford Circus','Tottenham Court Road',5) │
18. │ ('Oxford Circus','Notting Hill Gate',5) │
19. │ ('Oxford Circus','Chancery Lane',5) │
20. │ ('Oxford Circus','Bank',5) │
└────────────────────────────────────────────┘
递归 CTE 具有简单的基于迭代的执行逻辑，其行为类似于递归的自-自-...-自连接，一旦没有找到新的连接伙伴或满足了中止条件，就会停止自连接。为此，我们上面的 CTE 从执行 UNION ALL 子句的顶部部分开始，查询我们的 Connections 表以查找所有直接连接到 Oxford Circus 车站的中央线车站。这将返回一个绑定到 Reachable_Stations 标识符的表，如下所示
Initial Reachable_Stations table content
┌─Station_1─────┬─Station_2────────────┐
│ Oxford Circus │ Bond Street │
│ Oxford Circus │ Tottenham Court Road │
└───────────────┴──────────────────────┘
从现在开始，将仅执行 CTE 的 UNION ALL 子句的底部部分（递归地）
Reachable_Stations 与 Connections 表连接，以在 Connections 表中查找以下连接伙伴，其 Station_1 值与 Reachable_Stations 的 Station_2 值匹配
Connections table join partners
┌─Station_1────────────┬─Station_2─────┐
│ Bond Street │ Marble Arch │
│ Bond Street │ Oxford Circus │
│ Tottenham Court Road │ Holborn │
│ Tottenham Court Road │ Oxford Circus │
└──────────────────────┴───────────────┘
通过 UNION ALL 子句，将这些连接伙伴添加到 Reachable_Stations 表中（用 Oxford Circus 替换 Station_1 列），并且递归 CTE 的第一次迭代完成。在下一次迭代中，通过执行 CTE 的 UNION ALL 子句的底部部分，Reachable_Stations 再次与 Connections 表连接，以识别（并添加到 Reachable_Stations）Connections 表中所有新的连接伙伴，其 Station_1 值与 Reachable_Stations 的 Station_2 值匹配。这些迭代将持续进行，直到没有找到新的连接伙伴或满足了停止条件。在我们上面的查询中，我们使用 stops 计数器，当从起始车站到达指定数量的允许停止时，中止 CTE 的迭代循环。
请注意，结果将 Oxford Circus 列为从 Oxford Circus 出发以 2 站和 4 站可达的车站。从理论上讲，这是正确的，但实际上并不实用，这是因为我们的查询不关心任何方向或循环。我们将此留给读者作为一项有趣的练习。
作为额外的问题，我们想知道从Central Line上的Oxford Circus车站到Stratford车站需要多少站。我们再次使用Central Line地图来可视化这个问题。
为此，我们只需要修改递归CTE的终止条件（在将Stratford作为目标站点的加入伙伴添加到CTE表后，停止CTE的加入迭代）
WITH RECURSIVE Reachable_Stations AS
(
SELECT Station_1, Station_2, Line, 1 AS stops
FROM Connections
WHERE Line = 'Central Line'
AND Station_1 = 'Oxford Circus'
UNION ALL
SELECT rs.Station_1 c.Station_2, c.Line, rs.stops + 1 AS stops
FROM Reachable_Stations AS rs, Connections AS c
WHERE rs.Line = c.Line
AND rs.Station_2 = c.Station_1
AND 'Stratford' NOT IN (SELECT Station_2 FROM Reachable_Stations)
)
SELECT max(stops) as stops
FROM Reachable_Stations;
结果显示需要9站，这与上面Central Line的地图计划相匹配
┌─stops─┐
1. │ 9 │
└───────┘
递归CTE可以轻松回答有关此数据集的更多有趣问题。例如，数据集原始版本中的相对连接时间可以用来发现从Oxford Circus车站到Heathrow Airport车站的最快（跨越地铁线路）连接。敬请关注后续文章中对此问题的解决方案。
QUALIFY
由 Maksim Kita 贡献
此版本添加的另一个功能是QUALIFY子句，它允许我们根据窗口函数的值进行过滤。
我们将借助窗口函数 - 排名示例来了解如何使用它。数据集包含假设的足球运动员及其薪资。我们可以像这样将其导入ClickHouse
CREATE TABLE salaries ORDER BY team AS
FROM url('https://raw.githubusercontent.com/ClickHouse/examples/main/LearnClickHouseWithMark/WindowFunctions-Aggregation/data/salaries.csv')
SELECT * EXCEPT (weeklySalary), weeklySalary AS salary
SETTINGS schema_inference_make_columns_nullable=0;
让我们快速看一下salaries表中的数据
SELECT * FROM salaries LIMIT 5;
┌─team──────────────┬─player───────────┬─position─┬─salary─┐
1. │ Aaronbury Seekers │ David Morton │ D │ 63014 │
2. │ Aaronbury Seekers │ Edwin Houston │ D │ 51751 │
3. │ Aaronbury Seekers │ Stephen Griffith │ M │ 181825 │
4. │ Aaronbury Seekers │ Douglas Clay │ F │ 73436 │
5. │ Aaronbury Seekers │ Joel Mendoza │ D │ 257848 │
└───────────────────┴──────────────────┴──────────┴────────┘
接下来，让我们计算每个球员按位置的薪资排名。也就是说，他们的薪资与同位置的球员相比如何？
SELECT player, team, position AS pos, salary,
rank() OVER (PARTITION BY position ORDER BY salary DESC) AS posRank
FROM salaries
ORDER BY salary DESC
LIMIT 5
┌─player──────────┬─team────────────────────┬─pos─┬─salary─┬─posRank─┐
1. │ Robert Griffin │ North Pamela Trojans │ GK │ 399999 │ 1 │
2. │ Scott Chavez │ Maryhaven Generals │ M │ 399998 │ 1 │
3. │ Dan Conner │ Michaelborough Rogues │ M │ 399998 │ 1 │
4. │ Nathan Thompson │ Jimmyville Legionnaires │ D │ 399998 │ 1 │
5. │ Benjamin Cline │ Stephaniemouth Trojans │ D │ 399998 │ 1 │
└─────────────────┴─────────────────────────┴─────┴────────┴─────────┘
假设我们想过滤posRank，只返回每个位置薪资最高的3名球员。我们可能会尝试添加一个WHERE子句来实现这一点
SELECT player, team, position AS pos, salary,
rank() OVER (PARTITION BY position ORDER BY salary DESC) AS posRank
FROM salaries
WHERE posRank <= 3
ORDER BY salary DESC
LIMIT 5
Received exception:
Code: 184. DB::Exception: Window function rank() OVER (PARTITION BY position ORDER BY salary DESC) AS posRank is found in WHERE in query. (ILLEGAL_AGGREGATION)
我们无法做到这一点，因为WHERE子句在窗口函数被评估之前运行。在24.4版本之前，我们可以通过引入CTE来解决这个问题
WITH salaryRankings AS
(
SELECT player,
if(
length(team) <=25,
team,
concat(substring(team, 5), 1, '...')
) AS team,
position AS pos, salary,
rank() OVER (
PARTITION BY position
ORDER BY salary DESC
) AS posRank
FROM salaries
ORDER BY salary DESC
)
SELECT *
FROM salaryRankings
WHERE posRank <= 3
┌─player────────────┬─team────────────────────┬─pos─┬─salary─┬─posRank─┐
1. │ Robert Griffin │ North Pamela Trojans │ GK │ 399999 │ 1 │
2. │ Scott Chavez │ Maryhaven Generals │ M │ 399998 │ 1 │
3. │ Dan Conner │ Michaelborough Rogue... │ M │ 399998 │ 1 │
4. │ Benjamin Cline │ Stephaniemouth Troja... │ D │ 399998 │ 1 │
5. │ Nathan Thompson │ Jimmyville Legionnai... │ D │ 399998 │ 1 │
6. │ William Rubio │ Nobleview Sages │ M │ 399997 │ 3 │
7. │ Juan Bird │ North Krystal Knight... │ GK │ 399986 │ 2 │
8. │ John Lewis │ Andreaberg Necromanc... │ D │ 399985 │ 3 │
9. │ Michael Holloway │ Alyssaborough Sages │ GK │ 399984 │ 3 │
10. │ Larry Buchanan │ Eddieshire Discovere... │ F │ 399973 │ 1 │
11. │ Alexis Valenzuela │ Aaronport Crusaders │ F │ 399972 │ 2 │
12. │ Mark Villegas │ East Michaelborough ... │ F │ 399972 │ 2 │
└───────────────────┴─────────────────────────┴─────┴────────┴─────────┘
此查询有效，但相当笨拙。现在我们有了QUALIFY子句，我们可以过滤数据，而无需引入CTE，如下所示
SELECT player, team, position AS pos, salary,
rank() OVER (PARTITION BY position ORDER BY salary DESC) AS posRank
FROM salaries
QUALIFY posRank <= 3
ORDER BY salary DESC;
我们将获得与以前相同的结果。
联接性能改进
由 Maksim Kita 贡献
对于非常特定的JOIN用例，还有一些性能改进。
第一个是更好的谓词下推，当分析器确定何时可以将过滤条件应用于JOIN的两侧时。
让我们来看一个使用OpenSky数据集的示例，该数据集包含2019-2021年的空中交通数据。我们想要获取经过旧金山的十个航班列表，可以使用以下查询来实现
SELECT
l.origin,
r.destination AS dest,
firstseen,
lastseen
FROM opensky AS l
INNER JOIN opensky AS r ON l.destination = r.origin
WHERE notEmpty(l.origin) AND notEmpty(r.destination) AND (r.origin = 'KSFO')
LIMIT 10
SETTINGS optimize_move_to_prewhere = 0
我们禁用optimize_move_to_prewhere，以便ClickHouse不执行另一个优化，这将阻止我们看到JOIN改进的优势。如果我们在24.3上运行此查询，我们将看到以下输出
┌─origin─┬─dest─┬───────────firstseen─┬────────────lastseen─┐
1. │ 00WA │ 00CL │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
2. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
3. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
4. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
5. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
6. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
7. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
8. │ 00WA │ YSSY │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
9. │ 00WA │ YSSY │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
10. │ 00WA │ YSSY │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
└────────┴──────┴─────────────────────┴─────────────────────┘
10 rows in set. Elapsed: 0.656 sec. Processed 15.59 million rows, 451.90 MB (23.75 million rows/s., 688.34 MB/s.)
Peak memory usage: 62.79 MiB.
让我们看看24.4
┌─origin─┬─dest─┬───────────firstseen─┬────────────lastseen─┐
1. │ 00WA │ 00CL │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
2. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
3. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
4. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
5. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
6. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
7. │ 00WA │ ZGGG │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
8. │ 00WA │ YSSY │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
9. │ 00WA │ YSSY │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
10. │ 00WA │ YSSY │ 2019-10-14 21:03:19 │ 2019-10-14 22:42:01 │
└────────┴──────┴─────────────────────┴─────────────────────┘
10 rows in set. Elapsed: 0.079 sec.
因此速度快了约8倍。如果我们通过SELECT *返回所有列，则24.3中此查询的执行时间将增加到超过4秒，而在24.4中则为0.4秒，这提高了10倍。
让我们看看我们是否能理解它为什么更快。我们要关注的两行是这些
INNER JOIN opensky AS r ON l.destination = r.origin
WHERE notEmpty(l.origin) AND notEmpty(r.destination) AND (r.origin = 'KSFO')
我们WHERE子句中的最后一个谓词是r.origin = 'KSFO'。在上一行中，我们说过，我们只希望在l.destination = r.origin的情况下进行联接，这意味着l.destination = 'KSFO'也是。24.4中的分析器知道这一点，因此可以更早地过滤掉大量行。
换句话说，在24.4中，我们的WHERE子句实际上看起来像这样
INNER JOIN opensky AS r ON l.destination = r.origin
WHERE notEmpty(l.origin) AND notEmpty(r.destination)
AND (r.origin = 'KSFO') AND (l.destination = 'KSFO')
第二个改进是，如果JOIN后的谓词过滤掉任何未联接的行，分析器现在将自动将OUTER JOIN转换为INNER JOIN。
例如，假设我们最初编写了一个查询来查找旧金山和纽约之间的航班，包括直飞航班和中途停留航班。
SELECT
l.origin,
l.destination,
r.destination,
registration,
l.callsign,
r.callsign
FROM opensky AS l
LEFT JOIN opensky AS r ON l.destination = r.origin
WHERE notEmpty(l.destination)
AND (l.origin = 'KSFO')
AND (r.destination = 'KJFK')
LIMIT 10
我们后来添加了一个额外的过滤器，只返回r.callsign = 'AAL1424'的行。
SELECT
l.origin,
l.destination AS leftDest,
r.destination AS rightDest,
registration AS reg,
l.callsign,
r.callsign
FROM opensky AS l
LEFT JOIN opensky AS r ON l.destination = r.origin
WHERE notEmpty(l.destination)
AND (l.origin = 'KSFO')
AND (r.destination = 'KJFK')
AND (r.callsign = 'AAL1424')
LIMIT 10
SETTINGS optimize_move_to_prewhere = 0
由于我们现在需要联接右侧的callsign列具有值，因此LEFT JOIN可以转换为INNER JOIN。让我们检查24.3和24.4中的查询性能。
24.3
┌─origin─┬─leftDest─┬─rightDest─┬─reg────┬─callsign─┬─r.callsign─┐
1. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
2. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
3. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
4. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
5. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
6. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
7. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
8. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
9. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
10. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
└────────┴──────────┴───────────┴────────┴──────────┴────────────┘
10 rows in set. Elapsed: 1.937 sec. Processed 63.98 million rows, 2.52 GB (33.03 million rows/s., 1.30 GB/s.)
Peak memory usage: 2.84 GiB.
24.4
┌─origin─┬─leftDest─┬─rightDest─┬─reg────┬─callsign─┬─r.callsign─┐
1. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
2. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
3. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
4. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
5. │ KSFO │ 01FA │ KJFK │ N12221 │ UAL423 │ AAL1424 │
6. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
7. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
8. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
9. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
10. │ KSFO │ 01FA │ KJFK │ N87527 │ UAL423 │ AAL1424 │
└────────┴──────────┴───────────┴────────┴──────────┴────────────┘
10 rows in set. Elapsed: 0.762 sec. Processed 23.22 million rows, 939.75 MB (30.47 million rows/s., 1.23 GB/s.)
Peak memory usage: 9.00 MiB.
在24.4中，速度快了近三倍。
如果你想了解更多关于JOIN性能改进的实现方式，请阅读Maksim Kita的博客文章，文章中详细解释了一切。
关于24.4版本的介绍就到这里。我们诚邀您参加5月30日的24.5版本发布会。请务必注册，以便获得Zoom网络研讨会的详细信息。
