ClickHouse 中对 Join 类型的支持 - Part 1

ClickHouseInc 2023-07-26

197

ClickHouse是一个开源的面向列的数据库管理系统（DBMS），专为需要在大量数据上进行超低延迟分析查询的用例进行构建和优化。为了实现分析应用的最佳性能，通常会将表合并为一个过程，称为数据去规范化。通过扁平化表，可以最小化查询延迟，避免连接操作，但会增加增量ETL的复杂性。这种复杂性通常是可以接受的，因为可以换取亚秒级的查询性能。

然而，并非所有工作负载都适用于去规范化数据，特别是来自传统数据仓库的工作负载。有时，分析查询需要在连接时保持部分数据的规范化。规范化的表占用更少的存储空间，提供了数据组合的灵活性，但某些类型的分析查询需要进行连接操作。

幸运的是，与一些误解相反，ClickHouse完全支持连接操作！除了支持所有标准的SQL JOIN类型外，ClickHouse还提供了其他适用于分析工作负载和时间序列分析的JOIN类型。ClickHouse允许您在连接执行时选择6种不同的算法（我们将在博客系列的下一部分中详细探讨），或者允许查询规划器根据资源可用性和使用情况在运行时自适应选择和动态更改算法。

在ClickHouse中，即使对于大型表的连接操作，也可以实现良好的性能。然而，对于这种用例，用户需要仔细选择和调整连接算法以适应查询工作负载。虽然我们预计随着时间的推移，这个过程会变得更加自动化和启发式驱动，但本博客系列将提供对ClickHouse连接执行内部原理的深入了解，以便您可以优化应用程序中常见查询的连接操作。

在本文中，我们将使用一个规范化的关系数据库示例模式，以演示ClickHouse中可用的不同连接类型。在接下来的文章中，我们将深入了解ClickHouse中可用的6种不同的连接算法。我们将探索ClickHouse如何将这些连接算法集成到其查询流水线中，以尽快执行连接类型。未来的部分将涵盖分布式连接操作。

测试数据和资源

我们使用Venn图和示例查询来解释ClickHouse中可用的连接类型，这些示例查询基于来自关系数据集存储库的规范化IMDB数据集。

创建和加载表的说明:

上述的4个表中的数据代表电影。一个电影可以拥有一个或多个类型（genres）。电影中的角色由演员（actors）扮演。图中的箭头表示外键与主键之间的关系。举例来说，genres表中的movie_id列包含了movies表中对应行的id值。

电影和演员之间存在着多对多的关系。通过使用roles表，我们将这种多对多的关系规范化为两个一对多的关系。roles表的每一行都包含了movies表和actors表中id字段的值。

ClickHouse 中支持 Join 的类型

INNER JOIN
OUTER JOIN
CROSS JOIN
SEMI JOIN
ANTI JOIN
ANY JOIN
ASOF JOIN

INNER JOIN

内连接（INNER JOIN）对于每对在连接键上匹配的行，返回左表行的列值与右表行的列值的组合。如果一行有多个匹配项，则返回所有匹配项（这意味着对于具有匹配的连接键的行，会产生笛卡尔积）。

以下查询通过将movies表与genres表进行连接，找到每部电影的genre(s)：

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
INNER JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;


┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action    │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family    │
│ Harry Potter and the Half-Blood Prince │ Fantasy   │
│ Harry Potter and the Half-Blood Prince │ Thriller  │
│ DragonBall Z                           │ Action    │
│ DragonBall Z                           │ Adventure │
│ DragonBall Z                           │ Comedy    │
│ DragonBall Z                           │ Fantasy   │
│ DragonBall Z                           │ Sci-Fi    │
└────────────────────────────────────────┴───────────┘


10 rows in set. Elapsed: 0.126 sec. Processed 783.39 thousand rows, 21.50 MB (6.24 million rows/s., 171.26 MB/s.)

请注意，可以省略INNER关键字。使用以下其他连接类型之一，可以扩展或更改INNER JOIN的行为。

(LEFT RIGHT FULL) 其他的 JOIN

左外连接（LEFT OUTER JOIN）的行为类似于内连接（INNER JOIN），但是它还会返回左表中的非匹配行，同时用右表列的默认值填充。

右外连接（RIGHT OUTER JOIN）查询类似于左外连接，它会返回右表中的非匹配行，同时用左表列的默认值填充。

全外连接（FULL OUTER JOIN）查询结合了左外连接和右外连接的特性，它会返回左表和右表中的非匹配行，并使用左表和右表列的默认值填充。

下面的查询是用来找出所有没有流派的电影，它通过查询movies表中在genres表中没有匹配的所有行来实现。因此，在查询时，对于movie_id列会获得默认值0。

