Это многостраничный печатный вид этого раздела. Нажмите что бы печатать.

Вернуться к обычному просмотру страницы.

Обработка данных

1: Сценарии обработки данных

2: Использование интерфейса WorkFlow
3: Шаблоны примитивов обработки данных
4: Шаблоны выполнения произвольного кода
5: Шаблоны базовых операций с данными
6: Шаг переноса данных в другую БД
7: Использование параметров выполнения шага
8: Шаги сценария (старый интерфейс)
9: Дополнительные возможности

1 - Сценарии обработки данных

Полученные из источников учетных систем данные необходимо подготовить для использования в отчетах.

Подготовка данных включает процедуры-инструкции на языках SQL, 1C, Python для очистки, дополнения, агрегации, объединения и т.п. данных, расчета показателей, а также для сервисных операций выгрузки, загрузки, перемещения файлов, удаления архивных копий таблиц и т.д.

Для настройки процедур подготовки данных служит документ «Сценарий обработки данных», в котором настраиваются шаги сценария.

Список (журнал) сценариев вызывается либо с начальной страницы, либо из меню «Главная» / «Настройки» / «Сценарии»:

В списке сценариев можно создать новый сценарий (см. рисунок ниже, 1), отредактировать существующий и запустить сценарий:

Кнопка позволяет запустить на выполнение шаги сценария (см. рисунок выше, 2). Кнопка «Только примитивы» включает отбор служебных сценариев (примитивов) (см. рисунок выше, 3). Отключить этот отбор можно еще раз нажав на эту кнопку.

Настройка сценария обработки данных

Внешний вид документа представлен на рисунке ниже:

Варианты настройки и представления шагов сценария показаны на рисунке ниже.

Порядок настройки «Сценария обработки данных» следующий.

На вкладке «Основное» необходимо заполнить поля:

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

Для настройки шагов сценария существуют два варианта.

Вариант 1. На вкладке «Шаги сценария» создать и настроить шагисценария, написав для каждого шага скрипт на языке SQL\1С или использовать для настройки и авто-генерации скрипта для шага — готовые шаблоны.
Вариант 2. На вкладке «WorkFlow» настроить шаги обработки данных в визуальном интерфейсе WorkFlow (см. рисунок ниже, 2).

Вариант с интерфейсом WorkFlow для визуального проектирования ETL является более удобным и более функциональным, так как кроме основного процесса трансформации данных возможно дополнительно настроить сохранение информации по профилированию данных — сэмплы данных, статистику по значениям и качеству данных; а также сохранение и просмотр логов работы. Кроме того, шаги сценария, спроектированного в интерфейсе WorkFlow могут выполняться параллельно (см. рисунок ниже, 2). Таким образом, для новых сценариев обработки данных рекомендуется использовать WorkFlow, а вариант с таблицей шагов сценария использовать для изменения старых сценариев, для которых интерфейс WorkFlow недоступен и система сообщает о несовместимости.

В таблице «Шаги сценария»,
в интерфейсе WorkFlow,
на закладке «Параметры сценария» возможно ввести параметры, которые будут использоваться в настройках / тексте скриптов шагов (см. рисунок ниже). Значения параметров можно и не заполнять, если предполагается запуск сценария только через «Пакеты обработки данных» (подробно см. в этом разделе).

По кнопке «Записать и закрыть» на основной вкладке «Сценарий» сохраняется.

2 - Использование интерфейса WorkFlow

ModusETL развивается в концепции «low-code», что подразумевает возможность настраивать ETL-операции в визуальных интерфейсах и без необходимости написания кода. Появившийся в релизе ETL v. 1.5 (май 2021) интерфейс WorkFlow — является альтернативой настройке шагов сценария в виде таблицы, для которого возможно только последовательное выполнение шагов; в WorkFlow появилась возможность параллельного выполнения нескольких веток сценария, что обеспечивает большую производительность.

Интерфейс WorkFlow дает возможность проектировать сценарии обработки данных

размещая шаги (обработчики) на холсте, связывая их друг с другом для передачи между шагами данных или потока управления;
настраивая для шага правила обработки данных в визуальном мастере — сейчас возможно использовать десятки готовых мастеров (шаблонов) для выборки, группировки, соединения, объединения, дополнения, отбора данных и других ETL-операций.
настраивая в нужных точках выполнения сценария сбор и сохранение статистики для профилирования и проверки качества данных (шаблон «Просмотр»).

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

Описание интерфейса WorkFlow

Для открытия интерфейса в разделе сценария обработки данных выбрать кнопку «WorkFlow» как на рисунке ниже:

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

Список с кратким описанием назначения и основных функций шаблонов / мастеров обработки данных см. этот раздел.

Шаги связываются стрелкой, которая означает передачу управления иили передачу данных (см. рисунок ниже, 3).

Для шага возможно изменить «Наименование» в текстовом поле над шагом (см. рисунок ниже, 4).

Для настройки параметров шага — необходимо запустить мастер, воспользовавшись контроллом «Редактировать шаг» (см. рисунок выше, 5). Настройка с использованием шаблонов мастеров описана в этом разделе:

Как правило, в мастере настройки имеются закладки «Выбора источника», «Настройки полей запроса» и просмотра «Результата». На закладке «Результат» отображается текст запроса и результат запроса:

Элемент управления «Просмотр» (см. второй рисунок, 6) предназначен для просмотра образца (сэмпла) данных (см. рисунок выше). Если при открытии отобразится сообщение «Нет данных», то для выполнения сценария до текущего шага и получения сэмпла данных необходимо нажать «Выполнить до текущего цикла». Также возможно изменить количество отображаемых строк, установив параметр «Размер выборки».

Элемент управления для шага «Выполнить до текущего шага» (см. второй рисунок, 7) предназначен для запуска выполнения сценария до текущего шага — при выполнении отображается прогресс и лог выполнения:

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

После формирования диаграммы WorkFlow с шагами обработки данных и связями шагов, настройки точек сбора статистики по данным, источниками и приемниками данных — необходимо сохранить сценарий обработки данных и запустить на выполнение, воспользовавшись кнопками в верхней части WorkFlow «Сохранить», «Запустить», «Авторасстановка».

После запуска в интерфейсе (см. рисунок ниже, «S»), будет отображаться ход выполнения на прогресс-баре (см. рисунок ниже, «P»), выполненные шаги выделяются зелеными рамками и в таблице «Лог выполнения» выводится инфо о времени и длительности выполнения шагов (см. рисунок ниже, «L»):

После завершения выполнения сценария возможно анализировать «Лог выполнения», для возможной оптимизации наиболее длительных шагов. В случае ошибки — описание ошибки будет выведено в колонке «Ошибка лога выполнения».

Список шаблонов шагов сценария

Для реализации концепции «low-code» — возможности настраивать ETL-операции пользователем без специальной подготовки, в удобных визуальных интерфейсах и без написания кода в Modus ETL доступны мастера визуальной настройки обработки данных — шаблоны шагов сценария. Ниже — список используемых шаблонов. Этот список будет пополнятся и расширятся в новых релизах продукта:

Шаблон шага сценария	Описание функционала	Есть в Work Flow?
Произвольный код
Произвольный код SQL	Для ввода кода на языке SQL	Да
Произвольный код 1С	Для ввода кода на языке 1С	Да
Произвольный запрос 1С	Для ввода на языке 1С-запросов
Данные
Источник	Присоединяется ко входу шага-обработчика данных. Предназначен для выбора таблицы-источника. Нажав на элемент управления возможно просмотреть сэмпл данных	Да
Приемник	Присоединяется к выходу обработчика данных. Предназначен для формирования таблицы с набором полей из входящего потока и сохранения данных в таблицу. Элемент управления — для получения и просмотра сэмпла данных из таблицы. Если установить признак, то приемник может быть источником для последующих шагов сценария	Да
Просмотр	Используется для сохранения и просмотра статистики по данным	Да
Очистка таблицы	Производит очистку таблицы из базы данных, которую надо указать в поле «Приемник данных»	Да
Перенос данных	Предназначен для переноса данных между двумя различными базами внутри одного сценария	Да
Примитивы обработки данных		Да
Произвольная выборка данных	Для настройки выборки данных из одной или нескольких таблиц с функциями выбора полей, соединения таблиц, отбора и сортировки данных. Возможно применять функции преобразования к полям или добавлять новые поля, используя интерфейс «Конструктор выражений». На основании полей с типом «дата» и «дата и время» возможно добавлять поля с частями даты (год, квартал, месяц, номер недели, день недели и т.д.), воспользоавшись интерфейсом «Части даты»	Да
Группировка	Группирует данные, аналогично SQL-функции `GROUP BY`. Настраиваются: поля группировки и поля агрегатов с выбором функций: `SUM`, `MIN`, `MAX`, `AVG`, `COUNT`.	Да
Соединение (дополнение данных)	Дополняет данные основной таблицы/потока полями из одной или нескольких таблиц. Соединяет таблицы, аналогично SQL-функции `JOIN`. Настраиваются условия соединения таблиц и поля из таблиц с возможностью применения функций	Да
Объединение	Объединяет данные таблиц аналогично SQL-функции `UNION`. Настраиваются соответствия полей основной и добавляемых таблиц	Да
Отбор	Для отбора/фильтрации данных по условиям. Настраиваются: «Поле» / «Условие» / «Значения». Набор условий зависит от типа поля (строка, число, дата) и соответствует SQL-операторам: `=`, `≠`, `>`, `<`, `≥`, `≤`, `LIKE`, `IN(список)`, `not IN(список) NULL`, `IS NOT NULL`	Да
Маппинг полей двух таблиц	Копирует данные из одной таблицы в другую по настроенным правилам соответсвия (маппинга) полей	Да
Маппинг полей двух таблиц с предварительным удалением	Работает аналогично шаблону «Маппинг 2х таблиц», с одним отличием. На вкладке «Связь полей» задаются поля (ключ) для поиска. Если какие-то записи совпадут по ключу поиска, то они в таблице-приёмнике будут заменены соответствующими записями из таблицы-источника	Да
Unpivot	Транспонирует выбранные столбцы таблицы При этом заголовки выбранных столбцов переносятся в значения строк, а их значения — в один столбец.	Да
Pivot	Для формирования сводной таблицы настраиваются: - поля группировки — значения выводятся в строках; - поля для транспонирования строк в столбцы; - поля значений с функцией агрегации	Да
Создание таблицы БД	Используется для создания таблиц в базе данных хранилища	Да
Топ N	Для выбора строк по условиям. Настраивается: - размер выборки — N строк, - поля и направление сортировки. SQL эквивалент: `top N` / `limit N` с условием `Order by`	Да
Разделение строки	разделяет строки на массив подстрок, используя указанный разделитель	Да
НСИ — дополнение данных
Справочник НСИ	Эталонный справочник модуля НСИ для дополнения или обновления первичных данных	Да
Маппинг НСИ	Таблица соответствия между сущностями: справочниками, категориями / группами	Да
НСИ. Дополнение из эталонного справочника	Дополнение таблицы значениями из эталонного справочника НСИ. Настраиваются: связь таблиц — поля и условия; поле(я) для дополнения таблицы	Да
НСИ. Обновление из эталонного справочника	Замена значений в таблице значениями из эталонного справочника НСИ. Настраиваются: - связь таблиц — поля и условия; - поле(я) для обновления таблицы	Да
НСИ. Дополнение из маппинга	Дополнение таблицы значениями из маппинга НСИ. Настраиваются связь таблиц — поля и условия связывания, а также поле(я) для дополнения исходной таблицы	Да
Python
Кластеризация	Кластеризация используется для группировки объектов (наблюдений, событий) на основе данных, описывающих свойства объектов. Объекты внутри группы (кластера) должны быть похожими друг на друга и отличаться от других, которые вошли в другие кластеры. Возможно выбирать алгоритмы «KMean», «DbScan».	Да
Линейная регрессия	Регрессия используется для решения задач «Data Mining», таких, как прогнозирование и численное предсказание. Требуется предварительное обучение на обучающей выборке. Возможно выбрать из пяти линейных («Linear regression», «Ridge», «Lasso», «Elastic Net», «Lars») и двух древовидных («Decision tree», «Random forest regressor») моделей	Да
Сервисные операции
Настройка замены пустых значений	Заменяет значения по образцу	Да
Выгрузка таблицы в файл	Настраиваются параметры для выгрузки данных в файл	Да
Настройка индексов таблицы	Создает индексы в таблице для ускорения поиска	Да
Мастер настройки расчета фасетов таблицы	Расчет статистических данных таблицы (фасетов), котрые используется для быстрого знакомства с данными и профилирования данных	Да
Очистка хранилища от бэкапов	Удаления архивных копий таблиц, которые копируются и сохраняются при обновлении данных в целевой таблице «Составом выгрузки» в режиме «Скопировать и добавить». Такие архивные таблицы имеют общий признак: суффикс «_ГГГГММДД_Время» в наименовании	Да
Копирование бинарного файла в «BASE64»	Файлы, подходящие под заданную маску, кодируются по алгоритму «BASE64» и сохраняются в текстовый выходной «CSV»-файл	Да
Настройка удаления пробелов/спецсимволов из полей таблицы	Очистка строковых полей от «плохих» символов	Да
Запуск принудительного обновления куба	Настраиваются параметры интеграция с сервером «Полиматики» для обновления кубов по расписанию	Да
Сложный сценарий
Вставка результата запроса в таблицу	Записывает в выходную таблицу	Да
Смена имен таблиц	Изменяет имя таблицы по образцу	Да
Создание временной таблицы	Создает временную таблицу	Да
Верификация данных
Начало процесса верификации	Осуществляется запись в специальный журнал процессов о начале процесса верификации	Нет
Заменить значение	Настраивается замена значения при обнаружении ошибки	Нет
Записать в лог ошибок	Настраивается запись в лог ошибок описания ошибки при ее обнаружении	Нет
Завершение процесса верификации	Осуществляется запись в специальный журнал процессов об окончании процесса верификации, количестве обработанных строк и количестве выявленных ошибок	Нет
Мэппинг данных с заменой	Осуществляет проверку и замену значений поля таблицы в соответствии с предварительно настроенным справочником эталонных значений	Нет
Мэппинг предварительный анализ	Осуществляет проверку в соответствии с предварительно настроенным справочником эталонных значений	Нет
Проверка символьной строки	Осуществляется проверка значений поля на соответствие предопределенным правилам, например, проверка корректности СНИЛС, ИНН и т.п.	Нет
Проверка на NULL	Осуществляется поиск незаполненных (NULL) значений в поле таблицы для символьных, числовых или календарных типов данных.	Нет
Мэппинг данных с дополнением	Осуществляет проверку и дополнение таблицы новым полем в соответствии с предварительно настроенным справочником эталонных значений	Нет

