Сразу разочарую тех, кто пришел посмотреть, как ClickHouse "магически" разруливает распределенные таблицы, борется с огромными объемами данных и за миллисекунды выдает ответ. Да и таблиц будет не 11, а всего 2 (просто join'ов - 11).
К сожалению (к счастью), у меня размер данных небольшой, кликхаус всего один и он в докере. Так что здесь я просто буду писать обычный SQL-запрос, просто большой.
Задача
Опишу, что я хотел сделать. У меня pet-проект alltid-like, собирающий статистку для Hattrick (футбольный менеджер). Весь этот сбор статистики неплохо ложится на концепцию MergeTree в ClickHouse, поэтому я его и выбрал.
Есть два стула две таблицы: статистика по матчам и статистика по игрокам (по второй таблице тоже можно считать показатели команд).
Проблема больших объемов не стоит, потому что я легко из приложения гранулирую запросы по части первичного ключа (season, league_id, division_level, league_unit_id, round).
Итак, задача: ранжировать команду по силе в лиге по различным показателям. Не понятно? Пример:
Возьмем вторую таблицу match_details. Хотим выяснить, на каком месте по рейтингу полузащиты (rating_midfield) данная команда находится в лиге. Потом хотим выяснить, на каком месте команда находится по среднему рейтингу атаки (среднее rating_right_att, rating_mid_att, rating_left_att), защиты и т.д. Более того, у нас есть еще первая таблица player_stats, по которой мы тоже можем считать показатели (средняя зарплата, например, количество желтых карточек и т.д.). Итого, хочется свести все эти данные в одну таблицу, чтобы это выглядело примерно так:
Пишем отдельные запросы
Используя функцию rowNumberInAllBlocks(), нетрудно написать запрос к одной таблице. Запрос к match_details совсем простой:
С player_stats чуть-чуть сложнее, потом что там нужно аггрегировать. Но тоже ничего сложного:
Финальная сортировка в обоих случаях сделана по team_name, team_id просто для наглядности грядущего join'a.
Как нам одним запросом получить сразу полную таблицу? Ничего не придумал лучше, кроме как заджойнить все такие запросы
Начинаем джойнить
Начем с джойна двух вышеуказанных таблиц:
Немного громоздко, но ничего не поделать. Осталось просто "прикрутить" остальные запросы?
Проблема
В ClickHouse нельзя написать "множественный JOIN". Как вариант заджойнить несколько таблиц - это множество попарных джойнов подзапросов.
Схема "подцепления" нового JOIN:
Select базоые_поля, новая_пара_полей FROM (наш_join_запрос) as new_alias LEFT JOIN (новый запрос) as table_alias ON new_alias.team_id = table_alias.team_id
И так еще 9 раз. План есть, можно делать? Нет:
- слишком легко запутаться
- невозможно разобраться и при необходимости подправить запрос
- добавление/удаление частей подзапроса в будущем - это неимоверная БОЛЬ
Давайте лучше автоматизируем!
Пишем код
Пишу на Java, потому что умею проект на написан на этом языке. Для начала выделим в запросах к таблице "переменые":
- field - поле, по которому строим запрос (может быть функцией)
- field_alias - то, как мы его обозвали
- where, database - к делу не относятся, но мне понадобилось выделить и их тоже
То же самое для player_stats
По "схеме подцепления" есть
- базовые поля
- новые поля - при каждом "подцеплении" их нужно добавлять
- new_alias, table_alias - имена подзапросов. С table_alias все понятно: можно формировать его на основе имени field. С new_alias будет сложнее, нужно придумывать название уже для n "cцепленных" таблиц
Введем эти переменные и вспомогательный класс:
Теперь для того, чтобы указать, ранжирования по каким полям нам нужны, просто создаем список SqlRequestParam:
Все, теперь можно писать основной цикл, следуя "схеме подцепления". Позволим себе небольшую слабость и первый запрос JOIN'a напишем отдельно, а остальные будут формироваться в цикле по SqlRequestParam:
Готово! Итоговый SQL-запрос вышел порядка 500 строк, поэтому здесь я его приводить не буду. А вот полный код можно увидеть здесь.
Производительность
На самом "жирном" куске данных, которые я джойню, у меня записей в match_details - 25,000, в player_stats - 1,000,000. Такой запрос отработал меньше, чем за секунду. Других объемов пока не подразумевается, так что такое меня вполне себе устроило.
Вывод
Не представляю, как такую махину можно было бы написать "руками". Хорошо, когда есть возможность заставить машину что-то делать за тебя