Хочется поделиться опытом преображения одного представления кода в другой с помощью ETL процесса и графовой базы данных на актуальном в наши дни примере.
Вкратце есть база на mssql сервере есть хранимые процедуры. Есть база на postgres. Есть ETL процесс на Apache Air Flow. Запускаем процесс, по окончании в базе postgres появляются процедуры и данные.
Скажу сразу данный подход не является полным автоматом, который перенесет любую mssql базу на postgres. Это попытка систематизировать подобный переход, разбить его на управляемые небольшие части, которые типизируются и над которыми выполняются преобразования с возможностью контроля результата. Результатом же являются процедуры или функции на postgres.
Общая схема миграции процедур
В рамках ETL процесса для миграции процедур используется РДФ граф на базе Apache Jena Fuseki. Общий подход — вся информация о процедурах помещается в граф, классифицируется, добавляются связи между интересующими нас объектами. Затем начинается наращивание графа с помощью выполнения python модулей , с конечной целью построить “create procedure” команду, которая подается на исполнение в postgres на последнем этапе ETL процесса.
Для анализа и визуального контроля используется веб программа, где можно быстро добавлять отчеты и привязывать их к классификационному дереву графа с помощью кнопок. Без данной программы крайне тяжело ориентироваться в информации находящейся в графе.
Веб программа для анализа данных в графе
Сами отчеты это “select” запросы выполняемые в контексте родительского объекта отображаемые в виде кнопки. Для редактирования классификационного дерева и добавления отчетов используется стандартный инструмент для работы с РДФ графами Protege.
Protege используется для создания классификационного дерева и отчетов
Airflow DAG для миграции процедур
Два первых шага ETL процесса (“get_src_tables”, “load_src_data”) переносят данные — это стандартный pandas python модуль, перенос не идеален но он создает тестовый набор данных. Остальные шаги относятся к миграции процедур.
Основой переноса является данные из "information_schema" mssql server и планы исполнения хранимых процедур (“execution plan”). Шаг “get_proc_plan” используя сигнатуру процедур вызывает их на исполнение и сохраняет xml файл с планом. Затем это все экспортируется в граф для анализа и миграции.
Пример фрагмента плана хранимой процедуры в виде отчета веб программы
Именно разбивка процедуры на части используемая в плане исполнения есть основа миграции. Части плана типизированы к примеру на изображении выше есть “Select”, “COND” или “Update”. Т.е. теперь у нас не один большой текст процедуры, а небольшие куски, которые мы и будем анализировать и преобразовывать.
На уровне базы можно сделать инвентаризацию по типам планов исполнения.
Отчет по типам и их количеству в планах исполнения на уровне всей базы данных
Для создания postgres процедур берется тот же подход , каждой части mssql плана исполнения будет соответствовать postgres часть.
Принцип миграции заключается в следующем: Берется конкретный тип к примеру “Select”, анализируется все случаи применения и выделяются типичные случаи. К примеру в используемой для примера базе я нашел для 4 типа преобразования для типа “select”.
Для каждого типа преобразования пишется обработчик на питоне c помощью модуля sqlparse, который внутри каждой процедуры для частей типа “Select” будет пытаться выполнить соответствующее преобразование если этот случай будет найден.
Пример фрагмента отчета где с лева mssql часть а с права преобразованная postgres
Как упоминал в начале есть контроль исполнения каждого запуска ETL процесса с записью ошибок в тот же граф, где можно в целом отслеживать какие процедуры не создались и какие ошибки были при этом.
Отчет о запуске ETL процесса и ошибках в процедурах
Обычно при разработке я меняю python модуль относящийся к преобразованию конкретного типа плана исполнения и запускаю последний шаг ETL процесса под названием “prepare_proc” и после его завершения проверяю отчеты об ошибках, сравниваю с предыдущими запусками.
Пример запуска шага "prepare_proc" после изменений в python модулях
В данной публикации я описал принцип работы рамочного процесса для переноса процедур с mssql на postgres. В целом он может быть взят за основу вашего переноса и основное чем предстоит заниматься это анализом ваших процедур с помощью веб программы, выявлением типичных случаев преобразования и дальнейшим написанием парсеров на python sqlparse.
habr.com
Вкратце есть база на mssql сервере есть хранимые процедуры. Есть база на postgres. Есть ETL процесс на Apache Air Flow. Запускаем процесс, по окончании в базе postgres появляются процедуры и данные.
Скажу сразу данный подход не является полным автоматом, который перенесет любую mssql базу на postgres. Это попытка систематизировать подобный переход, разбить его на управляемые небольшие части, которые типизируются и над которыми выполняются преобразования с возможностью контроля результата. Результатом же являются процедуры или функции на postgres.
Общая схема миграции процедур
В рамках ETL процесса для миграции процедур используется РДФ граф на базе Apache Jena Fuseki. Общий подход — вся информация о процедурах помещается в граф, классифицируется, добавляются связи между интересующими нас объектами. Затем начинается наращивание графа с помощью выполнения python модулей , с конечной целью построить “create procedure” команду, которая подается на исполнение в postgres на последнем этапе ETL процесса.
Для анализа и визуального контроля используется веб программа, где можно быстро добавлять отчеты и привязывать их к классификационному дереву графа с помощью кнопок. Без данной программы крайне тяжело ориентироваться в информации находящейся в графе.
Веб программа для анализа данных в графе
Сами отчеты это “select” запросы выполняемые в контексте родительского объекта отображаемые в виде кнопки. Для редактирования классификационного дерева и добавления отчетов используется стандартный инструмент для работы с РДФ графами Protege.
Protege используется для создания классификационного дерева и отчетов
Airflow DAG для миграции процедур
Два первых шага ETL процесса (“get_src_tables”, “load_src_data”) переносят данные — это стандартный pandas python модуль, перенос не идеален но он создает тестовый набор данных. Остальные шаги относятся к миграции процедур.
Основой переноса является данные из "information_schema" mssql server и планы исполнения хранимых процедур (“execution plan”). Шаг “get_proc_plan” используя сигнатуру процедур вызывает их на исполнение и сохраняет xml файл с планом. Затем это все экспортируется в граф для анализа и миграции.
Пример фрагмента плана хранимой процедуры в виде отчета веб программы
Именно разбивка процедуры на части используемая в плане исполнения есть основа миграции. Части плана типизированы к примеру на изображении выше есть “Select”, “COND” или “Update”. Т.е. теперь у нас не один большой текст процедуры, а небольшие куски, которые мы и будем анализировать и преобразовывать.
На уровне базы можно сделать инвентаризацию по типам планов исполнения.
Отчет по типам и их количеству в планах исполнения на уровне всей базы данных
Для создания postgres процедур берется тот же подход , каждой части mssql плана исполнения будет соответствовать postgres часть.
Принцип миграции заключается в следующем: Берется конкретный тип к примеру “Select”, анализируется все случаи применения и выделяются типичные случаи. К примеру в используемой для примера базе я нашел для 4 типа преобразования для типа “select”.
Для каждого типа преобразования пишется обработчик на питоне c помощью модуля sqlparse, который внутри каждой процедуры для частей типа “Select” будет пытаться выполнить соответствующее преобразование если этот случай будет найден.
Пример фрагмента отчета где с лева mssql часть а с права преобразованная postgres
Как упоминал в начале есть контроль исполнения каждого запуска ETL процесса с записью ошибок в тот же граф, где можно в целом отслеживать какие процедуры не создались и какие ошибки были при этом.
Отчет о запуске ETL процесса и ошибках в процедурах
Обычно при разработке я меняю python модуль относящийся к преобразованию конкретного типа плана исполнения и запускаю последний шаг ETL процесса под названием “prepare_proc” и после его завершения проверяю отчеты об ошибках, сравниваю с предыдущими запусками.
Пример запуска шага "prepare_proc" после изменений в python модулях
В данной публикации я описал принцип работы рамочного процесса для переноса процедур с mssql на postgres. В целом он может быть взят за основу вашего переноса и основное чем предстоит заниматься это анализом ваших процедур с помощью веб программы, выявлением типичных случаев преобразования и дальнейшим написанием парсеров на python sqlparse.
ETL процесс для миграции процедур с mssql на postgreSQL. Часть 1. Введение
Хочется поделиться опытом преображения одного представления кода в другой с помощью ETL процесса и графовой базы данных на актуальном в наши дни примере. Вкратце есть база на mssql сервере есть...
habr.com