Перенос данных

Типы шаблонов и рекомендации по использованию шаблонов

Шаблоны из группы «Произвольный код» используются для ввода кода на языке 1С / SQL.

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

Шаблоны из группы «Примитивы обработки данных» используются для настройки выборок из таблиц, обработки-трансформации данных определенным способом.

Шаблоны из группы «НСИ — дополнение данных» используются для операций стандартизации и категоризации данных с использованием объектов НСИ — эталонных справочников и маппингов.

Шаблоны из группы «Python» используются для решения задач DataMining сегментации объектов, прогнозирования, предсказания и т.д., с использованием моделей продвинутой аналитики и библиотек Python.

Шаблоны из группы «Сервисные операции» используются для выгрузки таблиц в файлы, настройки индексов, замены значений по определенным правилам, очистки хранилища от архивных копий таблиц, кодирования файла в «BASE64» и других сервисных операций.

Шаблоны из группы «Верификация данных» используются для настройки логирования процесса верификации (проверки на установленные условия), настройки правил верификации и обработки выявленных ошибок.

Шаблоны из группы «Сложные сценарий» используются для оптимизации работы с таблицами.

3 - Шаблоны примитивов обработки данных

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

Произвольная выборка

Шаблон «Произвольная выборка» — эффективный инструмент для выборки, очистки, трансформации данных и вспомогательных расчётов. Шаг настраивается в окне «Мастер настройки выборки данных». По умолчанию активна вкладка «Настройка полей запроса».

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

Изначально задается один обязательный источник (его можно увидеть на первой вкладке «Выбор источника»). Если была задана связь с предыдущим шагом сценария, то это как раз таблица предыдущего шага (к примеру, созданного на основе шаблона «Источник» или других примитивов обработки данных). Однако, для новой таблицы можно выбирать и трансформировать поля из множества разных таблиц. Достаточно нажать кнопку со знаком «+» и указать дополнительную таблицу-источник в диалоговом окне «Выбор таблиц для сценариев». Новая таблица появится в левой секции. Контекстное меню (вызывается правой кнопкой мыши) позволяет удалять таблицы-источники.

Нужные для выборки поля перемещаются из левой секции в правую с помощью мыши (методом drag-and-drop), или кнопок «>» и «<». На предыдущем рисунке использованы поля «Товар» (из таблицы «test_xlsx_ad») и «Сумма» (из таблицы «demo_tbl»), а на основе поля «Дата» (из таблицы «demo_tbl») сгенерировано новое поле «Дата_Месяц». Точка напротив использованных полей в исходной таблице меняет цвет на синий.

В правой секции вкладки «Настройка полей запроса» можно:

добавлять, удалять поля новой таблицы. Предусмотрены кнопки «Добавить» и «Удалить всё»;
менять порядок полей сверху вниз (кнопками со стрелками над секцией). Это влияет на порядок полей слева направо (соответственно) формируемой таблицы;
задавать псевдоним нового поля ручным вводом в колонке «Псевдоним» (по умолчанию имена полей совпадают с исходными, либо генерируются автоматически при создании части даты);
трансформировать выражения, производить дополнительные расчёты с помощью вставок кода на SQL. В колонке «Поле/Выражение» двойной щелчок мыши выводит кнопку с символом троеточия «…». Нажатие на кнопку открывает подокно «Произвольное выражение» (рисунок ниже), где вручную вводится код;

менять тип полей в новой таблице с помощью колонки «Тип». Интерфейс аналогичный колонке «Поле/Выражение» — двойной щелчок мыши и кнопка «…»;
задавать сортировку по полю. Двойной щелчок мыши в колонке «Сорт.» задает сортировку по убыванию, возрастанию (поле «Товар»), либо удаляет сортировку. Сортировка работает, если в поле «Ограничить размер выборки» введено число строк. При включенной сортировке появляется символ треугольника.

В нижней части вкладки есть селектор «Выбирать различные данные». Он позволяет извлекать только уникальный список значений (без повторов).

Можно использовать поле с датой и временем в трансформированном виде (извлечь месяц, год и т.д.). Перед этим поле следует добавить в правую секцию, выделить его и нажать кнопку «Части даты». Появится окно с различными вариантами для выбора. После добавления трансформированного результата исходное поле с датой можно удалить, и оставить только преобразованный вариант даты.

В примере из поле «Дата» извлечено название месяца на русском языке. После закрытия окна в столбце «Поле/Выражение» генерируется код на SQL:

to_char(Дата, 'TMMonth')

Если в шаге данные собираются из нескольких таблиц, необходимо связать исходные таблицы между собой (вкладка «Связи таблиц»). Двойной щелчок мыши по названию нижестоящей таблицы «test_xlsx_ad» вызывает диалоговое окно «Соединения». В окне выбираются тип соединения («Левое») и связанные поля («Товар»).

При необходимости, есть функция фильтрации данных в исходных таблицах по какому-либо условию (вкладка «Настройка отбора данных»). При этом, можно задать сразу множество отборов.

Каждый отбор создаётся кнопкой «Добавить новый элемент» (правее есть кнопки группировки условий и удаления). Потом надо указать поле для отбора, вид сравнения и в колонке «Значение» ввести константу.

В итоге, в нижней части окна появится SQL-код условий отбора:

"ad_test_demo_tbl_t"."Товар" = 'Товар 1'

Можно использовать группу условий, которые должны одновременно выполняться (логическое «И»), либо не выполняться (логическое «НЕ»), либо достаточно выполнения одного любого условия из группы (логическое «ИЛИ»). Для этого надо выделить несколько условий и нажать кнопку «Сгруппировать условия«, а затем выбрать в заголовке группы нужный логический вариант. Код отбора SQL можно ввести вручную, если активировать режим «Текст отбора задается произвольным кодом».

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

Группировка

Шаблон «Группировка» позволяет агрегировать данные из таблицы-источника или таблицы предыдущего шага. Соответствует SQL-операции GROUP BY.

На следующем рисунке рассчитана сумма по полю «Сумма» для каждого значения поля «Товар».

Группируемые поля («Товар») перемещаются из левой секции «Поля источника» в нижнюю правую («Группировки») с помощью мыши (методом drag-and-drop). Точки напротив имён использованных полей поменяют цвет на синий.

Кнопки со стрелками над секцией «Группировки» позволяют менять порядок группируемых полей сверху вниз. Это влияет на их порядок в новой таблице слева направо (соответственно).

Поля для агрегаций (например, суммы, среднего, максимума, минимума, количества строк) также перемещаются с помощью мыши из левой секции «Поля источника» в верхнюю правую («Показатели»). Здесь изменение порядка строк сверху вниз производится с помощью контекстного меню (вызывается правой кнопкой мыши: там есть варианты для перемещения вверх и вниз).

Двойной щелчок мыши по полю в «Показатели» и нажатие кнопки «…» открывает диалоговое окно «Произвольное выражение». В окне можно вручную прописать код SQL для агрегации («Выражение»).

В каждой строке секций «Группировки» и «Показатели» есть столбец «Псевдоним». В столбце можно вручную ввести новое название колонки.

По умолчанию, агрегируемому выражению в секции «Показатели» присваивается тот же числовой тип, что и в исходном поле. На вкладке есть селектор «Показывать типы полей». Желательно его активировать. Тогда в агрегации можно менять тип чисел. Может понадобиться тип с большей разрядностью, если суммы в исходной таблице большие и итоговый расчёт выйдет за диапазон данных исходного поля. Иначе результатом группировки будет ошибка.

Результат шага отражается на одноименной вкладке: можно увидеть полученную таблицу и код SQL для её генерации.

Соединение

Шаблон «Соединение» нужен для объединения данных из нескольких таблиц. Для шага с таким шаблоном нужны связи из предыдущих шагов (например, таблиц-источников или вычисляемых таблиц с помощью примитивов). Должно быть минимум две таблицы для объединения. Рассмотрим пример соединения слева (аналог функции «ВПР» в MS Excel / операции LEFT JOIN в SQL). К таблице продаж по адресу надо добавить почтовый индекс.

В нижней секции «Условия связи таблиц» выбирается тип соединения («Левое»), с помощью кнопки «Добавить» добавляется строка условия равенства двух адресов. Две колонки называются по имени исходных таблиц («Продажи (основной)» и «Справочник»), а в выпадающих списках надо лишь выбрать два раза поле «Адрес». Между двумя полями есть столбец для вида условия, также с помощью выпадающего списка (в примере это равенство «=»).

Если ключ для связи составной (основан на двух и более полях), добавляются дополнительные строки условий (есть кнопка «Добавить»).

Шаг позволяет объединять за один раз более двух таблиц. В этом случае, в выпадающем списке «Присоединяемые таблицы» надо выбрать третью (четвертую и т.д.) таблицу и аналогичным образом задать связи между полями в секции «Условия связи таблиц».

Далее, следует выбрать поля, которые попадут в итоговую таблицу шага. На вкладке «Настройка полей запроса» в левой секции отображаются исходные таблицы и их поля. С помощью мыши методом drag-and-drop (или кнопками «>» и «<») их можно перетащить в правую секцию, где собираются поля и выражения итоговой таблицы. Точки напротив имён использованных полей поменяют цвет на синий.

Кнопки со стрелками над правой секцией позволяют менять порядок группируемых полей сверху вниз. Это влияет на их порядок в новой таблице слева направо (соответственно).

Кнопка «Добавить» добавляет новую строку, в которой можно создать выражение. Двойной щелчок мыши по столбцу «Поле/Выражение» и нажатие кнопки «…» открывает диалоговое окно «Произвольное выражение». В окне можно вручную прописать код SQL для расчёта нового показателя («Выражение»). Из рисунка выше можно понять, что адрес продаж переводится в верхний регистр.

В каждой строке правой секции есть столбец «Псевдоним». В столбце можно вручную ввести новое название колонки.

Если используется поле с датой и временем, его можно трансформировать (извлечь месяц, год и т.д.). Перед этим поле следует добавить в правую секцию, выделить его и нажать кнопку «Части даты». Появится окно с различными вариантами для выбора. После добавления трансформированного результата исходное поле с датой можно удалить.

Таблица, генерируемая шаблоном, отражается на вкладке «Результат». Здесь можно увидеть также код SQL для генерации данных.

Отбор

Шаблон «Отбор» используется для фильтрации таблицы по одному или группе полей. В логике SQL он соответствует оператору WHERE. Надо выбрать поля из таблицы предыдущего шага и указать условия фильтрации (одно или более).

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

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

На рисунке выше исходная таблица предыдущего шага фильтруется одновременно по трём полям:

наименование товара должно быть «Товар 1»;
дата продаж до 01.01.2024;
сумма продаж в каждой строке больше 50 000 руб.

Вкладка «Настройка отбора данных» имеет два секции: левую (источник данных) и правую (условия отбора для новой таблицы, которую создаёт шаг сценария).

Нужные для фильтрации поля перемещаются из левой секции в правую с помощью мыши (методом drag-and-drop), или кнопок «>» и «<». Точка напротив использованных полей в исходной таблице меняет цвет на синий.

В правой секции можно:

добавлять, удалять поля с условиями фильтрации. Предусмотрены кнопки «Добавить новый элемент» и «Удалить», а также «Сгруппировать условия»;
менять порядок условий сверху вниз (кнопками со стрелками над секцией);
задавать тип сравнения двойным щелчком мыши и выбором из выпадающего списка. Для дат кроме произвольной даты есть множество вариантов («Начало прошлого года», «Начало этого дня» и т.д. для дней, недель, месяцев и лет). Далее, в колонке «Значение» следует ввести константу, а перед именем поле активировать галочку.

В итоге, в нижней части окна появится SQL-код условий отбора.

Код отбора SQL можно ввести вручную, если активировать режим «Текст отбора задается произвольным кодом».

Top N

Шаблон «Top N» извлекает из таблицы исходных данных первые N записей. Соответствует оператору SQL LIMIT. При этом, в шаблоне есть возможность фильтрации и сортировки.

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

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

Выберем три самых продаваемых товара, но с суммой продаж более 1 300 000 руб. Отсортируем таблицу по полю «Сумма» и три записи с фильтром по полю «Сумма» от 1 300 000.

На первом шаге, следует указать поле для сортировки. Вкладка «Настройка сортировки» имеет два секции: левую (источник данных) и правую (условия отбора для новой таблицы, которую создаёт шаг сценария).

В правой секции можно:

менять порядок условий сверху вниз (кнопками со стрелками над секцией). Это важно, потому что можно одновременно сортировать более одного поля. Порядок полей сверху вниз влияет на очередность сортировки: сортировка полей выше приоритетнее, чем тех, что ниже;
добавлять, удалять поля с условиями фильтрации. Для этого предусмотрено контекстное меню, вызываемое правой кнопкой мыши;
задавать тип сортировки двойным щелчком мыши по колонке «Сортировка». Сортировать можно по убыванию или возрастанию (в данном случае логично по убыванию). В колонке »Сортировка» появится знак треугольника соответствующей формы.

В поле «Размер выборки» введём цифру «3».

На следующем шаге зададим фильтрацию (вкладка «Настройка отбора данных«).

Здесь также есть две секции: левая (список полей исходной таблицы с предыдущего шага) и правая (условия отбора для полей).

Нужные для фильтрации поля перемещаются из левой секции в правую с помощью мыши, или кнопок «>» и «<». Точка напротив использованных полей в исходной таблице меняет цвет на синий (в данном случае поле «Сумма»).

В правой секции можно:

добавлять, удалять поля с условиями фильтрации. Для этого предусмотрено контекстное меню, вызываемое правой кнопкой мыши, и кнопка «Добавить новый элемент» над секцией;
выбрать тип сравнения из выпадающего списка в колонке «Вид сравнения»;
задать вручную пограничное значение в поле «Значение».

