Привет! Меня зовут Роман Бунин, я BI-евангелист Yandex DataLens. При росте объёма данных, что неизбежно для любой компании, загрузка дашбордов может замедляться до десятков секунд. И чем больше появляется данных, тем медленнее становятся дашборды, особенно если вы хотите строить их по детализированным таблицам. Связка базы данных ClickHouse и BI-системы Yandex DataLens — популярное решение для анализа данных: эти инструменты нативно интегрируются и быстро работают вместе. В этой статье вместе с моими коллегами, архитекторами Yandex Cloud Игорем Путятиным и Кузьмой Лешаковым, покажем, как на основе таблицы из 150 миллионов строк построить максимально быстрый дашборд, и расскажем о технических ограничениях.

Зачем делать быстрые дашборды ещё быстрее: кейс Яндекс Go

Внутри Яндекса мы сами используем дашборды и продуктовые подходы к созданию системы отчётности. Поэтому регулярно опрашиваем коллег, что важно для них при работе с дашбордами. Опрос команды Яндекс Go показал, что самое узкое место этого инструмента — быстродействие. 

В 2022 году команда Go перешла на Yandex DataLens. Раньше ребята использовали Tableau, у инструмента есть своя проприетарная база данных Hyper, но она не позволяла добиться быстродействия, которое устраивало бы пользователей. 

DataLens использует асинхронный способ отрисовки и загрузки графиков и нативно подключается к ClickHouse, что позволило команде Go добиться ускорения основной отчётности.

Сначала команда перенесла данные из своего внутреннего хранилища YT (аналог вышедшего недавно в опенсорс YTsaurus) в ClickHouse. Это позволило задействовать возможности ClickHouse, чтобы оптимально хранить и обрабатывать данные. В итоге коллегам удалось добиться быстродействия гораздо выше, чем в связке с внутренней базой данных Tableau. 

Дальше нужно было понять, какой именно запрос уходит в базу от дашборда. Асинхронная загрузка чартов, которые находятся в области зрения пользователя, удобный инспектор и быстрая работа БД, которую можно очень глубоко настроить под определённые типы запросов, позволили в 6 раз ускорить загрузку отчёта с источником в 100 миллионов строк: от 24–27 секунд в Tableau, до 4–6 секунд в DataLens. Бизнес-пользователи почувствовали и оценили этот результат.

Совместное использование ClickHouse и Yandex DataLens

Мы вдохновились опытом Яндекс Go и решили поделиться с вами советами, как оптимизировать работу дашборда. 

В качестве тестового набора данных мы использовали базу отзывов о товарах, которую обычно предлагаем для тестирования ClickHouse — из 151 миллиона строк. Мы построили дашборд, который решал бы задачи категорийных менеджеров: с его помощью можно оценить, как часто люди пишут отзывы на товары, какие товары самые популярные — или наоборот, что стоит убрать из ассортимента. 

Для пользователей важна возможность просмотреть отзывы по товару:

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

Оптимизация скорости

Источник данных для дашборда — одна большая и широкая таблица с детализированными данными об отзывах о товарах. Содержимое дашборда представляет собой различные агрегации элементов таблицы. И не забываем, что нужно предусмотреть возможность просматривать неагрегированные данные — отдельные записи с отзывами.

Для такого сценария в качестве СУБД для поставки данных дашборду идеально подходит ClickHouse. Построение отдельных графиков и виджетов, которые используют небольшое количество полей в исходной таблице, происходит быстро благодаря колоночному хранению: сканируются только нужные колонки из исходной таблицы. Таким образом, нам не нужно создавать и поддерживать дополнительные таблицы агрегации — все графики и таблицы дашборда строятся на лету по одной таблице в ClickHouse.

Для решения задачи мы создали управляемый кластер ClickHouse в Yandex Cloud. Сервисы управляемых баз данных в Yandex Cloud позволяют развёртывать различные СУБД за пару минут — достаточно лишь выбрать нужную конфигурацию ресурсов в веб-интерфейсе и нажать кнопку «Создать кластер».

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

