Работа с JOIN в Clickhouse

Clickhouse поддерживает JOIN таблиц с несколькими нюансами. Синтаксис привычный:

ELECT * FROM table1 ANY|ALL INNER|LEFT JOIN table2 USING columns

Объединение таблиц

Нужно учесть, что колонки, по которым происходит объединение должны называться одинаково в двух таблицах. Пусть две таблицы carts и checkout имеют такую структуру:

carts: cart_id | product_id | count | added_date | added_time
checkouts: cart_id | product_id | paid_date | paid_time

таблицы хранят данные о корзинах и оформленных заказах

Тогда запрос будет выглядеть так:

SELECT cart_id, product_id, added_time, paid_time
FROM carts
ANY LEFT JOIN checkouts
USING cart_id, product_id

Колонки cart_id, product_id должны быть в таблицах carts и checkouts

В результате мы увидим все товары, добавленные в корзину, и дату покупки тех товаров, которые были куплены:

┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       1 │          1 │ 2018-03-24 11:11:36 │ 0000-00-00 00:00:00 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘
┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       1 │          2 │ 2018-03-24 11:11:41 │ 0000-00-00 00:00:00 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘
┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       2 │          1 │ 2018-03-24 11:11:46 │ 2018-03-24 11:13:27 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘
┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       3 │          3 │ 2018-03-24 11:11:50 │ 0000-00-00 00:00:00 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘

Как видно, только один товар (корзина) был куплен

Подзапросы

Некоторые колонки в таблицах могут называться одинаково. Тогда стоит использовать подзапросы и псевдонимы (aliases):

SELECT cart_id, product_id, when_added, when_bought

FROM (
SELECT cart_id, product_id, added_time as when_added FROM carts
)

ANY LEFT JOIN (
SELECT cart_id, product_id, paid_time as when_bought FROM checkouts
)

USING cart_id, product_id

Можно использовать псевдонимы и для колонок, которые используются в USING

Тип объединения LEFT|INNER

Тип LEFT сначала выберет все записи из левой таблицы, а затем добавит к ним найденные записи из правой. Это сработает так же, как и в примере выше.

Использование типа INNER приведет к тому, что в результате мы увидим только те записи, которые есть и в правой и в левой таблице:

SELECT cart_id, product_id, added_time, paid_time
FROM carts
ANY INNER JOIN checkouts
USING cart_id, product_id

В результате мы увидим только одну строку (т.к. в правой таблице всего одна запись):

┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       2 │          1 │ 2018-03-24 11:11:46 │ 2018-03-24 11:13:27 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘

Строгость объединения ANY|ALL

Использование ALL приведет к тому, что для каждого ключа из левой таблицы будут выбраны все соответствующие записи из правой:

SELECT

product_id,
added_time,
paid_time
FROM checkouts

ALL LEFT JOIN carts USING (product_id)

Довольно бессмысленный запрос 🙂

В результате увидим все купленные товары со всеми датами добавления их в корзины:

┌─product_id─┬──────────added_time─┬───────────paid_time─┐
│          1 │ 2018-03-24 11:11:36 │ 2018-03-24 11:13:27 │
│          1 │ 2018-03-24 11:11:46 │ 2018-03-24 11:13:27 │
└────────────┴─────────────────────┴─────────────────────┘

Для каждой записи из таблицы checkouts мы выбрали

Более популярным методом объединения при работе с аналитическими данными является ANY. Он выбирает первое попавшееся соответствие из правой таблицы:

SELECT

product_id,
added_time,
paid_time
FROM checkouts

ANY LEFT JOIN carts USING (product_id)

В этот раз мы увидим только одну строку (т.к. она одна в левой таблице):

┌─product_id─┬──────────added_time─┬───────────paid_time─┐
│          1 │ 2018-03-24 11:11:36 │ 2018-03-24 11:13:27 │
└────────────┴─────────────────────┴─────────────────────┘

Первая запись из правой таблицы, соответствующая записи в левой

Оптимизация JOIN

Clickhouse выполняет объединение до фильтрации WHERE. Т.е. сначала все результаты загружаются в память, а уже потом фильтруются, сортируются и группируются. Следовательно, подзапросы лучше использовать, когда нужно объединить только части таблиц. Например:

SELECT cart_id, product_id, added_time, paid_time

FROM (
SELECT * FROM carts WHERE added_date = today()
)

ANY LEFT JOIN (
SELECT * FROM checkouts
)

USING cart_id, product_id

Производительный вариант объединения при фильтрации

Это быстрый вариант запроса. А такой запрос будет работать медленнее, т.к. выгрузит данные из всей таблицы в память перед объединением и последующей фильтрацией:

SELECT cart_id, product_id, added_time, paid_time
FROM carts
ANY LEFT JOIN checkouts
USING cart_id, product_id
WHERE added_time = today()
Медленный вариант объединения при фильтрации

TL;DR

• JOIN в Clickhouse бывает двух типов — INNER (показывает только записи, которые есть и в левой и в правой таблице) и LEFT (показывает все результаты из левой таблицы).
• ANY позволяет выбрать только одну (первую) запись из правой таблицы, а ALL выгрузит все соответствующие записи из правой.
• Для оптимизации объединений лучше использовать подзапросы с фильтрацией.

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