Обмен в СМЭВ4 с использованием SQL запросов

Скопировать прямую ссылку на статью

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

Для обеспечения возможности выполнения SQL-запросов к витринам в качестве поставщика данных в СМЭВ4 участник взаимодействия должен выполнить ряд требований:

Зарегистрировать информационную систему в СМЭВ4
Установить в своем контуре Агент ПОДД, выполнив настройки для обмена с использованием SQL-запросов.
Разместить в своем контуре витрину данных, настроить ее на взаимодействие со СМЭВ4 (подробнее в разделе СМЭВ4 -> Витрина данных).
Связать витрину данных и информационную систему с использованием личного кабинета участника взаимодействия.
Зарегистрировать регламентированный запрос (описывается в статье “Регистрация регламентированного запроса в СМЭВ4”)

В контуре потребителя данных СМЭВ4:

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

На рисунке 1 представлена схема информационного обмена, на которой показано, как ИС передает SQL запрос Агенту ПОДД. А тот в свою очередь передает запрос в ядро ПОДД.

Рисунок 1 – Информационный обмен при выполнении запроса в СМЭВ4.

Примеры запросов с использованием SQL-синтаксиса

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

Примеры запросов используют следующую модель данных:

1. Витрина «fias», таблица «addrobj» в соответствии с таблицей 1.

Таблица 1 – Состав таблицы «addrobj»

№	Атрибут	Мнемоника
1	ID адресного объекта	AOGUID
2	Формальное наименование	FORMALNAME
3	Регион	REGIONCODE
4	OKATO	OKATO
5	OKTMO	OKTMO
6	Уровень	AOLEVEL

2. Витрина «oktmo», таблица «oktmo» в соответствии с таблицей 2.

Таблица 2 – Состав таблицы «oktmo»

№	Атрибут	Мнемоника
1	Регион	TER
2	Код района/округа	KOD1
3	Код поселения	KOD2
4	Код населенного пункта	KOD3

3. Витрина «egrul» версии 1.0, таблица «legalentity» в соответствии с таблицей 3.

Таблица 3 – Состав таблицы «legalentity» версии 1.0

№	Атрибут	Мнемоника
1	Основной государственный регистрационный номер	OGRN
2	Краткое наименование	SHORT_NAME
3	Индивидуальный номер налогоплательщика	INN
4	Код причины постановки на учет	KPP

4. Витрина «egrul» версии 2.0, таблица «legalentity» в соответствии с таблицей 4.

Таблица 4 – Состав таблицы «legalentity» версии 2.0

№	Атрибут	Мнемоника
1	Основной государственный регистрационный номер	OGRN
2	Краткое наименование	SHORT_NAME
3	Индивидуальный номер налогоплательщика	INN
4	Код причины постановки на учет	KPP
5	Код региона	REGION_CODE

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

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

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

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

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

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

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

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

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

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

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

4. Запросы с использованием 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

5. Запрос использованием 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

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

Запросы с указанием версии Витрины

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

select * from egrul.1.legalentity

Результат:

фф.png

Рисунок 2 – Результат исполнения запроса с указанием версии Витрины (вариант 1).

select * from egrul.2.legalentity

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

Результат:

Рисунок 3 – Результат исполнения запроса с указанием версии Витрины (вариант 2).

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

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

Регламентированные запросы

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

1) регламентированный запрос в формате параметризированного:

Исходный запрос от Потребителя данных ПОДД СМЭВ:

select * from oktmo.1.0.oktmo_view(‘Московская область’,7)

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

select id, whenadd, name, regionname, settlementtypename FROM oktmo.1.0.oktmo where regionname = ‘Московская область’ AND settlementtypeid = 7

2) регламентированный запрос без параметров:// Исходный запрос от Потребителя данных ПОДД СМЭВ:

select * from egrul.1.1.legalentity_view()

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

select * from egrul.2.legalentity

3)распределенный регламентированный запрос:

Исходный запрос от Потребителя данных ПОДД СМЭВ:

select * from podd.1.1.r_query(‘Москва’, 18)

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

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

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

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

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

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>

где:

sql – текст произвольного SQL-запроса, содержащего табличные параметры;
tableParams – описание передаваемого файла с данными для табличного параметра, где:

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

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

inns – табличный параметр (выступает в качестве названия параметра запроса);
<1.csv> – файл в формате csv (поддерживаемый формат), передаваемый в параметре запроса.

, чтобы оставить комментарий к статье