3. Использование ПОДД СМЭВ

3.1. SQL-синтаксис

Регламентированные SQL-запросы, регистрируемые в ПОДД СМЭВ должны соответствовать следующему синтаксису.

Таблица 3.3 SQL-синтаксис

№

Описание

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

Числовые типы данных

1

Типы данных INTEGER и SMALLINT

SELECT CAST(1 AS INT)

2

Типы данных REAL, DOUBLE PRECISION и FLOAT

SELECT CAST(1 AS FLOAT)

3

Типы данных DECIMAL и NUMERIC

SELECT CAST(1 AS NUMERIC)

4

Арифметические операторы

SELECT 10+1, 9-2, 8*3, 7/2

5

Числовые сравнения

SELECT 1 WHERE 1 < 2

6

Неявные преобразования между числовыми типами данных

SELECT int_column FROM t WHERE int_column = 1.00

Символьные типы данных

7

Тип данных CHARACTER

Длина по умолчанию 30

SELECT CAST(‘1111111111111111111111111111111111111111111’ AS CHAR)

8

Тип данных CHARACTER VARYING

Длина по умолчанию 30

SELECT CAST(‘1111111111111111111111111111111111111111111’ AS VARCHAR)

9

Символьные строки

SELECT ''

10

Функция CHARACTER_LENGTH

убирает завершающие пробелы из значений CHARACTER перед подсчётом символов

SELECT character_length(char_column) FROM t

11

Функция OCTET_LENGTH

SELECT octet_length(char_column) FROM t

12

Функция SUBSTRING

SELECT substring(char_column FROM 1 FOR 1) FROM t

13

Конкатенация символьных строк

SELECT ‘a’ || ‘b’ FROM t

14

Функции UPPER и LOWER

SELECT upper(‘a’),lower(‘B’) FROM t

SELECT int_column FROM t WHERE int_column > (SELECT DISTINCT (int_column) FROM t)

15

Функция TRIM

SELECT trim(‘a ‘) FROM t

16

Неявные преобразования между типами символьных строк

SELECT char_column FROM t WHERE char_column > varchar_column

17

Функция POSITION

SELECT position(‘A’ IN char_column) FROM t

18

Сравнения символов

SELECT char_column FROM t WHERE char_column > ‘a’

Идентификаторы

19

Идентификаторы с разделителями

SELECT 1 AS «t47»

20

Идентификаторы в нижнем регистре

SELECT 1 AS t48

21

Завершающее подчёркивание

SELECT 1 AS t49_

Базовое определение запросов

22

SELECT DISTINCT

SELECT DISTINCT int_column FROM t

23

Предложение GROUP BY

SELECT DISTINCT int_column FROM t GROUP BY int_column

24

GROUP BY может содержать колонки не из <списка выборки>

SELECT DISTINCT char_column FROM t GROUP BY lower(char_column)

25

Элементы списка выборки могут переименовываться

SELECT int_column AS K FROM t ORDER BY K

26

Предложение HAVING

SELECT count(*) FROM t HAVING count(*) > 0

27

Корреляционные имена в предложении FROM

SELECT K.column FROM t AS K

28

Переименование колонок в предложении FROM

SELECT column FROM t AS x(q, c)

Базовые предикаты и условия поиска

29

Предикат сравнения

SELECT column FROM t WHERE 0 = 0

30

Предикат BETWEEN

SELECT column FROM t WHERE ‘ ‘ BETWEEN ‘’ AND ‘’

31

Предикат IN со списком значений

SELECT column FROM t WHERE char_column IN (‘a’, upper(‘a’))

32

Предикат LIKE

SELECT column FROM t WHERE char_column LIKE ‘_’

33

Предложение ESCAPE в предикате LIKE

SELECT column FROM t WHERE ‘abc’ LIKE ‘abcX_’ ESCAPE ‘X’

34

Предикат NULL

SELECT column FROM t WHERE char_column IS NOT NULL

35

Предикаты количественного сравнения

SELECT column FROM t WHERE char_column = ANY (SELECT char_column FROM t)

36

Предикат EXISTS

SELECT column FROM t WHERE NOT EXISTS (SELECT char_column FROM t)

37

Подзапросы в предикате сравнения

SELECT column FROM t WHERE int_column > (SELECT max (int_column) FROM t)

38

Подзапросы в предикате IN

SELECT column FROM t WHERE char_column IN (SELECT char_column FROM t)

39

Подзапросы в предикате количественного сравнения

SELECT column FROM t WHERE char_column >= ALL(SELECT char_column FROM t)

40

Коррелирующие подзапросы

SELECT column FROM t WHERE int_column = (SELECT int_column FROM t2 WHERE t2.char_ column = t.char_column)

41

Условие поиска

SELECT column FROM t WHERE 0 <> 0 OR ‘a’ < ‘b’ AND int_column IS NOT NULL

Простые выражения с запросами

42

Табличный оператор UNION DISTINCT

SELECT column FROM t UNION DISTINCT SELECT column1 FROM t

43

Табличный оператор UNION ALL

SELECT column FROM t UNION ALL SELECT column1 FROM t

44

Табличный оператор EXCEPT DISTINCT

