Оптимизация дашбордов в Superset

В этой статье я бы хотела поделиться опытом в оптимизации дашбордов в Apache Superset. Мы в Газпромбанке перешли на этот BI-инструмент относительно недавно. В основном мы работаем в нем с движком Impala.

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

У коллег накопился большой опыт в оптимизации SQL-запросов и в стандартах разработки витрин. Но конкретно в работе с построением дашбордов есть своя специфика, которая не всегда учитывается, когда речь заходит об оптимальности работы. Superset так же проводит все расчеты на стороне источника через запросы к БД, как это происходит в обычных IDE. Только он преобразует конечный результат в графики, что скрывает от наших глаз сложные запросы, которые иногда там формируются.

Ниже мы рассмотрим, как можно оптимизировать работу с датасетами, графиками и содержанием дашборда, а также как ускорить загрузку с помощью агрегированных таблиц.

Зачем нам нужна оптимизация в Superset?

Рассмотрим основные ситуации, когда нужна оптимизация:

• Долгая загрузка дашбордов
  Выполнение запроса по регулярному расчету витрины в течение 30 секунд – это обычно очень быстро. Но если графики загружаются по 30 секунд, может отпасть желание ими пользоваться. Тогда получается, что дашборд не выполняет своего назначения. Если же запросы по какой-то причине работают час или больше, может вовсе накопиться очередь.

• Иногда дашборды не загружаются совсем
  Объем данных для отображения и количество графиков на странице могут быть слишком большими. Тогда страница в браузере может попросту упасть с ошибкой «Out of Memory» в процессе загрузки.

• В худшем случае обновление страницы с дашбордом вызовет memory spill
  То есть операция по выполнению запроса оказывается слишком затратной по памяти. Если объем использованной памяти превышает установленный лимит, данные начинают записываться на диск и вызывают memory spill (так это называется в метриках Cloudera Manager), что влияет на работоспособность системы для других пользователей.

Конечно, запросы от Суперсета крайне редко вызывают значительный memory spill (но бывало, он доходил у нас до нескольких терабайт в день). Однако его наличие как таковое говорит о проблемах с запросом, которые со временем могу усугубиться с приростом объема данных, а также замедлить загрузку дашборда. При этом обновление дашборда запускает не один запрос, а сразу несколько, в зависимости от количества графиков. Так что небольшой spill суммируется.

На каких уровнях возможна оптимизация?

Поскольку, открывая дашборд, мы запускаем запросы к БД, здесь применимы общие правила оптимизации SQL-запросов. Мы стараемся учитывать в своей работе, что запросы будут в общем случае работать лучше, если придерживаться следующих правил (хотя проблемы далеко не всегда решаются так просто):

• Собирать статистику для любой обновленной таблицы, в том числе временной (команда COMPUTE STATS в Impala);
• Уменьшать по возможности объем данных для ресурсоемких операций вроде DISTINCT или GROUP BY (использовать минимально возможное количество полей для группировки, использовать фильтры, материализовать наиболее тяжелые расчеты);
• Выносить наиболее тяжелые JOIN в отдельные временные таблицы, собирать партиционированные «темпы»;
• Материализовать подзапросы и CTE, если это уместно.

Теперь же давайте обратимся к специфике нашего BI-инструмента.

Архитектура Суперсета в общем виде такова:



Данные из источника формируются в датасет (измененный в SQL-Lab, внутренней IDE Суперсета, или же без изменений). Они не хранятся физически в Суперсете, а берутся каждый раз из БД. Каждый график формирует свой запрос, обращаясь к выбранному датасету, за исключением тех случаев, когда у него есть свежий кэш (кто-то уже загружал его n минут назад). Наконец, все графики собираются на дашборде и разом запускают свои запросы при его обновлении.

Как происходит загрузка дашборда:

1. Проверка кэша
   При открытии дашборда происходит проверка кэша для каждого графика. Администраторы устанавливают значение по умолчанию: у нас это 13 минут. Вручную частота его обновления задается в настройках датасета, используемого на дашборде. То есть если таймаут кэша n минут, а вы заходили на страницу дашборда менее n минут назад, он загрузится быстро, взяв данные из кэша, не выполняя новый запрос.
2. SQL-запрос
   Если свежих данных в кэше нет, начинают работать SQL-запросы, из которых формируются графики. Для каждого графика работает свой запрос. Их можно посмотреть на дашборде (кнопка view query в выпадающем меню графика).

Какие возможны уровни оптимизации, исходя из этой архитектуры?

• На уровне таблиц в БД (оптимизация таблиц в БД до создания датасета в Суперсете: агрегация и постановка на регулярные обновления, материализация, оптимизация запросов);
• На уровне датасетов (оптимизация при создании виртуальных датасетов в Суперсете с минимизацией тяжелых операций, правильно выбранный тип датасета);
• На уровне графиков (контроль запросов при их создании, увеличение таймаута кэша);
• На уровне дашбордов (при создании фильтров, вкладок, выборе количества графиков).

Оптимизация запросов: датасеты и графики

Посмотрим, как оптимизировать процесс создания датасетов и графиков.

Запросы для графиков на дашбордах формируются Суперсетом автоматически:

1. Из созданного нами датасета (FROM);
2. Из заданных метрик и группировок при создании графика (SELECT, GROUP BY и остальные части);
3. Наконец, к запросам прибавляются фильтры с дашборда и с графика (WHERE).

Виртуальные и физические датасеты

Важно знать разницу между типами датасетов.

В самом Суперсете не хранятся данные из БД. Он сохраняет только кэш для быстрой повторной загрузки графиков. При принудительном обновлении через кнопку Force refresh на графике / Refresh dashboard на дашборде или по прошествии таймаута, данные берутся уже из нового запроса к БД, для каждого графика отдельно.



Физические датасеты не хранятся «физически» в памяти Суперсета, а ссылаются на таблицы из БД, как и виртуальные датасеты.

Их отличие заключается в том, что виртуальный датасет создается через SQL Lab, где можно написать не только простой SELECT * FROM ..., но и более сложный запросы с группировками, джойнами и т. д.

Физический же датасет создается через вкладку Datasets → + DATASET. А при загрузке csv-файла он также загружается в новую таблицу в указанную БД и затем обращается к ней.

Чем отличаются запросы к разным типам датасетов?

Физические:

При создании графика на физическом датасете в запросе для него будет: FROM *название БД и таблицы*, без подзапросов. Например:

SELECT article AS article, sum(quantity*unit_price_eur) AS `Сумма продаж`
FROM db_name.bakery_sales
GROUP BY article
ORDER BY `Сумма продаж` DESC
LIMIT 1000

Виртуальные:

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

SELECT article AS article, sum(quantity*unit_price_eur) AS `Сумма продаж`
FROM (SELECT * FROM db_name.bakery_sales) AS virtual_table
GROUP BY article
ORDER BY `Сумма продаж`
DESC LIMIT 1000

Если запрос для датасета в SQL Lab будет сложнее, то и подзапрос на каждом графике будет сложнее:

SELECT article AS article, sum(quantity*unit_price_eur) AS `Сумма продаж`
FROM (SELECT cast(sale_date as date) as sale_date, time as sale_time, article, quantity, unit_price_eur FROM db_name.bakery_sales) AS virtual_table
GROUP BY article
ORDER BY `Сумма продаж` DESC
LIMIT 5

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

Подытожим:

Стоит помнить, что любой виртуальный датасет формирует подзапрос внутри каждого запроса для графика, а физический датасет обращается напрямую к таблице в БД. Если вы выполняете простой select * from ..., то можно сразу создать физический датасет.

Оптимизация виртуального датасета без изменений исходной таблицы в БД

Если вы все-таки используете не физический, а виртуальный датасет, созданный с SQL Lab, можно заранее специфицировать запрос, из которого будут браться данные для графиков:

• Количество атрибутов
  Если в витрине сотни колонок, вместо * укажите только те колонки, которые будут нужны для графиков и фильтров.
• Количество строк в датасете (фильтры)
  Витрина может содержать исторические данные за большой промежуток времени или данные по категориям, которые нас в рамках задачи не интересуют. Тогда датасет можно заранее отфильтровать, чтобы не делать это на каждом графике (есть также более сложный способ предварительной фильтрации агрегированного виртуального датасета, уже не содержащего нужных колонок, с помощью шаблонов Jinja, которого мы здесь касаться не будем).
• Сокращение строк и их количества
  Длинные тексты, как и большое количество строк, возможно, никто не будет читать. Вместо простого выбора текстового атрибута укажите, например, left(col_nm, 100) для первых 100 символов или другого значимого числа символов в строке.
• Лишние действия
  Избавьтесь от лишних операций distinct, group by, join, если они не необходимы. Группировка иногда дублируется в запросе датасета и в редакторе самого графика.

Оптимизация графиков

Помимо запроса для датасета (FROM), можно обратить внимание и на другие части запроса для графика, которые формируются при его создании. Обычно на графике указываются метрики, группировки, сортировка, лимит строк.

• Упрощение
  Большое количество группировок на графике скорее всего будет тяжело читать, а запрос усложнится. Вместо этого, возможно, стоит сделать несколько графиков или отказаться от одной из группировок в пользу фильтра на дашборде, где будет выбираться нужная группа.
• Сложные метрики
  Иногда в метрики добавляются лишние DISTINCT, из-за которых запрос усложняется, а результат не меняется (когда вы уверены, что строки и так не дублируются).
• Лимит строк
  В некоторых случаях стоит обратить внимание на лимит строк. Для столбчатой диаграммы не имеет смысла делать слишком много столбцов, потому что это будет сложно читаться, занимать больше памяти и дольше загружаться. То же можно сказать и о таблицах. Вряд ли кто-то будет смотреть таблицу на 10 тысяч строк на дашборде.

Регулярность обновления кэша

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

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

На графике есть кнопка принудительного обновления Force Refresh, там же указано, как давно были закэшированы данные. Установить вручную таймаут кэша, до которого он действителен, можно в настройках датасета во вкладке SETTINGS (он будет действителен для всех графиков, использующих этот датасет):


Теперь кэш будет храниться дольше (у нас – дольше 13 минут), если только не обновить его вручную:


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

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

Оптимизация дашборда: графики и вкладки

При создании дашборда как целого вы самостоятельно контролируете его структуру.

• Количество графиков
  Чем больше графиков на странице, тем больше запросов выполняется к БД одновременно. Это не значит, что нужно стремиться уместить как можно больше разнообразной информации в одном графике. Но некоторые графики могут на самом деле оказаться лишними.
• Вкладки
  Если разделить дашборд на смысловые блоки, по разным срезам или как-то еще, то эти части можно разместить в разных вкладках. Вкладки загружаются, когда на них нажимают. Поэтому это сократит количество запросов от страницы за один подход.
  Если у вас есть большие таблицы, которые уточняют детали по тому, что уже приведено на других графиках, их тоже можно поместить в отдельную вкладку, чтобы в нее переходил только тот, кому это необходимо.
  Еще одно небольшое дополнительное преимущество вкладок, если у вас есть доступ к таблице с метриками по запускаемым на кластере запросам, – по ним можно отследить, какими графиками не пользуются: соответствующих запросов, формируемых графиками, просто не будет в таблице.
• Добавить фильтры
  Это уменьшит объем данных для всех графиков, на которые они распространяются. Особенно хорошо, если это фильтр по полю партиционирования таблицы.

Оптимизация с помощью агрегированной таблицы

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

Особенность (а для кого-то недостаток) такого способа заключается в том, что для него нужно создавать пайплайн в Airflow, который будет регулярно обновлять данные в физической таблице, либо же запускать скрипт для расчетов вручную. Однако, при наличии доступа к Airflow, это не так уж сложно сделать, особенно если использовать готовые шаблоны для скриптов и DAG`ов, создав их один раз.

Что можно заранее выполнить в материализованных таблицах

• Использовать промежуточные таблицы и следовать рекомендациям по оптимизации для вашей СУБД. Запрос для создания или обновления агрегированной таблицы может быть сложным. Он выполняется один раз для нового среза с целью упростить последующие повторяющиеся запросы. Для него время выполнения не является настолько принципиальным, как для запроса для загрузки графика на дашборде.
• Материализовать view. Представления (вью) могут вызывать memory spill даже на простых запросах и вместе с тем в целом дольше выполнять запрос, если они используют много источников. Сохранив данные в физическую таблицу, если это возможно, вы ускорите работу запросов для дашборда. Стоит добавить поле для партиционирования, если таблица выходит большой.
• Выбрать только нужные для дашборда столбцы.
• Применить фильтры. Заранее отфильтровать данные по нужным временным срезам и категориям, чтобы не делать это каждый раз для виртуального датасета.
• Выполнить join'ы. Джойны усложняют запросы, поэтому их также можно выполнить единожды, особенно если их несколько.
• Сгруппировать и агрегировать данные. Этот способ может уменьшить итоговую таблицу, к которой будет обращаться дашборд в тысячи или даже миллионы раз, в зависимости от количества группировок и изначального количества строк, срезов и категорий в каждом атрибуте.

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

Итог

Существует множество способов оптимизации дашбордов на разных уровнях. Некоторые из них применимы для различных BI-инструментов, а некоторые учитывают специфику только одного из них.

Важно определить, где «узкое место» на конкретном дашборде, чтобы выбрать подходящее решение. Иногда проблема банально в большом количестве графиков, а иногда она может быть на источнике данных, до которого невозможно дотянуться.