ClickHouse支持的Join类型

2023-07-25 20:31 abce 阅读(1015) 评论(0) 编辑收藏举报

ClickHouse是一种面向列的开源数据库管理系统，专为需要对大量数据进行超低延迟的分析查询的场景而构建和优化。为使分析应用达到最佳性能，通常会反范式联合表。扁平的表可以避免连接，从而有助于最大限度地减少查询延迟，但代价是ETL的复杂性会增加，而这通常是可以接受的，以换取亚秒级的查询。

不过，对于某些工作负载，例如传统的数据仓库的工作负载，对数据进行反范式处理并不总是切实可行的，而且，有时分析查询的部分源数据需要保持范式化。这些范式化表占用的存储空间更少，数据组合也更灵活，但在某些类型的分析中，它们需要在查询时进行join。

幸运的是，与一些误解相反，ClickHouse完全支持join！除了支持所有标准SQL Join类型外，ClickHouse还提供了对分析型工作负载和时间序列分析有用的附加Join类型。ClickHouse支持6种不同的算法用于执行Join，或者允许优化器在运行时根据资源可用性和使用情况自适应地选择和动态更改算法。

在ClickHouse中，即使是大表的Join也能实现良好的性能，但这种使用情况、目前尤其需要用户针对其查询工作负载仔细选择和调整Join算法。虽然我们希望随着时间的推移，这也能变得更加自动化和启发式。

在本文中，将使用范式化关系数据库示例模式来演示ClickHouse中可用的不同Join类型。

测试数据和资源准备

四张表。

一部电影可属于一种或多种风格。图中的箭头表示外键依赖。

ClickHouse中支持的Join类型

·inner join
·outer join
·cross join
·semi join
·anti join
·any join
·asof join

INNER JOIN

查看每部电影的风格

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关键字可以省略。

(LEFT/RIGHT/FULL) OUTER JOIN

例如：

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会生成两个表的笛卡尔集，不考虑连接条件，左表的每一行记录都会和右表每一个行匹配。

例如：

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条件后，会变成inner join：

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.)

这一点可以通过explain命令来确认：

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.

经过语法优化的CROSS JOIN查询版本中的INNER JOIN子句包含ALL关键字，这是为了在CROSS JOIN重写为INNER JOIN时仍能保持其笛卡尔集语义而明确添加的，因为INNER JOIN可以禁用笛卡尔集。

此外，如上所述，右外连接可以省略OUTER关键字，但可以添加可选的ALL关键字，因此可以写成ALL RIGHT JOIN，这样就可以正常工作了。

(LEFT/RIGHT) SEMI JOIN

LEFT SEMI JOIN 查询返回在右表中至少有一条连接键匹配的左表的中每一行的列值。只返回找到的第一个匹配项（笛卡尔集被禁用）。

ARIGHT 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返回右表中在右表中没有匹配的行。

我们之前的外连接示例查询的另一种表述方式是使用反连接来查找数据集中没有类型的电影：

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 ANY 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

Martijn Bakker和Artem Zuikov于2019年为ClickHouse实现的ASOF JOIN提供了非精确匹配功能。如果左表中的某一行在右表中没有完全匹配的记录，那么右表中最接近的匹配记录就会被用作匹配记录。

这对时间序列分析特别有用，可以大大降低查询的复杂性。

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

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

使用 ASOF JOIN 可以轻松而简洁地完成这项工作，我们可以使用ON子句指定精确匹配条件，使用AND子句指定最接近匹配条件--对于特定的股票（精确匹配），我们要从报价表中查找时间正好或早于该股票交易时间（非精确匹配）的 "最接近"时间的记录：

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.