SELECT column FROM t EXCEPT DISTINCT SELECT column1 FROM t

45

Колонки, объединяемые табличными операторами, могут иметь разные типы данных

SELECT char_column FROM t UNION SELECT 5

46

Табличные операторы в подзапросах

SELECT column FROM t WHERE ‘a’ IN (SELECT char_column FROM t UNION SELECT char_column FROM t)

Функции множеств

47

AVG

SELECT avg(int_column) FROM t

48

COUNT

SELECT count(int_column) FROM t

49

MAX

SELECT max(int_column) FROM t

50

MIN

SELECT min(int_column) FROM t

51

SUM

SELECT sum(int_column) FROM t

52

Дополнение ALL

SELECT sum(ALL int_column) FROM t

53

Дополнение DISTINCT

SELECT sum(DISTINCT int_column) FROM t

54

Оператор SELECT, возвращающий одну строку

SELECT count(*) FROM t

Базовая поддержка курсоров

55

Колонки ORDER BY, отсутствующие в списке выборки

SELECT int_column FROM t ORDER BY char_column

56

Выражения значений в предложении ORDER BY

SELECT int_column FROM t ORDER BY -int_column

57

Поддержка NULL (NULL вместо значений)

SELECT int_column FROM t WHERE int_column IS NULL

Базовое соединение таблиц

58

Внутреннее соединение (но не обязательно с ключевым словом INNER)

SELECT a.int_column FROM t a JOIN t b ON a.int_column = b.int_column

59

Ключевое слово INNER

SELECT a.int_column FROM t a JOIN t b ON a.int_column = b.int_column

60

LEFT OUTER JOIN

SELECT a.int_column, b.int_column FROM t a LEFT OUTER JOIN t b ON a.int_column = b.int_column

61

RIGHT OUTER JOIN

SELECT a.int_column, b.int_column FROM t a RIGHT OUTER JOIN t b ON a.int_column = b.int_column

62

Внешние соединения могут быть вложенными

SELECT a.int_column FROM t a LEFT OUTER JOIN t b ON a.int_column = b.int_column LEFT OUTER JOIN t2 c ON a.int_column = c.int_column

63

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

SELECT t2.int_column FROM (t2 LEFT OUTER JOIN t ON t.int_column = t2.int_column) j INNER JOIN t2 ON j.int_column = t2.int_column

64

Поддерживаются все операторы сравнения (а не только =)

SELECT column FROM t WHERE 0 = 1 OR 0 > 1 OR 0 < 1 OR 0 <> 1

Базовая поддержка даты и времени

65

Тип данных DATE (включая поддержку строк DATE)

SELECT ‘2012-07-12’ AS "DATE"

66

Тип данных TIME (включая поддержку строк TIME) с точностью до секунд как минимум с 0 знаков после запятой

SELECT ‘1:2:3’ AS "TIME"

67

Тип данных TIMESTAMP (включая поддержку строк TIMESTAMP)

с точностью до секунд как минимум с 0 и 6 знаками после запятой

SELECT ‘2012-07-12’ AS "TIMESTAMP"

68

Предикаты сравнения с типами данных DATE, TIME и TIMESTAMP

SELECT column FROM t3 WHERE date_column = date_column AND time_column = time_column AND timestamp_column = timestamp_column

69

Явное приведение (CAST) между типами даты/времени и типами символьных строк

SELECT cast(date_column AS VARCHAR(10)) FROM t3

70

CURRENT_DATE

SELECT current_date FROM t

71

LOCALTIME

SELECT localtime FROM t

72

LOCALTIMESTAMP

SELECT localtimestamp FROM t

Расширенная поддержка даты и времени

73

FOR SYSTEM_TIME (запрос данных, актуальных на указанную дату и время)

SELECT column FROM t FOR SYSTEM_TIME AS OF 'YYYY-MM-DD hh:mm:ss'

SELECT column FROM t FOR SYSTEM_TIME AS OF TIMESTAMP 'YYYY-MM-DD hh:mm:ss'

74

Указание «TIMESTAMP» опционально Вычисление интервала

(c указанием единиц времени: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)

select ('YYYY-MM-DD hh:mm:ss' - 'YYYY-MM-DD hh:mm:ss') MONTH

75

Функция CAST

SELECT cast(int_column AS INT) FROM t

Выражение CASE

76

Простой оператор CASE

SELECT CASE WHEN 1 = 0 THEN 5 ELSE 7 END FROM t

77

Оператор CASE с условиями

SELECT CASE 1 WHEN 0 THEN 5 ELSE 7 END FROM t

78

NULLIF

SELECT nullif(int_column, 7) FROM t

79

COALESCE

SELECT coalesce(int_column,7) FROM t

80

Длинные идентификаторы

SELECT 1 AS A12345678901234567890123456789

81

Спецсимволы Unicode в идентификаторах

SELECT 1 AS Я12345678901234567890123456789

82

Спецсимволы Unicode в текстовых строках

SELECT U&'\6553'

83

Национальные символы

SELECT 'Я'

84

Скалярные значения подзапросов

SELECT int_column  FROM t WHERE int_column = (SELECT count(*) FROM t)

