Создание распределенного SQL регламентированного запроса

Распределённый регламентированный запрос (распределённый РЗ) — это запрос, который предназначен для вывода связанных данных, размещённых на двух или более Витринах данных.

  Примечание

 В Витрине данных конфигурации Лайт поддерживается работа с распределёнными РЗ начиная с версии 1.16.2

Для распределённого РЗ ядро СМЭВ4 автоматически разбивает запрос и формирует подзапросы.  Количество подзапросов соответствует числу задействованных таблиц Витрин данных. В случае распределённого запроса в мнемонике запроса используется префикс «podd» (например, распределённый РЗ podd.search_by_code_region).

Принцип работы распределённого РЗ

Агент-Потребитель направляет распределённый РЗ, в соответствии с последовательностью исполнения запроса (фиксированное, динамическое или параллельное исполнение). Запрос попадает в топик <мнемоника Витрины>.query.rq первой витрины, затем от витрины к витрине данные передаются через табличные параметры в топики <мнемоника Витрины>.query.tp и в соответствии с последовательностью исполнения применяются алгоритмы соединения (например, INNER JOIN).

Для реализации использования распределённого РЗ, необходимо выполнить ряд действий:
          1)     Создать распределённый РЗ;
          2)     Добавить связь витрин с информационными системами в ЛК УВ;
          3)     Отправить распределённый РЗ в ядро СМЭВ4 в ЕИП НСУД;
          4)     Потребителю данных получить доступ к распределённому РЗ в ЛК УВ;
          5)     Установить компоненты в Витринах данных, участвующих в распределённом РЗ;
          6)     Проверить работу РЗ.

Создание распределённого РЗ

Для создания распределённого РЗ, пользователю необходимо быть зарегистрированным в ЕИП НСУД, и быть наделённым правами роли Онтолог:

Рисунок 1 – Профиль пользователя в ЕИН НСУД. Роли пользователя

Для того, чтобы зарегистрировать распределённый РЗ, необходимо выполнить следующие шаги:

  1.   Для начала необходимо перейти в ЕИП НСУД. На левой панели необходимо раскрыть вкладку Модель данных. В раскрывшемся списке необходимо выбрать Регламентированные запросы и из выпадающего списка нажать на Запросы SQL, как показано на рисунке 2:

Рисунок 2 – ЕИП НСУД. Переход в Регламентированные запросы SQL

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

Рисунок 3 – ЕИП НСУД. Добавление нового Регламентированного SQL запроса

  3.   В выпадающем списке необходимо выбрать Стандартный/распределённый:

Рисунок 4 – ЕИП НСУД. Добавление нового Регламентированного SQL запроса

  4.   В открывшейся карточке для создания нового Регламентированного SQL запросы необходимо заполнить Общие сведения о РЗ:

Рисунок 5 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Общие сведения

Общие сведения, подлежащие обязательному заполнению:

• Владелец – необходимо выбрать из перечня ведомство-владельца, создаваемого РЗ;
• Мнемоника – техническое наименование создаваемого РЗ;
• Дата начала действия РЗ – указать дату, с которой РЗ будет доступен;
• Описание – добавить краткое описание РЗ.

  5.   Далее необходимо пролистать ниже и заполнить окна с Характеристиками и Данными по запросу: 

Рисунок 6 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Характеристики и данные запроса

Характеристики запроса:

• Оптимизация – поставить галочку для автоматической оптимизации запроса.

Данные запроса:

• Поставщик данных – выбрать из списка ведомства-поставщиков данных для каждой витрины, участвующей в распределённом РЗ.

  6.   Далее необходимо пролистать ниже до SQL конструктора (деревья атрибутов витрин организаций – поставщиков данных). В дереве атрибутов необходимо раскрыть состав атрибутов необходимых Витрин данных, и выбрать галочками атрибуты таблиц, данные которых будут собираться с помощью распределённого РЗ:

Рисунок 7 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Дерево атрибутов

  7.   При необходимости можно добавить входные параметры в запрос. Для этого в блоке Входные параметры необходимо нажать на Добавить:

Рисунок 8 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Входные параметры

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

Рисунок 9 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Входные параметры

Если требуется создать несколько входных параметров для РЗ, то процедуру необходимо повторить, пока не будут добавлены все необходимые входные параметры.

  8.   Далее необходимо пролистать ещё ниже до добавления параметров запроса. В распределённом РЗ необходимо заполнить алгоритм выполнения запроса – JOIN, в соответствии с режимами работы данного алгоритма:

Рисунок 10 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Выбор алгоритма выполнения запроса

В открывшимся окне необходимо задать необходимый вид соединения алгоритма JOIN:

Рисунок 11 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Выбор алгоритма выполнения запроса

Ниже необходимо задать таблицу, с которой осуществляется соединение и атрибуты, участвующие в соединении, и нажать на «Сохранить»:

Рисунок 12 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Выбор таблицы и атрибутов, участвующих в соединении

  9.   Ниже представлен SQL-запрос, сформированный автоматически. В примере было перечисление 2ух витрин gorodarf и regionsrf. При необходимости можно поправить запрос в *Итоговом SQL запросе:

Рисунок 13 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Формирование SQL запроса

  10.   Пролистав ниже, в окне Дополнительная информация необходимо задать настройку Ограничение на использование запроса для распределённого РЗ. Данная настройка задает Последовательность выполнения запроса от витрины к витрине. В примере ниже используется Фиксированная последовательность:

Рисунок 14 – ЕИП НСУД. Окно создания нового Регламентированного SQL запроса. Дополнительная информация (настройка для распределённых РЗ)

Существуют 3 варианта последовательностей исполнения запроса:

• Фиксированная последовательность – последовательность выполнения запроса задаётся вручную;
• Параллельное исполнение – исполнение без запроса оценки по подзапросам у поставщиков;
• Динамическое исполнение – определяется динамически в процессе исполнения запроса (по статистике с витрин или запросам оценок).

После внесения всех настроек необходимо нажать на «Сохранить».

  11.   При необходимости использования табличных параметров в РЗ, можно добавить их после создания РЗ:

          1)     В карточке распределённого РЗ необходимо перейти на вкладку Табличные параметры:

Рисунок 15 – ЕИП НСУД. Карточка Регламентированного SQL запроса. Табличные параметры

          2)     В открывшемся списке табличных параметров необходимо нажать на Добавить параметр:

Рисунок 16 – ЕИП НСУД. Карточка Регламентированного SQL запроса. Добавление табличного параметра

          3)     Заполните открывшуюся карточку нового табличного параметра и нажмите кнопку «Сохранить»:

Рисунок 17 – ЕИП НСУД. Карточка Регламентированного SQL запроса. Новый табличный параметр

Для вызова табличных параметров в РЗ воспользуйтесь описанием, приведённым в документе Методические рекомендации по работе с СМЭВ4, размещённом на портале ЕСКС в Документах Межведомственного электронного взаимодействия в разделе СМЭВ 4 (п.п 2.3.1.3 Выполнение запроса с табличным параметром).

Добавление связи витрин с информационными системами

Перед отправкой распределённого РЗ в тестовый и продуктивный контур СМЭВ4, необходимо убедиться, что Витрины данных привязаны к своим Информационным системам (ИС) в ЛК УВ. Если связь с витриной отсутствует, необходимо добавить связь информационной системе, как показано на рисунке ниже:

Рисунок 18 – ЛК УВ. Карточка Информационной системы. Добавление связи с Витриной данных

Отправка распределённого РЗ в ядро СМЭВ4 в ЕИП НСУД

Для того, чтобы отправить распределённый РЗ в ядро СМЭВ4, необходимо:

  1.   В ЕИП НСУД перейти к Регламентированными запросами SQL, в поиске найти ранее созданный распределённый РЗ и нажать на него. 

  2.   В карточке распределённого РЗ на правой панели Взаимодействия с ПОДД СМЭВ необходимо выбрать среду (в примере указана тестовая среда) и нажать на «Отправить» (Рисунок 19).

  Примечание

 Перед отправкой РЗ в продуктивную среду необходимо сначала пройти этап согласования РЗ на вкладке Согласование, и лишь после согласования отправить РЗ в продуктивную среду СМЭВ4

Процесс отправки запроса на согласование РЗ и просмотр статусов его обработки описаны в Инструкции по работе в ЕИП НСУД, расположенной на портале ЕСКС в Документах Межведомственного электронного взаимодействия в разделе ЕИП НСУД (п.п 4.14.3 Обработка и согласование заявки на создание РЗ).

  Важно!

 При использовании в распределенном РЗ "чужих" витрин, будет создан дополнительный лист согласования с поставщиками данных этих витрин (в случае отклонения РЗ со стороны ведомств-поставщиков данных витрин, РЗ не будет согласован)

Рисунок 19 – ЕИП НСУД. Карточка распределённого РЗ. Отправка РЗ в тестовый ПОДД СМЭВ

  3.   После отправки РЗ в ядро СМЭВ4 необходимо обновить страницу клавишей F5 до обновления статуса отправки запроса.

Статус отправки запроса - Отклонен

При возникновении ошибок при отправке запроса статус отправки поменяется на Отклонен (Рисунок 20):

Рисунок 20 – ЕИП НСУД. Карточка распределённого РЗ. Статус отправки РЗ в тестовый ПОДД СМЭВ

В данном случае необходимо разобраться в причинах. Для этого потребуется скачать JSON файл с ответом (Рисунок 21):

Рисунок 21 – ЕИП НСУД. Карточка распределённого РЗ. Скачать ответ проверки отправки РЗ в ядро СМЭВ4

Зачастую в ответном JSON можно отследить причину ошибки. В режиме редактирования РЗ необходимо внести соответствующие правки и переотправить запрос на регистрацию РЗ в ядро СМЭВ4.

Для детального разбора проблем следует составить заявку в СЦ, указать мнемонику РЗ, приложить к заявке состав ответа JSON и назначить на команду ЕИП НСУД. 

Получение потребителем доступа к распределённому РЗ