Для дат есть множество вариантов («Произвольная дата» (с выбором из календаря), «Начало прошлого года», «Начало этого дня» и т.д. для дней, недель, месяцев и лет).

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

Объединение

Шаблон «Объединение» позволяет объединить разные таблицы (из предыдущих шагов) построчно. Соответствует логике SQL-оператора UNION.

На первом шаге (вкладка «Настройки объединения») надо указать, как поля разных таблиц соответствуют друг другу. На вкладке появляется таблица-конструктор. Каждая её колонка — это поля таблиц, участвующих в объединении (их может быть более двух). Если у полей разных таблиц одинаковые названия и идентичные типы данных, то процесс ускорит кнопка «Заполнить»: этот инструмент сам произведет расстановку. Если нужна корректировка, в каждой ячейке есть раскрывающийся список и можно выбрать соответствующее поле вручную.

При включенном селекторе «Только уникальные» из итоговой (объединённой) таблицы удаляются повторяющиеся строки. В коде SQL из оператора UNION исключается параметр ALL. На вкладке «Результат» можно посмотреть, какая таблица получится на выходе, и какой текст запроса на SQL соответствует всему шагу.

Unpivot

Шаблон «Unpivot» выполняет действия, обратные шаблону «Pivot», преобразуя столбцы данных в строки. Соответствует одноимённому оператору SQL.

Например, есть данные о продаже товаров помесячно следующего вида.

На следующем рисунке — пример настройки шаблона.

В левой секции окна настройки шаблона выделены все поля, поскольку они все задействованы.

На вкладке «Настройка полей запроса» в имени «Категория» вводим «Месяц», а в «Имя поля значения» вводим «Сумма продаж в руб.». В нижнюю правую секцию «Значение (категория)» следует переместить (мышью методом «drag-and-drop» или кнопками «<» и «>») поля с месяцами, и напротив каждого месяца можно вручную ввести полное наименование. С помощью контекстного меню можно удалить лишние поля из списка.

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

В «Тип поля значения» можно скорректировать тип (например, увеличить разрядность чисел, чтобы не было переполнения).

Pivot

Шаблон «Pivot» производит действия, обратные шаблону «Unpivot»: он преобразует строки данных в столбцы. Соответствует одноимённому оператору SQL. Например, есть данные о продаже товаров помесячно следующего вида. Требуется данные с названиями месяцев перевести в столбцы.

В окне настройки шаблона есть четыре секции:

«Поля источника» — перечень полей исходной таблицы;
«Строки (группировки)» — для полей, которые останутся в столбцах (в данном случае поле «Товар»);
«Столбцы» — для поля, которое из столбца трансформируется в строки («Месяц»);
«Значения» — числовое поле со значениями («Сумма продаж в руб.»).

Поля из секции «Поля источника» добавляются в другие три секции с помощью мыши методом drag-and-drop. Слева от названия полей, использованных в секции «Поля источника», появляется цветная точка.

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

В рассматриваемом примере выбирается вариант суммирования, и укрупнения информации не произойдёт.

Результат работы шаблона (вкладка «Результат») — трансформированная таблица и SQL-код для шага.

Маппинг 2х таблиц

Шаблон «Маппинг 2х таблиц» позволяет копировать данные из одной таблицы в другую, используя настроенные правила соответствия (маппинга) полей. С помощью шаблона надо показать, какие поля первой таблицы соответствуют каким полям другой таблицы в рамках SQL-инструкции INSERT INTO. Также можно произвести фильтрацию исходных данных и некоторые преобразования.

Например, необходимо скопировать данные из одной таблицы (таблица-источник) в другую таблицу (таблица-приёмник) (без округа ЮФО). Данные поля «Регион» таблицы-источника соответствует полю «Область» таблицы-приёмника.

На вкладке «Источник и приёмник» надо выбрать имена двух таблиц в базе данных.

Округ ЮФО отфильтрован на вкладке «Настройки отбора данных».

Каждый отбор создаётся кнопкой «Добавить новый элемент» (правее есть кнопки группировки условий и удаления). Потом надо указать поле для отбора, вид сравнения (из выпадающего списка) и в колонке «Значение» ввести константу.

Можно задать сразу множество отборов, а также группироваить их (когда условия одновременно выполняются — логическое «И», не выполняются — логическое «НЕ», либо достаточно выполнения одного любого условия из группы — логическое «ИЛИ»). Для этого надо выделить несколько условий и нажать кнопку «Сгруппировать условия», а затем выбрать в заголовке группы нужный логический вариант.

Далее, на одноимённой вкладке устанавливается связь полей (рисунок ниже). Из левой секции «Источник» (где есть перечень полей таблицы-источника) с помощью мыши методом drag-and-drop нужные поля перемещаются в правую секцию «Приёмник». Если поля называются одинаково, можно воспользоваться кнопкой «Автосопоставление». Предусмотрена кнопка и быстрой очистки сопоставлений. Над обоими секциями есть кнопки «Показать таблицу» для предварительного просмотра данных.

Чтобы суммы продаж были в одном измерении, поле таблицы-источника «Сумма_продаж_млн_руб.» перед копированием в поле «Продажи_в_руб.» таблицы-приёмника надо умножить на 1000000. После двойного щелчка мышью по имени перемещенного поля в правой секции («Сумма_продаж_млн_руб.») появится кнопка «…», которая вызовет диалоговое окно «Произвольное выражение». В этом окне можно ввести необходимую формулу для вычислений.

После выполнения шага маппинга можно получить необходимый результат (рисунок ниже).

Создание таблицы БД

Шаблон «Создание таблицы БД» позволяет создать пустую таблицу базы данных с определенным набором полей нужного типа. Для шаблона не нужны предыдущие шаги, чтобы получить входные данные. Такой шаг соответствует SQL-оператору CREATE TABLE.

На вкладке «Основная» задается «Имя таблицы», схема, и с помощью кнопки «Добавить» создать каждое поле. В колонке «Имя поля» вручную вводится имя поля. Двойной щелчок мыши в колонке «Тип поля» активизирует кнопку «…», которая позволяет задать тип (из категории «Типы данных» или «Пользовательские типы»).

Клавиши со стрелками вверх и вниз меняют порядок полей в новой таблице слева-направо соответственно.

На вкладке «Основная» также предусмотрен селектор «Создать если отсутствует» и «Удалить и создать». Выбранный режим выделяется синим цветом текста и влияет на алгоритм шаблона: в первом случае таблица просто создается с нуля, если её нет, во втором случае она всегда удаляется и заново создается.

Селектор «Скрыть служебные параметры» скрывает настройки: подтип данных для поля, и допускаются ли пустые значения (NULL) в конкретном поле. Результат работы шаблона (вкладка «Результат») — код на PostgreSQL.

Кнопка «Готово» в верхнем левом углу шаблона завершает настройку шага.

Разделение строки

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

Шаблон соответствует оператору UNNEST с параметром STRING_TO_ARRAY, которые специфичны для PostgreSQL.

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

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

Кнопка «Показать входные данные» выгружает в нижней секции пример исходной таблицы. Очевидно, что из списка полей «Разделяемое поле» надо выбрать «Температура», и укажем «Разделитель» («,»).