85

Расширенный предикат NULL

SELECT column FROM t WHERE row(int_column, int_column) IS NOT NULL

3.2. Примеры запросов с использованием SQL-синтаксиса, поддерживаемого ПОДД СМЭВ

Далее приведены примеры SQL-выражений Регламентированных SQL-запросов.

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

3.2.1. Запрос с использованием табличных выражений

with cte as (
select shortname, regioncode, oktmo from fias.addrobj
where formalname = ‘Москва’
)
select * from cte

Запросы с использованием базовых операторов

  1. Запрос с использованием EXCEPT:

select oktmo from fias.addrobj
EXCEPT
select kod2 from oktmo.oktmo

  1. Запрос с использованием INTERSECT:

select oktmo from fias.addrobj
INTERSECT
select kod2 from oktmo.oktmo

  1. Запросы с использованием UNION:

select oktmo from fias.addrobj
UNION
select kod2 from oktmo.oktmo

  1. Запросы с использованием ORDER BY:

select shortname, formalname from fias.addrobj
where aolevel = 1
order by formalname

select shortname, formalname from fias.addrobj
where aolevel = 1
order by formalname desc

  1. Запрос использованием GROUP BY, HAVING:

select ao.regioncode, count(*) N from fias.addrobj ao
left join oktmo.oktmo o on o.kod2 = ao.oktmo
where o.kod2 is null
group by ao.regioncode
having COUNT(*) > 3000
order by N desc

3.2.2. Указание в запросе версии Витрины

select * from egrul.1.0.legalentity
select * from egrul.2.0.legalentity

Результат может содержать разное количество возвращаемых атрибутов в соответствии со структурой таблицы «legalentity» Витрины «egrul» версий 1.0 и 2.0.

3.2.3. Запросы с использованием JOIN

select le.short_name, rc.region_full_name from egrul.legalentity le
join egrul.region_codes rc on le.region_code = rc.region_code

SELECT ao.oktmo, o.name, o.kod
from fias.addrobj ao
JOIN oktmo.oktmo o on ao.oktmo = o.kod2
WHERE ao.offname = ‘Москва’ AND o.regionname = ‘город Москва’

select le.short_name, le.region_code, o.regionid, o.name, o.regionname from egrul.legalentity le
left join oktmo.oktmo o on cast(o.regionid as varchar) = le.region_code
where o.kod2 is null

select le.short_name, le.region_code, o.regionid, o.name, o.regionname from egrul.legalentity le
full join oktmo.oktmo o on cast(o.regionid as varchar) = le.region_code
where le.ogrn is null or o.kod2 is null

select le.short_name, le.region_code, o.regionid, o.name, o.regionname from egrul.legalentity le
right join oktmo.oktmo o on cast(o.regionid as varchar) = le.region_code
where le.ogrn is null

select le.short_name, rc.region_full_name from egrul.legalentity le
cross join egrul.region_codes rc
where rc.region_code = le.region_code

3.3. Выполнение регламентированных SQL-запросов

3.3.1. Правила выполнения

Вызов Регламентированного SQL-запроса может быть выполнен с использованием:

Возможность указания надстроек при вызове регламентированного SQL-запроса ограничена и в будущем будет отключена. Поэтому рекомендуется сразу использовать методы упрощенного вызова:

  • через REST-интерфейс в соответствии c Раздел 2.3.1.1.1;

  • через JDBC-интерфейс:

CALL <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(<параметры>)

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

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

select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(<параметры>) <надстройки (опционально)>

где:

  • <мнемоника Витрины> – для простых запросов (к одной Витрине) задается мнемоника соответствующей Витрины. Для распределенного запроса вместо мнемоники Витрины указывается префикс «podd»;

  • <версия Регламентированного SQL-запроса> – задается в формате «major.minor»;

  • <мнемоника Регламентированного SQL-запроса> – мнемоника вызываемого Регламентированного SQL-запроса;

  • <параметры> – указываются в скобках через запятую для Регламентированного SQL-запроса с параметрами, заданными в определении.

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

  • <надстройки (опционально)> – дополнительные условия фильтрации и операций над получаемыми данными ()например, order by, limit, where и т.д.)

Внимание

если запрос не является аналитическим и необходимо получение результата выполнения в реальном времени (запросы граждан на портале ЕПГУ, предоставление услуг в МФЦ и т.п.), то обязательно указание лимита (limit) с минимально возможным значением (максимально допустимое значение зависит от конфигурации и настроек Витрины Поставщика, по умолчанию равно 1000):

POST «https://<host>:<port>/query?async=false»

Accept-Version:1
Content-Type:application/x-www-form-urlencoded; encoding=utf-8

priority:NORMAL
timeout:60
sql: select * from fias.1.0.addrobj_view('001') limit 10