SELECT m.name
FROM movies AS m
LEFT JOIN genres AS g ON m.id = g.movie_id
WHERE g.movie_id = 0
ORDER BY
    m.year DESC,
    m.name ASC
LIMIT 10;




┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘


10 rows in set. Elapsed: 0.092 sec. Processed 783.39 thousand rows, 15.42 MB (8.49 million rows/s., 167.10 MB/s.)

注意关键字 OUTER 关键字也可以不用.

CROSS JOIN

交叉连接（CROSS JOIN）在不考虑连接键的情况下产生两个表的完全笛卡尔积。左表的每一行都与右表的每一行组合在一起。

因此，下面的查询将movies表的每一行与genres表的每一行组合在一起：

SELECT
    m.name,
    m.id,
    g.movie_id,
    g.genre
FROM movies AS m
CROSS JOIN genres AS g
LIMIT 10;


┌─name─┬─id─┬─movie_id─┬─genre───────┐
│ #28  │  0 │        1 │ Documentary │
│ #28  │  0 │        1 │ Short       │
│ #28  │  0 │        2 │ Comedy      │
│ #28  │  0 │        2 │ Crime       │
│ #28  │  0 │        5 │ Western     │
│ #28  │  0 │        6 │ Comedy      │
│ #28  │  0 │        6 │ Family      │
│ #28  │  0 │        8 │ Animation   │
│ #28  │  0 │        8 │ Comedy      │
│ #28  │  0 │        8 │ Short       │
└──────┴────┴──────────┴─────────────┘


10 rows in set. Elapsed: 0.024 sec. Processed 477.04 thousand rows, 10.22 MB (20.13 million rows/s., 431.36 MB/s.)

虽然前面的示例查询本身并没有太多意义，但可以通过使用WHERE子句将匹配的行关联起来，来扩展查询，以实现查找每个movies的genre的内连接行为：

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;


┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action    │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family    │
│ Harry Potter and the Half-Blood Prince │ Fantasy   │
│ Harry Potter and the Half-Blood Prince │ Thriller  │
│ DragonBall Z                           │ Action    │
│ DragonBall Z                           │ Adventure │
│ DragonBall Z                           │ Comedy    │
│ DragonBall Z                           │ Fantasy   │
│ DragonBall Z                           │ Sci-Fi    │
└────────────────────────────────────────┴───────────┘


10 rows in set. Elapsed: 0.150 sec. Processed 783.39 thousand rows, 21.50 MB (5.23 million rows/s., 143.55 MB/s.)

一个CROSS JOIN的另一种语法是在FROM子句中用逗号分隔的方式指定多个表。

如果查询的WHERE部分中存在连接表达式，ClickHouse会将CROSS JOIN重写为INNER JOIN。

我们可以通过EXPLAIN SYNTAX来检查示例查询是否符合这种情况（它返回查询在执行之前被重写的语法优化版本）：

EXPLAIN SYNTAX
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;


┌─explain─────────────────────────────────────┐
│ SELECT                                      │
│     name AS name,                           │
│     genre AS genre                          │
│ FROM movies AS m                            │
│ ALL INNER JOIN genres AS g ON id = movie_id │
│ WHERE id = movie_id                         │
│ ORDER BY                                    │
│     year DESC,                              │
│     name ASC,                               │
│     genre ASC                               │
│ LIMIT 10                                    │
└─────────────────────────────────────────────┘


11 rows in set. Elapsed: 0.077 sec.

在进行语法优化的交叉JOIN查询版本中，内部JOIN子句包含了"ALL"关键字。该关键字的作用是保持交叉JOIN的笛卡尔积语义，即使被重新编写为内部JOIN，这样可以禁用笛卡尔积。

根据上述说明，可以省略外部连接，并且可以添加可选的"ALL"关键字，你可以写所有的外部连接，它会正常工作。

(LEFT RIGHT) SEMI JOIN

左半连接（LEFT SEMI JOIN）查询会返回左表中至少在右表中有一个连接键匹配的行的列值。它只返回找到的第一个匹配项，并禁用笛卡尔积。

右半连接（RIGHT SEMI JOIN）查询与之类似，它返回右表中至少在左表中有一个匹配的行的值，同样只返回找到的第一个匹配项。

下面的查询旨在找出在2023年参演过电影的所有演员/女演员。请注意，如果使用普通的（INNER）连接，同一个演员/女演员如果在2023年中扮演了多个角色，将会出现多次匹配结果：

SELECT
    a.first_name,
    a.last_name
FROM actors AS a
LEFT SEMI JOIN roles AS r ON a.id = r.actor_id
WHERE toYear(created_at) = '2023'
ORDER BY id ASC
LIMIT 10;