Ниже — результат работы шага (вкладка «Результат»). Из каждой строки исходной таблицы получилось 6 строк. Значения полей «Дата» и «Метеостанция» дублируются для каждой новой строки, а поле «Температура» разделено на 6 значений.

Маппинг 2х таблиц с предварительным удалением

Шаблон «Маппинг 2х таблиц с предварительным удалением» работает аналогично шаблону Маппинг 2х таблиц, с одним отличием. На вкладке «Связь полей» задаются поля (ключ) для поиска (в колонке «Поиск»). В таблицу-приёмник попадут все записи из таблицы-источника. Но если какие-то записи совпадут по ключу поиска, то они в таблице-приёмнике будут заменены соответствующими записями из таблицы-источника.

4 - Шаблоны выполнения произвольного кода

С помощью шаблонов в данной группе можно писать код на языке 1С или SQL для загрузки данных из базы 1С, либо создания, выборки данных, очистки и т.д. произвольных таблиц СУБД.

Произвольный код 1С

Шаблон «Произвольный код 1С» позволяет написать произвольный код на языке 1С для извлечения данных из базы 1С.

Произвольный запрос 1С

Данный шаблон позволяет получить данные из базы ETL с помощью языка запросов 1С. Он является устаревшим и не рекомендуется к использованию.

Произвольный код SQL

Шаблон «Произвольный код SQL» позволяет написать SQL-код для выгрузки данных из базы SQL. Шаблон может быть без предыдущего шага, поскольку исходные данные будут загружаться из таблиц, указанных в произвольном коде.

Предусмотрен выбор типа запроса в выпадающем списке «Тип»: выборка данных SELECT («SEL»), выполнение набора команд («EXEC») или подзапрос («Вложенный запрос»):

если выбран тип запроса «EXEC», то в произвольный код можно внести набор инструкций, разделенных точкой с запятой — все они по очереди будут выполнены. Здесь также могут быть инструкции, которые не приводят к генерации таблиц данных. Например, это операции создания макета таблицы (CREATE TABLE), очистки (TRUNCATE) и т.д.;
тип запроса «SEL» подходит, если код генерирует на выходе таблицу с данными, и тогда их можно посмотреть в результате работы шага и передать в следующий шаг. Например, в шаг «Перенос данных»; в этом случае внизу вкладки «Основное» есть вкладка «Дополнительно» / «Структура данных», где можно перенастроить поля таблицы;
тип запроса «Вложенный запрос» полезен, если надо сгенерировать таблицу данных, и передать эти данные как вложенный запрос на следующий шаг, сделанный также в шаблоне «Произвольный код SQL». В настройке следующего шаге кнопка «Вложенные запросы» откроет диалоговое окно с наименованием шага предыдущего вложенного запроса. Двойной щелчок по наименованию внесёт имя данных вложенного запроса в текущий запрос.

Кнопка «Вложенный запрос» работает аналогичным образом, когда перед шагом «Произвольный код SQL» есть другой шаг типа «Произвольная выборка», «Группировка» и т.д. В этом случае кнопка «Вложенные запросы» открывает диалоговое окно «Доступные шаги сценария» с данными «входящей» таблицы (рисунок ниже). Двойной щелчок мыши по названиям её полей или самой таблицы добавляет фрагмент кода (с именем поля / таблицы соответственно) в запрос.

5 - Шаблоны базовых операций с данными

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

Источник

Шаблон «Источник» — основа для работы. Он позволяет указать таблицу базы, из которой будут браться исходные данные для сценария. Если начать редактирование «Источника», то откроется диалоговое окно «Выбор таблиц для сценариев».

По умолчанию информация отражается в виде таблиц и схем (режим «Таблица»). В нижней части окна приведен список всех доступных таблиц и схем, к которым относятся эти таблицы. Для быстрого поиска нужной таблицы по её названию можно ввести текст в окне «Поиск (Ctrl + F)». По мере ввода каждого символа, в нижней части будут отфильтровываться совпадения названий таблиц.

Двойным щелчком мыши по строке можно завершить выбор таблицы для «Источника». Над иконкой шага появится имя выбранной таблицы.

Предусмотрен и другой режим поиска — «Составы выгрузки». Для активации режима следует нажать одноименную кнопку. В нижней секции будут сведения о составе выгрузок, именах таблиц, правил выгрузки, даты изменения, создания и режима записи.

Расширенный поиск помогает искать таблицу, когда в списке много таблиц с похожими названиями. Можно искать совпадение символов в начале строки, по части строки и по точному совпадению, причем не только в имени таблицы, но и схеме (выпадающий список «Где искать:»).

Кнопка «Обновить конфигурацию» позволяет обновить в диалоговом окне данные, которые появились недавно. На рабочем холсте WorkFlow сценария может быть более одного источника, если требуется объединять данные из разных таблиц.

Приёмник

Когда произведены все загрузки, очистки, трансформации, обогащения данных и т.д., после шаблона с последними операциями добавляют «финальный» шаг с «Приёмником». С помощью шаблона надо всего лишь задать имя набора данных, созданного после всех расчётов и преобразований. Имя впечатывается в строку ввода «Приемник данных» диалогового окна «Мастер настройки приемника данных». В дальнейшем, это имя следует использовать в Аналитическом портале при создании набора данных.

В верхнем правом углу Мастера есть кнопки для вспомогательных функций: можно сохранить файл (в формате «*.txt»), распечать таблицу на принтер, посмотреть содержимое, получить ссылку на финальную таблицу.

В нижней части окна Мастера отражается код SQL (удаление таблицы приемника, повторное её создание на базе предыдущего шага).

Можно установить галочку в селекторе «Является источником». Тогда появится возможность использовать приёмник дальше в сценарии, т.е. протянуть из него стрелку для следующего шага, и такой приёмник не будет «финальным».

Просмотр

Шаблон «Просмотр» сам по себе не влияет на результат работы сценария. Однако, это удобный и полезный инструмент. Он описывает данные, полученные на предыдущем шаге. Из списка полей предыдущего шага («Поля» в левой нижней части окна) методом drag-and-drop необходимо переместить анализируемые поля в секцию «Статистика» (в правой нижней части окна). По каждому полю еще можно указать (вводом с клавиатуры) лимит количества значений (ограничение выборки в столбце «Лимит кол-во значений (TOP N)»).

Выбор вкладки «Качество данных» (правее вкладки «Статистика») открывает доступ к следующим функциям:

соответствие значений поля какому-либо условию;
поиск дублей значений.

Аналогично секции «Статистика», поля для изучения перемещаются в секцию «Качество данных» (кнопка «Добавить» создает пустую строку, кнопка «Копировать» — копирует существующую). Для каждого поля в колонке «Тип проверки» есть выпадающий список со значениями «Дубли» или «Условие».

Чтобы сформировать условие для конкретного поля, надо выделить строку с этим полем, затем в нижней правой подсекции нажать кнопку «Добавить условие» (в виде белого плюса в круге зеленого цвета). Рядом также есть кнопка удаления условия.

Для числовых полей можно использовать операторы сравнения («больше», «меньше», «больше или равно», «меньше или равно», «равно», «не равно» — «!=») либо вхождения в диапазон («IN»).

