По роду деятельности (автоматизация процессов и разработка архитектуры информационных систем) часто приходится сталкиваться с необходимостью написать скрипт и получить результат «здесь и сейчас» для неожиданно «прилетевшей» задачи в ситуации, когда нет возможности оперативно привлечь внешних разработчиков.
Решению одной из таких задач будет посвящен обзор. В какой-то момент появилась необходимость проанализировать на основе открытых данных “Единого реестра субъектов малого и среднего предпринимательства” Федеральной налоговой службы (далее Реестр МСП) динамику по месяцам количества организаций определенного вида деятельности, а именно, сельхозпредприятий. Подходы, которые использовались при ее решении, надеюсь будут полезны тем, кто ищет варианты обработки больших структурированных массивов данных 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 получаем следующий результат:
