JOIN в ClickHouse: INNER, LEFT, ANY и ASOF на примерах

ClickHouse поддерживает JOIN, но подход к соединениям здесь отличается от классической OLTP-базы. Для быстрых аналитических запросов важно понимать размер правой таблицы, тип JOIN, ключи соединения и альтернативы: денормализацию, словари и materialized views

Учебный датасет

CREATE TABLE demo.events
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    amount Decimal(12, 2)
)
ENGINE = MergeTree
ORDER BY (event_date, event_type, user_id);
CREATE TABLE demo.users
(
    user_id UInt64,
    country LowCardinality(String),
    plan LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY user_id;

events — фактовая таблица. users — справочник пользователей. Это типичная схема для аналитики: много событий и сравнительно небольшая таблица атрибутов

INNER JOIN

INNER JOIN оставляет только строки, где есть совпадение с обеих сторон

SELECT
    u.country,
    count() AS events,
    sum(e.amount) AS revenue
FROM demo.events AS e
INNER JOIN demo.users AS u USING (user_id)
GROUP BY u.country
ORDER BY revenue DESC;

Если для события нет пользователя в справочнике, строка исчезнет из результата. Для отчетов это может быть правильно, а может скрыть проблему качества данных

LEFT JOIN

LEFT JOIN сохраняет все строки из левой таблицы. Если справа нет совпадения, правые поля будут заполнены значениями по умолчанию или NULL, в зависимости от настроек и типов

SELECT
    if(u.country = '', 'unknown', u.country) AS country,
    count() AS events
FROM demo.events AS e
LEFT JOIN demo.users AS u USING (user_id)
GROUP BY country
ORDER BY events DESC;

ANY JOIN

ANY JOIN полезен, когда справа может быть несколько совпадений, но вам нужен только один результат. Например, если справочник по ошибке содержит дубли, обычный JOIN может размножить строки, а ANY LEFT JOIN возьмет один матч

SELECT
    e.user_id,
    e.event_type,
    u.plan
FROM demo.events AS e
ANY LEFT JOIN demo.users AS u USING (user_id);

Это не замена чистым данным. Если справа не должно быть дублей, лучше чинить справочник. Но для аналитического запроса ANY может защитить от неожиданного размножения строк

ASOF JOIN

ASOF JOIN нужен для временных рядов: найти ближайшее предыдущее значение по времени. Например, событие пользователя и актуальный на тот момент тариф или курс валюты

CREATE TABLE demo.plan_history
(
    user_id UInt64,
    changed_at DateTime,
    plan LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (user_id, changed_at);
SELECT
    e.user_id,
    e.event_time,
    e.event_type,
    p.plan
FROM demo.events AS e
ASOF LEFT JOIN demo.plan_history AS p
ON e.user_id = p.user_id AND e.event_time >= p.changed_at;

Почему JOIN может быть тяжелым

В аналитике левая таблица часто огромная. Если правая таблица тоже большая, запросу может понадобиться много памяти и времени. Поэтому для постоянных отчетов часто делают денормализованную таблицу событий, словарь или materialized view

• Маленький справочник: JOIN обычно нормален.
• Большая правая таблица: проверьте память и план запроса.
• Повторяющийся отчет: лучше подготовить витрину.
• Ключ-значение справочник: рассмотрите dictionaries.

EXPLAIN для JOIN

EXPLAIN PLAN
SELECT e.user_id, u.plan
FROM demo.events AS e
ANY LEFT JOIN demo.users AS u USING (user_id);

EXPLAIN помогает увидеть общий план выполнения. Для серьезной оптимизации смотрите не только план, но и фактические метрики выполнения, объем прочитанных данных и настройки JOIN

Когда заменить JOIN materialized view

Если один и тот же JOIN используется в каждом отчете, можно заранее записывать нужные поля в агрегированную таблицу. Например, считать выручку по странам в materialized view при вставке событий, а не соединять таблицы каждый раз

Чеклист перед тяжелым JOIN

• Проверьте количество строк в правой таблице.
• Проверьте, уникален ли ключ справа, если вы ожидаете один матч.
• Уберите из SELECT лишние колонки до соединения.
• Отфильтруйте левую таблицу по дате до JOIN, если это возможно.
• Запустите EXPLAIN и посмотрите общий план.
• Для постоянных отчетов рассмотрите materialized view или словарь.

Почти всегда дешевле уменьшить данные до соединения, чем соединить все, а потом фильтровать результат. Поэтому условия по дате, типу события и другим сильным фильтрам лучше применять как можно раньше

Пример с предварительной фильтрацией

SELECT
    u.country,
    count() AS purchases,
    sum(e.amount) AS revenue
FROM
(
    SELECT user_id, amount
    FROM demo.events
    WHERE event_date >= today() - 7
      AND event_type = 'purchase'
) AS e
LEFT JOIN demo.users AS u USING (user_id)
GROUP BY u.country
ORDER BY revenue DESC;

Что проверить перед JOIN в production

Когда JOIN лучше оставить как есть?

Когда правая таблица небольшая, ключи чистые, а запрос не стоит в горячем дашборде. Для учебной схемы достаточно понимать INNER, LEFT и ANY, но для реальной нагрузки сначала проверьте EXPLAIN и количество строк после соединения

Что делать, если JOIN повторяется в каждом отчете?

Есть два частых пути: денормализовать часть полей в fact table или предрассчитать результат в витрине. Второй путь подробно разобран в материале Materialized View в ClickHouse

Где искать причину странных дублей?

Сначала в данных правой таблицы: один ключ может соответствовать нескольким строкам. Потом в модели хранения и типах ключей. Если ключи приехали как строки, даты или nullable-поля без контроля, полезно вернуться к уроку CREATE TABLE в ClickHouse и проверить схему