заполнение форм статистической отчетности где хранится база данных
Инструкция по установке и настройке ПО для off-line модуля подготовки отчетов
Развитие Единой системы сбора, обработки, хранения и представления статистических данных (ЕССО) в части электронного сбора данных
ЕДИНАЯ СИСТЕМА СБОРА, ОБРАБОТКИ, ХРАНЕНИЯ И ПРЕДСТАВЛЕНИЯ СТАТИСТИЧЕСКИХ ДАННЫХ
ИНСТРУКЦИЯ ПО УСТАНОВКЕ И НАСТРОЙКЕ ПО ДЛЯ OFF-LINE МОДУЛЯ ПОДГОТОВКИ ОТЧЕТОВ
Внимание! В новом ПО off-line модуля предусмотрена возможность автоматического обновления программного обеспечения и шаблонов форм, а так же возможность отправки отчетов непосредственно из off-line модуля без входа в web-кабинет. Для этого после установки выполните настройки адреса сервера системы web-сбора, описанные в п.3 данной инструкции
1. Программно-аппаратное обеспечение
1.1 Операционная система
Операционная система клиентских станций: Microsoft Windows 2000, Microsoft Windows XP, Microsoft Windows Vista, Microsoft Windows 7.
1.2 Клиентское программное обеспечение
В качестве клиентского программного обеспечения для заполнения форм статистической отчетности необходимо установить:
§ ПО «Заполнение форм статистической отчетности » (off-line модуль подготовки отчетов),
Так же для заполнения форм статистической отчетности через off-line модуль подготовки отчетов требуется наличие на компьютере следующего программного обеспечения, в зависимости от версии используемой операционной системы:
§ Framework 2.0 SP2. (дистрибутив размещен на сайте Татарстанстата) или Framework 3.5 SP1
§ На клиентской станции, где будет проводиться подписание отчетов, необходима установка КриптоПро SharpeiRTE. (дистрибутив размещен на сайте Татарстанстата)
§ Операционная система Microsoft Windows 2000 Service Pack 4; Microsoft Internet Explorer 6.0 Service Pack 1 и выше; «КриптоПро CSP 3.6» или другое СКЗИ, используемое в Росстате;
§ Операционная система Windows XP Service Pack 2: Microsoft Internet Explorer 7.0; «КриптоПро CSP 3.6» или другое СКЗИ, используемое в Росстате;
§ Операционная система Microsoft Windows Vista Service Pack 1; Microsoft Internet Explorer 8.0 Service Pack 1 и выше; «КриптоПро CSP 3.6» или другое СКЗИ, используемое в Росстате.
1.3 Техническое обеспечение
Клиентские станции в количестве, соответствующем планируемому Респондентом количеству рабочих мест, с характеристиками не ниже следующих:
объем оперативной памяти:
объем свободного места на жестком диске:
2. Установка off-line модуля подготовки отчетов
Все дистрибутивы размещены на сайте Татарстанстата www. ***** в разделе «Электронная отчетность» подраздел «Система web-сбора».
При отсутствии доступа к интернету Вы можете получить дистрибутивы при личном посещении Татарстанстата или районного отделения государственной статистики. (Необходимое По будет скопировано на предоставленный Вами носитель, например, флешку)
2.1 Установка дополнительного программного обеспечения.
1) Если ранее не было установлено программное обеспечение Framework 2.0 SP2 или Framework более высокой версии, выполните установку.
2) Если на данной рабочей станции Вы не планируете подписывать отчеты, либо планируете отправлять подготовленные с использованием off-line модуля отчеты через операторов связи, выполнять данный пункт не обязательно.
Для подписания и отправки отчетов в ТОГС необходимо установить дополнительное программное обеспечение КриптоПро SharpeiRTE. Перед установкой выберите версию ПО SharpeiRTE, соответствующую вашей версии ОС.
Внимание! После установки ПО SharpeiRTE необходимо перезагрузить компьютер.
2.2 Установка off-line модуля.
1) Разархивируйте содержимое дистрибутива и запустите файл OfflineApplication. SetupWin. msi или Setup.exe. В результате будет запущен мастер установки off-line модуля.
2) При запуске мастера установки пользователю отображается предупреждение о необходимости установки ПО SharpeiRTE если off-line модуль планируется использовать для отправки отчетов в ТОГС напрямую. Иначе ПО SharpeiRTE можно не устанавливать.
Предупреждение о необходимости установить ПО SharpeiRTE
Мастер установки off-line модуля
3) Нажмите кнопку «Далее». В Результате откроется окно, в котором будет предложено указать папку для установки off-line модуля. По умолчанию клиент устанавливается в папку «C:\Program Files\Росстат. Web-сбор. Оффлайн-модуль\». При необходимости ее можно изменить.
4) На следующем шаге Вам будет предложено создать ярлык быстрого запуска на рабочем столе.
5) Ход процесса установки отображается в виде индикатора прогресса.
6) После окончании процесса установки нажмите кнопку «Закрыть».
Для проверки правильности установки off-line модуля на ваш компьютер выполните следующие действия:
Откройте системное меню Microsoft Windows и выберите пункт «Пуск ► Программы ► Заполнение форм статистической отчетности» или запустите ярлык «Заполнение форм статистической отчетности» с рабочего стола.
Ярлык «Заполнение форм статистической отчетности»
В MS Windows 7 или выше в пункте «Пуск» ярлык приложения располагается в верхней части списка установленных приложений.
В случае успешной установки откроется рабочее окно off-line модуля подготовки отчетов (см. рисунок ниже).
Проверка правильности установки off-line модуля подготовки отчетов
Хранение статистической информации осуществляется в области данных текущего пользователя операционной системы MS Windows.
C:\Documents and Settings\ >>\Application Data\ESSO
Каталог статистических данных
В MS Windows 7 данные хранятся в каталоге:
Это означает, что если на одном компьютере работают два и более оператора под разными учетными записями – у каждого из них будет отображаться свой набор форм, организаций и статистической отчетности.
Аварийные ситуации
1. При первом запуске программы выдается сообщение об обнаружении ошибки
На рабочей станции не установлен Framework 2.0 SP2 или Framework более высокой версии. Возможно установлен Framework 2.0 без сервис-пака.
Для устранения ошибки скачайте с сайта Татарстанстата www. ***** в разделе «Электронная отчетность» подраздел «Система web-сбора» дистрибутив Framework 2.0 SP2, разархивируйте на свой компьютер и выполните установку.
2. Программа не запускается без каких-либо сообщений.
На компьютере установлен только Framework 2.0 SP1.
Для устранения ошибки скачайте с сайта Татарстанстата www. ***** в разделе «Электронная отчетность» подраздел «Система web-сбора» дистрибутив Framework 2.0 SP2, разархивируйте на свой компьютер и выполните установку.
3. Настройка адреса сервера системы web-сбора для автоматического обновления программного обеспечения и шаблонов форм
3.1 Настройка off—line модуля взаимодействие с сервером ТОГС (подключение к сайту on-line).
Для этого в главном меню off-line модуля подготовки отчетов необходимо выбрать пункт Сервис ►Настройки ►Подключение к сайту on-line.
В качестве настроек подключения к сайту on-line пользователь должен задать строку для подключения к серверу ТОГС: https://sbor. *****/streport
Настройка параметров подключения к сайту on-line
В случае наличия в организации прокси-сервера, для работы off-line модуля подготовки отчетов могут использоваться настройки обозревателя Internet Explorer или настройки, заданные вручную.
Для настройки Internet Explorer необходимо выполнить следующие действия:
1) Выбрать в стартовом меню «Start ► Settings ► Control Panel ► Internet Options» или открыть окно Internet Explorer и в меню выбрать «Tools ► Internet Options».
2) В открывшемся окне переключиться на вкладку Connections, нажать кнопку LAN Settings и настроить прокси-сервер.
3) Проверить корректность загрузки в Internet Explorer страницы системы web-сбора https://sbor. *****/streport
В случае невозможности соединения с сайтом системы web-сбора, необходимо обратиться к Вашему системному администратору.
ПО прокси-сервера должно быть настроено системным администратором стандартным образом, либо разрешены соединения к серверу ТОГС, минуя прокси-сервер.
ПО брандмауэра и антивируса должно быть настроено таким образом, чтобы разрешать все обращения off-line модуля подготовки отчетов к серверу ТОГС.
3.2 Настройка режима обновления ПО и шаблонов форм.
Для этого в главном меню off-line модуля подготовки отчетов необходимо выбрать пункт Сервис ►Настройки ►общие настройки. В полях «Обновление приложения» и «Обновление шаблонов форм» установите необходимый режим обновления.
— автоматическое обновление. РЕКОМЕНДУЕТСЯ! (при наличии обновления ПО будет выведено сообщение о том что проводится обновление, при наличие обновления шаблонов форм будет выведен список новых шаблонов для выбора)
— проверять наличие обновления, запрашивать пользователя (при наличии обновления ПО или шаблонов форм будет выведено сообщение о том что доступно обновление и выведен запрос разрешения на обновление)
— обновление вручную. (проверка наличия обновлений и обновление проводится только по запросу пользователя)
В случае настройки обновления приложения в ручном режиме обновление приложения производится через пункт главного меню «Сервис ► Обновление ПО».
Выполняется проверка новой версии на сайте ТОГС и, при наличии таковой, загружает ее на рабочую станцию респондента и устанавливает.
В случае использования последней версии приложения будет показано следующее сообщение:
Сообщение об отсутствии новой версии off-line модуля
3.3 Настройка редактора отчетов.
Для этого в главном меню off-line модуля подготовки отчетов необходимо выбрать пункт Сервис ►Настройки ►редактор отчетов. Для периодичного авто сохранения отчетов проставьте флажок в чекбоксе «Сохранять черновики автоматически». Укажите интервал времени через который проводить авто сохранение отчетов при заполнении и редактировании.
4. Загрузка шаблонов форм
Вы можете получить формы для заполнения одним из следующих способов:
§ загрузить форму (шаблон ЭВФ) с сайта системы WEB-сбора ТОГС;
§ получить пакет форм по электронной почте. Рассылка осуществляется автоматически при наступлении отчетного периода, шаблон прикрепляется к письму с уведомлением о необходимости сдать отчет;
§ скачать пакет форм или шаблоны отдельных форм с сайта Татрстанстата www. ***** в разделе «Электронная отчетность», подраздел «Шаблоны форм ЭВФ»
4.1 Загрузка шаблона (ов) с сайта системы WEB-сбора
Для загрузки формы с сайта on-line модуля подготовки отчетов необходимо выполнить следующие действия:
1) Запустить off-line модуль подготовки отчетов.
Если Вы настроили off-line модуль на автоматическое обновление шаблонов форм, о при запуске программы будет предложен список форм для загрузки в off-line модуль.
Если обновление настроено в режиме «вручную» необходимо выбрать в меню Файл команду обновления шаблонов форм для заполнения с сайта системы WEB-сбора «Загрузить шаблоны(ы) из ТОГС» или нажать кнопку 
на панели задач списка шаблонов.
Обновление форм с сайта ТОГС
3) Будет выведен список форм, доступных на сервере для загрузки. Флажками отмечены формы, которые будут загружены. Если Вам нужно загрузить другой набор форм – снимите или установите флажки напротив требуемых форм.
Выбор шаблонов форм для загрузки
4) В случае успешной загрузки будет выведено сообщение о количестве загруженных и обновленных форм.
Информационное сообщение о количестве загруженных форм
5) Полученные формы статистической отчетности подключаются к системе автоматически.
Формы доступны в модуле
4.2 Загрузка шаблонов из файла
Полученные по электронной почте или формы загружаются в off-line модуль подготовки отчетов с использованием команды главного меню «Файл ►Загрузить шаблон(ы) из файла». Полученные формы статистической отчетности подключаются к системе автоматически.
Команда загрузки форм
Если полученные формы упакованы в архив типа zip, в поле «тип файлов» выберите «пакет шаблонов (*zip)», выберите нужный пакет
Будет выведен список форм, содержащихся в пакете. Флажками отмечены все формы. Если Вам нужно загрузить другой набор форм – снимите или установите флажки напротив требуемых форм.
После загрузки выбранных форм на экран будет выведен отчет о загрузке
Подготовка и отправка форм статистики в электронном виде из «1С:Предприятия»
Статистика, как говорится, знает все. Точнее, она стремится знать все. А для того, чтобы это было возможно, ей требуются разнообразные данные от каждой «бизнес-единицы», от каждой организации. Статистических форм много, показатели, которые требуется предоставить, очень разнообразны и, порой, явно в данных бухгалтерского учета они не содержатся.
Чтобы воспользоваться этим отчетом, следует в форме Регламентированная и финансовая отчетность, в разделе Статистика выбрать строку Прочие формы статистической отчетности. Если вы уже работали с регламентированной отчетностью в программах «1С», в целом последовательность действий будет вам знакома, однако имеет одно существенное отличие: прежде, чем нажать кнопку OK для создания отчета, следует выбрать требуемую статистическую форму из списка, который можно открыть, нажав кнопку Выбрать форму. В списке надо выбрать необходимую форму из загруженных, организацию и необходимый период составления отчета.
После этого по нажатию кнопки ОК будет открыта форма для заполнения (рис. 1).
Поскольку данный отчет предназначен для работы со многими статистическими формами и именно для передачи в электронном виде, в нем не предусмотрен вывод формы на печать в утвержденной Росстатом форме (можно лишь распечатать экранную форму).
После того, как в программах будут реализованы статистические отчеты в «обычном» виде, универсальный отчет будет исключен из списка отчетов.
Подготовка и отправка форм статистики в электронном виде из «1С:Предприятия 8»
Статистика, как говорится, знает все. Точнее, она стремится знать все. А для того, чтобы это было возможно, ей требуются разнообразные данные от каждой «бизнес-единицы», от каждой организации. Статистических форм много, показатели, которые требуется предоставить, очень разнообразны и, порой, явно в данных бухгалтерского учета они не содержатся.
Чтобы воспользоваться этим отчетом, следует в форме Регламентированная и финансовая отчетность, в разделе Статистика выбрать строку Прочие формы статистической отчетности. Если вы уже работали с регламентированной отчетностью в программах «1С», в целом последовательность действий будет вам знакома, однако имеет одно существенное отличие: прежде, чем нажать кнопку OK для создания отчета, следует выбрать требуемую статистическую форму из списка, который можно открыть, нажав кнопку Выбрать форму. В списке надо выбрать необходимую форму из загруженных, организацию и необходимый период составления отчета.
После этого по нажатию кнопки ОК будет открыта форма для заполнения (рис. 1).
Поскольку данный отчет предназначен для работы со многими статистическими формами и именно для передачи в электронном виде, в нем не предусмотрен вывод формы на печать в утвержденной Росстатом форме (можно лишь распечатать экранную форму).
После того, как в программах будут реализованы статистические отчеты в «обычном» виде, универсальный отчет будет исключен из списка отчетов.
Онлайн и оффлайн модули подготовки отчетов в Росстат через сервис Web-сбор
Web-сбор — это система, которая позволяет предприятиям передавать данные в Росстат без личного посещения представителей. Сегодня система позволяет пользоваться оффлайн-версией и загружать любые статистические сведения. Портал веб-сбор позволяет предоставлять организациям любые виды отчётности для ИП и ООО. Сюда входят налоговые сборы, бухгалтерия, общая статистика по предприятию. Какие данные вносят компании в отчет:
On-line (Онлайн)
На официальном портале системы Веб-сбор есть 2 варианта загрузки отчетности:
OFF-line (Оффлайн)
Отправка отчетности в Росстат через оффлайн-модуль сервиса Web-сбор — более усложненный вариант. В этом случае представителям организации необходимо сначала на официальном портале Статрегистра скачать файл для загрузки на компьютер. Далее загруженный архив распаковывается, запускается дистрибутив. Особое внимание уделяется техническому оснащению клиентской стороны. Для установки системного файла потребуется ОС Microsoft Windows 7, 8.1, 10. Объем оперативной памяти не должен быть меньше 2 Гб. Требуемое место на жестком диске — более 50 мб.
Перед началом работы потребуется установить систему криптографической защиты информации, то есть СКЗИ. Для этих целей необходимо использовать сервис КриптоПро CSP. Информацию о его установке можно получить на официальном сайте разработчиков. Следующий этап — получение закрытого и открытого ключа ЭП (электронной подписи) представителя фирмы. Это необходимо для защиты данных. Для этого следует посетить УЦ, который является доверительной системой Росстата, сделать официальный запрос на получение ключевых данных. Список доступных удостоверяющих центров определяет ТОГС. Он передается пользователю администратором.
Сведения о получении ключевой информации находятся на сайтах каждого удостоверяющего центра. Далее респондентом получаются ключи шифрования. Все сертификаты устанавливаются в хранилище. Для этого:
Выбранный контейнер станет ключевым. Теперь нужно выбрать пункт “Готово”. Респонденту также необходимо подать заявку в ТОГС для дальнейшей отправки данных в оффлайн-режиме. Для этого представителю предприятия следует написать заявление в ТОГС об участии в процессе электронного сбора данных. Во время подачи заявления пользователю следует передать на магнитном носителе сертификат открытого ключа, который был получен ранее. После обработки данных респонденту будут выданы персональные данные: логин, пароль, ссылка на сайт для загрузки справочных сведений, облегчающих пользование системой.
Получение дистрибутива OFF-line модуля подготовки отчетов
Получить его можно тремя способами:
Ссылки на дистрибутивы обычно предоставляет работник ТОГС, который ответственен за получение отчетности от конкретного представителя. Также эти полномочия приобретает администратор системы, если он получил поручение со стороны ответственного сотрудника. Если ссылки на дистрибутив нет, или ее не получается открыть, то респонденту стоит обратиться в ТОГС, обсудить с ответственным работником способы получения дистрибутива.
Установка OFF-line модуля подготовки отчетов
Перед установкой модуля отчетности стоит перепроверить компьютер на соответствие выдвинутым техническим требованиям. Также пользователю потребуется перед открытием оффлайн-модуля установить программу SharpeiRTE.
Это приложение, которое дает возможность применять криптографическую защиту для системы Microsoft. Net Framework. Респондент перед загрузкой сервиса Sharpei RTE должен выбрать ту системную версию, которая соответствует версии операционной системы его оборудования. По завершению установки сервиса потребуется перезагрузить компьютер.
Так выглядит стартовое окно OFF-line модуля подготовки отчетов
Web-сбор — это система, которая позволяет любым предприятиям предоставлять налоговую, бухгалтерскую, статистическую отчетность в Росстат без личного посещения государственного органа. Системой можно пользоваться в онлайн и оффлайн-режиме. В первом случае пользователь просто заходит на сайт площадки, вносит данные, прикрепляет файлы и отправляет их в Статрегистр.
Во втором случае респонденту потребуется установить программу, получить ключевые данные, доступ к сервису, дистрибутив от администратора ТОГС. После этого можно приступать к пользованию сервисом и отправлению статистических данных. Установка программы не требует особых навыков, с ней может справиться штатный работник, знающий систему Microsoft Windows.
Статистика в кармане: Портативная СУБД с базой Росстата для исследователей и не только
Возможно, глядя на этот старый советский плакат, вы подумаете, что я здесь буду агитировать сознательных граждан России участвовать в переписи населения… Но спешу вас разуверить: статья вовсе не об этом. Скорее это рассказ о том, какая официальная статистика собирается в России, где ее искать и как скачать все данные к себе на компьютер или портативное устройство (отсюда и название) и удобно ей пользоваться. Если вам это интересно, читайте дальше!
Государственный статистический учет в России
Статистика вчера и сегодня
Статистические данные по нашей стране собираются довольно давно: экономические, социальные и иные показатели собирались и публиковались еще при царской власти, затем более масштабно — при советской власти и, наконец, в XXI веке (особенно с переходом на электронные базы данных) российская статистика окончательно приобрела свои современные очертания. В России сбором и обработкой статистических данных занимается Федеральная служба государственной статистики, она же просто Росстат. Своей основной целью эта служба называет «удовлетворение потребностей органов власти и управления, средств массовой информации, населения, научной общественности, коммерческих организаций и предпринимателей, международных организаций в разнообразной, объективной и полной статистической информации».
Вот как Росстат описывает становление современного статистического учета в Российской Федерации:
Начало ХХI века ознаменовалось для российской статистики принципиальными событиями. Прежде всего, в этот период были заложены единые основы осуществления официального статистического учета, которые были установлены Федеральным законом “Об официальном статистическом учете и системе государственной статистики в Российской Федерации”, принятым 29 ноября 2007 г. (далее – Закон о статистике).
Также в этот период были приняты Федеральный закон “Об информации, информационных технологиях и о защите информации” от 27 июля 2006 г.; Федеральный закон “О персональных данных” от 27 июля 2006г.; Кодекс Российской Федерации об административных правонарушениях от 30 декабря 2001 г. и другие законы и нормативные акты Правительства Российской Федерации, касающиеся правового регулирования официального статистического учета в Российской Федерации.
Основной целью Закона о статистике является создание правовых основ реализации единой государственной политики в области официального статистического учета, направленной на обеспечение информационных потребностей государства и общества в полной, объективной, научно-обоснованной и своевременной официальной статистической информации о социальных, экономических, демографических, экологических и других общественных явлениях в Российской Федерации.
С его принятием получили правовое регулирование многие направления деятельности в сфере официального статистического учета, осуществление которых ранее было затруднено. Установлены правила, соответствующие международным принципам официальной статистики, принятым Статистической комиссией ООН (1994 г.), и механизмы их реализации.
Полный текст исторический справки вы можете прочитать на сайте Росстата.
В 2019 г. Росстат утвердил «Стратегию развития Росстата и системы государственной статистики до 2024 года» (документ можно скачать по ссылке), в которой ставятся следующие задачи:
Разработчикам, общественным организациям, гражданам, журналистам, работающим в новом направлении – журналистике данных, в качестве основы для создания удобных и полезных приложений и сервисов необходима информация, размещаемая в форме открытых данных. Открытые данные, размещаемые в форме набора отдельных показателей, менее полезны и востребованы, чем логически увязанные последовательности (например, построенные в единой методологии временные ряды, ранжированные последовательности).
Потребуется обеспечить максимальную информационную открытость ведомства. Сегодня недостаточно производить и хранить имеющуюся статистическую информацию. Необходимо использовать все каналы взаимодействия с пользователями для популяризации статистики
и повышения статистической грамотности.
Отсюда постулируются и довольно-таки смелые плановые показатели 2024 г.:
Единый статистический ресурс — ЕМИСС
Сегодня вся статистическая информация, собираемая Росстатом, хранится в единой базе статистических данных — ЕМИСС, доступной на сайте www.fedstat.ru. По состоянию на момент написания этой статьи, база ЕМИСС содержит 7230 показателей, собранных от 65 государственных ведомств. На главной странице есть диаграмма, показывающая, что ежемесячно обновляется более 1000 показателей.
ЕМИСС представляет собой государственный информационный ресурс, объединяющий официальные государственные информационные статистические ресурсы, формируемые субъектами официального статистического учета в рамках реализации федерального плана статистических работ.
Доступ к официальной статистической информации, включенной в состав статистических ресурсов, входящих в межведомственную систему, осуществляется на безвозмездной и недискриминационной основе.
Для удобства несколько прямых ссылок:
Делаем свой «клон» ЕМИСС
При всех преимуществах «веб-морды» ЕМИСС (которая даже поддерживает контекстный поиск и позволяет выгружать датасеты в формате Excel), у нее есть несколько существенных недостатков, которые вообще присутствуют у подавляющего большинства открытых данных, доступных через веб:
Структура базы данных
Я начал с того, что скачал с ЕМИСС полный перечень доступных датасетов (ссылку приводил выше), а также несколько самих датасетов в формате XML. Подробное описание данных ЕМИСС вы можете прочитать в вышеуказанном Руководстве пользователя, а я лишь приведу граф структуры БД, полученный на основе анализа этих XML (картинка кликабельна).
Как вы видите, структура довольна типична для реляционных БД, что, с одной стороны, повышает читаемость и надежность, а с другой стороны, добавляет гибкость, позволяя при необходимости вносить изменения в одну часть БД, не затрагивая другие.
Собственно данные (т.е. численные наблюдения по годам, кварталам и т.д.) находятся в таблице obs («observations»), которая связана внешними ключами с таблицей датасетов (datasets), классификаций (codevals), единиц измерений (units) и периодов наблюдений (periods).
Таблица датасетов, содержащая информацию о наборах данных, связана с таблицами служб (agencies), подразделений (departments), рубрик (classifier), классификаторов (codes), единиц измерений (units) и периодов наблюдений (periods).
Остальные таблицы являются служебными, и мы не будем на них подробно останавливаться.
Представления
Специально для удобства выгрузки данных в читаемом виде (с раскрытием зависимостей между таблицами) я создал несколько представлений:
Представление all_datasets, как явствует из названия, выводит информацию обо всех датасетах в таблице datasets, с отображением данных из связанных служебных таблиц. Например, чтобы вывести первые 10 датасетов (с валидными данными — об этом дальше), выполним:
| select * from all_datasets where data_start > 0 limit 10 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| id | classifier | dataset | updated | prepared | next_update | description | agency | department | data_start | data_end | prep_by | prep_contact |
| 1 526 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество должностных лиц, совершивших преступления, освобожденных от уголовной ответственности по нереабилитирующим основаниям | 2020-08-27 15:57:49 | 2020-12-10 05:04:48 | 2021-03-31 21:00:00 | В связи с внесением изменений в форму статистического наблюдения | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 018 | 2 020 | Карпова Ю.А. | 8(495)-982-76-43 gosstat.gprf@genproc.ru |
| 1 479 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | 2020-08-27 15:50:08 | 2020-12-10 05:04:49 | 2021-03-31 21:00:00 | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru |
| 1 419 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления | 2020-08-27 15:48:12 | 2020-12-10 05:16:15 | 2021-03-31 21:00:00 | Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 011 | 2 020 | Карпова Ю.А. | 8-495-982-76-43 gosstat.gprf@genproc.ru |
| 1 335 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления занимающих должности в органах законодательной власти на уровне органа местного самоуправления | 2020-08-27 15:48:57 | 2020-12-10 05:16:17 | 2021-03-31 21:00:00 | Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 011 | 2 020 | Карпова Ю.А. | 8-495-982-76-43 gosstat.gprf@genproc.ru |
| 1 172 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления занимающих должности в органах законодательной власти на уровне Российской Федерации | 2020-08-27 15:48:39 | 2020-12-10 05:16:19 | 2021-03-31 21:00:00 | Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 011 | 2 020 | Карпова Ю.А. | 8-495-982-76-43 gosstat.gprf@genproc.ru |
| 1 540 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления занимающих должности в органах законодательной власти на уровне субъекта Российской Федерации | 2020-08-27 15:48:48 | 2020-12-10 05:16:21 | 2021-03-31 21:00:00 | Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 011 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru |
| 1 520 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти (за исключением правоохранительных) на уровне органа местного самоуправления | 2020-08-27 15:49:57 | 2020-12-10 05:16:28 | 2021-03-31 21:00:00 | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru |
| 1 125 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти (за исключением правоохранительных) на уровне Российской Федерации | 2020-08-27 15:49:40 | 2020-12-10 05:16:29 | 2021-03-31 21:00:00 | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru |
| 1 272 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти (за исключением правоохранительных) на уровне субъекта Российской Федерации | 2020-08-27 15:49:49 | 2020-12-10 05:16:23 | 2021-03-31 21:00:00 | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | Генеральная Прокуратура Российской Федерации | Главное организационно-аналитическое управление ГП РФ | 2 016 | 2 020 | Боголюбов А.Е. | (495)-982-76-55 kazimirov@genproc.gov.ru |
| 1 167 | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти на уровне органа местного самоуправления | 2016-02-20 11:25:28 | 2020-12-10 05:16:40 | 2021-03-31 21:00:00 | Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 011 | 2 015 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru |
Чтобы получить сами данные (наблюдения за определенные периоды), используется представление all_data, которое объединяет таблицу obs со всеми связанными с ней.
Первые 10 наблюдений:
| select * from all_data limit 10 | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| id | ds_id | dataset | classifier | description | prepared | updated | next_update | agency | department | data_start | data_end | prep_by | prep_contact | year | release | unit | code | category | value |
| 1 752 008 | 1 526 | Количество должностных лиц, совершивших преступления, освобожденных от уголовной ответственности по нереабилитирующим основаниям | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения | 2020-12-10 05:04:48 | 2020-08-27 15:57:49 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 018 | 2 020 | Карпова Ю.А. | 8(495)-982-76-43 gosstat.gprf@genproc.ru | 2 020 | январь-июнь | человек | РФ | Российская Федерация | 2 099 |
| 1 743 580 | 1 479 | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | 2020-12-10 05:04:49 | 2020-08-27 15:50:08 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru | 2 016 | январь-декабрь | единица | РФ | Российская Федерация | 10 907 |
| 1 743 579 | 1 479 | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | 2020-12-10 05:04:49 | 2020-08-27 15:50:08 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru | 2 016 | январь-июнь | единица | РФ | Российская Федерация | 7 165 |
| 1 743 582 | 1 479 | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | 2020-12-10 05:04:49 | 2020-08-27 15:50:08 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru | 2 017 | январь-декабрь | единица | РФ | Российская Федерация | 13 122 |
| 1 743 581 | 1 479 | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | 2020-12-10 05:04:49 | 2020-08-27 15:50:08 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru | 2 017 | январь-июнь | единица | РФ | Российская Федерация | 7 179 |
| 1 743 583 | 1 479 | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | 2020-12-10 05:04:49 | 2020-08-27 15:50:08 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru | 2 018 | I полугодие | единица | РФ | Российская Федерация | 7 193 |
| 1 743 584 | 1 479 | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | 2020-12-10 05:04:49 | 2020-08-27 15:50:08 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru | 2 018 | январь-декабрь | единица | РФ | Российская Федерация | 12 770 |
| 1 743 585 | 1 479 | Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). | 2020-12-10 05:04:49 | 2020-08-27 15:50:08 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 016 | 2 020 | Карпова Ю.А. | (495)-982-76-43 gosstat.gprf@genproc.ru | 2 018 | январь-декабрь | человек | РФ | Российская Федерация | 12 770 |
| 1 676 937 | 1 419 | Количество лиц, совершивших преступления | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. | 2020-12-10 05:16:15 | 2020-08-27 15:48:12 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 011 | 2 020 | Карпова Ю.А. | 8-495-982-76-43 gosstat.gprf@genproc.ru | 2 011 | I полугодие | человек | РФ | Российская Федерация | 7 098 |
| 1 676 939 | 1 419 | Количество лиц, совершивших преступления | По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности | Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. | 2020-12-10 05:16:15 | 2020-08-27 15:48:12 | 2021-03-31 21:00:00 | Генеральная Прокуратура Российской Федерации | Главное управление правовой статистики и информационных технологий | 2 011 | 2 020 | Карпова Ю.А. | 8-495-982-76-43 gosstat.gprf@genproc.ru | 2 011 | январь-декабрь | человек | РФ | Российская Федерация | 11 978 |
Наконец, поскольку представление all_data довольно тяжеловесное, так как серверу необходимо выполнить множество вложенных запросов, я создал «облегченный» вариант — представление data_lite, в котором гораздо меньше выводимых полей.
| select * from data_lite where year > 0 limit 10 | ||||||||
|---|---|---|---|---|---|---|---|---|
| id | ds_id | dataset | year | release | unit | code | category | value |
| 3 568 133 | 3 637 | Пассажирооборот по видам транспорта общего пользования | 1 970 | миллиард пассажиро-километров | Вид транспорта | Автобусный | 100,09999847 | |
| 364 291 | 307 | Грузооборот по видам транспорта | 1 970 | миллиард тонно-километров | Вид транспорта | Автомобильный транспорт | 116 | |
| 2 441 822 | 2 689 | Перевозки грузов по видам транспорта | 1 970 | миллион тонн | Вид транспорта | Автомобильный транспорт | 7 853 | |
| 364 292 | 307 | Грузооборот по видам транспорта | 1 970 | миллиард тонно-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 116 | |
| 2 441 823 | 2 689 | Перевозки грузов по видам транспорта | 1 970 | миллион тонн | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 7 853 | |
| 3 568 132 | 3 637 | Пассажирооборот по видам транспорта общего пользования | 1 970 | миллиард пассажиро-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 100,09999847 | |
| 3 568 135 | 3 637 | Пассажирооборот по видам транспорта общего пользования | 1 975 | миллиард пассажиро-километров | Вид транспорта | Автобусный | 157,30000305 | |
| 364 293 | 307 | Грузооборот по видам транспорта | 1 975 | миллиард тонно-километров | Вид транспорта | Автомобильный транспорт | 184 | |
| 2 441 824 | 2 689 | Перевозки грузов по видам транспорта | 1 975 | миллион тонн | Вид транспорта | Автомобильный транспорт | 11 650 | |
| 364 294 | 307 | Грузооборот по видам транспорта | 1 975 | миллиард тонно-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 184 | |
Именно эти представления используются в клиентской части для получения данных из БД. Но для того, чтобы их получить, надо сначала данные загрузить 🙂
Импорт данных в БД
Здесь все должно быть очевидным: на вход функции подаются значения для всех полей таблицы datasets (например, полное название датасета, дата последнего обновления, ФИО ответственного и т.д.) и связанных внешних таблиц (например, название ответственной службы, подразделения, рубрика и т.д.). СУБД последовательно обновляет сначала внешние таблицы, а затем таблицу datasets, добавляя в нее как исходные данные, так и новые значения внешних ключей. Функция возвращает ID добавленного или обновленного датасета.
А вот с функцией add_data немного по-другому: исходные данные в нее передаются как текст в формате JSON, а уже преобразование и извлечение полей данных происходит в самой функции. Это связано, с одной стороны, с желанием упростить вызов функции из клиента (любой клиент может сформировать исходные данные в виде JSON-строки), а с другой стороны, с богатыми возможностями непосредственной работы с JSON в самом Postgres (к слову, у меня версия 13 на локальном хосте).
Итак, все, что теперь надо сделать в клиентской части, — это подготовить исходные данные в формате JSON-строки и передать эту строку на вход функции add_data (вместе с форматом дат, который можно оставить по умолчанию), а она уже сама добавит необходимые данные во все связанные таблицы БД и выдаст количество добавленных записей, ID датасета и ID последнего добавленного наблюдения. Cool! Но что у нас с поиском по базе?
Реализация поиска
Поиск по шаблону
Для обычного параметрического поиска используем стандартные SQL-запросы к представлениям и полагаемся на скорость движка Postgres. Например, посмотрим на пассажирооборот автобусов с 2010 по 2018 гг. по стране, выведя годовые показатели:
| id | ds_id | dataset | year | release | unit | code | category | value |
|---|---|---|---|---|---|---|---|---|
| 2 461 653 | 2 747 | Пассажирооборот автобусов по маршрутам регулярных перевозок | 2 014 | значение показателя за год | тысяча пассажиро-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 120 055 168 |
| 2 461 656 | 2 747 | Пассажирооборот автобусов по маршрутам регулярных перевозок | 2 015 | значение показателя за год | тысяча пассажиро-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 118 061 520 |
| 2 461 658 | 2 747 | Пассажирооборот автобусов по маршрутам регулярных перевозок | 2 016 | значение показателя за год | тысяча пассажиро-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 116 885 384 |
| 2 461 661 | 2 747 | Пассажирооборот автобусов по маршрутам регулярных перевозок | 2 017 | значение показателя за год | тысяча пассажиро-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 116 041 928 |
| 3 637 186 | 3 737 | Пассажирооборот автобусов | 2 017 | значение показателя за год | тысяча пассажиро-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 123 381 632 |
| 2 461 664 | 2 747 | Пассажирооборот автобусов по маршрутам регулярных перевозок | 2 018 | значение показателя за год | тысяча пассажиро-километров | Классификатор объектов административно-территориального деления (ОКАТО) | Российская Федерация | 114 817 920 |
Этот запрос у меня выполняется в среднем за 20 мс. Тот же запрос из представления all_data — за 20-30 мс (то есть примерно за такое же время). Более сложные запросы дают время выполнения все равно менее секунды, что очень хорошо для довольно объемной базы (на настоящее время у меня 1.4 млн. записей в obs).
Но мы же все хорошо знаем ограничения оператора LIKE, не так ли? 🙂 Зависимость от регистра букв, простота шаблонов поиска и т.п… Но у нас же Postgres! С такой-то современной СУБД можно и горы свернуть:
*, REGEXP_MATCH(ES) и т.д.
Для тех же, кто более подробно хочет ознакомиться с шаблонным поисков в PostgreSQL, есть отличная документация на русском языке в переводе российской компании PostgresPro. (Хочу также обратить внимание на интересный блог этой компании на Хабре.)
Хорошо, с параметрическим поиском понятно. А как быть, если мы хотим по всей базе поискать что-то вроде «школы и детские сады» или «автобус трамвай поезд» (то есть так, как мы обычно «гуглим» в сети)? Для этого есть полнотекстовый поиск.
Полнотекстовый поиск
Как говорится в справочной документации, обычный поиск по шаблону не имеет возможности определять словоформы (т.е. не найдет строку со словом «трамваев» при поиске по слову «трамвай»), а также не позволяет упорядочить результаты поиска по релевантности (как это делают сетевые поисковые системы) и потенциально выполняется медленнее из-за отсутствия индексации.
Я не буду здесь вдаваться в подробности и терминологию полнотекстового поиска в Postgres — можете углубиться в эту тему самостоятельно, если вы с ней не очень хорошо знакомы. Мы же сразу перейдем к практике.
Итак, для реализации полнотекстового поиска по базе (наподобие того, что делает Google, Yandex, Bing и иже с ними) во все таблицы, имеющие текстовые поля, добавлено поле search, содержащее поисковые индексированные данные для поиска. Тип данных этого поля — tsvector. Также для всех таких таблиц созданы соответствующие индексы в БД (в Postgres есть два вида поисковых индексов, я использую GIN как наиболее оптимальный). Например, создание индекса для таблицы agencies выглядит так:
Чтобы индексы постоянно обновлялись при обновлении данных в таблицах, используются триггерные функции для соответствующих таблиц, вызываемые при обновлении / добавлении данных. Например, для таблицы datasets определена функция datasets_update_insert:
Обратите внимание на этот пример: поле search обновляется из двух объединенных векторов (индексов) — название датасета и описание методологии, причем первый из них (название) имеет более высокий приоритет поиска (ему присваивается вес «A»). Таким образом, релевантность результатов поиска будет выше для совпадений в поле name, чем для совпадений в поле description. Также обратите внимание на явное указание локали «russian» для функции векторизации (Postgres и так бы справился, применив системную локаль, но так более наглядно и надежно). Внутренние механизмы морфологического парсера действуют исходя из заданной локали (языка), именно поэтому в БД с русскими текстами движок будет «понимать», что «трамвай» и «трамваями» имеют одну основу. А в английском языке таким же образом будут объединены, например, слова «John» и «John’s» или «read» и «reading».
Для упрощения полнотекстовых запросов я реализовал также 4 вспомогательные функции-утилиты:
1. Ищем «школы и детские сады» в датасетах (любые совпадения слов «школа» и словосочетания «детский сад»)
2. Ищем «уголовное дело» и «Верховный Суд» в наблюдениях за 2018 год (показатели за год)
В каждом примере показаны оба варианта: «веб-поиск» (search_*_web) и поиск с ручным форматированием ключевых слов (search_*). Результаты поиска в обоих вариантах будут идентичными.
С точки зрения вывода результатов все эти функции работают также одинаково: к возвращаемым результатам добавляется столбец ranking, который отражает ранг (релевантность) каждого результата в виде числа от 0 до 1 (чем выше, тем более релевантен результат). Результаты возвращаются по умолчанию отсортированными по этому столбцу (по убыванию), так что наиболее релевантные будут сверху.
Конечно, можно обойтись и без этих самописных функций-утилит, выполняя запросы вручную. Это может пригодиться, если требуется дополнительные степени свободы, например, указать конкретно что и как должно быть найдено в определенных таблицах и как объединять и сортировать результаты. Например, выведем наблюдения, для которых в соответствующих датасетах содержится слово «стоимость» (естественно, со всеми возможными словоформами), причем только для Российской Федерации в целом (исключая фразы типа «столица Российской Федерации») и только за весь год:
Кроме перечисленных «фишек», в БД еще есть несколько служебных серверных функций, на которых здесь не будем останавливаться. Во всем остальном это — стандартная реляционная БД Postgres. Конечно, ее можно и дальше оптимизировать: создавать пользовательские домены (т.е. типы данных), внедрять дополнительные функции проверки данных и вешать их на триггеры, но все это уже дело будущего. А пока посмотрим на клиентскую часть.
Клиентская часть
Как я уже говорил, клиент написан на Python (Python 3 или, если быть точнее, Python 3.9). Исходный код (включающий, кстати, и актуальный бэкап БД) доступен на Github.
Фронтэнд
Сразу оговорюсь, что работа над клиентом в части фронтэнда еще ведется. Окончательный вариант пользовательского интерфейса как таковой отсутствует на данный момент. Я пытался реализовать веб-приложение на Flask, но пока мои потуги уперлись в отсутствие опыта веб-разработки 🙂 Тем не менее, наработки доступны в ветке webapp, к которой, мне кажется, я еще вернусь. В идеале я планировал опубликовать полноценное веб-приложение на Heroku или Google App Engine, чтобы люди могли пользоваться без необходимости локального приложения и установки PostgreSQL. Как говорится, мне надо еще поучить «матчасть». Возможно, кто-то из вас сможет предложить какую-то другую идею или самостоятельно форкнуть проект и допилить интерфейс — смотрите сами!
Пока я не собрался силами вернуться к веб-приложению, я решил, что все-таки нужен какой-то минимальный интерфейс для работы с базой. Здесь у меня две основные опции: GUI (графическое приложение на каком-нибудь Qt или даже Tk) и REPL (чистая консоль). Возиться с GUI не не захотелось (памятуя трудоемкость других моих проектов, в частности, генератора кроссвордов на базе Qt5)… Поэтому выбор решился в пользу REPL.
В текущей реализации REPL основан на библиотеке Python Prompt Toolkit, которая поддерживает цветовые схемы консоли (на базе CSS и не только), отображение прогресс-баров, всплывающие диалоги, подсказки в нижней и правой панели консоли и 100500 разных других «плюшек», нужных и не очень. На сегодняшний день REPL работает довольно топорно: логика работы программы основана на иерархическом меню с односимвольными командами, которые заносятся в стек и выполняются на основе гигантского if… elif… else. Пример работы текущей версии консольного интерфейса показан ниже (терминал — ConEmu-Maximus 5 для Windows 64-bit).
Ввод пароля БД при старте приложения
Ввод команд и панель подсказок внизу окна
Последовательный вывод результатов поиска с форматированием
Отображение рубрик датасетов в иерархическом порядке
REPL пока также не завершен и еще допиливается (пока нет импорта / экспорта данных, реализации SQL-запросов и много еще чего), но концепция должна быть понятна.
Бэкэнд
Бэкэнд состоит из 3 модулей:
Модуль rsengine
В модуле rsengine определен единственный класс — Russtat, в задачи которого входит загрузка исходных датасетов и их реестра в формате XML с сайта ЕМИСС, обновление локального реестра датасетов в формате JSON, парсинг и конвертация данных в JSON с опциональным сохранением их в директории проекта (для последующего переноса в БД или иных целей).
Множественные операции (такие как скачивание и процессирование датасетов) выполняются параллельно в нескольких процессах при помощи multiprocessing.Pool, что значительно ускоряет работу. Детали реализации смотрите в исходниках, которые, кстати, я постарался сразу подробно документировать с созданием «человеческой» справки на Doxygen (см. в директории doc). Справка пока, правда, только на английском, но вас же это не смущает? 🙂
Модуль psdb
В модуле psdb определены два класса: класс Psdb, отвечающий за «низкоуровневую» работу с БД PostgreSQL, и унаследованный от него класс Russtatdb, который более конкретно заточен на работу с нашей локальной БД с учетом ее специфики, в частности, определенных в ней серверных функций.
В качестве движка для PostgreSQL используется psycopg2 (де-факто стандартный Python клиент для этой СУБД). В качестве альтернативы я пробовал также подход на основе ORM (PonyORM), но уперся в определенные трудности, связанные с отсутствием поддержки типа tsvector (напомню, это для полнотекстового поиска) в ORM. Любопытные могут посмотреть на мои «старания» в ветке ponyorm. Вообще же я для себя понял, что ORM — это не мое 🙂 Достаточно знания SQL и стандартного питоновского подхода к управлению БД (см. PEP 249, он же DB-API).
В классе Psdb определены, в частности, методы sqlquery и fetch, которые я постарался сделать достаточно мощными для любых запросов и возврата результатов в виде итератора («сырого» курсора БД), списка, словаря или датафрейма pandas.
В классе Russtatdb определены, в основном, методы поиска (по датасетам и наблюдениям), форматированного вывода результатов (в т.ч. для нужд REPL), а также импорта данных в БД (метод add_data). Этот последний метод, по сути, является оберткой вокруг серверной функции с тем же названием, о который мы уже говорили выше. Точно такими же обертками являются методы findin_datasets и findin_data, которые вызывают соответствующие функции поиска по шаблону в БД.
Модуль russtat
В модуле russtat, который содержит точку входа в клиентское приложение (вернее, одну из точек входа, так как другой является repl.py), основной функцией является update_db. Как следует из ее названия, она обновляет данные в БД: сначала при необходимости обновляется реестр датасетов с сайта ЕМИСС, затем скачиваются сами датасеты (можно указать диапазон и возможность пропуска уже существующих датасетов) и импортируются в БД при помощи метода add_data. Это делается, как я уже говорил, в мультипроцессовом режиме, с выводом промежуточных и финального логов (в консоль или указанный файл).
Таким образом, если у вас чистая (только что созданная) БД, то начать необходимо с вызова russtat.py (в аргументы командной строки можно передавать параметры обновления БД, но для чистой БД лучше все оставить по умолчанию, т.е. не передавать параметры).
— название статьи нельзя ругать за чистый кликбейт 🙂
Напоследок еще раз добавлю, что в исходниках присутствует полный бэкап БД со всеми данными (dbbackup.backup), а также SQL-код для создания чистой БД (dbcreate.sql). Вы можете воспользоваться любой из этих опций через стандартный админский интерфейс Postgres (pgAdmin), либо в другом любимом клиенте. В pgAdmin надо создать новую БД (с любым именем), затем вызвать на новой БД контекстное меню и выбрать Restore… Далее указать dbbackup.backup для БД с данными или dbcreate.sql для чистой БД (в этом случае во вкладке Restore Options надо установить флажок «Only schema», т.е. не копировать данные):
Внимание: блокировки!
Апдейт
Поскольку Росстат мой IP заблокировал после обнаружения активности «бота» (хотя нигде на сайте ЕМИСС положений о таком ограничении нет), предупреждение всем, кто хочет использовать этот или иной подобный скрипт. Используйте соответствующие методы обхода IP блокировки.