Ускорение сводных таблиц с помощью GROUPING SETS

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

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

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

В Metabase 64 мы выпустили крупное обновление для сводных таблиц. Теперь вместо этого Metabase может попросить базу данных вычислить всю сводную таблицу в рамках одного запроса, используя стандартную функцию SQL (GROUPING SETS), которую поддерживают большинство современных хранилищ данных. Результатом является та же сводная таблица, но в наших тестах она работает в 2–3 раза быстрее, а ее детали и итоговые значения гарантированно согласованы.

В этой статье подробно рассматривается новый подход: как представление сводного запроса устроено внутри процессора запросов (QP) Metabase, какой SQL генерируют старый и новый пути, как работают механизмы ограничения строк и с какими особенностями диалектов мы столкнулись при внедрении нового пути.

Что такое сводный запрос?

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

Конкретно, следующий запрос представляет собой сводную таблицу с двумя разрезами и одной метрикой (количеством). Запрос представляет собой последовательность вызовов функций Clojure, написанных с использованием библиотеки MBQL от Metabase. Metabase использует MBQL для построения запросов, созданных с помощью настроек сводной таблицы в графическом конструкторе запросов, до того как запрос будет скомпилирован.

Разрезы, указанные в разделе :rows, становятся заголовками строк в сводной таблице. Разрезы, указанные в разделе :columns, становятся заголовками столбцов. Агрегация заполняет каждую пересекающуюся ячейку (в данном случае агрегацией является count).

Учитывая два разреза и включенные итоговые значения, для отрисовки сводной таблицы требуется четыре набора результатов:

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

Старый путь с множеством запросов

Исторически run-pivot-query генерировал один подзапрос для каждого подмножества разрезов сводной таблицы, выполнял их отдельно в базе данных и объединял результаты на уровне приложения. Для нашего примера с двумя разрезами это означает четыре подзапроса, по одному для каждой строки таблицы выше:

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

Мы включили части NULL AS <column> и <n> AS "pivot-grouping" исключительно для наглядности, поскольку интерфейс ожидает единообразную форму строки во всех четырех подзапросах: [source, plan, pivot-grouping, count]. Реальные подзапросы опускали бы отброшенные столбцы, и конструктор запросов вместо этого встраивал бы константную битовую маску и недостающие значения NULL в каждую строку.

Сложность с лимитом строк

Metabase применяет ограничение по умолчанию :max-results (10000) к агрегированным запросам. Если применить этот лимит наивно к каждому подзапросу сводной таблицы, результаты могут разойтись. Например, подробный подзапрос может достичь лимита и отбросить строки, которые подзапросы промежуточных итогов все еще учитывают. Когда эти подсчеты расходятся, пользователи видят промежуточные итоги, которые не сходятся.

Старый путь с несколькими запросами решал эту проблему с помощью быстрого выхода: если первый подзапрос (на уровне деталей) усекается параметром :max-results, оставшиеся подзапросы промежуточных итогов полностью пропускаются, а :pivot_rows_truncated устанавливается в true. Таким образом, интерфейс получает либо полную сводную таблицу, либо усеченные детали без промежуточных итогов и с предупреждением (чтобы Metabase не показывала неверные данные).

Однако с новым нативным путем мы смогли полностью отказаться от всей системы обнаружения и пропуска.

Нативный путь с использованием GROUPING SETS

GROUPING SETS — это расширение оператора GROUP BY, входящее в стандарт SQL. Оно позволяет одному запросу выполнять агрегацию по нескольким группировкам одновременно. Вы перечисляете каждый набор столбцов для группировки, и база данных возвращает все эти группировки в рамках одного результата. Это те же строки, которые вы получили бы от операции UNION ALL для отдельных запросов GROUP BY. В каждой строке столбцы, которые не входят в группировку данной строки, возвращаются как NULL. ROLLUP и CUBE являются сокращениями для распространенных шаблонов наборов группировки.

Мы могли бы обойти проблему ограничения строк с помощью UNION ALL. Просто запустить четыре подзапроса, объединить их через union и обернуть результаты во внешний запрос с единственным LIMIT. Но тогда база данных все равно выполняла бы четыре независимые агрегации по записям.

Однако с GROUPING SETS мы можем позволить планировщику запросов базы данных вычислить все четыре уровня промежуточных итогов за один проход по таблице фактов. Его функция GROUPING() также напрямую передает нам битовую маску группировки сводной таблицы (вместо необходимости использовать жестко закодированную константу для каждой ветви).

Для приведенного выше примера с двумя разрезами нативный путь компилируется в:

Функция GROUPING() помечает каждую строку уровнем ее промежуточных итогов. Порядок ее битов обратен соглашению о группировке сводной таблицы в Metabase, поэтому компилятор меняет местами список аргументов.

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

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

Преимущества и ограничения нативного пути

У нового нативного пути есть очевидные преимущества:

• Улучшенная производительность: один общий проход. GROUPING SETS позволяет базе данных вычислять все уровни промежуточных итогов за один проход по таблице фактов. Каждое хранилище, оптимизированное для рабочих нагрузок в стиле сводных таблиц, умеет это делать.

Улучшенная производительность: один общий проход. GROUPING SETS позволяет базе данных вычислять все уровни промежуточных итогов за один проход по таблице фактов. Каждое хранилище, оптимизированное для рабочих нагрузок в стиле сводных таблиц, умеет это делать.

• Согласованные результаты. Детали и итоги получаются из одного и того же запроса, поэтому любое усечение применяется к обоим.

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

• Удобство проверки и отладки. Все вычисление представляет собой единый SQL-оператор. Вы можете скопировать его в клиент базы данных, запустить, выполнить EXPLAIN или отследить, как формируется конкретный промежуточный итог.

Удобство проверки и отладки. Все вычисление представляет собой единый SQL-оператор. Вы можете скопировать его в клиент базы данных, запустить, выполнить EXPLAIN или отследить, как формируется конкретный промежуточный итог.

Тем не менее, стоит отметить и несколько ограничений:

• Не все базы данных поддерживают GROUPING SETS. Флаг :native-pivot-tables ограничивает эту возможность. Драйвер базы данных должен наследоваться от :sql, а его диалект должен принимать GROUP BY GROUPING SETS. Это охватывает большинство аналитических хранилищ данных, однако MySQL (которая предлагает только WITH ROLLUP) и SQLite (где поддержка отсутствует вообще) остаются на пути с несколькими запросами.

Не все базы данных поддерживают GROUPING SETS. Флаг :native-pivot-tables ограничивает эту возможность. Драйвер базы данных должен наследоваться от :sql, а его диалект должен принимать GROUP BY GROUPING SETS. Это охватывает большинство аналитических хранилищ данных, однако MySQL (которая предлагает только WITH ROLLUP) и SQLite (где поддержка отсутствует вообще) остаются на пути с несколькими запросами.

• Отсутствует поддержка агрегаций с оконными функциями. :offset, :cum-count и :cum-sum вычисляют свое значение для каждой строки на основе других строк в результирующем наборе (накопительный итог, смещение и т. д.). Вычисления поверх результатов GROUPING SETS включали бы строки промежуточных итогов, что не имеет смысла.

Отсутствует поддержка агрегаций с оконными функциями. :offset, :cum-count и :cum-sum вычисляют свое значение для каждой строки на основе других строк в результирующем наборе (накопительный итог, смещение и т. д.). Вычисления поверх результатов GROUPING SETS включали бы строки промежуточных итогов, что не имеет смысла.

Что дальше

Некоторые сводные таблицы по-прежнему не могут использовать GROUPING SETS: любые таблицы на MySQL или SQLite, а также запросы с агрегациями на основе оконных функций. Промежуточный шаг UNION ALL, описанный ранее, по-прежнему работает для них: он устраняет разрывы в количестве поездок туда-обратно и лимитах строк, не требуя от хранилища данных планирования совмещенного сканирования.

MongoDB — это совсем другая история. Ее конвейер агрегации может выражать ту же идею «нескольких уровней группировки в одном запросе», но с помощью $facet, а не SQL. Это требует отдельной инженерной работы, поэтому в версии v64 MongoDB остается на пути с несколькими запросами.

Попробуйте более быстрые сводные таблицы

Сводные таблицы на основе одного запроса появились в Metabase 64.

Нативный путь выбирается при соблюдении двух условий: настройка MB_USE_NATIVE_PIVOT_TABLES включена (в v64 она отключена по умолчанию, пока не докажет свою надежность на достаточном количестве реальных инсталляций), а драйвер заявляет поддержку функции :native-pivot-tables. Если хотя бы одно условие не выполняется, Metabase возвращается к пути с несколькими запросами.

Включите их в Self-hosted: обновитесь до Metabase 64, установите эту переменную окружения и перезапустите Metabase:

Если вы запускaeте JAR:

Если вы запускаете Docker:

Включите их в Metabase Cloud: обратитесь в службу поддержки, и мы включим их для вас.

Как только они включены, сводные таблицы в поддерживаемых базах данных (PostgreSQL, Redshift, Snowflake, BigQuery, Oracle, SQL Server, Presto, Starburst, Vertica, Athena, ClickHouse, Druid и драйверы семейства Hive: Spark SQL, Databricks) автоматически переключаются на путь с одним запросом. Все остальное продолжает работать так же, как и раньше. Вам не нужно менять какие-либо вопросы или дашборды.

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

• Как мы выбирали LibreChat и в итоге получили агента для Slack
• Встречайте Repro-Bot, нашего агента для приоритизации задач на GitHub
• Как мы создали десять пользовательских субагентов, чтобы обуздать кодовую базу на Clojure объемом 500 тыс. строк
• Повышение производительности популярного инструмента разработки на Clojure — clojure-lsp
• PA: постоянный агент для автоматизации рабочего процесса разработчика