Хочется поделиться опытом преображения одного представления кода в другой с помощью ETL процесса и графовой базы данных на актуальном в наши дни примере.
Вкратце есть база на mssql сервере есть хранимые процедуры. Есть база на postgres. Есть ETL процесс на Apache Air Flow. Запускаем процесс, по окончании в базе postgres появляются процедуры и данные.
Скажу сразу данный подход не является полным автоматом, который перенесет любую mssql базу на postgres. Это попытка систематизировать подобный переход, разбить его на управляемые небольшие части, которые типизируются и над которыми выполняются преобразования с возможностью контроля результата. Результатом же являются процедуры или функции на postgres.
![Общая схема миграции процедур Общая схема миграции процедур](https://habrastorage.org/webt/lb/z_/h7/lbz_h7j9mpe4gduthzmizmxm9bc.png)
В рамках ETL процесса для миграции процедур используется РДФ граф на базе Apache Jena Fuseki. Общий подход — вся информация о процедурах помещается в граф, классифицируется, добавляются связи между интересующими нас объектами. Затем начинается наращивание графа с помощью выполнения python модулей , с конечной целью построить “create procedure” команду, которая подается на исполнение в postgres на последнем этапе ETL процесса.
Для анализа и визуального контроля используется веб программа, где можно быстро добавлять отчеты и привязывать их к классификационному дереву графа с помощью кнопок. Без данной программы крайне тяжело ориентироваться в информации находящейся в графе.
![Веб программа для анализа данных в графе Веб программа для анализа данных в графе](https://habrastorage.org/webt/yj/d0/ou/yjd0ouxyforox-v_ggoqk8tsfom.png)
Сами отчеты это “select” запросы выполняемые в контексте родительского объекта отображаемые в виде кнопки. Для редактирования классификационного дерева и добавления отчетов используется стандартный инструмент для работы с РДФ графами Protege.
![Protege используется для создания классификационного дерева и отчетов Protege используется для создания классификационного дерева и отчетов](https://habrastorage.org/webt/aj/mz/do/ajmzdofdjuqwbqqihbjxz8ni71o.png)
![Airflow DAG для миграции процедур Airflow DAG для миграции процедур](https://habrastorage.org/webt/2h/zw/8j/2hzw8jbup-nr_qy2u6iakz7j4vc.png)
Два первых шага ETL процесса (“get_src_tables”, “load_src_data”) переносят данные — это стандартный pandas python модуль, перенос не идеален но он создает тестовый набор данных. Остальные шаги относятся к миграции процедур.
Основой переноса является данные из "information_schema" mssql server и планы исполнения хранимых процедур (“execution plan”). Шаг “get_proc_plan” используя сигнатуру процедур вызывает их на исполнение и сохраняет xml файл с планом. Затем это все экспортируется в граф для анализа и миграции.
![Пример фрагмента плана хранимой процедуры в виде отчета веб программы Пример фрагмента плана хранимой процедуры в виде отчета веб программы](https://habrastorage.org/webt/k6/tp/82/k6tp82ecxi1sbmifroqjy9ecivs.png)
Именно разбивка процедуры на части используемая в плане исполнения есть основа миграции. Части плана типизированы к примеру на изображении выше есть “Select”, “COND” или “Update”. Т.е. теперь у нас не один большой текст процедуры, а небольшие куски, которые мы и будем анализировать и преобразовывать.
На уровне базы можно сделать инвентаризацию по типам планов исполнения.
![Отчет по типам и их количеству в планах исполнения на уровне всей базы данных Отчет по типам и их количеству в планах исполнения на уровне всей базы данных](https://habrastorage.org/webt/d2/xp/wt/d2xpwt-jusekcpsisb0lgir-prm.png)
Для создания postgres процедур берется тот же подход , каждой части mssql плана исполнения будет соответствовать postgres часть.
Принцип миграции заключается в следующем: Берется конкретный тип к примеру “Select”, анализируется все случаи применения и выделяются типичные случаи. К примеру в используемой для примера базе я нашел для 4 типа преобразования для типа “select”.
Для каждого типа преобразования пишется обработчик на питоне c помощью модуля sqlparse, который внутри каждой процедуры для частей типа “Select” будет пытаться выполнить соответствующее преобразование если этот случай будет найден.
![Пример фрагмента отчета где с лева mssql часть а с права преобразованная postgres Пример фрагмента отчета где с лева mssql часть а с права преобразованная postgres](https://habrastorage.org/webt/o-/tb/0_/o-tb0_v_qmjh_doshle8wxdepyk.png)
Как упоминал в начале есть контроль исполнения каждого запуска ETL процесса с записью ошибок в тот же граф, где можно в целом отслеживать какие процедуры не создались и какие ошибки были при этом.
![Отчет о запуске ETL процесса и ошибках в процедурах Отчет о запуске ETL процесса и ошибках в процедурах](https://habrastorage.org/webt/y-/us/8u/y-us8utn2eccozt6ocavq3fogha.png)
Обычно при разработке я меняю python модуль относящийся к преобразованию конкретного типа плана исполнения и запускаю последний шаг ETL процесса под названием “prepare_proc” и после его завершения проверяю отчеты об ошибках, сравниваю с предыдущими запусками.
![Пример запуска шага "prepare_proc" после изменений в python модулях Пример запуска шага "prepare_proc" после изменений в python модулях](https://habrastorage.org/webt/ue/qo/o9/ueqoo9yw6cbteppmprtn9kue6_c.png)
В данной публикации я описал принцип работы рамочного процесса для переноса процедур с mssql на postgres. В целом он может быть взят за основу вашего переноса и основное чем предстоит заниматься это анализом ваших процедур с помощью веб программы, выявлением типичных случаев преобразования и дальнейшим написанием парсеров на python sqlparse.
Ролик на ютубе (поставьте HD quality если нечеткая картинка)
Код в git.
dag
Веб программа (Angular)
В дальнейших частях я планирую описать как это все инсталлировать, добавлять отчеты в веб программу и как писать собственные парсеры.
Благодарю за внимание.
Комментарии (4)
Tzimie
00.00.0000 00:00Интересно, но на сложных процедурах все это споткнется
jfi76 Автор
00.00.0000 00:00Спасибо за отклик.
Речь идет не столько о сложности процедур, а о том, что написание парсера для конкретного
случая самая дорогая операция и Вы должны, четко понимать конечную эффективность. Я использую термин покрытие.
Чем больше процедур Вы покроете тем целесообразнее потраченное время.
Наоборот чем сложнее и уникальнее преобразование, тем больше повод подумать написать этот кусок в ручную.
Подход реально используется для систем анализа систем автоматического страхования, которые представляют набор правил на javascript (rule set).
Цель - создания репортов описывающих правила принятия страховых решений в виде таблиц решений (if конструкции преобразуются в таблицы решений) а также нахождение ошибок в правилах принятия решений. Наборы разные содержат тысячи правил, нигде нет 100% покрытия.
Но покрытие 70-80% вполне достижимо.
Т.е. к примеру часть процедур не будет до конца рабочими и Вы будете знать эти места.
k-semenenkov
00.00.0000 00:00Может кому пригодится для проверки результатов переноса - Windows GUI для сравнения и синхронизации:
https://ksdbmerge.tools/cross-dbms-diff-merge-overview.html
есть бесплатная версия которая позволяет сравнить две произвольные таблицы между MSSQL и постгресом, настроить мапинг если имена таблицы и/или полей не совпадают (я автор)
nnstepan
Очень интересно, с нетерпением жду следующих частей