Правила указания параметров:

  1. Если параметры заданы в SQL-выражении Регламентированного SQL-запроса в виде «?», то при вызове они должны быть указаны в порядке, соответствующем определению Регламентированного SQL-запроса. Количество параметров при вызове должно соответствовать количеству параметров в определении Регламентированного SQL-запроса.

  2. Для запроса, в SQL-выражении которого параметры заданы как именованные, доступен:

    1. Вызов с использованием мнемоники именованного параметра.

      Для этого необходимо задать значения всех именованных параметров, для которых не задано значение по умолчанию, в виде «<мнемоника параметра>=><значение параметра>» (Раздел 3.3.2).

      Мнемоника именованного параметра при вызове запроса должна соответствовать мнемонике именованного параметра в определении Регламентированного SQL-запроса.

      Использование именованных параметров позволяет при вызове задавать значения параметров в любом порядке и только один раз, независимо от количества мест, где этот параметр используется в определении Регламентированного SQL-запроса.

    2. Вызов без использования мнемоники именованного параметра.

      Для этого необходимо задать значения всех именованных параметров в порядке, соответствующем определению Регламентированного SQL-запроса.

  3. Совместное использование именованных и неименованных параметров не допускается.

3.3.2. Примеры вызова и преобразования исходного запроса

  1. Регламентированный SQL-запрос с параметрами без имени:

// Исходный запрос от Потребителя данных ПОДД СМЭВ:
select * from oktmo.1.0.oktmo_view(‘Московская область’,7)

// Определение Регламентированного SQL-запроса
select id, whenadd, name, regionname, settlementtypename FROM oktmo.1.0.oktmo where regionname = ? AND settlementtypeid = ?

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
select id, whenadd, name, regionname, settlementtypename FROM oktmo.1.0.oktmo where regionname = ‘Московская область’ AND settlementtypeid = 7

  1. Регламентированный SQL-запрос с именованными параметрами:

// Исходный запрос от Потребителя данных ПОДД СМЭВ (вызов через мнемонику параметра)
select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(param=>'000', lim=>20)

// Исходный запрос от Потребителя данных ПОДД СМЭВ
select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>('000',20)

// Определение Регламентированного SQL-запроса
SELECT name, code FROM datamart.1.0.regions WHERE code=:param and id in (select id from :param) limit :lim

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
SELECT name, code FROM datamart.1.0.regions WHERE code='000' and id in (select id from '000') limit 20

  1. Регламентированный SQL-запрос с параметром типа TIMESTAMP:

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к Pulsar
select * from fias.1.0.addrobj_view('001', '000', TIMESTAMP '1970-01-01 00:00:00')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру
CALL fias.1.0.addrobj_view('001', '000', TIMESTAMP '1970-01-01 00:00:00')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру с отдельным блоком параметров
datamart: fias
mnemonic: addrobj_view
majorVersion: 1
minorVersion: 0
params:{ “type”: “STRING”, “value”:”001”},{ “type”: “STRING”, “value”:“002”},{ “type”: “TIMESTAMP”, “value”:“2007-12-03Т10:00:00.000Z”}

// Определение РЗ
SELECT oktmo, formalname, startdate, enddate FROM datamart.9.1.addrobj WHERE areacode=? and citycode=? and startdate<=?

  1. Регламентированный SQL-запрос с параметром типа DATE:

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к Pulsar
select * from fias.1.0.addrobj_view('001', '000', '1970-01-01')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру
CALL fias.1.0.addrobj_view('001', '000', '1970-01-01')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру с отдельным блоком параметров
datamart: fias
mnemonic: addrobj_view
majorVersion: 1
minorVersion: 0
params:{ “type”: “STRING”, “value”:”001”},{ “type”: “STRING”, “value”:“002”},{ “type”: “DATE”, “value”:“2007-12-03”}

// Определение РЗ
SELECT oktmo, formalname, startdate, enddate FROM datamart.9.1.addrobj WHERE areacode=? and citycode=? and startdate>=?

  1. Регламентированный SQL-запрос без параметров:

// Исходный запрос от Потребителя данных ПОДД СМЭВ:
select * from egrul.1.1.legalentity_view()

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
select ogrn, short_name, inn, kpp, region_code from egrul.2.legalentity

  1. Распределенный Регламентированный SQL-запрос:

// Исходный запрос от Потребителя данных ПОДД СМЭВ:
select * from podd.1.1.r_query(‘Москва’, 18)

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
select ao.oktmo, o.name, o.kod from fias.1.0.addrobj ao LEFT JOIN oktmo.1.0.oktmo o on ao.oktmo = o.kod2 WHERE ao.offname= ‘Москва’ AND o.regionid = 18

3.3.3. Выполнение запроса с системным параметром

Системные параметры не описываются в определении Регламентированного SQL-запроса и могут быть заданы Потребителем для любого Регламентированного SQL-запроса при выполнении запроса.

Таблица 3.4 содержит поддерживаемые в ПОДД СМЭВ параметры.

Таблица 3.4 Поддерживаемые в ПОДД СМЭВ параметры

№

Мнемоника

Назначение

1

settings_for_system_time

Системный параметр для получения актуальных на заданный момент данных.

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

2

settings_for_system_time_started

Системный параметр для получения результата выполнения запроса с данными, добавленными за указанный диапазон времени.

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

3

settings_for_system_time_finished

Системный параметр для получения результата выполнения запроса с данными, удаленными за указанный диапазон времени.

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