Доступ к РЗ предоставляется в ЛК УВ (один из способов получения доступа описан ниже):

  1.   Для этого Потребителю данных необходимо авторизоваться в ЛК УВ. На главной странице нужно перейти на вкладку СМЭВ4, в подразделе Все запросы (SQL-запрос) необходимо нажать на «Выбрать»:

Рисунок 22 – ЛК УВ. Раздел СМЭВ4

  2.   В открывшемся окне нужно ввести мнемонику ранее созданного распределённого РЗ для того, чтобы найти его из перечня доступных РЗ. Далее в информационном окне найденного РЗ необходимо нажать на Ещё действия и в выпадающем списке выбрать Получить доступ:

Рисунок 23 – ЛК УВ. Подраздел Все запросы (SQL-запрос)

  3.   В предложенном перечне выбрать среду (в примере используется тестовая среда):

Рисунок 24 – ЛК УВ. Получение доступа к РЗ. Выбор среды

  4.   Далее необходимо выбрать ИС, для которой необходим доступ:

Рисунок 25 – ЛК УВ. Получение доступа к РЗ. Выбор системы

  5.   Следом вписать мнемонику распределённого РЗ и проставить галочку. После чего нажать на «Продолжить»:

Рисунок 26 – ЛК УВ. Получение доступа к РЗ. Выбор РЗ

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

Рисунок 27 – ЛК УВ. Получение доступа к РЗ

Описание способов получения доступа к РЗ в ЛК УВ описано в Руководстве пользователя ЛК УВ на портале ЕСКС в разделе Документы Межведомственного электронного взаимодействия в разделе ЕИП НСУД (см. п.п.5.7.5 Получение доступов к регламентированным запросам типа SQL-запрос).

Установка компонентов в Витринах данных, участвующих в распределённом РЗ

Для того, чтобы Витрина данных могла уметь работать с распределёнными РЗ, необходимо наличие следующих компонентов в её составе:

• podd-adapter-mppr – логический модуль, который предназначен для загрузки данных в многопоточном режиме (в том числе и для табличных параметров);
• podd-adapter-mppw – логический модуль, который исполняет запросы, записывающие большой объём данных в Prostore;
• podd-avro-defragmentator – преобразует пакеты с данными табличных параметров, поступающие от Агента СМЭВ4 (в брокер сообщений Kafka), к формату, позволяющему обрабатывать их в многопоточном режиме;
• podd-adapter-group-tp – модуль группировки данных табличных параметров;
• podd-adapter-import-tp – модуль импорта данных табличных параметров.

Компоненты podd-adapter-mppr и podd-adapter-mppw входят в число минимально необходимого (основного) набора компонентов Витрины данных. Специальные компоненты для работы с табличными параметрами входят в число дополнительных компонентов Витрины.

  Примечание

 В Витрине данных конфигурации установки Лайт указанные компоненты уже предустановлены

Установка компонентов podd-adapter-mppr и podd-adapter-mppw для Витрины данных конфигурации «Стандарт» описана в статье – Установка витрины в конфигурации стандарт. Минимальный набор.

Установка компонентов для работы с табличными параметрами для Витрины данных конфигурации «Стандарт» описана в статье – Установка витрины в конфигурации стандарт. Дополнительные компоненты.

Проверка работы РЗ

Проверить работу распределённого РЗ можно направив запрос с Агента-Потребителя.

Пример запроса:

curl -X POST -H "Accept-Version:1" -H "Content-Type: application/json" -d '{"sql": {"sql": "Select * from podd.версия_РЗ.мнемоника_растпределённого_РЗ("параметры")"}}' http://localhost:8192/query --silent -m 30

Рисунок 28 – Результат выполнения распределённого РЗ

Частая причина отсутствия ответа на распределённый РЗ

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

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

Примеры ошибок при вызове распределённого РЗ:

• Ошибка: "PERMISSION_DENIED: AuthorizeRequestException: Доступ запрещён":

Рисунок 29 – Ошибка при вызове распределённого РЗ: Доступ запрещён

Решение: получить доступ к распределённому РЗ ИС-Потребителю в ЛК УВ.

• Ошибка: "UNSUPPORTED_VERSION_OF_METADATA: RegulatedQueryVersionNotFoundException: Не найден регламентированный запрос podd.1.1.search_by_code_regio()"

Рисунок 30 – Ошибка при вызове распределённого РЗ: Не найден регламентированный запрос

Решение: убедиться в правильности составления РЗ, сверить в данными РЗ в ЕИП НСУД. Также данная ошибка может возникать, если распределённый РЗ не был отправлен в ПОДД СМЭВ.

• Ошибка: "VALIDATION: SqlExtensionNotAllowedException: Использование запросов с надстройками запрещено"

Рисунок 31 – Ошибка при вызове распределённого РЗ: Использование запросов с надстройками запрещено

Решение: при вызове распределённого РЗ запрещается использовать надстройки (например, limit).

В случае, если ошибка не попадает в перечень перечисленных выше категорий, рекомендуется проанализировать ещё, воспользовавшись статьёй – Как найти причину ошибки в СМЭВ4.