┌─first_name─┬─last_name──────────────┐
│ Michael    │ 'babeepower' Viera     │
│ Eloy       │ 'Chincheta'            │
│ Dieguito   │ 'El Cigala'            │
│ Antonio    │ 'El de Chipiona'       │
│ José       │ 'El Francés'           │
│ Félix      │ 'El Gato'              │
│ Marcial    │ 'El Jalisco'           │
│ José       │ 'El Morito'            │
│ Francisco  │ 'El Niño de la Manola' │
│ Víctor     │ 'El Payaso'            │
└────────────┴────────────────────────┘


10 rows in set. Elapsed: 0.151 sec. Processed 4.25 million rows, 56.23 MB (28.07 million rows/s., 371.48 MB/s.)

(LEFT RIGHT) ANTI JOIN

左反连接（LEFT ANTI JOIN）返回左表中所有非匹配行的列值。

类似地，右反连接（RIGHT ANTI JOIN）返回右表中所有非匹配行的列值。

我们之前的外连接示例查询的另一种形式是使用反连接来查找数据集中没有genre的movie：

该查询使用左反连接将movies表的id与genres表的movie_id进行连接。它将返回movies表中所有没有与genres表匹配的行，即数据集中没有流派的电影的标题。这样，查询结果将只包含没有流派的电影的标题。

SELECT m.name
FROM movies AS m
LEFT ANTI JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    year DESC,
    name ASC
LIMIT 10;


┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘


10 rows in set. Elapsed: 0.077 sec. Processed 783.39 thousand rows, 15.42 MB (10.18 million rows/s., 200.47 MB/s.)

(LEFT RIGHT INNER) ANY JOIN

左任意连接（LEFT ANY JOIN）是左外连接（LEFT OUTER JOIN）和左半连接（LEFT SEMI JOIN）的组合。在ClickHouse中，左任意连接会返回左表中每一行的列值，与右表中匹配行的列值进行组合，或者与右表的默认列值进行组合（如果没有匹配项）。如果左表的某一行在右表中有多个匹配项，ClickHouse仅返回第一个匹配项的组合列值（禁用笛卡尔积）。

同样地，右任意连接（RIGHT ANY JOIN）是右外连接（RIGHT OUTER JOIN）和右半连接（RIGHT SEMI JOIN）的组合。

而内任意连接（INNER ANY JOIN）是内连接（INNER JOIN）并禁用了笛卡尔积。

为了演示左任意连接的用法，我们使用一个抽象示例和两个临时表（left_table和right_table）：

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
LEFT ANY JOIN right_table AS r ON l.c = r.c;


┌─l_c─┬─r_c─┐
│   1 │   0 │
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘


3 rows in set. Elapsed: 0.002 sec.

这是使用 RIGHT ANY JOIN 进行的相同查询：

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
RIGHT ANY JOIN right_table AS r ON l.c = r.c;


┌─l_c─┬─r_c─┐
│   2 │   2 │
│   2 │   2 │
│   3 │   3 │
│   3 │   3 │
│   0 │   4 │
└─────┴─────┘


5 rows in set. Elapsed: 0.002 sec.

这是使用一个 INNER ANY JOIN 的查询：

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
INNER ANY JOIN right_table AS r ON l.c = r.c;


┌─l_c─┬─r_c─┐
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘


2 rows in set. Elapsed: 0.002 sec.

ASOF JOIN

ASOF JOIN提供了非精确匹配的能力。如果左表中的一行在右表中没有精确匹配，那么将使用最接近的右表行作为匹配。

这在时间序列分析中非常有用，并可以大大降低查询的复杂性。

我们将以股票市场数据的时间序列分析为例。一个quotes表包含了基于每天特定时间的股票符号报价。在我们的示例数据中，价格每10秒更新一次。一个trades表列出了符号的交易 - 在特定时间买入的特定数量的符号：

为了计算每笔交易的具体成本，我们需要将交易与其最接近的报价时间进行匹配。

使用ASOF JOIN，这个过程非常简单和紧凑，我们可以在ON子句中指定精确匹配条件，并在AND子句中指定最接近匹配条件 - 我们要寻找在交易日期之前或者正好等于交易日期的quotes表中的最接近行：

SELECT
    t.symbol,
    t.volume,
    t.time AS trade_time,
    q.time AS closest_quote_time,
    q.price AS quote_price,
    t.volume * q.price AS final_price
FROM trades t
ASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.time
FORMAT Vertical;


Row 1:
──────
symbol:             ABC
volume:             200
trade_time:         2023-02-22 14:09:05
closest_quote_time: 2023-02-22 14:09:00
quote_price:        32.11
final_price:        6422


Row 2:
──────
symbol:             ABC
volume:             300
trade_time:         2023-02-22 14:09:28
closest_quote_time: 2023-02-22 14:09:20
quote_price:        32.15
final_price:        9645


2 rows in set. Elapsed: 0.003 sec.