ClickHouse でサポートされている JOIN タイプ

今すぐ ClickHouse Cloud を始めて、$300 のクレジットを受け取りましょう。ボリュームベースの割引について詳しくは、お問い合わせいただくか、料金ページをご覧ください。

本ブログ記事は連載の一部です。

ClickHouseはオープンソースの列指向DBMSであり、大量のデータに対する超低レイテンシの分析クエリを必要とするユースケース向けに構築・最適化されています。分析アプリケーションで最高のパフォーマンスを引き出すには、データの非正規化と呼ばれる処理によってテーブルを結合しておくのが一般的です。フラット化されたテーブルは結合処理を回避してクエリレイテンシを最小限に抑えるのに役立ち、ETLの複雑さが増すものの、通常は1秒未満のクエリ応答を得るためのトレードオフとして受け入れられます。

しかし、従来のデータウェアハウスなどから移行されたワークロードをはじめ、データの非正規化が常に現実的とは限らず、分析クエリの元データの一部を正規化されたまま保持しなければならないケースもあることを私たちは認識しています。こうした正規化されたテーブルはストレージ消費量が少なく、データ組み合わせの柔軟性にも優れますが、特定の分析ではクエリ実行時に結合処理が必要になります。

幸いなことに、一部の誤解とは異なり、ClickHouseではJOINが完全にサポートされています。すべての標準SQL JOINタイプのサポートに加え、ClickHouseは分析ワークロードや時系列分析に役立つ独自の追加JOINタイプも提供しています。JOINの実行においては、6種類の異なるアルゴリズム(本ブログ連載の次回で詳しく解説します)から選択することも、リソースの可用性と使用状況に応じてクエリプランナーに実行時での適応的な選択や動的な変更を任せることも可能です。

ClickHouseでは大きなテーブル同士の結合であっても優れたパフォーマンスを発揮できますが、特にこのユースケースでは現時点で、クエリワークロードに合わせてJOINアルゴリズムを慎重に選択・調整する必要があります。この処理も将来的にはより自動化され、ヒューリスティック主導になると見込んでいますが、本ブログ連載ではClickHouseにおけるJOIN実行の内部構造を深く理解し、アプリケーションで使われる一般的なクエリ向けにJOINを最適化できるようにします。

今回の記事では、正規化されたリレーショナルデータベースのサンプルスキーマを使用して、ClickHouseで利用可能なさまざまなJOINタイプを説明します。次回以降の記事では、ClickHouseで利用できる6種類のJOINアルゴリズムの内部動作を詳しく見ていきます。JOINタイプを可能な限り高速に実行するために、ClickHouseがこれらのJOINアルゴリズムをクエリパイプラインにどのように統合しているかを探ります。今後の回では分散JOINも取り上げる予定です。

テストデータとリソース

ClickHouseで利用可能なJOINタイプを説明するために、relational dataset repositoryに由来する正規化されたIMDBデータセットをもとに、ベン図とサンプルクエリを使用します。

テーブルの作成と読み込みの手順はこちらにあります。クエリを再現したいユーザー向けに、このデータセットはplaygroundでも利用可能です。

サンプルデータセットから 4 テーブルを使用します。

これら 4 テーブルのデータは**映画(movies)を表しています。映画は1つまたは複数のジャンル(genres)を持つことができます。映画内の役(roles)は俳優(actors)**によって演じられます。上の図の矢印は外部キーと主キーのリレーションシップを示しています。たとえば、genresテーブルの行のmovie_idカラムには、moviesテーブルの行のid値が含まれます。

映画と俳優の間には多対多のリレーションシップがあります。この多対多のリレーションシップは、rolesテーブルを使用して2つの1対多のリレーションシップに正規化されています。rolesテーブルの各行には、moviesテーブルとactorsテーブルのidカラムの値が含まれます。

ClickHouseでサポートされているJOINタイプ

INNER JOIN

INNER JOINは、結合キーが一致する行のペアごとに、左テーブルの行のカラム値と右テーブルの行のカラム値を組み合わせて返します。ある行に複数のマッチが存在する場合はすべてのマッチが返されます(結合キーが一致する行に対して直積が生成されることを意味します)。

次のクエリは、moviesテーブルとgenresテーブルを結合して各映画のジャンルを検索します。


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キーワードは省略可能です。

以降で紹介する他のJOINタイプを使用することで、INNER JOINの動作を拡張または変更できます。

(LEFT / RIGHT / FULL) OUTER JOIN

LEFT OUTER JOINはINNER JOINと同様に動作しますが、一致しない左テーブルの行に対して、ClickHouseは右テーブルのカラムにデフォルト値を返します。

RIGHT OUTER JOINクエリも同様で、一致しない右テーブルの行の値とともに、左テーブルのカラムのデフォルト値を返します。

FULL OUTER JOINクエリはLEFT OUTER JOINとRIGHT OUTER JOINを組み合わせたもので、左テーブルと右テーブルの一致しない行の値を、それぞれ右テーブルと左テーブルのカラムのデフォルト値とともに出力します。

なお、ClickHouseではデフォルト値の代わりにNULLを返すように設定することも可能です(ただし、パフォーマンス上の理由からあまり推奨されません)。

次のクエリは、genresテーブルに一致するレコードがなく、クエリ実行時にmovie_idカラムにデフォルト値0が割り当てられるmoviesテーブルの全行を検索することで、ジャンルのないすべての映画を特定します。


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は、結合キーを考慮せずに2つのテーブルの完全な直積を生成します。左テーブルの各行が右テーブルのすべての行と組み合わされます。

したがって、次のクエリは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句を追加することで、各映画のジャンルを検索する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.)

✎

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.

✎

構文最適化されたCROSS JOINクエリ版のINNER JOIN句にはALLキーワードが含まれています。これは、直積を無効化できるINNER JOINに書き換えられた後でも、CROSS JOINの直積セマンティクスを維持するために明示的に追加されたものです。

前述のとおりRIGHT OUTER JOINではOUTERキーワードを省略でき、オプションのALLキーワードを追加できるため、ALL RIGHT JOINと記述しても問題なく動作します(all rightにかけたつくりになっています)。

(LEFT / RIGHT) SEMI JOIN

LEFT SEMI JOINクエリは、右テーブルに結合キーの一致が1つ以上ある左テーブルの行について、そのカラム値を返します。最初に見つかったマッチのみが返されます(直積は無効化されます)。

RIGHT SEMI JOINクエリも同様で、左テーブルに1つ以上のマッチがある右テーブルのすべての行の値を返しますが、最初に見つかったマッチのみが返されます。

次のクエリは、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は一致しない右テーブルのすべての行についてカラム値を返します。

先ほどの外部結合のサンプルクエリの別表現として、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です。

values テーブル関数を使って作成した2つの一時テーブル(left_tableとright_table)による抽象的な例で、LEFT 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
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

2019年に Martijn Bakker 氏と Artem Zuikov 氏によって ClickHouse に実装された ASOF JOIN は、非厳密な一致(non-exact matching)機能を提供します。左側のテーブルの行が右側のテーブルで完全一致しない場合、右側のテーブルから最も近い一致行が代わりに使用されます。

これは時系列データの分析に特に有用であり、クエリの複雑さを大幅に低減できます。

例として株式市場データの時系列分析を行います。quotes テーブルには、1 日の特定の時間に基づいた銘柄の気配値が含まれています。今回のサンプルデータでは、価格は 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.

なお、ASOF JOIN の ON 句は必須であり、AND 句の非厳密な一致条件と並んで完全一致の条件を指定します。

ClickHouse は現時点では、結合キーの一部に完全一致を含まない結合を(まだ)サポートしていません。

まとめ

本ブログ記事では、ClickHouse がすべての標準 SQL JOIN タイプに加えて、分析クエリを強化する特殊な結合をどのようにサポートしているかを紹介しました。サポートされているすべての JOIN タイプについて解説し、実演しました。

本シリーズの次回以降では、今回紹介した結合タイプを可能な限り高速に実行するために、ClickHouse が従来の結合アルゴリズムをクエリパイプラインにどのように適応させているかを探っていきます。

ご期待ください！

今すぐ ClickHouse Cloud を始めて、$300 のクレジットを受け取りましょう。30 日間の無料トライアル終了後は、従量課金プランに移行できます。ボリュームベースの割引について詳しくはお問い合わせください。詳細は料金ページをご覧ください。

ClickHouse でサポートされている JOIN タイプ

テストデータとリソース

ClickHouseでサポートされているJOINタイプ

INNER JOIN

(LEFT / RIGHT / FULL) OUTER JOIN

CROSS JOIN

(LEFT / RIGHT) SEMI JOIN

(LEFT / RIGHT) ANTI JOIN

(LEFT / RIGHT / INNER) ANY JOIN

ASOF JOIN

まとめ

Subscribe to our newsletter

最近の記事

ClickHouse が dbt プラットフォームで利用可能になりました

Replica-aware routing のパブリックベータ

ClickHouse の新しい TimeSeries エンジンのご紹介: Prometheus のドロップイン代替

ClickHouse の AI Functions: SQL を AI 時代へアップグレード

Follow us

ClickHouse でサポートされている JOIN タイプ

テストデータとリソース #

ClickHouseでサポートされているJOINタイプ #

INNER JOIN #

(LEFT / RIGHT / FULL) OUTER JOIN #

CROSS JOIN #

(LEFT / RIGHT) SEMI JOIN #

(LEFT / RIGHT) ANTI JOIN #

(LEFT / RIGHT / INNER) ANY JOIN #

ASOF JOIN #

まとめ #

Subscribe to our newsletter

最近の記事

ClickHouse が dbt プラットフォームで利用可能になりました

Replica-aware routing のパブリックベータ

ClickHouse の新しい TimeSeries エンジンのご紹介: Prometheus のドロップイン代替

ClickHouse の AI Functions: SQL を AI 時代へアップグレード

Follow us

テストデータとリソース

ClickHouseでサポートされているJOINタイプ

INNER JOIN

(LEFT / RIGHT / FULL) OUTER JOIN

CROSS JOIN

(LEFT / RIGHT) SEMI JOIN

(LEFT / RIGHT) ANTI JOIN

(LEFT / RIGHT / INNER) ANY JOIN

ASOF JOIN

まとめ