Сказ о том, как я сменил слона на дом
В этой статье будет рассказано про переезд с PostgreSQL на Clickhouse в одном очень интересном социальном проекте, с пока ещё большим (а в перспективе огромным) объёмом данных.
Имеем мы базу данных PostgreSQL, в которую с периодичностью два раза в неделю льётся поток данных. Данные эти преобразовываются, и есть потребность по этим преобразованным данным делать большие аналитические запросы, а по их результатам формировать отчёты и графики.
Когда данных было мало, с этой задачей прекрасно справлялся и Postgres, однако, надо понимать, что Postgres - не аналитическая БД и под такие задачи не заточен. Да, можно настраивать и оптимизировать, но до каких пор?
В итоге решили найти более подходящую под эти цели СУБД для аналитических запросов.
ClickHouse полностью решает основную проблему с долгим исполнением больших аналитических запросов, и ему не так больно считать COUNT. Да и в целом ClickHouse изначально разрабатывался ребятами из Yandex для хранения и быстрого анализа логов из метрики.
Сами ребята из ClickHouse на сайте с доками опубликовали интересные GIF’ки со сравнением столбцовых и строковых СУБД.
Более детальные причины того, почему столбцовые СУБД строят отчёты быстрее, можно почитать в той же документации. Если тезисно, то причина в самом подходе хранения: в столбцовой СУБД данные хранятся по столбцам, что позволяет прочитать только необходимые данные, минуя чтение всей строки.
Поработав с ClickHouse, выявили для себя следующие плюсы:
- Низкий порог вхождения (в большинстве своём это всё тот же старый добрый SQL)
- Это бесплатно! (OpenSource)
- Нереальная скорость выполнения запросов для отчётов
- Хорошее сжатие данных
- Классное русскоязычное комьюнити в телеге
- Все симптомы BIGDATA (кластеризация, репликация)
- Сделан неглупыми людьми из Yandex
С чем мы столкнулись при переезде?
Самая очевидная задача, которая возникает при переезде с любой СУБД, звучит следующим образом: как забрать со старой базы данных все данные и безболезненно положить эти данные в новую базу с новой структурой?
Перенести данные в Clickhouse можно разными способами:
- Собрать всё в локальный файл (json, csv, tsv и др.)
- Использовать инструменты для миграции данных (DBeaver)
- Использование специальных движков таблиц
- Самописный скрипт
Вторая проблема, с которой я столкнулся, это наша потребность преобразовывать данные, так как в clickhouse нет полноценного оператора UPDATE и DELETE. Есть не так много надёжных вариантов обновлять данные, а именно:
В случае с мутациями это работает как старый добрый UPDATE, однако, он изменяет всю таблицу, то есть читает огромный кусок данных, что для точечных обновлений не особо подходит, так как частые мутации сильно сказываются на накопителях.
В clickhouse для решения этой проблемы предусмотрены специальные движки, а именно:
Первые два движка подразумевают наличие в таблице спец. полей, которые необходимо учитывать в секции WHERE при написании запроса. Обновление происходит не затиранием записи, а путём дублирования и установки определённого значения в спец. поле для отличия последней копии. Данные при этом не удаляются.
Отличие первого от второго в том, что у второго движка иной алгоритм свёртывания, который позволяет вставлять данные в несколько потоков, даже без соблюдения порядка вставки.
Последний движок тоже вставляет копию записи при обновлении, но отличается тем, что схлопывает дубли по специальному полю или набору полей, указанной в секции ORDER BY. Схлопывание происходит в случайный момент времени или по запросу OPTIMIZE. Почти напоминает привычный UPDATE, и не так болезненно для точечных изменений.
C точечным удалением похожая история, варианты следующие:
Был выбран второй вариант, ввиду тех же причин с мутациями. В перспективе можно и полностью удалять помеченные записи, используя ALTER.
Так как проект написан на Ruby on Rails, то хотелось бы интегрировать clickhouse максимально быстро, просто и без изменения кода. В документации clickhouse есть раздел, который называется “Библиотеки для интеграции от сторонних разработчиков”, там указаны проверенные гемы, среди который можно заметить clickhouse-activerecord. Он работает почти так же, как и обычный activerecord, плюс ко всему не надо писать новые модели, всё завелось с коробки.
Из минусов можно отметить, что с этим гемом неадекватно работает rspec. Это связано с тем, что для отправки sql запросов используется http протокол, который в тестах принято не использовать.
4. Оптимизация больших запросов.
Потом долгое время никаких проблем не наблюдалось и мы решили визуализировать нашу базу и ужаснулись, графики строились совсем не так быстро, как мы ожидали…
Хотя вины Clickhouse здесь абсолютно никакой нет, запрос, который мы посылаем, достаточно жирный и сложный. Для того чтобы его упростить, мы решили кешировать промежуточные результаты в так называемых представлениях (VIEW). Если отойти от терминов и максимально упростить, то VIEW по сути как алиас для запросов.
В clickhouse есть три вида представлений:
Обычное представление просто сохраняет сам запрос в качестве шаблона, результат запроса при этом не сохраняется, и при вызове сначала выполнится запрос, что нам категорически не подходит.
MATERIALIZED VIEW - сохраняет результат выполнения запроса, однако для сохранения исходных данных нужно использовать секцию POPULATE, и есть ряд нюансов, которые усложняют агрегацию данных.
LIVE VIEW - как раз делает то, что нам нужно: сохраняет запрос в качестве шаблона, а также сохраняет результат. Можно настраивать время обновления данных, а также количество пакетов, после которых будет происходить обновление. Вроде идеально подходит, и даже первое время была задействована эта штука, но есть небольшое НО. Так как LIVE VIEW обновляется в случае изменения таблицы, которая указана в секции FROM, а мы часто обновляем данные, эта штука нам не совсем подходит.
В качестве альтернативы были выбраны внешние словари.
Внешние словари это обычные таблицы, но с движком DICTIONARY. Для таких таблиц обязательно надо указывать источник данных. В качестве источника можно использовать: файл, http ресурс, другая таблица в БД, запрос. Используя внешние словари, мы добились желаемого результата, теперь можно сохранить результат промежуточного запроса в словаре, который сам будет динамически подгружать данные в заданный временной интервал, при этом сохраняя доступ к текущим данным.
Время выполнения большого аналитического запроса:
PostgreSQL : 8 мин. 16 сек. ClickHouse: 0,603 сек.
И ещё немного по самому больному (COUNT).
Замер скорости отработки бота-нотификатора по итогам парсинга:
PostgreSQL: 17 мин. 40 сек. (только первый запрос) ClickHouse: 2,212 сек.
Каждую задачу нужно решать теми инструментами, которые для этого предназначены.
Нельзя сказать, что Clickhouse хуже или лучше PostgreSQL. Просто его создавали для других задач.
Clickhouse, Postgres, Внешние словари, Clickhouse UPDATE, Clickhouse DELETE, Clickhouse VIEWS