Диски типа local ssd не отказоустойчивы, а потому Managed ClickHouse обязал нас для надёжности создать кластер как минимум с одной репликой.

Окончательная конфигурация кластера получилась такой: два хоста, в каждом по 12 vCPU, 48 GB RAM и 368 GB local ssd storage.

Исходные данные лежат в виде CSV-файла в публичном S3-хранилище. Его загрузку в ClickHouse мы выполнили одной операцией вставки благодаря тому, что ClickHouse поддерживает внешние таблицы, загружающие данные по протоколу S3.

Таблица с отзывами хранится в ClickHouse в формате ReplicatedMergeTree. Этот формат также называется движком. В таблицах такого типа данные хранятся поколоночно, отсортированными и сжатыми. Также вместе с таблицей всегда создаётся первичный индекс по атрибутам сортировки.

В нашем дашборде применяются суммарные агрегации по всей таблице (общее количество отзывов, средняя оценка), а также агрегации по дате, по категориям и по продуктам плюс фильтрация по этим же измерениям. Для оптимизации мы выбрали сортировку данных по категории, затем по продукту. Продукт — измерение с самой высокой кардинальностью из использующихся, а категория однозначно зависит от продукта. Храня данные отсортированными подобным образом, мы добиваемся быстрой фильтрации по категориям и продуктам.

Такая сортировка обеспечивает быструю работу вкладки с детальным отзывами, даже если нужно открыть несколько отзывов из 150 миллионов. При запросе отзывов по конкретному продукту ClickHouse знает, в каком месте файла с данными начинаются данные об этом продукте, и читает только эту часть файла. При этом данные по искомому продукту идут последовательно и не разбросаны по разным частям файла, то есть для вывода отзывов базе данных достаточно прочитать небольшую порцию данных в середине файла.

Для ускорения отображения дашборда мы использовали механизм проекций, который поддерживает ClickHouse. Проекции — это дополнительные структуры предагрегированных данных, которые хранятся в СУБД и привязаны к основной таблице. При выполнении запроса оптимизатор может читать данные из проекции вместо таблицы, если агрегации в запросе совпадают с агрегациями в проекции. Проекции обновляются асинхронно командой MATERIALIZE, и эта операция сопоставима по времени с запросом из таблицы, не имеющей проекции. В нашем примере это достаточно сделать однократно.

Для дашборда мы создали три проекции: по дате помесячно, по категориям продуктов и по продуктам. Таким образом, при первоначальном открытии дашборда для двух графиков и двух таблиц, загружавшихся дольше всего, данные считываются из проекций.

Для дальнейшего повышения производительности кластера можно было бы применить горизонтальное масштабирование, то есть увеличение числа хостов в кластере. ClickHouse поддерживает шардирование и параллельную обработку запросов. Так получилось бы кратно увеличить производительность базы данных.

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

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

Видно, что скорость загрузки большинства чартов — не больше одной секунды. Дольше всего загружается детальная таблица товаров, так как на ней отображаются самые подробные данные.

На скриншоте — пример замера скорости с помощью инспектора:

Мы также поработали на стороне DataLens и дизайна дашборда:

• Вместо функции COUNTD для подсчёта уникальных пользователей использовали функцию APPROX_COUNTD.

• Для топа товаров и продуктов использовали пагинацию с ограничением на вывод одновременно 10 категорий или товаров.

• Таблицу с детальными отзывами мы вынесли на отдельную вкладку, переход на которую происходит по клику на название категории или товара на основном дашборде. Это позволяет легко выбрать нужный товар, не выводя при этом в таблицу все отзывы по всем товарам.

Выводы

Для демонстрации возможностей работы ClickHouse c Yandex DataLens мы разработали дашборд, который берёт данные из таблицы в ClickHouse. Теперь все чарты загружаются в среднем за полторы-две секунды — ниже таблица со временем загрузки. Это отличный результат, которого получилось добиться, используя базовую функциональность DataLens и ClickHouse. Вы можете сами оценить скорость работы дашборда на видео ниже или попробовать его публичную версию.