Адекватно ли пользователи оценивают товар или это нечестная борьба конкурентов?
Как сделать быстрый дашборд по таблице из 150 млн строк
BI‑евангелист Yandex DataLens Роман Бунин вместе с архитектором Yandex Cloud Игорем Путятиным рассказали, как Yandex DataLens и Yandex Managed Service for ClickHouse® помогают создавать супербыстрые дашборды.
13 июля 2023 г.
15 минут чтения
При росте объёма данных, что неизбежно для любой компании, загрузка дашбордов может замедляться до десятков секунд. И чем больше появляется данных, тем медленнее становятся дашборды, особенно если вы хотите строить их по детализированным таблицам. Связка базы данных ClickHouse и BI‑системы Yandex DataLens — популярное решение для анализа данных: эти инструменты нативно интегрируются и быстро работают вместе. В этой статье мы покажем, как на основе таблицы из 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. Бизнес‑пользователи почувствовали и оценили этот результат.
Совместное использование Yandex Managed Service for ClickHouse и Yandex DataLens
Мы вдохновились опытом Яндекс Go и решили поделиться с вами советами, как оптимизировать работу дашборда.
В качестве тестового набора данных мы использовали базу отзывов о товарах, которую обычно предлагаем для тестирования Yandex Managed Service for ClickHouse — из 151 миллиона строк. Мы построили дашборд, который решал бы задачи категорийных менеджеров: с его помощью можно оценить, как часто люди пишут отзывы на товары, какие товары самые популярные — или наоборот, что стоит убрать из ассортимента.
Для пользователей важна возможность просмотреть отзывы по товару:
Оптимизация скорости
Источник данных для дашборда — одна большая и широкая таблица с детализированными данными об отзывах о товарах. Содержимое дашборда представляет собой различные агрегации элементов таблицы. И не забываем, что нужно предусмотреть возможность просматривать неагрегированные данные — отдельные записи с отзывами.
Для такого сценария в качестве СУБД для поставки данных дашборду идеально подходит ClickHouse. Построение отдельных графиков и виджетов, которые используют небольшое количество полей в исходной таблице, происходит быстро благодаря колоночному хранению: сканируются только нужные колонки из исходной таблицы. Таким образом, нам не нужно создавать и поддерживать дополнительные таблицы агрегации — все графики и таблицы дашборда строятся на лету по одной таблице в ClickHouse.
Для решения задачи мы создали управляемый кластер Yandex Managed Service for ClickHouse в Yandex Cloud. Сервисы управляемых баз данных в Yandex Cloud позволяют развёртывать различные СУБД за пару минут — достаточно лишь выбрать нужную конфигурацию ресурсов в веб‑интерфейсе и нажать кнопку «Создать кластер».
Дашборд может отображать данные за произвольный период, в том числе и по всем имеющимся датам, и к таблице будут идти запросы полного сканирования. При таком профиле нагрузки критично быстродействие дисков. Поэтому мы выбрали диски типа local ssd, которые обеспечивают максимальную производительность.
Диски типа local ssd не отказоустойчивы, а потому Yandex Managed Service for ClickHouse обязал нас для надёжности создать кластер как минимум с одной репликой.
Окончательная конфигурация кластера получилась такой: два хоста, в каждом по 12 vCPU, 48 GB RAM и 368 GB local ssd storage.
Исходные данные лежат в виде CSV‑файла в публичном S3‑хранилище. Его загрузку в Yandex Managed Service for ClickHouse мы выполнили одной операцией вставки благодаря тому, что ClickHouse поддерживает внешние таблицы, загружающие данные по протоколу S3.
Таблица с отзывами хранится в ClickHouse в формате ReplicatedMergeTree. Этот формат также называется движком. В таблицах такого типа данные хранятся поколоночно, отсортированными и сжатыми. Также вместе с таблицей всегда создаётся первичный индекс по атрибутам сортировки.
Yandex Managed Service for ClickHouse
В нашем дашборде применяются суммарные агрегации по всей таблице (общее количество отзывов, средняя оценка), а также агрегации по дате, по категориям и по продуктам плюс фильтрация по этим же измерениям. Для оптимизации мы выбрали сортировку данных по категории, затем по продукту. Продукт — измерение с самой высокой кардинальностью из использующихся, а категория однозначно зависит от продукта. Храня данные отсортированными подобным образом, мы добиваемся быстрой фильтрации по категориям и продуктам.
Такая сортировка обеспечивает быструю работу вкладки с детальным отзывами, даже если нужно открыть несколько отзывов из 150 миллионов. При запросе отзывов по конкретному продукту Yandex Managed Service for ClickHouse знает, в каком месте файла с данными начинаются данные об этом продукте, и читает только эту часть файла. При этом данные по искомому продукту идут последовательно и не разбросаны по разным частям файла, то есть для вывода отзывов базе данных достаточно прочитать небольшую порцию данных в середине файла.
Для ускорения отображения дашборда мы использовали механизм проекций, который поддерживает Yandex Managed Service for ClickHouse. Проекции — это дополнительные структуры предагрегированных данных, которые хранятся в СУБД и привязаны к основной таблице. При выполнении запроса оптимизатор может читать данные из проекции вместо таблицы, если агрегации в запросе совпадают с агрегациями в проекции. Проекции обновляются асинхронно командой MATERIALIZE, и эта операция сопоставима по времени с запросом из таблицы, не имеющей проекции. В нашем примере это достаточно сделать однократно.
Для дашборда мы создали три проекции: по дате помесячно, по категориям продуктов и по продуктам. Таким образом, при первоначальном открытии дашборда для двух графиков и двух таблиц, загружавшихся дольше всего, данные считываются из проекций.
Для дальнейшего повышения производительности кластера можно было бы применить горизонтальное масштабирование, то есть увеличение числа хостов в кластере. Yandex Managed Service for ClickHouse поддерживает шардирование и параллельную обработку запросов. Так получилось бы кратно увеличить производительность базы данных.
Для каждого чарта мы замерили время загрузки с помощью инспектора и собрали их в единую таблицу:
| Время загрузки, мс | |
|---|---|
| Чарт 1: индикатор — количество отзывов | 575 |
| Чарт 2: индикатор — количество пользователей | 953 |
| Чарт 3: индикатор — количество отзывов на пользователя | 628 |
| Чарт 4: линейный график — количество пользователей и отзывов | 2556 |
| Чарт 5: индикатор — средняя оценка | 673 |
| Чарт 6: линейчатая диаграмма — средняя оценка | 681 |
| Чарт 7: таблица — топ категорий | 1143 |
| Чарт 8: линейный график — ср. отзыв | 766 |
| Чарт 9: таблица — топ товаров | 5451 |
| Среднее время | 1492 |
Так как чарты на дашборде загружаются асинхронно, то нельзя сказать, что дашборд грузится за какое‑то конечное количество секунд. Поэтому мы замерили время загрузки каждого чарта по отдельности.
Видно, что скорость загрузки большинства чартов — не больше одной секунды. Дольше всего загружается детальная таблица товаров, так как на ней отображаются самые подробные данные.
На скриншоте — пример замера скорости с помощью инспектора:
Мы также поработали на стороне Yandex DataLens и дизайна дашборда:
-
Вместо функции COUNTD для подсчёта уникальных пользователей использовали функцию APPROX_COUNTD.
-
Для топа товаров и продуктов использовали пагинацию с ограничением на вывод одновременно 10 категорий или товаров.
-
Таблицу с детальными отзывами мы вынесли на отдельную вкладку, переход на которую происходит по клику на название категории или товара на основном дашборде. Это позволяет легко выбрать нужный товар, не выводя при этом в таблицу все отзывы по всем товарам.
Выводы
Для демонстрации возможностей работы Yandex Managed Service for ClickHouse c Yandex DataLens мы разработали дашборд, который берёт данные из таблицы в ClickHouse. Теперь все чарты загружаются в среднем за полторы‑две секунды. Это отличный результат, которого получилось добиться, используя базовую функциональность DataLens и Yandex Managed Service for ClickHouse. Вы можете сами попробовать публичную версию дашборда.