Возможности комбинаторов в ClickHouse
Что делать с запросами к СУБД, выполнение которых затягивается на десятки минут, как можно оптимизировать вложенные операторы, чтобы получить нужные данные за секунды? За счет чего подобные операции выполняются в Visiology автоматически? Ответы на эти вопросы мы попробуем дать сегодня на примере небольшого синтетического теста со сложным SQL-запросом, и разберемся при чем тут комбинаторы в ClickHouse. Эта статья будет полезна тем, кто интересуется SQL-оптимизаторами, а также всем существующим и будущим пользователям Visiology, кто хочет заглянуть под капот системы. Если вы из их числа, добро пожаловать под кат :)
По мере того, как количество данных на BI-платформе растет, становится очевидно, что для комфортной работы важны не только возможности системы, но и ее производительность. В Visiology есть возможность обрабатывать запросы, затрагивающие сотни миллионов строк, благодаря тому, что в Visiology используется собственный движок ДанКо. Тут помогают синтаксис DAX и высокопроизводительная колоночная СУБД ClickHouse. Чтобы платформа работала быстро в состав движка ДанКо входят SQL-оптимизаторы, которые значительно повышают эффективность выполнения запросов.
Важной частью DAX-движка ДанКо в Visiology являются SQL оптимизаторы, которые помогают производить преобразования SQL с учетом используемой модели данных.
Комбинаторы ClickHouse дают дополнительные преимущества для оптимизаторов SQL ДанКо, которых нет в других реляционных базах данных.
Рассмотрим пример работы одного из SQL оптимизаторов ДанКо, который использует комбинаторы ClickHouse и информацию о кардинальности (в смысле баз данных, т.е., по сути, доля уникальных значений в столбцах таблиц) на тестовых данных:
Таблица
salesсо 100 миллионами записей с полями уникальный номер заказаorder_number, количествоamount, дата заказаorder_dateи тип продажиsales_type(число с тремя значениями: 0, 1 и 2).Таблица календаря
datesс 10 тысячами записей с полем датаdate, месяцmonthи годyear.
Будет использован следующий SQL для генерации таблиц в ClickHouse:
CREATE OR REPLACE TABLE sales
(
order_number Int64,
amount Float64,
order_date Date,
sales_type Int64
) ENGINE = Log;
INSERT INTO sales
SELECT toString(1000000000 + number) AS order_number,
number % 100 AS amount,
dateAdd(number % 700, toDate('2023-01-01')),
number % 3 AS sales_type
FROM system.numbers
LIMIT 100000000;CREATE OR REPLACE TABLE dates(date Date, month Int64, year Int64) ENGINE = Log;
INSERT INTO DATES
SELECT dateAdd(number, toDate('2023-01-01')) AS date,
toMonth(dateAdd(number, toDate('2023-01-01'))) AS month,
toYear(dateAdd(number, toDate('2023-01-01'))) AS year
FROM system.numbers
LIMIT 10000;Наш пример может показаться простым, но, тем не менее, он интересен даже просто с точки зрения работы реляционных баз данных. Это будет синтетический запрос с проблемами производительности (ведь на запросе без проблем производительности ничего и не видно), но на его примере можно проиллюстрировать возможности SQL оптимизаторов. Видно, что до оптимизации в этом запросе есть не то, что один CROSS JOIN, а даже вложенные друг в друга CROSS JOIN в CROSS JOIN:
-- до оптимизации - выполняется 12 секунд
SELECT expression_result.grouped_sales_type AS grouped_sales_type,
expression_result.grouped_date AS grouped_date,
if(if(expression_result.grouped_date <=
max(if(toLastDayOfMonth(expression_result.grouped_date) = expression_result.date,
toLastDayOfMonth(addYears(expression_result.date, -1)),
addYears(expression_result.date, -1))),
true, false) AND
if(expression_result.grouped_date >=
min(if(toLastDayOfMonth(expression_result.date) = expression_result.date,
toLastDayOfMonth(addYears(expression_result.date, -1)),
addYears(expression_result.date, -1))),
true, false), true, false) AS condition
FROM (SELECT grid.grouped_sales_type AS grouped_sales_type,
grid.date AS grouped_date,
dates.date AS date
FROM dates AS dates
CROSS JOIN (SELECT grid.grouped_sales_type AS grouped_sales_type,
dates.date AS date
FROM (SELECT sales.sales_type AS grouped_sales_type
FROM sales AS sales
GROUP BY sales.sales_type) AS grid
CROSS JOIN dates AS dates) AS grid
) AS expression_result
GROUP BY expression_result.grouped_sales_type,
expression_result.grouped_date;Для такого вида запроса можно дать следующее словесное описание:
Проверка попадания даты в период и особая логика для последнего дня месяца
Да, глубокого смысла в запросе нет, он полезен с точки зрения иллюстрации возможностей оптимизации. Ведь это, казалось бы, совершенно безнадежный запрос — 2 вложенных друг в друга CROSS JOIN. Такого вида SQL при увеличении количества записей в таблице календаря dates, например, до 30 тысяч, может выполняться минуты. Но ClickHouse дает в чем-то уникальные возможности оптимизации за счет комбинаторов. В итоге можно сравнительно легко оптимизировать запрос с таким уровнем вложенности.
Итак, после оптимизации методами ДанКо, мы получаем:
-- after - выполняется меньше секунды
SELECT expression_result.grouped_sales_type AS grouped_sales_type,
expression_result.grouped_date AS grouped_date,
if(if(expression_result.grouped_date <=
maxMerge(complex_date_max_state), true, false) AND if(expression_result.grouped_date >=
minMerge(complex_date_min_state),
true, false), true, false) AS condition
FROM (SELECT grid.grouped_sales_type AS grouped_sales_type,
grid.date AS grouped_date,
maxStateMerge(complex_max_state) AS complex_date_max_state,
minStateMerge(complex_min_state) AS complex_date_min_state
FROM (SELECT
maxState(if(toLastDayOfMonth(dates.date) = dates.date,
toLastDayOfMonth(addYears(dates.date, -1)),
addYears(dates.date, -1))) AS complex_max_state,
minState(if(toLastDayOfMonth(dates.date) = dates.date,
toLastDayOfMonth(addYears(dates.date, -1)),
addYears(dates.date, -1))) AS complex_min_state
FROM dates AS dates)
AS dates
CROSS JOIN (SELECT grid.grouped_sales_type AS grouped_sales_type,
dates.date AS date
FROM (SELECT sales.sales_type AS grouped_sales_type
FROM sales AS sales
GROUP BY sales.sales_type) AS grid
CROSS JOIN dates AS dates) AS grid
GROUP BY grid.grouped_sales_type AS grouped_sales_type,
grid.date AS grouped_date
) AS expression_result
GROUP BY expression_result.grouped_sales_type,
expression_result.grouped_date;Видно, что оптимизация происходит «на 3 уровня вниз» с использованием maxMerge/MaxStateMerge/maxState (и аналогично maxMerge/MaxStateMerge/maxState), позволяет успешно выполнить менее чем за секунду достаточно «безнадежный» с точки зрения производительности синтетический запрос с двумя вложенными CROSS JOIN для таблицы продаж с 100 млн записей и календарем с 10 тысячами записей, причем, обратите внимание, вложенные CROSS JOIN остаются на месте! Это достигается за счет комбинаторов ClickHouse.
Интересно, что оптимизация производится с учетом кардинальностей, а sales_type у нас — низкокардинальный (т.е. содержит небольшое количество уникальных значений — всего 3 разных значения). Поэтому GROUP BY по sales_type оправдан и приносит улучшение производительности.
Если в запросе вместо низкокардинального sales_type (с тремя разными значениями) будет использоваться уникальный (и высококардинальный) order_number, то GROUP BY order_number не будет иметь смысла с точки зрения производительности, соответственно, такой в таком случае ДанКо и не будет делать такую оптимизацию из соображений кардинальности.
Заключение
Конечно, такого вида пример — лишь показательный образец применения комбинаторов на синтетическом SQL, и я здесь привел его только для демонстрации возможностей оптимизаторов ДанКо, которые используют особенности ClickHouse. Но подобная задача может быть актуальна при автоматическом построении запросов самых разных типов.
Желаю успехов в BI и построении дашбордов!
