Привет, Хабр! Миграция баз данных из одной СУБД в другую представляет собой особо сложную задачу, требующую тщательного планирования, подготовки и исполнения. В 2023 году в Сбербанке завершился масштабный проект по миграции крупного аналитического хранилища данных с платформы Teradata на GreenPlum. Особое внимание уделялось таким направлениям, как:
Повышенная трудоёмкость ручного переписывания SQL‑скриптов из‑за разницы в диалекте и специфических встроенных функций Teradata.
Перенос архива данных из Teradata, объемом более 400 Тб.
Кросс‑платформенная проверка качества данных в ходе и после завершения миграции.
Этой статьёй мы открываем небольшую серию, посвящённую технологическим решениям вышеупомянутых сложностей. И начнём с рассмотрения сервиса миграции кода из Teradata в GreenPlum, который автоматизирует и упрощает конвертацию сложных запросов.
Введение
Одной из самых важных и трудозатратных частей миграции является конвертация кода, который используется для работы с данными в Teradata, в код, который будет работать в Greenplum. Сюда входят не только SQL-запросы, но и скрипты, функции, процедуры, отчёты и приложения, которые зависят от данных в Teradata.
Перед тем, как перейти к рассмотрению сервиса миграции кода, давайте рассмотрим, какие трудности и проблемы возникают при конвертации кода из Teradata в Greenplum, и почему это является нетривиальной задачей.
Трудности миграции кода
Миграция кода из одного DWH в другое является сложной задачей по нескольким причинам:
Различия диалектов SQL между разными DWH могут привести к ошибкам при миграции, поскольку синтаксис запросов и операторов может различаться.
Перенос кода может потребовать модификации или замены частей кода.
Несоответствие типов данных.
Для решения этих проблем используются сервисы миграции кода, ручная или полуавтоматическая корректировка. В нашем случае был реализован сервис миграции. Давайте подробнее поговорим о нём, его архитектуре, функциональности и результатах использования.
Сервис миграции кода
Это инструмент, который позволяет автоматически преобразовывать код SQL, написанный для одной базы данных, в код, совместимый с другой базой данных. Таким образом, сервис миграции кода упрощает и ускоряет перенос данных и бизнес-логики из одной системы в другую. Основные функции сервиса:
Конвертация кода из синтаксиса Teradata в синтаксис GreenPlum для таблиц, представлений (view), процедур или произвольного кода.
Конвертация всех объектов схемы или по типу, или по выбранным конкретным объектам.
Автоформатирование конвертированного кода для повышения читаемости.
Конвертация специальных символов, таких как $ и #, в именах таблиц и полей в соответствии с заданными пользователем правилами (в формате регулярных выражений).
Конвертация кода Teradata в объекты Informatica с помощью парсинга XML‑файла.
Проверка согласованности структур объектов Teradata и GreenPlum.
Проверка согласованности процедур и метаданных Teradata и GreenPlum.
Возможность конвертации «на лету»: вставка произвольного кода в окно формы и получение результата одним кликом.
Создание профиля конвертации для сохранения параметров конвертации индивидуально для пользователей.
Сохранение в момент конвертации оригинального скрипта Teradata и результата — скрипта GreenPlum, а также информации о сеансе конвертации (дата, время, имя среды).
Возможность конвертации всех схем сразу в соответствии с выбранным источником.
Просмотр оригинального скрипта Teradata и созданного скрипта GreenPlum непосредственно в интерфейсе с возможностью редактирования.
Возможность скачать результаты конвертации в виде архива.
Архитектура сервиса миграции кода
Сервис разработан в соответствии с MTV-шаблоном (англ. Model, Templates, Views — Модели, Шаблоны, Представление). Это шаблон архитектуры программного обеспечения, который предполагает разделение программы на три слабосвязанных компонента, каждый из которых отвечает за свою область:
Модели: объекты Python, предназначенные для взаимодействия сервиса с базой данных (в нашем случае Teradata, GreenPlum), а именно — добавления, изменения, удаления.
Шаблоны: пользовательский интерфейс сервиса. HTML‑страница, с которой пользователь напрямую взаимодействует через браузер.
Представление: модуль, который интерпретирует действия пользователя, содержит функции обработчика запросов. Содержит часть бизнес‑логики для связи интерфейсной части, баз данных и модуля конвертации. Подготавливает данные перед конвертацией.
Также используется модуль конвертации, предназначенный для преобразования скриптов из одной СУБД в другую. Разработан на Python 3.6. Для разбора структур SQL используется открытая библиотека pyparsing (2.1.4). Для форматирования преобразованного кода используется библиотека sql_formatter.
Код сервиса размещён на выделенном HTTP-сервере Сбербанка. Операционная система — Red Hat Enterprise Linux 8.5. На этом хосте работает WSGI веб-сервер (обеспечивает взаимодействие между Python и веб-сервером). Реализован с использованием библиотеки Gunicorn и фреймворка Django, преобразующих HTTP-запросов в объекты языка Python. Архитектура сервиса миграции кода:
Модуль конвертации
Источником данных является Teradata. Соединение реализовано через библиотеку Python teradatasql. Для хранения настроек сервиса (профилей конвертации), используется среда разработчика — кластер GreenPlum.
Используемая в решении библиотека pyparsing — это библиотека классов Python, которая позволяет быстро и легко создавать рекурсивно-нисходящие парсеры (подробнее об используемой в pyparsing грамматике можно прочитать тут). Сначала определяются основные части грамматики, затем они объединяются в более сложные выражения для различных ветвей полного грамматического синтаксиса. Последовательности допустимых символов языка, несущие некоторую семантическую нагрузку, обычно называются «лексемами». Лексемы, в свою очередь, формируются из отдельных групп символов – «токенов». Затем они объединяются в более сложные выражения для различных ветвей грамматического синтаксиса. Их сочетание возможно благодаря определению отношений, таких как:
Какие выражения должны следовать друг за другом в грамматике, например: «ключевое слово if, после которого идёт логическое выражение».
Какие выражения являются заменителями друг друга в определённом случае в грамматике, например: «SQL‑команда может начинаться со слов SELECT, INSERT, UPDATE или DELETE».
Какие выражения являются необязательными, например: «номер телефона не обязательно начинается с кода региона, заключённого в скобки».
Какие выражения повторяются, например: «открытый тег XML может содержать ноль или более атрибутов».
Про лексемы и токены можно прочитать тут.
Процесс конвертации
Для минимизации возможных изменений в процессе разработки, с учётом зависимости объектов, были введены следующие правила:
Типы данных и размерности переносились «как есть», за исключением представленных в таблице ниже.
Тип данных Teradata
Тип данных GreenPlum
char
text (variable length)
varchar
text (variable length)
decimal
numeric (variable precision)
number
numeric (variable precision)
Одним из недостатков разделов GreenPlum является то, что они требуют регулярного обслуживания, например, добавления новых разделов, удаления старых или реорганизации существующих. Подробнее о разделении можно прочитать тут.
Teradata имеет некоторые отличия от GreenPlum:
Teradata поддерживает раздел DEFAULT, который используется для хранения строк, не соответствующих ни одному из других разделов. Он применяется для обработки нулевых значений NULL или других неожиданных значений в столбце ключа раздела. GreenPlum не имеет раздела DEFAULT, поэтому рекомендуется использовать ограничение NOT NULL для столбца ключа раздела или обрабатывать нулевые значения NULL в определении раздела.
Teradata поддерживает PARTITION BY, что позволяет пользователю указать выражение разделения для таблицы. В GreenPlum PARTITION BY отсутствует, но поддерживается субразделение, что означает, что каждый раздел может быть дополнительно разделён на субразделы по другому ключевому столбцу. Однако рекомендуется ограничивать количество уровней субразделов двумя, так как большее количество уровней может увеличить длительность планирования запроса и накладные расходы на метаданные.
При переносе использовались два типа разбиения: RANGE и LIST. Разбиения типа LIST будут использованы, если на Teradata для них есть условие PARTITION BY.
Ключи распределения GreenPlum, которые определяют, как данные таблицы распределяются по сегментам кластера GreenPlum, совпадают с теми, которые обеспечивают уникальность и целостность данных в Teradata. Также приведём некоторые рекомендации:
Если один столбец не может обеспечить равномерное распределение, то используйте составной ключ распределения не более чем из двух столбцов. Дополнительные столбцы в распределении удлиняют хеширование.
Если составной ключ распределения из двух столбцов не может обеспечить равномерное распределение данных, то используйте случайное распределение. Ключи распределения с несколькими столбцами в большинстве случаев требуют операций перемещения для объединения таблиц, поэтому они не имеют преимуществ перед случайным распределением.
Не рекомендуется использовать в качестве ключей столбцы, которые будут использоваться в условии WHERE в запросах, потому что в этом случае при выполнении запроса часть сегментов будет простаивать, так как будут отфильтрованы строки.
Рекомендуется использовать в качестве ключей столбцы, используемые как ключи разбиения, потому что в этом случае весь раздел будет в одном сегменте.
Подробнее о ключах распределения можно прочитать тут.
Данные в heap-таблицах хранятся в цепочке сегментных файлов по 1 ГБ, разделённых на блоки одинакового размера. В GreenPlum каждый блок занимает 32 КБ. Блоки обрабатываются в бэкенде с помощью буфера блоков в общей памяти (shared buffers). Append only-таблицы подходят для вставки больших объёмов данных, которые редко обновляются. Append only-таблицы не поддерживают операции обновления (UPDATE) и удаления (DELETE) данных в некоторых типах транзакций, а также имеют ограничения на использование индексов и триггеров. Транзакции, изменение данных и индексы осуществляются через дополнительные heap-таблицы. Данные сохраняются в файлах, состоящих из блоков разной длины, которые могут быть сжаты. Поэтому для таблиц в GreenPlum будет применяться способ хранения append only и параметры
compresstype=zstdиcompresslevel=‘1’.
Подробнее о моделях хранения можно прочитать тут.
А теперь рассмотрим создание профиля конвертации в нашем сервисе миграции кода.
Создание профиля конвертации
Установка индивидуальных параметров позволяет повысить качество конвертации и сократить количество ручных исправлений в коде GreenPlum. Пользователь может использовать существующие профили (без возможности редактирования параметров) или создавать собственные.
Описание параметров профиля конвертации:
Таблица соответствия имён схем в Teradata и GreenPlum. Параметр меняет указанное имя схемы источника в Teradata на целевое имя схемы в GreenPlum. Если имена совпадают, параметр не обязателен. Может использоваться фрагмент имени, например,
prd3_1_db_ s_grnplm_as_t_didsd_db_.Шаблоны для определения схем. Параметр влияет на качество определения имён схем и улучшает парсинг объектов. Можно задать имя или конечный фрагмент.
Правила трансформации имен объектов Teradata → GreenPlum. Параметр устанавливает правила трансформации служебных спецсимволов в именах таблиц, представлений и процедур, а также имён атрибутов, полей таблиц. В GreenPlum есть ограничения на использование символов $ и # в имени или атрибуте объекта. Алгоритм поиска символа в имени объекта задаётся в формате регулярного выражения и определяет, на какой символ необходимо поменять.
Прочие настройки
Создание таблицы с опцией append only. Параметр задаётся, если при создании таблицы нужно явно указывать опцию append only.
Тип данных, к которому будет преобразовано значение NULL. Параметр задаётся, если необходимо явное преобразование значения константы NULL к определённому типу. Может быть полезен, когда в коде много конструкций UNION, в которых присутствуют поля с константами NULL. В этом случае GreenPlum требует явно указывать тип константы NULL. Например, если параметр определён как text, то все константы NULL в коде будут иметь вид
NULL::text.Шаблон имени схем на GreenPlum. Параметр влияет на качество парсинга: после того, как на первом шаге конвертации имена схем Teradata заменяются на целевые в GreenPlum, поиск объектов схем выполняется с использованием шаблона имени схемы в GreenPlum. Например, в целевом коде GreenPlum могут быть объекты схем
s_grnplm_as_t_didsd_210_db_tmd,s_grnplm_as_t_didsd_210_db_stgиs_grnplm_as_t_didsd_210_db_dwh. В этом случае в качестве шаблона необходимо указать общий для всех схем фрагмент:s_grnplm_as_t_didsd_.Шаблон имени схем на Teradata. Параметр является опциональным: используется в том случае, если необходимо сконвертировать одновременно объекты нескольких схем по заданной маске имени схемы. Например, требуется за один сеанс сконвертировать объекты схем
PRD3_1_210_DB_TMD,PRD3_1_210_DB_STGиPRD3_1_210_DB_DWH. В этом случае нужно указать значениеPRD3_1_210_.Имя схемы Teradata, которое выбирается по умолчанию. Параметр является опциональным: улучшает качество конвертации произвольного кода. Если при конвертации не удалось определить имя схемы Teradata стандартными методами, то на следующем шаге схема будет искаться по этому параметру. Например,
PRD3_1_210_DB_DWH.
После настройки вводится имя нового профиля и он сохраняется.
Пользовательские сценарии использования интерфейса сервиса
Интерфейс пользователя реализован в виде веб-страницы.
Для конвертации кода объектов со стенда разработки DEV Teradata необходимо:
Выбрать соответствующий пункт меню.
Выбрать созданный ранее профиль конвертации.
Из выпадающего списка стендов выбрать необходимый.
Из выпадающего списка выбрать нужную схему (для упрощения поиска начните вводить фрагмент названия).
Из выпадающего списка выбрать нужный тип объекта (таблица, представление, процедура, все объекты).
Из выпадающего списка выбрать нужные объекты или «Выбрать все».
Нажать кнопку «Выполнить конвертацию». После конвертации всех выбранных объектов появится всплывающее окно с уведомлением. Затем появится кнопка для загрузки архива с результатами конвертации.
Конвертация кода Informatica
Вы можете конвертировать объекты репозитория Informatica в GreenPlum. Для этого необходимо в Informatica штатным способом экспортировать репозиторий в XML-файл. В сервисе миграции:
выбрать пункт «Конвертация кода Informatica»;
выбрать XML‑файл с объектами репозитория.
Затем будет сформирован новый XML‑файл с кодом для GreenPlum. Его необходимо импортировать в Informatica штатным способом.
Конвертация произвольного кода
Для конвертации кода «на лету» необходимо:
выбрать соответствующий пункт меню;
скопировать произвольный код Teradata в окно ввода или загрузить код из файла;
нажать на кнопку «Выполнить конвертацию на GreenPlum».
Полученные результаты
Для рассмотрения результатов конвертации кода возьмём первый пример с функцией QUALIFY. Оригинальный код Teradata:
Конвертированный код для GreenPlum:
В сконвертированном коде представлено использование вложенного запроса, которому дали псевдоним Qualify_1 для замены функции QUALIFY.
В качестве второго примера возьмём код Teradata с использованием псевдонимов (aliases). В Teradata широко используются конструкции с псевдонимами, когда фрагменту кода присваивается имя, которое далее можно использовать в условии WHERE:
В GreenPlum такая конструкция работать не будет, использование псевдонимов в условии WHERE не допускается:
Сервис конвертации кода умеет обходить это ограничение: он автоматически определяет именованные участки кода и помещает оригинальный код в условие WHERE. Конвертированный код для GreenPlum:
Сервис миграции кода из Teradata в GreenPlum может успешно справляться со сложными запросами.
Заключение
В наших условиях благодаря сервису конвертация кода ускорилась в 2 раза, а в 60 % случаев не понадобились какие-либо правки. То есть сервис миграции кода оказался эффективным и удобным инструментом для переноса кода из Teradata в GreenPlum.
Автор: Николай Абрамов, эксперт профессионального сообщества Сбера SberProfi DWH/BigData. Профессиональное сообщество отвечает за развитие компетенций в таких направлениях как экосистема Hadoop, PostgreSQL, GreenPlum, а также BI-инструментах Qlik, Apache SuperSet и др.
