Произвольные наборы данных - Публичная база знаний Modus
Общая информация
В Modus BI реализовано два сценария создания наборов данных:
- «Простые и составные наборы» — создаются в разделах портала «Подключения» и «Правила загрузки данных». Данные из внешнего источника загружаются во внутренний «ClickHouse»
портала. На основе простых наборов можно строить составные наборы данных. Подробнее см. раздел «Простые и составные наборы данных»
- «Произвольные наборы данных» — создаются в разделе портала «Источники». В этом сценарии вы подключаетесь напрямую к внешнему источнику данных и формируете набор с помощью произвольного SQL-запроса. Именно этому сценарию посвящена данная статья.
Примечание — интерфейсы и логика работы в этих двух сценариях различаются. В произвольных наборах данных нет общего списка всех имеющихся наборов — вы сразу переходите к настройке конкретного произвольного набора данных.
Обратите внимание! Начиная с версии 3.17.32 Modus BI:
- «Произвольные наборы данных» — это одна из двух вкладок раздела настроек портала «Наборы данных». В зависимости от настроек портала вкладки могут быть скрыты: в этом случае раздел «Наборы данных» открывается сразу в режиме «Произвольные наборы данных». Также произвольные наборы данных доступны через раздел «Источники». Независимо от способа перехода, работа с произвольными наборами данных выполняется в одной и той же форме.

- Источник данных для произвольного набора может быть создан автоматически на основании подключения с включённым параметром «Использовать данное подключение для создания Произвольного набора данных». В Modus BI Cloud параметр доступен всегда. В Modus BI (On-Premise) параметр доступен, если облачный функционал активирован через метаданные портала. Подробнее см. разделы «Настройка подключений» и «Настройка функционала модели данных».
Может лишнее? - В Modus BI в произвольных наборах данных доступен расширенный редактор SQL с деревом метаданных источника. Подробнее см. подраздел «Расширенный редактор SQL».

Настройка произвольного набора данных
Каждый набор относится к конкретному источнику. Для просмотра настроенных наборов данных перейдите в раздел «Настройки / Наборы данных», выберите источник данных.

Для настройки нового набора данных нажмите на кнопку «Добавить набор». Заполните поля «Имя набора данных» (обязательно уникальное), «Заголовок набора данных» и нажмите на кнопку «Создать».

Набор создан, далее необходимо его настроить. Для этого найдите набор данных в списке наборов и нажмите по нему кнопкой мыши. Откроется окно нового набора данных, где еще не введены настройки.
Откройте поле для ввода текста запроса для получения данных набора нажатием на иконку глаза напротив поля «SQL Запрос».

Для удобства работы с SQL-запросами в поле «SQL Запрос» используется многофункциональный редактор кода на базе CodeMirror. Редактор предоставляет подсветку синтаксиса SQL с учетом диалекта выбранного источника данных, нумерацию строк, автоматическое закрытие скобок и кавычек, поддержку мультикурсорного редактирования, а также функции «Undo/Redo». При вводе текста работает автодополнение стандартных SQL-ключевых слов и встроенных функций (автодополнение также доступно по сочетанию клавиш «

Обратите внимание! Начиная с версии 3.17.32 Modus BI в произвольных наборах данных доступен расширенный режим редактора SQL. Расширенный режим предоставляет доступ к метаданным выбранного источника данных и вспомогательным панелям, не изменяя текущую логику проверки запроса, обновления выходных полей и сохранения набора данных. Подробнее см. подраздел «Расширенный редактор SQL».
Базовый редактор SQL
Редактор корректно обрабатывает системные конструкции проекта, такие как !env.NAME!, !env.NAME.BEGIN!, !env.NAME.END!, а также выражения с fallback-логикой внутри фигурных скобок (например, {!env.REGION!; 2}). Эти конструкции распознаются как валидный синтаксис и не помечаются как ошибка.
Высоту окна редактора можно изменять, перетаскивая за нижнюю границу окна.

Введите текст запроса на языке SQL в поле (1):
- синтаксис запроса должен соответствовать синтаксису диалекта SQL, используемого в источнике. Наличие ошибок в запросе можно проверить, нажав на кнопку «Проверить запрос» (2);
- если запрос обращается к базе данных и к схеме базы данных, которые были указаны по умолчанию в настройках раздела «Источники данных», их можно не указывать в тексте запроса;
- в Наборах данных возможно выполнять объединение таблиц через JOIN — главное учитывать, что такие SQL-запросы выполняются непосредственно в СУБД при каждом открытии отчета. Поэтому важно, чтобы СУБД справлялась с нагрузкой и такие запросы были оптимизированы. Modus ETL, в свою очередь, позволяет выполнять предварительную обработку и объединение данных из разных источников — это оптимально при сложной трансформации, высокой нагрузке или необходимости кэширования результатов.
Нажмите на кнопку «Проверить запрос» (2). Если запрос прошел проверку на работоспособность, в правом нижнем углу отобразится сообщение «SQL запрос прошел проверку» и выходные поля запроса заполнятся (3).

- в выходных полях запроса по умолчанию названия, алиасы (псевдонимы) и типы полей заполняются из базы данных;
- реализована возможность изменения значения алиасов, чтобы сделать их названия более интуитивно понятными, что в дальнейшем упростит использование набора данных;
- при необходимости заполните описание полей. После окончания настройки набора нажмите на кнопку «Сохранить».
ЗАМЕНЕН
Расширенный редактор SQL
Чтобы перейти в расширенный режим, нажмите на кнопку «Расширенный редактор» под кнопкой «Проверить запрос». Редактор откроется в модальном окне и получит актуальное текущее значение SQL из формы набора данных. Введённый SQL не теряется при переходе между базовым и расширенным режимами.
Рабочая область редактора представлена:
- Метаданные выбранного источника данных.
- Редактор CodeMirror.
- Шаблоны и служебные вставки.
- Области 1 и 3 имеют фиксированную ширину с возможностью регулировки — потяните за край области, чтобы изменить её ширину. Для SQL-скрипта, содержимого боковых панелей работают внутренние скроллы.
- Флаг «Добавлять имя таблицы при вставке поля», кнопки «Проверить запрос» и «Принять».

Рабочая область 1. Метаданные выбранного источника данных
В рабочей области 1 отображаются метаданные выбранного источника данных: схемы, таблицы, view и поля. В верхней части панели отображается наименование текущего источника данных. Сменить источник в расширенном режиме нельзя.
- В области доступны:
- Обычные пользовательские таблицы и view текущей базы данных из всех доступных пользовательских схем.
- Поля объектов и их типы.
- Область поддерживает:
- Поиск.
- Разворачивание и сворачивание узлов схем и объектов.
- Корректное состояние загрузки.
- Пустое состояние или сообщение об ошибке, если метаданные временно недоступны.
- Метаданные доступны без обязательного предварительного выполнения текущего SQL-запроса.
Если у источника заполнено поле схемы, объекты группируются по узлам схем. Для источников, у которых схема не заполнена (например, SQLite или ClickHouse), дополнительный уровень схемы не создаётся.
Таблицы и view различаются по типу — без добавления префикса к имени.
Каждый объект раскрывается как узел дерева и показывает список полей. Для каждого поля отображается исходный тип СУБД. Если поле имеет нормализованный тип, он используется для информационной классификации.
Ошибка отдельного объекта (например, объект не найден, доступ запрещён, внутренняя ошибка) отображается в состоянии соответствующего узла и не скрывает успешно загруженные объекты того же ответа. Если метаданные источника в целом недоступны, панель отображает сообщение об ошибке.
Рабочая область 2. Редактор CodeMirror
Работа в поле SQL-запроса рассмотрена в подразделе «Базовый редактор SQL».
Рабочая область 3. Шаблоны и служебные вставки
В рабочей области 3 отображаются вспомогательные вставки:
- шаблоны переменных окружения;
- шаблоны серверных переменных;
- список распространённых агрегирующих функций: SUM, AVG, MIN, MAX, COUNT, DISTINCT и другие.
Вставка объектов
Объекты из рабочих областей 1 и 3 вставляются в SQL с помощью Drag-and-Drop в область редактора. Дополнительно поддерживается вставка двойным кликом.
- Из области 1 в редактор вставляются:
- Имена таблиц и view.
- Имена полей.
- Из области 3 в редактор вставляются:
- Шаблоны переменных окружения.
- Шаблоны серверных переменных.
- Иные поддерживаемые системные и общеупотребимые конструкции.
Логика работы:
- При перетаскивании таблицы или view система формирует синтаксически безопасный для текущего SQL-диалекта идентификатор:
- Если схема заполнена, она сохраняется в идентификаторе.
- Если схема пустая, искусственный префикс не добавляется.
- При перетаскивании поля вставляется имя поля. Формат вставки регулируется флагом «Добавлять имя таблицы при вставке поля» (по умолчанию выключен)::
- При выключенном параметре вставляется имя поля.
- При включённом параметре вставляется имя поля с синтаксически безопасным квалификатором объекта.
- Вставка происходит в текущую позицию каретки. Если редактор временно потерял фокус, используется последняя известная позиция каретки. Если каретка ранее не была установлена, вставка выполняется в конец SQL-скрипта. Если включён мультикурсор, вставка выполняется по всем активным курсорам.
- Если для корректной SQL-вставки требуется экранирование или кавычки в соответствии с диалектом текущего источника, вставка использует синтаксически безопасную форму идентификатора.
Проверка запроса
В расширенном режиме доступны две кнопки: «Проверить запрос» и «Принять»:
- Кнопка «Проверить запрос» проверяет текущий текст SQL из редактора. При успешной проверке:
- Отрабатывает текущая бизнес-логика модуля проверки запроса.
- Список выходных полей набора данных обновляется по текущим правилам формы.
- Сохраняются ранее введённые атрибуты совпавших полей.
- Пользователь получает штатное сообщение об успешности выполнения запроса.
- При неуспешной проверке:
- Пользователь получает штатное сообщение об ошибке.
- Текст SQL в редакторе не удаляется.
- Расширенный режим не закрывается.
- Ранее существующий валидный список выходных полей не удаляется.
- Проверка SQL не запускается автоматически на каждый ввод символа.
- Кнопка «Принять» закрывает модальное окно и возвращает пользователя к базовому редактору с тем же текстом SQL.
Автодополнение и системный синтаксис
Расширенный режим работает в режиме SQL с определением диалекта по типу текущего источника данных. Автодополнение вызывается как при вводе текста, так и по сочетанию клавиш <Ctrl>+<Space>. Для источников, у которых нет нативного диалекта в CodeMirror, используется согласованный fallback-режим.
Выпадающее меню автодополнения, содержит:
- стандартные SQL-ключевые слова;
- встроенные SQL-функции;
- таблицы и view выбранного источника данных.
Редактор корректно распознаёт системный синтаксис проекта и не помечает его как ошибку:
! env . NAME !,! env . NAME . BEGIN !,! env . NAME . END !;- конструкции с fallback-логикой внутри фигурных скобок;
- иные серверные и системные переменные проекта.
Подсветка ошибок
Если проверка запроса возвращает ошибку и позволяет определить проблемное место, это место визуально акцентируется в редакторе. Если точная привязка ошибки к позиции невозможна, отображается текстовое сообщение без потери введённого SQL.
В расширенном режиме сохраняются базовые возможности редактора: нумерация строк, подсветка синтаксиса SQL, подсветка и автозакрытие скобок и кавычек, Undo/Redo, поддержка мультикурсорного редактирования, корректный вертикальный внутренний скролл и стабильная работа с длинными запросами.
Столбец «Иерархия». Удалить «Набор данных»
Примечание — столбец «Иерархия» позволяет логически сгруппировать связанные поля данных под общим названием для удобства навигации и организации работы с набором данных. Указание иерархии групп упрощает структурирование информации и последующий анализ при использовании их при настройке компонентов. Имя группы иерархии может быть любое, главное, отличное от имени поля.
Например, поля можно разделить по группам:
- «География» с полями: регион, город, адрес;
- «Финансы» с полями: сумма, прибыль, бюджет;
- «Время» с полями: год, месяц, дата.
Имя группы иерархии может быть любое, главное, отличное от имени поля.
Ниже приведен пример настройки столбца «Иерархия» и отображения данных полей в наборе данных при настройке компонента.


При необходимости удаления набора данных выберите значение в поле «Наборы данных» и нажмите на кнопку «Удалить». Отобразиться предупреждающее сообщение «Вы действительно хотите удалить текущий набор данных?» с кнопками «Отмена» и «Удалить». При нажатии на кнопку «Удалить» набор удалится, а при нажатии на кнопку «Отмена» – нет.

Примечание — если набор данных уже используется в хотя бы одной визуализации, то при попытке его удаления возникнет конфликт и используемый набор данных не будет удален.
Настройки RLS
В форме произвольного набора данных может быть доступен блок «Настройки RLS». Блок позволяет привязать к набору данных профиль и поле RLS, сформировать SQL-запрос RLS и включить ограничения с помощью переключателя. Подробнее о настройке прав доступа на уровне записей см. раздел «Настройка прав доступа на уровне записей (RLS)».

Дополнение набора данных полями с частями даты
Части даты обычно используются при анализе и визуализации данных в разрезе выбираемых пользователем временных интервалов — годы, кварталы, месяцы, недели, дни недели, часы и т.д.
Для использования частей дат не нужно знать SQL-функции для работы с датами и их особенности для разных СУБД, а достаточно выбрать требуемые части даты в удобных интерфейсах.
Для использования функционала:
- В наборе данных настройте дополнительные поля с частями даты. Для этого нажмите на иконку шестеренки рядом с полем типа «Дата» (см. рисунок ниже, U) и выберите необходимые дополнения (X).

- В Конструкторе компонента разместите дополнительные поля с частями даты (см. рисунок ниже, P) на полке «Значения», «Категории», «Серии», «Фильтры» (R), а также настройте правила детализации в разделе «Дрилл» (D):

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

Восстановить удаленный набор данных
Если набор данных был удален, его восстановление возможно, изменив признак «deleted» в базе метаданных портала. Удаленные записи остаются в базе портала с признаком «deleted = 1».
Чтобы восстановить набор данных, выполните следующие шаги:
- Подключитесь к базе метаданных, используя любой доступный клиент для работы с СУБД (например, DBeaver, SQL Developer и т. д.). Посмотреть название базы метаданных возможно в разделе metadata в файле настроек «modusbi.json», который расположен в каталоге исполняемого файла Аналитического портала.
- В таблице «
dataset» найдите нужный набор данных. - Измените значение поля «
deleted» с 1 на 0. - Перейдите в портал и проверьте, появился ли восстановленный набор данных. Если данные не отобразились, попробуйте перезагрузить портал, для этого перейдите в «Настройки/ Настройки портала/ Перезагрузка» и обновить страницу сочетанием клавиш «Ctrl + F5».