Вызов Регламентированного SQL-запроса с системным параметром осуществляется аналогично вызову Регламентированного SQL-запроса с заданием значения для именованного параметра, в соответствии со следующими правилами:

  1. Параметр задается в запросе только один раз в виде именованного параметра с соответствующей мнемоникой.

  2. Значение параметра settings_for_system_time должно соответствовать формату YYYY-MM-DD hh:mm:ss.

  3. Значение параметров settings_for_system_time_started и settings_for_system_time_finished должно соответствовать формату (int1, int2). Значение int1 должно быть меньше, чем int2.

  4. Задание параметра недоступно при вызове Регламентированного SQL-запроса без SQL-выражения.

  5. Для простого запроса: для Витрины, к которой обращается Регламентированный SQL-запрос с данным параметром, должна быть задана настройка поддержки обработки данного параметра в соответствии с «Руководством администратора Агента СМЭВ4». [28]

  6. Для распределенного запроса: для всех Витрин, к которым обращается Регламентированный SQL-запрос с данным параметром, должна быть задана настройка поддержки обработки данного параметра в соответствии с «Руководством администратора Агента СМЭВ4».

  7. В подзапросе в блоке FROM должна отсутствовать конструкция «FOR SYSTEM_TIME».

Пример вызова с параметром settings_for_system_time:

// вызываемый SQL-запрос (вариант 1)
select * from fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time=>'YYYY-MM-DD hh:mm:ss')

// вызываемый SQL-запрос (вариант 2)
select * from fias.1.0.addrobj_func(settings_for_system_time=>?, p1=>?, p2=>?)
params: {"type": "TIMESTAMP", "value": "YYYY-MM-DD hh:mm:ss"},{"type": "STRING", "value": "001"},{"type": "STRING", "value": "002"}

// SQL-выражение Регламентированного SQL-запроса
SELECT <атрибуты> FROM <витрина>.<таблица> WHERE id=:p1 AND region=:p2

Пример вызова с параметрами settings_for_system_time_started и settings_for_system_time_finished:

// вызываемый SQL-запрос (вариант 1)
select * from fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time_started=>'122,124')
select * from fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time_finished=>'122,124')

// вызываемый SQL-запрос без настроек через JDBC-интерфейс
call fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time_started=>'122,124')

// вызываемый SQL-запрос с параметрами, передаваемыми отдельным блоком
select * from fias.1.0.addrobj_func(settings_for_system_time_finished=>?, p1=>?, p2=>?)
params: {"type": "STRING", "value": "122,124"},{"type": "STRING", "value": "001"},{"type": "STRING", "value": "002"}

// SQL-выражение РЗ
SELECT <атрибуты> FROM <витрина>.<таблица> WHERE id=:p1 AND region=:p2

3.3.4. Выполнение запросов с использованием табличных параметров, передаваемых Потребителем данных для обогащения

Выполнение запроса, включающего табличный параметр, осуществляется аналогично выполнению любого запроса к Витрине Поставщика данных. Описание синтаксиса выполнения запроса приведено в Раздел 3.1.

Для выполнения запроса с табличным параметром необходимо:

  1. Определить структуру таблицы, указанной в табличном параметре.

  2. Указать запрос, содержащий табличный параметр.

  3. Указать источник данных для табличного параметра.

Эти операции могут быть выполнены с использованием REST или JDBC-интерфейса.

3.3.4.1. Запрос с использованием REST-интерфейса

При использовании REST-интерфейса данные собираются в CSV файл, который прикрепляется к запросу, содержащему информацию о структуре таблицы и SQL-выражение. Формат запроса приведен в Раздел 2.3.1.2.

В теле запроса передаются следующие параметры:

sql:SELECT el.inn, er.region_name FROM @inns el LEFT JOIN egrul.2.region_codes er ON SUBSTRING(el.inn,1,2) = er.region_code
priority:NORMAL
tableParams:{“name”: “inns”, “columns”:[{“name”: “id”, “type”: “INTEGER”},{“name”: “inn”, “type”: “STRING”}]}
inns:<l.csv>

где:

  1. sql – текст для вызова Регламентированного SQL-запроса, содержащего табличные параметры;

  2. tableParams – описание передаваемого файла с данными для табличного параметра, где:

    • name – табличный параметр;

    • columns – перечень названий столбцов и их типов, содержащихся в файле с данными для табличного параметра.

  3. inns – файл с данными для табличного параметра, где:

    • inns – табличный параметр (выступает в качестве названия параметра запроса);

    • <1.csv> – файл в формате CSV (поддерживаемый формат), передаваемый в параметре запроса.

Пример CSV файла (разделитель – запятая):

1, 4345310593
2, 4311003795
3, 4345336320

3.3.4.2. Запрос с использованием JDBC-интерфейса

При использовании JDBC-интерфейса для описание передаваемой в качестве параметра таблицы и добавления данных расширяются функции драйвера JDBC. Для доступа к данным формируется итератор.

После передачи данных выполняется запрос, включающий табличный параметр в формате @имя_параметра.

Пример использования представлен в Раздел 2.3.3.1.

3.3.5. Получение двоичных объектов в результатах запроса

ПОДД СМЭВ предоставляет возможность получения в качестве результата выполнения Регламентированных SQL-запросов:

  • скалярных параметров (значение которых представляет собой число, строку или дату);

  • двоичных объектов (соответствующих файлам, размещенным на Витринах Потребителей данных).

Для получения в качестве ответа двоичных объектов необходимо выполнение следующих условий:

  • Витрина Поставщика данных поддерживает тип данных «двоичный объект»;

  • в метаданных Витрины Поставщика данных, зарегистрированной в ПОДД СМЭВ, для соответствующих атрибутов должен быть установлен тип данных «двоичный объект» (описание типов данных ПОДД СМЭВ приведено в Раздел 1.5.5).

Процесс выполнения такого SQL-запроса приведён на рисунке 14, описание процесса приведено далее.

  1. ИС Потребителя передаёт SQL-запрос в Агент СМЭВ4.

  2. Далее приём, обработка и передача запроса:

    Агент Потребителя данных → Ядро ПОДД СМЭВ → Агент Поставщика данных → Витрина Поставщика данных осуществляется аналогично обычному информационному обмену с использованием Регламентированных SQL-запросов (Раздел 1.4.1).

  3. Витрина данных осуществляет обработку запроса и возвращает ответ.

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

    • непосредственно сам двоичный объект;

    • уникальная ссылка на получение двоичного объекта с Витрины Поставщика данных (см. шаги Рисунок - 3.1).

    Решение о том какое значение будет передано (объект или ссылка на него принимается Витриной данных).

  4. Далее ответ доставляется до ИС Потребителя аналогично обычному обмену (Раздел 1.4.1).

    Пример возврата двоичного объекта и ссылки на двоичный объект приведен в Раздел 2.3.1.1.3.

  5. Если в результате выполнения запроса передана ссылка, то для получения двоичного объекта ИС Потребителя направляет запрос на получение вложения по ссылке, которую получил в ответе.

    Запрос двоичного объекта по ссылке считается отдельным обменом, идентификатор обмена (requestId) запроса по ссылке уникальный (в случае нескольких ссылок - уникальный для каждой из ссылок).

    Описание запроса на получение двоичного объекта по ссылке:

    • через REST-интерфейс приведено в Раздел 2.3.1.3;

    • через JDBC-интерфейс приведено далее.

  6. Далее приём, обработка и передача запроса Агент Потребителя данных → Ядро ПОДД СМЭВ → Агент Поставщика данных → Витрина Поставщика данных осуществляется аналогично обычному обмену (Раздел 1.4.1). Для передачи запроса двоичного объекта на Витрину Поставщика используется специальный топик blob.rq.

  7. В случае, если в теле запроса содержится ссылка на двоичный объект:

    • Адаптер PODD Витрины данных отправляет запрос в BLOB-адаптер на получение этого файла.

    • BLOB-адаптер, считывает ссылку на двоичный объект и обращается в Хранилище BLOB-объектов на стороне Ведомства.

    • После получения двоичного объекта, возвращает его в ПОДД СМЭВ.

    Для передачи ответа на запрос двоичного объекта от Витрины Поставщика данных используется специальные топики blob.rs и blob.err.

  8. ПОДД СМЭВ передает ИС Потребителя данных двоичный объект (файл), организуя двоичный поток между Витриной Поставщика данных и ИС Потребителя данных.

    В процессе передачи Агент Поставщика данных разбивает поток данных на сегменты (чанки) и подписывает каждый сегмент подписью Поставщика данных. Перед передачей данных Потребителю данных подпись проверяется Ядром ПОДД СМЭВ.

Пример программного кода для JDBC-драйвера, реализующего получение двоичного объекта:

package dev.nsud.jdbc
import org.junit.jupiter.api.Test
import java.sql.Connection
import java.sql.DriverManager
import org.junit.jupiter.api.Assertions
import java.sql.SQLException

class Features {
    private getConnectionURI() {
        val host = System.getProperty(“agent.host”, “localhost”)
        val port = System.getProperty(“agent.port”, “8182”)
        return “jdbc:podd://$host:$port”
    }
    @Test
    fun `ожидается успешное соединение с базой данных`() {
        Assertions.assertDoesNotThrow { DriverManager.getConnection(getConnectionURI()) }
    }
    @Test
    fun `ожидается успешное получение бинарных данных `() {
        Assertions.assertDoesNotThrow {
            val expect = getExpectedBytes()
            val con = DriverManager.getConnection(getConnectionURI())
            val statement = con.createStatement()
            statement.executeQuery(“select binaryColumn from datamart.table where id=1”)
            val resultSet = statement.resultSet
            Assertions.assertEquals(expect, resultSet.getBlob(0))
        }
    }
}
Информационный обмен при выполнении запроса с использованием ПОДД СМЭВ

Рисунок - 3.1 Информационный обмен при выполнении запроса с использованием ПОДД СМЭВ

3.3.6. Получение печатной формы в результатах запроса

Запросы для получения печатной формы в результатах запроса не содержат SQL-выражения в определении Регламентированного SQL-запроса и являются процедурами, выполняемыми на стороне Витрины. Вызов запроса от ИС Потребителя аналогичен вызову обычного Регламентированного SQL-запроса.

Приём, обработка и передача запроса по пути Агент Потребителя данных → Ядро ПОДД СМЭВ → Агент Поставщика данных осуществляется аналогично обычному информационному обмену с использованием Регламентированных SQL-запросов (Раздел 1.4.1). Особенностью обработки запросов является добавление Агентом Поставщика данных алиаса сертификата в запрос, согласно настройкам, заданным в соответствии с «Руководством администратора Агента СМЭВ4» [28], для последующей подписи печатной формы.

3.4. Выполнение запросов к REST-сервису ИС Ответчика

Для выполнения запроса к REST-сервису ИС Ответчика необходимо:

  1. Определить доступные REST-сервисы.

  2. Составить URI запроса в соответствии со спецификацией OpenAPI зарегистрированного REST-сервиса ИС Ответчика, загруженной в ПОДД СМЭВ.

URI запроса формируется путём конкатенации мнемоники Агента Ответчика, префикса в URL (basePath) соответствующего REST-сервиса ИС Ответчика и path операции. Сформированный запрос должен совпадать с запросом из спецификации OpenAPI.

Формат запроса для обмена с использованиям REST-сервиса ИС Ответчика имеет вид:

<HTTP-метод> <адрес>:<порт>/<systemMnemonic><basePath><path>

где:

  • HTTP-метод – метод из поддерживаемых REST-сервисом;

  • <адрес> – IP-адрес Агента Инициатора;

  • <порт> – порт для обращения Агента Инициатора к Ядру ПОДД СМЭВ в соответствии с «Руководством администратора ПОДД СМЭВ» [28];

  • systemMnemonic – мнемоника Агента Ответчика, на стороне которого развернут REST-сервис;

  • basePath – префикс в URL соответствующего REST-сервиса ИС Ответчика;

  • path – путь операции, указанный в спецификации OpenAPI соответствующего REST-сервиса ИС Ответчика.

Пример URI запроса соответствует примеру спецификации OpenAPI, приведенному в Раздел 1.5.5 данного документа:

GET 10.81.4.30:29164/agent-oktmo/region-service/region/123

Выполнение запросов осуществляется через REST-интерфейс Агента СМЭВ4 (см. Раздел 2.3.1.3 данного документа).

3.5. Сквозная идентификация запросов

Для диагностики проблем возникающих в ходе информационных обменов через ПОДД СМЭВ в части отслеживания всей цепочки сообщений, возникающих в ходе информационного обмена обеспечена сквозная идентификация запросов.

Сквозная идентификация в ПОДД СМЭВ основывается на идентификаторах, приведённых в Таблица 3.5.

На Рисунок - 3.2, Рисунок - 3.3 и Рисунок - 3.4 приведён порядок возникновения этих идентификаторов.

Таблица 3.5 Виды идентификаторов ПОДД СМЭВ

№

Вид

Код

Источник идентификатора

1

Клиентский идентификатор

(идентификатор клиента)

clientRequestId

Инициатор обмена

2

Идентификатор обмена

(идентификатор процесса)

requestId

ПОДД СМЭВ при получении запроса на:

  • информационный обмен (например, выполнение регламентированного SQL-запроса)

  • процесс (например, регистрации подписки)

Витрина данных:

  • если является инициатором обмена (например, уведомление о наличии новой дельты)

  • если происходит манипуляции данными без участия ПОДД (например, загрузка в таблицу)

3

Идентификатор подзапроса ПОДД СМЭВ – Витрина

subRequestId

ПОДД СМЭВ

4

Идентификатор подзапроса Адаптер – Prostore

dataRequestId

Адаптер Витрины
Сквозная идентификация запросов для информационного обмена с использованием регламентированных SQL-запросов

Рисунок - 3.2 Сквозная идентификация запросов для информационного обмена с использованием регламентированных SQL-запросов

Сквозная идентификация запросов для информационного обмена с использованием запросов к REST-сервису ИС Ответчика

Рисунок - 3.3 Сквозная идентификация запросов для информационного обмена с использованием запросов к REST-сервису ИС Ответчика

Сквозная идентификация запросов для информационного обмена с использованием Рассылок

Рисунок - 3.4 Сквозная идентификация запросов для информационного обмена с использованием Рассылок

Правила использования клиентского идентификатора

  1. Клиентский идентификатор опционален.

    Исключение из правила – УВ явно добавил обязательное поле ClientRequestID в спецификацию OpenAPI для информационного обмена с использованием Регламентированных REST-запросов. В данном случае, валидация осуществляется в соответствии со спецификацией.

  2. Клиентский идентификатор должен соответствовать стандарту UUID.

  3. Ответственность за используемый формат несёт инициатор обмена. ПОДД не осуществляет проверку по формату и версиям UUID.

    Исключение из правила – УВ явно добавил поле ClientRequestID в спецификацию OpenAPI для информационного обмена с использованием Регламентированных REST-запросов. В данном случае, валидация осуществляется в соответствии со спецификацией.

  4. Допустимо использовать один клиентский идентификатор для связывания нескольких запросов. ПОДД СМЭВ не осуществляет проверку на уникальность.

  5. Способ передачи клиентского идентификатора приведён в Раздел 2.3.

3.6. Лимитирование регламентированных запросов Потребителя к информационным ресурсам Поставщика

3.6.1. Лимиты

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

Лимит может быть установлен на следующие информационные ресурсы Поставщика:

  • на конкретный Регламентированный SQL-запрос;

  • на все Регламентированные SQL-запросы к конкретной Витрине;

  • на конкретный Регламентированный REST-запрос (спецификацию OpenAPI ИС Поставщика);

  • общий на все Регламентированные SQL и REST-запросы от ИС Потребителя к ИС Поставщика.

Лимит может быть следующих типов:

  • на Количество запросов;

  • на Размер запросов;

  • на Размер ответов.

При создании лимита указывается его значение (количество или размер) и период (у которого два назначения – период контроля соблюдения значения и период блокировки).

3.6.2. Условия для добавления лимитов

  1. В ПОДД должны быть зарегистрированы ИС Поставщика и ИС Потребителя;

  2. Если лимит задаётся на Витрину в ПОДД СМЭВ должны быть:

    • зарегистрирована Витрина;

    • Витрина связана с ИС Поставщика;

  3. Если лимит задаётся на конкретный Регламентированный SQL-запрос, в ПОДД СМЭВ должны быть:

    • зарегистрирована Витрина;

    • Витрина связана с ИС Поставщика;

    • выданы права ИС Потребителя на Регламентированный SQL-запрос.

  4. Если лимит задаётся на конкретную спецификацию OpenAPI в ПОДД СМЭВ должны быть:

    • зарегистрирована спецификация OpenAPI;

    • выданы права ИС Инициатора на спецификацию OpenAPI.

  5. При выставлении лимитов недопустимо совмещение:

    • общего на все Регламентированные SQL и REST-запросы ИС Потребителя к ИС Поставщика лимита и лимита на другие ресурсы;

    • лимита на Витрину и лимита на Регламентированный SQL-запрос к этой Витрине.

  6. Лимит на конкретный Регламентированный SQL-запрос ограничивает запросы Потребителя по всем версиям этого запроса.

  7. Лимит на все Регламентированные SQL-запросы к конкретной Витрине ограничивает запросы Потребителя по всем версиям модели этой Витрины.

  8. При установке лимита на Витрину и ИС также могут учитываться произвольные SQL-запросы. Реализация исключения таких запросов из процесса лимитирования не предусмотрена, так как права доступа на выполнение произвольных SQL-запросов предоставляются автоматически и только владельцам Витрин на их собственные Витрины.

3.6.3. Блокировки

В случае превышения лимитов, заданных Поставщиком, ПОДД СМЭВ блокирует Потребителю доступ к соответствующему информационному ресурсу. Например, если для ИС Потребителя указано два лимита к двум Регламентированным SQL-запросам и заблокирован доступ к одному из них, она может продолжить пользоваться другим доступным запросом. При получении запроса от заблокированного Потребителя, ПОДД СМЭВ возвращает ответ с соответствующей ошибкой. Блокировка снимается:

  • при истечении времени блокировки;

  • при удалении соответствующего лимита;

  • при редактировании соответствующего лимита (любых атрибутов кроме наименования);

  • при получении запроса на принудительное снятие блокировки.

3.6.4. Управление лимитами и блокировками

Управление лимитами и блокировками доступно из ЛК УВ.

3.6.5. Алгоритм лимитирования

Лимитирование осуществляется в соответствии со следующим алгоритмом (Рисунок - 3.5):

  1. ПОДД СМЭВ формирует окна наблюдения от 1 января 1970 года 00:00:00.000 в соответствии с указанным значением периода лимита.

  2. ПОДД СМЭВ хранит в памяти значение счётчика предыдущего и текущего окна наблюдения. После разблокировки значения счётчиков обнуляются.

  3. ПОДД СМЭВ вычисляет счётчик инкрементируя значение при каждом полученном запросе.

  4. ПОДД СМЭВ принимает решение о блокировке на основании имеющихся счётчиков:

    • Если значение предыдущего окна наблюдения нет, то берётся значение инкрементированного счётчика текущего окна.

    • Если полученное значение меньше значения лимита, то запрос будет обработан.

    • Если значение предыдущего окна наблюдения есть, то ПОДД СМЭВ вычисляет коэффициенты наложения окна наблюдения на интервалы по формуле:

K = N + M * R
где:
- N - количество запросов в текущем интервале (с учетом полученного),
- M - количество запросов на предыдущем интервале,
- R - коэффициент наложения на предыдущий интервал (0...1).

  • Если полученное значение меньше значения лимита, то запрос будет обработан.

Пример:

Установлен лимит: 4 запроса в минуту
С 0 до 0:59 - пришло 5 запросов,
В 1:30 - пришёл ещё один запрос

результат вычисления:
- N = 1,
- M = 5,
- R = (90 - 60) / 60 = 0.5

K = 1 + 5 * 0.5 = 3.5 меньше предельного, запрос будет обработан.
Алгоритм лимитрирования запросов

Рисунок - 3.5 Алгоритм лимитрирования запросов