Сегодня поговорим об одном блоке в нашем облачном BI Modus BI Cloud – графическом интерфейсе для работы с составными наборами данных, о том, как он устроен и для чего нужен. Поехали!
Что такое графический редактор Workflow?
Для того, чтобы пользователям было проще проектировать свои наборы данных, мы приняли решение использовать графический редактор и не опираться на конфигурирование кода.
Со временем в Modus BI Cloud будет применяться как классический запрос с помощью кода из запросов SQL, так и составной набор данных. Составной набор данных содержит описание модели – пользователь может генерировать запрос к данным динамически, оперируя теми объектами, которые выбирает на дашборде.
Соответственно, чтобы правильно объединить поля из разных таблиц, необходимо эти связи между таблицами указать.
Workflow в аналитическом портале предназначен для работы с таблицами на уровне их визуализации. Это значит, что мы не применяем сложные модели и пользуемся схемой «звезда».
Схема звезда — это когда одна большая таблица фактов объединяется крайними частями с таблицей справочников. И выбирая нужные поля из таблиц справочников, наш запрос автоматически строится для того, чтобы нужную сущность выбрать.
«Звезда» и «снежинка»
Есть несколько типов организации связи между таблицами:
1) Звезда - одна большая таблица фактов объединяется крайними частями с таблицей справочников, для чего используется только одна ссылка для каждой таблицы. Это более простая конструкция, которая значительно упрощает написание сложных запросов.
2) Снежинка - кроме таблицы фактов, есть таблицы справочников, которые могут ссылаться на другие таблицы справочников. Схема «снежинки» занимает меньше дискового пространства и лучше сохраняет целостность данных. Но основной ее минус – это сложность запросов, т.к. каждый запрос должен пройти несколько соединений таблиц, чтобы получить соответствующие данные.
В перспективе мы планируем выполнять частичную материализацию. То есть данные у нас будут организованы в схему, а материализированы, то есть записаны на диск. Такая схема должна сокращать количество запросов и возвращаться либо к схеме звезды, либо использовать денормализированную таблицу.
Денормализированная витрина таблицы – это такая таблица, которая содержит в себе сразу все связи, которые можно построить, и работает с ними. Она хорошо подходит для ClickHouse, на котором организовано наше хранилище.
Мы планируем развивать оба механизма: «звезду» с использованием элементов dictionary и, когда dictionary не исправляются, денормализованные таблицы.
Базовый синтаксис объединения таблиц
Таблицы могут быть объединены между собой четырьмя способами:
- left join – соединение всех записей таблицы слева и только тех записей таблицы справа, которые удовлетворяют условию
- right join – соединение записей таблицы слева, который удовлетворяют условию, и всех записей таблицы справа
- inner join – внутреннее соединение таблиц по методу пересечения, т.е. из двух таблиц выбираются только записи, удовлетворяющие условию
- outer join – это внешнее соединение, означает то, что левая и правая таблица выбираются полностью, и совпадения соединяются. А там, где они не соединятся, соответственно, слева появятся пустые правые колонки, а справа появятся пустые левые колонки.
Движок графического интерфейса «запоминает» структуру связи между таблицами и хранит описание таблицы и связи между ними на уровне метаданных порталов.
Когда пользователь выбирает поле из таблицы A и из таблицы B, то графический интерфейс, зная схему, строит соединение с учетом тех связей, которые указаны.
Если этот функционал реализовать с помощью кода, то он будет менее гибким и будет всегда отображать данные с заданными условиями. Например, нужно выбрать данные таблицы продаж и номенклатуры из таблицы складов и соединить их вместе. Если бы мы писали код запроса, то при выборе данных продаж всегда отображалась бы и справочная информация по складам, и справочная информация по товарам.
В графическом интерфейсе мы генерируем текст запроса только в момент выбора полей, поэтому показываться будут только выбранные поля.
Параллельно мы развиваем шаблонизаторы запросов. В системах визуализации может использоваться не только чистый SQL, а SQL с некоторым кодом.
У нас для этого есть пользовательские переменные на SQL-запросе: если выбрано какое-то переменное условие, оно срабатывает, отрабатывает один текст запроса, а в итоговый запрос летит либо одна, либо вторая часть запроса. И такие же блоки есть в ApacheSuperset. Это синтаксис на Python и специальная библиотека, которая позволяет таким образом шаблонизировать запрос и работать.
Главная задача визуализации наборов – снизить порог вхождения в продукт, стараясь максимально избежать деградации производительности получения данных. Планируется, что в рамках разработки аналитического портала подходы будут комбинироваться. Простые и быстрые – на графических моделях, сложные по структуре на произвольном коде с использованием шаблонизатора набора данных.