Для текстовых строк в условии может быть длина строки («LEN»), поиск по совпадению текста («LIKE»), вхождение в диапазон текстовых значений («IN»). Также возможен поиск по варианту, когда в тексте только цифры («Только цифры [0-9]»).

В столбце «Значение» (правее колонки «Условие») следует ввести те значения, с которыми производится сравнение.

В каждой строке настройки условия можно включить селектор «NOT» (первый столбец): он задает, что условие не соблюдается (логическое «НЕ»).

Для типа проверки «Дубли» не предусмотрено условий сравнения.

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

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

Очистка таблицы

Шаблон «Очистка таблицы» производит очистку таблицы из базы данных, которую надо указать в поле «Приемник данных». В итоге, шаг генерирует код SQL TRUNCATE TABLE с указанием имени таблицы.

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

Перенос данных

Шаблон шага «Перенос данных» предназначен для переноса данных между двумя различными базами внутри одного сценария. Например, перенос данных в базу данных витрин ClickHouse из базы данных ядра на базе PostgreSQL.

Для работы шага обязательно должен быть установлен агент выполнения сценария («Информация» / «Основные настройки» / «Агент для выполнения сценариев»).

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

На следующем шаге (закладка «Связь полей»). требуется установить связи между полями таблицы-источника и таблицы-приемника. Для этого над именем поля таблицы-источника (в левой секции окна) нужно нажать левую кнопку мыши, и, не отпуская её, сдвинуть указатель мышь в имя поля таблицы-приёмника (в правой секции окна).

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

Если в таблицах имена полей схожи, и у них совпадает тип, можно ускорить процесс связывания с помощью кнопки «Автосопоставление». Рядом есть кнопка «Очистить все сопоставления», которая удаляет связи.

Перенос данных логируется документом «Перенос данных» («Логи» / «Переносы данных»).

6 - Шаг переноса данных в другую БД

Шаблон шага «Перенос данных» находится в группе шагов «Данные» и предназначен для переноса данных между двумя различными базами внутри одного сценария. Например, перенос данных в базу данных витрин ClickHouse из базы данных ядра на основе PostgreSQL.

Для работы шага обязательно должен быть установлен агент выполнения сценария («Информация» / «Основные настройки» / «Агент для выполнения сценариев»).

Настройка мастера

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

Перенос данных логируется документом «Перенос данных» («Логи» / «Переносы данных»).

7 - Использование параметров выполнения шага

Настройки работы ADO для конкретного шага

Каждому шагу сценария обработки данных можно указать параметры работы ADO, которые будут учитываться при выполнении шага. Для этого нужно дважды кликнуть на нужный шаг — откроется окно с параметрами шага (см. рисунок ниже, 1). Далее перейти на вкладку «Настройки работы ADO» (см. рисунок ниже, 2):

Создать новый элемент и заполнить параметры:

«Шаг сценария», должен заполнится автоматически (см. рисунок ниже, 1);
«Вид настройки» («Опции выполнения метода execute» или «Таймаут выполнения команды») (см. рисунок ниже, 2);
Параметры, зависящие от вида настройки (см. рисунок ниже, 3);

Далее, следует записать и закрыть элемент (см. рисунок ниже, 4). Теперь при выполнении шага будут учитываться заданные параметры ADO:

Настройки работы ADO для категорий шагов

Для применения параметров работы ADO для определенных (отобранных) шагов, можно воспользоваться «Правилами применения настроек ADO». Для этого необходимо перейти в меню «Главное» / «Настройки» / «Правила применения настроек ADO»:

Создать новый элемент и заполнить параметры «Наименование» (см. рисунок ниже, 1) и «Отбор» (см. рисунок ниже, 2) — для определения нужных шагов и записать (см. рисунок ниже, 3) изменения. Далее перейти на вкладку «Настройки работы ADO» (см. рисунок ниже, 4):

На вкладке «Настройки работы ADO» создать новый элемент и заполнить параметры:

«Правило», должен заполнится автоматически (см. рисунок ниже, 1);
«Вид настройки» («Опции выполнения метода execute» или «Таймаут выполнения команды») (см. рисунок ниже, 2);
параметры, зависящие от вида настройки (см. рисунок ниже, 3).

Далее, следует записать и закрыть элемент (см. рисунок ниже, 4). Теперь, к шагам, соответствующим отбору, при их выполнении будут применятся заданные настройки работы ADO.

8 - Шаги сценария (старый интерфейс)

Для настройки каждого шага на вкладке «Шаги сценария (старый интерфейс)» предусмотрены управляющие элементы:

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

При добавлении шагов сценария следует заполнить поля:

Поле	Назначение поля	Правила заполнения и работы
Кнопка «Включить / Выключить»	Выключить шаг, т.е. пропускать шаг при авто-выполнении сценария	Для выключения шага необходимо нажать на кнопку «Выключить»
Вид шага	Выбор вида скрипта	Выбрать из: «SQL-запрос» / «1C-запрос» / «Произвольный код на языке 1С»
Тип шага	Выбор типа шага	Выбрать из трех значений: «EXEC» — исполняемый, «SEL» — выборка данных, «Вложенный запрос» — результаты шага будут доступны в последующих шагах сценария
Запускать 1 раз	Признак, что шаг допустимо выполнять только один раз	Если допускается только однократное выполнение шага, то установить флаг и тогда при ошибке выполнения скрипта система не будет делать несколько попыток выполнить шаг (количество попыток устанавливается в системных настройках)
Текст запроса	Текст запроса	Заполнить текст запроса на языке SQL или 1C
Имя таблицы	Текстовое поле, отражающее название таблицы, в которую вносятся изменения на этом шаге сценария	Заполняется вручную, используется для графического изображения процесса обработки данных. Графическое изображение можно включить в пакете обработки (подробно см. в этом разделе
Комментарий	Описание для шага	Заполнить описание шага

Настройка сценария, используя шаблоны шагов сценария

Для облегчения настройки шагов возможно воспользоваться готовыми преднастроенных шаблонов (мастеров). Текст скрипта на языке SQL / 1С сформируется автоматически на основании выбранных пользователем настроек.

Для использования шаблонов нужно при создании нового шага сценария, выбрать шаблон из списка «Шаблоны шагов сценария»:

После формирования текста запроса и сохранения параметров шага, в дереве сценария шаги созданные при помощи шаблонов выделяются иконкой (см. рисунок выше).

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

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

После удаления появляется возможность выбрать шаблон «Задать шаблон».

Результирующий текст запроса шага сценария обработки данных получится после ввода всех необходимых для шаблона настроек при помощи мастера настроек, как будет описано ниже. Чтобы сохранить шаг введите его наименование и нажмите кнопку «Записать»:

Просмотр текста скрипта для шаблона шага доступен в справочнике «Шаблоны шагов сценария» на вкладке «Текст запроса»:

Настройка параметров шаблонов шага при помощи мастера

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

Для создания шага с помощью шаблона необходимо нажать кнопку «Задать шаблон».

После выбора необходимого шаблона открывается мастер, например, «Произвольная выборка данных»:

Далее на примере «Произвольной выборки данных» будут описаны особенности интерфейса и управляющие элементы, характерные для других шаблонов (мастеров).

При использовании шаблона «Произвольная выборка данных» необходимо:

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

Описанные этапы настройки представлены на рисунках ниже.

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

Форма настройки полей также позволяет настроить произвольное выражение, форма доступна при нажатии на кнопку «fx» . Это позволяет применить, например, строковые, календарные, числовые и другие функции:

Настройка связей между таблицами состоит из шести шагов.

Для установки связи между таблицами нужно перенести одну таблицу на другую (см. рисунок ниже, 1), переход к детальной настройке связи осуществляется двойным кликом.
выбор типа соединения (см. рисунок ниже, 2).
Доступные варианты: левое; правое; внутреннее; внешнее (полное).
выбор поля первой таблицы (см. рисунок ниже, 3);
выбор оператора соединения (см. рисунок ниже, 4).
Доступные варианты операторов: «=» — равно; «<>» — не равно; «<» — меньше; «<=» — меньше или равно; «>» — больше; «>=» — больше или равно.
выбор поля второй таблицы (см. рисунок ниже, 5):

Настройка отбора данных состоит из трёх шагов:

выбор поля таблицы, к которому применяется отбор (см. рисунок ниже, 1);
выбор вида сравнения (см. рисунок ниже, 2). Например, «Равно», «Больше или равно», «В списке», «Заполнено» и т.п.;
ввод значения для применения отбора данных (см. рисунок ниже, 3);

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

Пример: «значение поля больше или равно, или значение поля не заполнено»:

В результате шаг сценария будет содержать SQL-запрос по указанным настройкам мастера:

Группировка шагов сценария

Шаги сценария можно объединять в логические группы.

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

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

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

Настройка группировки шагов

Для создания группы в дереве сценария необходимо нажать на кнопку «Группа». Для добавления нового шага в группу необходимо установить курсор в дереве сценария на группу и добавить шаг, нажав на кнопку «Шаг»:

Для добавления существующего шага в группу необходимо выбрать шаг и в поле «Шаг родитель» указать группу, в которую необходимо перенести выбранный шаг:

Настройка безопасного обновления данных

Для создания группы шагов в режиме безопасного обновления данных необходимо создать группу и выбрать тип группировки «Безопасное обновление данных таблицы».

В этом режиме в группе создаются служебные шаги:

Выберите таблицу в списке «Имя таблицы». Для этой таблицы включится механизм безопасного обновления. Нажмите кнопку «Записать»:

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

В служебном шаге «Создание временной таблицы» формируется таблица-копия с аналогичной структурой исходной таблицы. Наименование таблицы-копии формируется по маске «Имя таблицы_TMP_yyyyMMddHHmmssfff».

Далее все манипуляции с данными необходимо производить в таблице-копии. Например, используя шаблон «Мэппинг полей 2х таблиц» генерируется SQL-запрос, в котором операция вставки данных производится в таблицу-копию. Мастер шаблона определяет, что шаг сценария расположен в группе с типом «Безопасное обновление данных таблицы» и подменяет наименование таблицы-приемника на системную переменную «{!Контекст_ИмяВременнойТаблицы!}».

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

Настройка шагов сценария как подзапросов

Для возможности использовать результаты выполнения шага в других шагах сценария необходимо указать тип шага «Вложенный запрос».

Ниже приведен пример настройки шага сценария.

Создаем первый шаг сценария шаблоном «Произвольная выборка данных», указывая что результат будет доступен в других шагах сценария:

В результате формируется шаг сценария с типом «Вложенный запрос».

Создаем второй шаг сценария с видом шага «SQL» и типом «SEL». В блоке «Текст запроса» указываем запрос, например, select from и далее перейдя по ссылке «Вложенные» необходимо выбрать ранее созданный шаг:

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

«select from ({!системный идентификатор!}) AS Step_1»

Запуск сценария

Существуют три варианта запуска шагов сценария

запускать каждый шаг или группу шагов по кнопке «Выполнить шаг» в дереве сценария;
запустить выполнение всех шагов сценария по кнопке на вкладке «Основное»;
запустить выполнение всех шагов сценария в интерфейсе WorkFlow по кнопке «Запустить» — как было описано выше.

При запуске на выполнение всех шагов сценария открывается интерфейс, в котором показывается ход выполнения шагов сценария:

После завершения работы сценария — открывается «Лог выполнения сценария»:

9 - Дополнительные возможности

Сценарий обработки данных. Работа с шаблоном расчета фасетов таблицы

Мастер настройки расчета фасетов таблицы используется при проведении анализа данных с помощью отчета «Фасеты»:

Подробнее о работе отчета см. раздел Просмотр результатов расчета фасетов таблицы.

Настройка расчета фасетов таблицы

Для расчета фасетов таблицы необходимо создать специальный сценарий. В сценарии создать новый шаг и выбрать из шаблонов мастер настройки расчета фасетов (см. рисунок ниже, 1, 2, 3):

Для настройки расчета фасетов таблиц необходимо

выбрать базу данных (см. рисунок ниже, 1);
выбрать название модели (см. рисунок ниже, 2), в случае если расчет необходимо провести для таблицы, которая находится в составе какой-либо модели данных;
выбрать таблицу (см. рисунок ниже, 3), поставить галку «Таблицы из конфигурации базы данных» (см. рисунок ниже, 4) в случае если расчет необходимо провести для таблицы, которая не находится в составе какой-либо модели данных;
нажать кнопку «Далее» для перехода не следующий этап настройки.

На закладке «Настройка полей» (см. рисунок ниже) необходимо:

проставить галку выбора поля (см. рисунок ниже, 1);
ввести значение «Количество строк в фасете» = «-1» (см. рисунок ниже, 2), в случае если расчет фасетов по выбранному полю не требуется;
ввести значение «Количество строк в фасете» = «100» (см. рисунок ниже, 3), в случае если требуется частичный расчет фасетов по выбранному полю.

Примечание: при частичном расчете фасетов значения по рассчитываемому атрибуту таблицы сохраняются с применением ограничения заданного в поле «Количество строк в фасете». Например, расчет полного фасета по полям уникальных идентификаторов является избыточным ввиду необходимости сохранить полный список уникальных значений. При расчете фасетов по таким полям необходимо ограничивать расчет значением минимально достаточным для анализа.

После завершения настройки необходимо нажать кнопку «Готово». Текст шага сценария сгенерируется и будет готов для запуска.

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

Просмотр результатов расчета фасетов таблицы

Для просмотра результатов расчета фасетов таблицы необходимо открыть отчет «Фасеты», расположенный на начальной странице в разделе «Отчеты»:

Для запуска отчета «Фасеты» необходимо

выбрать базу данных (см. рисунок ниже, 1);
выбрать название модели (см. рисунок ниже, 2), в случае если расчет необходимо провести для таблицы, которая находится в составе какой-либо модели данных, либо выбрать модель «m1_По умолчанию»;
выбрать таблицу (см. рисунок ниже, 3);

В блоке списка полей таблицы необходимо выбрать поля, по которым требуется провести анализ и нажать кнопку «Сформировать». После формирования отчета в блоке значений отобразятся списки значений по выбранным полям:

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

Подробная информация отображается в дополнительном окне:

Окно с подробной информацией можно также открыть при двойном нажатии на наименование поля в блоке выбора полей таблицы: