По роду деятельности (автоматизация процессов и разработка архитектуры информационных систем) часто приходится сталкиваться с необходимостью написать скрипт и получить результат «здесь и сейчас» для неожиданно «прилетевшей» задачи в ситуации, когда нет возможности оперативно привлечь внешних разработчиков.
Решению одной из таких задач будет посвящен обзор. В какой-то момент появилась необходимость проанализировать на основе открытых данных “Единого реестра субъектов малого и среднего предпринимательства” Федеральной налоговой службы (далее Реестр МСП) динамику по месяцам количества организаций определенного вида деятельности, а именно, сельхозпредприятий. Подходы, которые использовались при ее решении, надеюсь будут полезны тем, кто ищет варианты обработки больших структурированных массивов данных XML, но распространенные средства обработки такие как SelectFromXML, он-лайн XML обработчики по каким-то причинам не подходят. Либо ограничен функционал, либо возникают проблемы при работе с кириллической кодировкой, либо не обеспечивается необходимая производительность, либо ограничены ресурсы «железа». Программисты и профессионалы надеюсь не буду слишком строги к стилю кодирования и выбору способов реализации, а критика и советы в комментариях приветствуются.
Итак задача:
На февраль 2018 года реестр МСП содержит 18 zip-архивов размером 3-4Gb. Каждый архив содержит около 5-6 тыс. файлов, содержащих сведения о примерно 6 миллионах организаций, общим объемом около 40Gb. Из этого массива требуется отобрать только те, которые относятся к сельхозпредприятиям и проанализировать динамику количества этих предприятия по месяцам.
Исходные файлы ФНС размещены по ссылке
Файлы описания организаций содержат следующую структуру:
Обработка будет выполняться в оболочке bash на виртуальной Linux машине с 2-я ядрами, 8 Gb оперативной памяти и 100Gb дискового пространства:
Скрипт должен обеспечить скачивание zip-архивов с сайта ФНС, переименование файлов для удобства последующей обработки, распаковку, обработку парсером (используется xmlstarlet) для поиска организаций, соответствующих заданных в скрипте критериям, очистку диска от временных файлов (в процессе обработки исходные файлы занимают десятки Gb), сохранение в формате, удобном для последующего использования в системах анализа данных и импорта в программы для работы с электронными таблицами (в нашем случае будет использоваться формат csv).
Скачивание и переименование выполним с использованием wget. Чтобы скрипт понимал, какие архивы с РМСП ему обрабатывать, создадим файл, под условным названием «полетное задание», где укажем, какие файлы обрабатывать и как именовать полученный результат.
Конфигурационный файл имеет следующую структуру:
Ссылка на файл, название результирующего файла, отметка о необходимости обработки '*' (для случаев, если возникает необходимость загрузить не весь набор файлов).
rmspfiles.txt
По завершению скачивания с переименованием запускается цикл по полученным архивам: unzip, поиск в XML нужных записей, запись результата в csv файлы. Перед обработкой следующего архива очищается место на диске от исходных файлов.
Не смотря на простоту задачи, скрипт пройдя этапы отладки и совершенствования получился достаточно замысловатым.
Итак, что получилось в итоге:
Загрузчик файлов:
2. Парсер
Весь массив данных из 18 файлов общим объемом в сотни Gb обрабатывается около 6 часов.
Процесс обработки записывается в файлы для последующей отладки и оптимизации скрипта.
После импорта в MS Excel получаем следующий результат:
Решению одной из таких задач будет посвящен обзор. В какой-то момент появилась необходимость проанализировать на основе открытых данных “Единого реестра субъектов малого и среднего предпринимательства” Федеральной налоговой службы (далее Реестр МСП) динамику по месяцам количества организаций определенного вида деятельности, а именно, сельхозпредприятий. Подходы, которые использовались при ее решении, надеюсь будут полезны тем, кто ищет варианты обработки больших структурированных массивов данных XML, но распространенные средства обработки такие как SelectFromXML, он-лайн XML обработчики по каким-то причинам не подходят. Либо ограничен функционал, либо возникают проблемы при работе с кириллической кодировкой, либо не обеспечивается необходимая производительность, либо ограничены ресурсы «железа». Программисты и профессионалы надеюсь не буду слишком строги к стилю кодирования и выбору способов реализации, а критика и советы в комментариях приветствуются.
Итак задача:
На февраль 2018 года реестр МСП содержит 18 zip-архивов размером 3-4Gb. Каждый архив содержит около 5-6 тыс. файлов, содержащих сведения о примерно 6 миллионах организаций, общим объемом около 40Gb. Из этого массива требуется отобрать только те, которые относятся к сельхозпредприятиям и проанализировать динамику количества этих предприятия по месяцам.
Исходные файлы ФНС размещены по ссылке
Файлы описания организаций содержат следующую структуру:
<Файл ИдФайл="VO_RRMSPSV_0000_9965_20170110_01b07970-41d2-4d1e-bb80-0abee395d333" ВерсФорм="4.01" ТипИнф="РЕЕСТРМСП" КолДок="900"> <ИдОтпр> <ФИООтв Фамилия="-" Имя="-"/> </ИдОтпр> <Документ ИдДок="4e28d9a9-c004-0f72-a27d-7d677620df81" ДатаСост="10.01.2017" ДатаВклМСП="01.08.2016" ВидСубМСП="2" КатСубМСП="1" ПризНовМСП="2"> <ИПВклМСП ИННФЛ="636204531704"> <ФИОИП Фамилия="МАРЫШЕВ" Имя="ВЯЧЕСЛАВ" Отчество="ВЛАДИМИРОВИЧ"/> </ИПВклМСП> <СведМН КодРегион="63"> <Регион Тип="ОБЛАСТЬ" Наим="САМАРСКАЯ"/> <Район Тип="РАЙОН" Наим="БЕЗЕНЧУКСКИЙ"/> <НаселПункт Тип="УЛИЦА" Наим="СОВЕТСКАЯ"/> </СведМН> <СвОКВЭД> <СвОКВЭДОсн КодОКВЭД="42.21" НаимОКВЭД="Строительство инженерных коммуникаций для водоснабжения и водоотведения, газоснабжения" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="52.21.2" НаимОКВЭД="Деятельность вспомогательная, связанная с автомобильным транспортом" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="74.30" НаимОКВЭД="Деятельность по письменному и устному переводу" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="63.91" НаимОКВЭД="Деятельность информационных агентств" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="95.23" НаимОКВЭД="Ремонт обуви и прочих изделий из кожи" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="42.21" НаимОКВЭД="Строительство инженерных коммуникаций для водоснабжения и водоотведения, газоснабжения" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="62.09" НаимОКВЭД="Деятельность, связанная с использованием вычислительной техники и информационных технологий, прочая" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="25.72" НаимОКВЭД="Производство замков и петель" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="47.54" НаимОКВЭД="Торговля розничная бытовыми электротоварами в специализированных магазинах" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="42.22.1" НаимОКВЭД="Строительство междугородних линий электропередачи и связи" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="47.99" НаимОКВЭД="Торговля розничная прочая вне магазинов, палаток, рынков" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="82.19" НаимОКВЭД="Деятельность по фотокопированию и подготовке документов и прочая специализированная вспомогательная деятельность по обеспечению деятельности офиса" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="49.32" НаимОКВЭД="Деятельность такси" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="42.22.2" НаимОКВЭД="Строительство местных линий электропередачи и связи" ВерсОКВЭД="2014"/> </СвОКВЭД> </Документ> <Документ ИдДок="7a14e521-68a3-9514-7540-04cb03799ac4" ДатаСост="10.01.2017" ДатаВклМСП="10.09.2016" ВидСубМСП="2" КатСубМСП="1" ПризНовМСП="1"> <ИПВклМСП ИННФЛ="636204538611"> <ФИОИП Фамилия="РУЧКАНОВА" Имя="ЛЮДМИЛА" Отчество="АЛЕКСЕЕВНА"/> </ИПВклМСП> <СведМН КодРегион="63"> <Регион Тип="ОБЛАСТЬ" Наим="САМАРСКАЯ"/> <Район Тип="РАЙОН" Наим="БЕЗЕНЧУКСКИЙ"/> <НаселПункт Тип="УЛИЦА" Наим="МОЛОДЕЖНАЯ"/> </СведМН> <СвОКВЭД> <СвОКВЭДОсн КодОКВЭД="47.11" НаимОКВЭД="Торговля розничная преимущественно пищевыми продуктами, включая напитки, и табачными изделиями в неспециализированных магазинах" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="47.25.12" НаимОКВЭД="Торговля розничная пивом в специализированных магазинах" ВерсОКВЭД="2014"/> </СвОКВЭД> </Документ> <Документ ИдДок="ad8636bb-78c3-763c-52d2-4fe5a93e9a8f" ДатаСост="10.01.2017" ДатаВклМСП="10.09.2016" ВидСубМСП="2" КатСубМСП="1" ПризНовМСП="1"> <ИПВклМСП ИННФЛ="636204540794"> <ФИОИП Фамилия="МИЧУРОВА" Имя="ТАТЬЯНА" Отчество="АЛЕКСАНДРОВНА"/> </ИПВклМСП> <СведМН КодРегион="63"> <Регион Тип="ОБЛАСТЬ" Наим="САМАРСКАЯ"/> <Город Тип="ГОРОД" Наим="САМАРА"/> <НаселПункт Тип="УЛИЦА" Наим="ВЛАДИМИРСКАЯ"/> </СведМН> <СвОКВЭД> <СвОКВЭДОсн КодОКВЭД="47.41" НаимОКВЭД="Торговля розничная компьютерами, периферийными устройствами к ним и программным обеспечением в специализированных магазинах" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="49.20.9" НаимОКВЭД="Перевозка прочих грузов" ВерсОКВЭД="2014"/> <СвОКВЭДДоп КодОКВЭД="47.78" НаимОКВЭД="Торговля розничная прочая в специализированных магазинах" ВерсОКВЭД="2014"/> </СвОКВЭД> </Документ>
Обработка будет выполняться в оболочке bash на виртуальной Linux машине с 2-я ядрами, 8 Gb оперативной памяти и 100Gb дискового пространства:
%Cpu0 : 6.1 us, 2.0 sy, 0.0 ni, 91.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu1 : 54.1 us, 11.2 sy, 0.0 ni, 6.1 id, 28.6 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 8258760 total, 64684 free, 5645284 used, 2548792 buff/cache KiB Swap: 2129916 total, 1157076 free, 972840 used. 2271428 avail Mem
Скрипт должен обеспечить скачивание zip-архивов с сайта ФНС, переименование файлов для удобства последующей обработки, распаковку, обработку парсером (используется xmlstarlet) для поиска организаций, соответствующих заданных в скрипте критериям, очистку диска от временных файлов (в процессе обработки исходные файлы занимают десятки Gb), сохранение в формате, удобном для последующего использования в системах анализа данных и импорта в программы для работы с электронными таблицами (в нашем случае будет использоваться формат csv).
Скачивание и переименование выполним с использованием wget. Чтобы скрипт понимал, какие архивы с РМСП ему обрабатывать, создадим файл, под условным названием «полетное задание», где укажем, какие файлы обрабатывать и как именовать полученный результат.
Конфигурационный файл имеет следующую структуру:
Ссылка на файл, название результирующего файла, отметка о необходимости обработки '*' (для случаев, если возникает необходимость загрузить не весь набор файлов).
rmspfiles.txt
http://data.nalog.ru/opendata/7707329152-rsmp/data-08262016-structure-08012016.zip;20160826;* http://data.nalog.ru/opendata/7707329152-rsmp/data-09102016-structure-08012016.zip;20160910;* http://data.nalog.ru/opendata/7707329152-rsmp/data-10102016-structure-08012016.zip;20161010;* http://data.nalog.ru/opendata/7707329152-rsmp/data-11252016-structure-08012016.zip;20161125;* http://data.nalog.ru/opendata/7707329152-rsmp/data-12122016-structure-08012016.zip;20161212;* http://data.nalog.ru/opendata/7707329152-rsmp/data-01112017-structure-08012016.zip;20170111;* http://data.nalog.ru/opendata/7707329152-rsmp/data-02102017-structure-08012016.zip;20170212;* http://data.nalog.ru/opendata/7707329152-rsmp/data-03102017-structure-08012016.zip;20170310;* http://data.nalog.ru/opendata/7707329152-rsmp/data-04102017-structure-08012016.zip;20170410;* http://data.nalog.ru/opendata/7707329152-rsmp/data-05102017-structure-08012016.zip;20170510 http://data.nalog.ru/opendata/7707329152-rsmp/data-11062017-structure-08012016.zip;20170611 http://data.nalog.ru/opendata/7707329152-rsmp/data-07102017-structure-08012016.zip;20170710 http://data.nalog.ru/opendata/7707329152-rsmp/data-08102017-structure-08012016.zip;20170810 http://data.nalog.ru/opendata/7707329152-rsmp/data-09112017-structure-08012016.zip;20170911 http://data.nalog.ru/opendata/7707329152-rsmp/data-10102017-structure-08012016.zip;20171010 http://data.nalog.ru/opendata/7707329152-rsmp/data-11102017-structure-08012016.zip;20171110 http://data.nalog.ru/opendata/7707329152-rsmp/data-12112017-structure-08012016.zip;20171211 http://data.nalog.ru/opendata/7707329152-rsmp/data-01112018-structure-08012016.zip;20180111
По завершению скачивания с переименованием запускается цикл по полученным архивам: unzip, поиск в XML нужных записей, запись результата в csv файлы. Перед обработкой следующего архива очищается место на диске от исходных файлов.
Не смотря на простоту задачи, скрипт пройдя этапы отладки и совершенствования получился достаточно замысловатым.
Итак, что получилось в итоге:
Загрузчик файлов:
#!/bin/bash # **************** batch downloader from rmsp v 1.0. 2018-02-15 *********************** start=`date +%s` dt=`date` logFn='output_wget.log' printf "********************************************************************************************\n" | tee tmp_output.log echo "* ${dt} wget *" | tee -a tmp_output.log printf "*********************************************************************************************\n\n" | tee -a tmp_output.log # download loop считываем файлы по ссылкам из “полетного задания”, переименовываем и сохраняем в папке zip2 IFS=';' while read line; do read -r -a array <<< "$line" echo "${array[0]} | ${array[1]} " # wget ${array[0]} -O ./zip2/${array[1]}.zip | tee -a tmp_output.log 2>&1 # get filesize of external - этот параметр пишется в лог для оценки производительности обработчика FILESIZE=$(wget --spider ${array[0]} 2>&1 | awk '/Length/ {print $2}') # - c - continue, 3>&1 - размер файла wget -c ${array[0]} -O ./zip2/${array[1]}.zip 3>&1 | tee -a tmp_output.log end=`date +%s`; runtime=$((end-start)); dt=`date '+%Y-%m-%d %H:%M:%S'` printf "%s %4d sec %10d %s [ %s" ] ${dt} $runtime $FILESIZE ${array[0]} ${array[1]} | tee -a tmp_output.log done < rmspfiles.txt echo "" | tee -a tmp_output.log //записываем в файл для последующей отладки результаты работы cat tmp_output.log $logFn > tmp_output2.log; mv tmp_output2.log $logFn
2. Парсер
#!/bin/bash # 2018-02-16 Версия 1.1 Добавлены столбцы в итоговый файл # 2018-02-19 Добавлены кавычки для предотвращение переноса строки в номерах лицензий в excell # 2018-02-19 Добавлен sed для замены /n -> ; @@; -> \n # удалены для лицензий кавычки # задаем разделитель колонок для итоговых файлов (в нашем случае табуляция) sp=' ' # Задаем параметры обработчика, пути для исходных и результирующих файлов, названия файлов для журналов обработки. path_src="./src" path_zip="./zip2" path_res="./res" t1="p1.log" t2="p2.log" t3="parsz.log" fnExt=""$1 start=`date +%s` dt=`date '+%Y-%m-%d %H:%M:%S'` # Результат выводим в лог echo "**** | parsz | ${dt} unzip from: $path_zip/$fnExt.zip to $path_src/$fnExt" # | tee $t1 # -q quiet mode (-qq => quieter) # -o overwrite files WITHOUT prompting # -j junk paths. The archive's directory structure is not recreated; all files are deposited in the extraction directory (by default, the current one). unzip -j -q -o $path_zip/$fnExt.zip -d $path_src/$fnExt/ end=`date +%s` runtime=$((end-start)) MOREF1=`ls "$path_src/$fnExt/" | wc -l` echo " ${dt}, $runtime sec [${MOREF1}] | files from: $path_src/$fnExt/ to $path_res/$fnExt.csv" | tee -a $t1 echo "ИНН$spНаименование МСП\ $spКатегория МСП\ $spВид МСП\ $spВид Деятельности (Основной ОКВЭД)\ $spРегионНаим\ $spРайонТип\ $spРайонНаим\ $spгородТип\ $spгородНаим\ $spНаселПунктТип\ $spНаселПунктНаим\ $spДатаСост\ $spДатаВключения\ $spНомерЛицензии\ $spФайлИмя@@\ " > $path_res/res-$fnExt.csv /usr/bin/find $path_src/$fnExt/ -name "*.xml" | xargs -n1 xmlstarlet sel -T -f -t -m "//Документ/ОргВклМСП[contains(@НаимОрг,'СЕЛЬСКОХОЗЯЙСТВЕНН')]" \ -v "@ИННЮЛ" -o "$sp" \ -v "@НаимОрг" -o "$sp" \ --if "../@КатСубМСП=1" -o "Микро" --else --if "../@КатСубМСП=2" -o "Малые" --else -o "Средние" --break --break -o "$sp" \ --if "../@ВидСубМСП = 1" -o "Организация" --else -o "ИП" --break -o "$sp[" \ -v "../СвОКВЭД/СвОКВЭДОсн/@КодОКВЭД" -o "]" \ -v "../СвОКВЭД/СвОКВЭДОсн/@НаимОКВЭД" -o "$sp" \ -v "../СведМН/Регион/@Наим" -o "$sp" \ -v "../СведМН/Район/@Тип" -o "$sp" \ -v "../СведМН/Район/@Наим" -o "$sp" \ -v "../СведМН/Город/@Тип" -o "$sp" \ -v "../СведМН/Город/@Наим" -o "$sp" \ -v "../СведМН/НаселПункт/@Тип" -o "$sp" \ -v "../СведМН/НаселПункт/@Наим" -o "$sp" \ -v "../@ДатаСост" -o "$sp" \ -v "../@ДатаВклМСП" -o "$sp" \ -v "../СвЛиценз/@НомЛиценз" -o "$sp" \ -o "$fnExt@@" \ -n >> $path_res/res-$fnExt.csv end=`date +%s` runtime=$((end-start)) dt=`date '+%Y-%m-%d %H:%M:%S'` echo " ${dt}, $runtime sec :parsing" | tee -a $t1 # Удаляем переносы строк в значениях за исключением последних в строках sed -e ':a;N;$!ba;s/\n/;/g' $path_res/res-$fnExt.csv > $path_res/sed_tmp.csv sed -e 's/@@;/\n/g' $path_res/sed_tmp.csv > $path_res/res-$fnExt.csv end=`date +%s` runtime=$((end-start)) dt=`date '+%Y-%m-%d %H:%M:%S'` echo " ${dt}, $runtime sec :sed " | tee -a $t1 cat $t1 $t3 > $t2; mv $t2 $t3 # удаляем исходные XML файлы rm -rf $path_src/$fnExt/* echo "Удаляем исходные XML файлы rm -rf $path_src/$fnExt/*" rm $t1
Весь массив данных из 18 файлов общим объемом в сотни Gb обрабатывается около 6 часов.
Процесс обработки записывается в файлы для последующей отладки и оптимизации скрипта.
После импорта в MS Excel получаем следующий результат: