Трансляция графических HANA Calculation View в SQL, или как переехать так, чтобы ничего не поменялось
Повинуясь всеобщей тенденции и следуя духу времени, мы в Норникеле переводим расчёты нашего хранилища на уровень БД. Так мы ускоряем обработку данных и формирование отчётности, да и система работает стабильнее.
Но пока мы оптимизируем и переносим «классический» код в базу, запрашиваемые заказчиком алгоритмы усложняются, а обрабатываемых данных становится больше. Чтобы склонить чашу весов на нашу сторону в этом извечном противостоянии мы решили применить новый метод!
Приветствую! Я — эксперт команды поддержки и развития корпоративного хранилища данных. В 2019 году нами было принято волевое решение нарушить баланс в силе: перевести часть самых тяжёлых расчётов на новый для нас тип СУБД на графических ускорителях. Так получилось, что этот переезд пока не случился в полном объёме, но мы собрали уникальный опыт, который позволит рассказать о том, как качественно переехать с HANA на любой диалект SQL. Если вы хотите мигрировать с немецкой in-memory, на, например, идейно верный в текущих реалиях PostgreSQL, но не понимаете, как именно это сделать — читайте под катом.
Трудности на старте
В первую очередь нам предстояло выбрать СУБД. Раньше мы не использовали базы на графических ускорителях, так что нам пришлось рассмотреть много вариантов и с большим трудом выбрать одного финалиста. В ходе долгой дискуссии и короткой драки мы остановились на SQreamDB. Критерии отбора и процесс принятия решения выходят за рамки этой статьи, но — было весело.
Действительно сложные расчёты
В отдельный блокнотик мы выписали задачи, которые собрались взвалить на новую СУБД. Список оказался большим, и тут появились сомнения:, а справится ли?
Посудите сами.
Большинство расчётов уровня БД, размещённых в нашей HANA, реализовано с помощью т.н. графических представлений — Graphical Calculation View. (Далее будем называть этот тип просто CV, сивишки или View.) Такие представления содержат программу расчёта в виде графа со следующими типами узлов-операций:
проекция — аналог простого select в SQL;
агрегация — тот же select, но с group by и функциями агрегации для заданных полей в SQL;
join — кроме классических inner, outer, left, right join умеет также text join и referential join;
union — как простой union в SQL, без intersect или except;
rank — единственная оконная функция из мира SQL.
Источниками данных могут выступать разные объекты БД: таблицы, представления и другие Calculation View. Т.е. можно строить иерархическую систему расчётов — аналог предиката with в SQL. Стоит отметить, что некоторые из реализованных (и успешно работающих!) расчётов включают до 25 уровней вложенности, а используемые объекты БД содержат десятки, иногда сотни полей. Плюс большинство полей участвует в логике расчёта более сложной, чем просто передача по иерархии представлений от источника до витрины. Поэтому наши сомнения в способности новой БД всё это повторить не выглядели беспочвенными!
Серьёзные риски
Реализовать все это предстояло на платформе SQreamDB, имеющей собственный диалект SQL. И не просто реализовать, а добиться полной сходимости результатов с исходной CV. К тому же, цена перехода на СУБД высока не только с точки зрения кода:
нужно закупить аппаратное обеспечение и лицензии на новое ПО;
включить новые сервера в ландшафт хранилища технически и организационно;
пройти процедуры информационной безопасности;
обучить и/или нанять команду поддержки и развития новой платформы;
определить рамки и провести миграцию расчётов на новую платформу.
При всем этом великолепии есть риск угробить кучу сил и обнаружить, что производительность упала. А без замеров понять это заранее невозможно.
Чтобы не кидаться в омут с головой, мы решили сначала немного помочить ножки — и проверить работоспособность на одной из самых сложных CV системы. Логика была такая: если SQream вывезет её, то остальное вывезет и подавно.
Танго на костылях
Начали в лоб: принялись переписывать логику сивишек на SQream-диалект руками. Благо не своими руками, а делегировали подрядчику.
Как я уже сказал, для конвертации выбрали одну из центральных CV системы. Она содержит в себе много блоков расчёта (порядка сотен), а также обрабатывает ощутимые объёмы данных — десятки миллионов строк аналитичностью до 200 полей.
Поле с граблями
Сразу столкнулись с серьёзной проблемой: CV в КХД обновляются часто, результаты работы меняются. В текущие и нижестоящие CV смежными командами активно вносятся правки в рамках работ по устранению ошибок или запросам на изменения, а значит, все изменения необходимо вручную дублировать в SQreamDB.
Из этого следует, что, когда предлагаемый механизм расчёта окажется в разработке, а в эксплуатации будет старое графическое представление, встанет ребром вопрос взаимодействия команд разработки.
Мы придумали несколько решений этой проблемы:
Замораживать любые изменения мигрируемых представлений на период их перевода на новую платформу.
Ключевой недостаток: нарушение нормальной эксплуатации хранилища данных. Изменения — важная часть жизненного цикла продукта.Дублировать любое вносимое в графическое представление изменение в разрабатываемый код на новой платформе.
Ключевой недостаток: необходимо обучать всю команду поддержки хранилища как новой платформе, так и архитектуре разрабатываемого решения.Формировать по вносимым изменениям отдельный реестр для разработчика кода новой платформы.
Ключевой недостаток: риск переноса окончания работ по новому решению примерно на никогда.
И… ни одно решение нам не понравилось. Как же обойти эти грабли?
Обходим грабли технично
Тут мы подумали: если в компании принят специальный подход к управлению изменениями (показать не могу, простите), а ручное переписывания CV с ним несовместимо — то давайте все сразу автоматизируем!
Так родилась идея написать конвертер с языка графических схем на целевой язык (в данном случае — SQreamDB, обобщая — на любой).
При таком подходе:
Конвертация выполняется быстро, её можно выполнять часто, получая новые версии по мере внесения изменений в исходные представления — так мы избегаем «фризов» в цикле поддержки существующего КХД;
«Переводчиком» может быть отдельно выделенный сотрудник или команда — нет необходимости обучать всю команду поддержки новым инструментам.
Однако и у этого решения есть недостаток: над конвертером придётся конкретно потрудиться. А если понадобится доделывать — потрудиться ещё конкретнее.
Для начала (в качестве пробного захода) мы решили конвертировать в SQL Script. Это позволяет относительно быстро и безболезненно выявить подводные камни трансляции из графического представления, сравнивая результаты исполнения исходной и целевой реализации на одно и той же платформе сразу во время разработки конвертера. Перейти к особенностям нового языка SQreamDB мы сможем, только удостоверившись, что вопросов к «графике» не осталось.
Конвертер — модель для сборки
Укрупнённо для конвертации нужно выполнить 4 шага:
Получить xml указанной CV.
Конвертировать xml в структуры внутренних переменных конвертера.
Преобразовать полученную структуру в целевой язык, разделяя компоненты на категории исходных языков: графический элемент, формула на языке Calculation Engine, формула на SQL-скрипте.
Повторить первые три шага для графических элементов, представляющих собой вложенные CV.
Разберём каждый шаг подробнее.
1. Как получить xml указанной CV
На то, что графические CV хранятся в БД в виде xml-структур, указывает нам кнопка «Display XML» в редакторе представлений.
Способ просмотра CV в виде xml-структуры
Можно открыть и посмотреть все знакомые нам элементы в виде xml, а также все их характеристики, формулы, координаты размещения на поле графического дизайнера и прочую служебную информацию.
Но как получить эту структуру программно с сервера приложений?
Тут можно вспомнить про транзакцию scts_hta «SCTS_HOTA_ORGANIZER», которая умеет получать CV с уровня БД и упаковывать их в файлы архивов для передачи в транспортную систему stms.
Нехитрое исследование кода приводит нас к классу cl_cts_hot_hana_connector, отвечающий за связь с БД и получение нужных нам структур. К сожалению, напрямую его использовать не получится, т.к. он сильно специфичен для транспортной системы, нужные нам методы приватны, а наследование закрыто. Но нужного для задачи конвертации кода не много, и можно создать свой отдельный класс коннектора, скопировав и адаптировав только нужные методы исходного стандартного класса. Таким образом, получаем свой zcl_cvs_hana_connector, который умеет подключаться к БД под выделенным для этих функций служебным пользователем БД, получать списки пакетов и представлений и считывать их содержимое (xml).
2. Как конвертировать xml во внутреннюю структуру
Этот шаг одновременно и простой, и сложный.
Простота в том, что чтение xml имеет поддержку на уровне языка и выполняется одной командой: call transformation.
Сложность кроется в параметре id, для заполнения которого требуется создать отдельную программу преобразования xml<->объект_данных. Да, одно и то же преобразование может работать в две стороны. В ABAP-е есть возможность задавать подобные преобразования в трёх разных форматах: XSL transformations, Simple transformations и asXML. Все они описаны в стандартной справке по ABAP в разделе «Transformations for XML».
Для нашей задачи достаточно возможностей Simple transformation. Такие трансформации ведутся в отдельной транзакции STRANS. Программа в декларативном формате описывает структуру входящего xml и указывает, в какие поля объекта данных каким образом помещать получаемые значения и атрибуты. Возможно указывать на необязательность присутствия тех или иных веток в xml, а также обращаться к методам классов для преобразования значений на лету в процессе трансформации.
Мы привели здесь лишь возможности, которые оказались необходимы для поставленной задачи. Полный список возможностей вы можете найти в стандартной справке.
В результате текущего шага появился объект данных ABAP, хранящий информацию о запрошенном представлении:
пакет, имя и тип представления;
список переменных — разреженный, позволяющий хранить данные переменных всех возможных типов;
список мэппингов переменных на нижестоящие представления;
список источников данных;
список элементов представления — разреженный, позволяющий хранить информацию элементов от join’ов до источников данных;
указатель на первый элемент представления;
текстовое поле с исходным xml (для отладки).
3. Как преобразовать структуру в целевой язык
Путь в тысячу ли начинается с одного трёх шагов. Нам потребуются:
a) язык графических структур (компиляция),
b) CE-выражение (парсинг и компиляция),
c) SQL-скрипт (передача 1-к-1 без преобразования)
Цель трансляции: для каждого элемента графического представления сгенерировать select-утверждение в виде внутренней таблицы для SQL script или в виде with-конструкции для какого-то другого диалекта SQL. Для удобства последующего перехода к другим диалектам SQL финальную генерацию утверждений выполняет отдельный класс-рендер, реализующий интерфейс zif_cvs_renderer. А дальше мы будем расширять функциональность транслятора, дописывая новые рендеры, реализующие тот же интерфейс.
Для начала трансляции берем вышестоящую операцию (это может быть операция проекции или агрегации) и переходим к шагу 3.а.
a) Трансляция графической операции
Это общий шаг для любой графической операции. Кроме проекции и агрегации тут могут обрабатываться другие типы. Суть — сгенерировать select-утверждение на основе настроечной таблицы, полученной на этапе 2.
У каждой операции может быть один или несколько источников. Источником может быть объект БД (таблица или представление), другое графические представление или нижестоящая операция. Указание на объект БД при этом вставляется напрямую, а другое графическое представление и нижестоящая операция требуют предварительно вставить в целевой скрипт их select-утверждения путём рекурсивного выполнения обработки этапа 3.
Подробнее о типах операций (выражения в угловых скобках <> означают выбор содержимого внутренней таблицы этапа 2):
Проекция — генерируется простое утверждение вида select <список полей> from <источник> where <условие>. При этом любое из <полей> может оказаться расчётным и представлять собой либо CE-выражение, либо SQL-скрипт, равно как и выражение из <условия>. Такие выражения транслируются в целевой скрипт шагами 3.b. и 3.c соответственно.
Агрегация — генерируется утверждение вида select <список полей (список агрегируемых полей)> from <источник> where <условие> group by <список полей – список агрегируемых полей>. Так же, как и для проекции, отдельные поля и условие могут представлять собой CE-выражения и SQL-скрипты. Также при генерации списка полей нужно сверяться со списком агрегируемых полей, в котором указаны функции агрегации: sum, count, max, min и т.п.
Join — генерируется утверждение вида select <список целевых полей> from <источник_1> <тип join'а> join <источник_2> on <список объединяемых полей>. В отличие от предыдущих операций, здесь нет возможности задать условие выбора, однако есть ряд интересных обработок: список целевых полей нужно обогащать алиасами источников (1 или 2) в соответствии с таблицей мэппинга источников (поставляется в той же структуре этапа (1)). То же самое нужно выполнять со списком объединяемых полей. Объединение выполняет логический оператор and, тип join’а — простой мэппинг утверждений типа 'leftOuter'→'left outer', 'referential'=>'left outer', 'inner'→'', (или 'cross', если список объединяемых полей пуст).
Union — генерируется утверждение вида select <список полей> from <источник_1> *(union all select <список полей> from <источник_n>). Здесь *(…) — повторение от второго до последнего источника, может быть 0 повторений. Важное замечание: в графическом union используется логика именно union all (результат 3 часов поисков расхождений с эталоном).
Rank — генерируется утверждение вида select <список имен полей>, <имя колонки rank> from (select <список полей>, rank () over (partition by <поля окна функции rank> order by <поле сортировки> <направление сортировки>) »<имя колонки rank>» from <источник>) where <имя колонки rank> <= <пороговое значение>. Из того, что требует объяснения: <список имен полей> отличается от <список полей> тем, что в первом лишь перечисляются имена полей, а во втором могут быть выражения вычисления для расчётных полей. Также условие where не создается, если не заполнено <пороговое значение>.
b) Трансляция CE-выражения
Алгоритм парсинга CE-выражений на удивление прост и хорошо описан в официальной справке SAP в разделе оператора CE_CALC. Вот как он выглядит:
И действительно, достаточно создать набор одноимённых методов, каждый из которых отвечает за свой оператор: b для or, b1 для and и т.д., и вся конвертация происходит строго по вышеуказанной «спецификации». Сложности возникают лишь в двух местах:
Оператор сложения в этом языке работает и как суммирование для числовых значений, и как конкатенация для строк. Однако в языке SQL эти операции выполняются разными операторами: + и ||, и при попытке «сложить» число со строкой или две строки получится ошибка. Решается это передачей информации о типах операндов. Например, если первый операнд — это числовая константа, можно из метода e3 вернуть вверх эту информацию, и метод e решит, нужно ли заменять + на || или нет.
Тот же оператор сложения вполне работает с null-значениями, позволяя их складывать или конкатенировать. В мире SQL для этого нужно делать ifnull (), а для этого нужна информация от нижестоящих операторов: могут ли они быть null. Например, если сложение идёт с константой, то такой оператор не может быть null, и лишний раз для него выполнять ifnull () не нужно. Но если в нижестоящем выражении встречается поле источника данных, и нет защиты в виде CE-функции isnull, case или чего-то подобного, то такое выражение может быть null и для него нужна проверка ifnull ().
Самое объемное место в трансляторе CE-выражений — это функция id. Она содержит мэппинг всех CE-функций на SQL. Например, в ней содержится правило преобразования case (arg1, cmp1, value1, cmp2, value2, …, default) в map (
c) Трансляция SQL-скрипта
С этим всё просто: передаём его 1-к-1 в целевое утверждение. Конечно, для перевода в Sqreqm DB или любой другой диалект потребуется парсинг текущего скрипта, но на данном этапе обойдёмся без этого.
4. Особенности рекурсивной развёртки нижестоящих CV
Порой в ходе рекурсивного обхода структуры операций графического представления одна и та же операция используется в качестве источника для нескольких вышестоящих операций. В целевом сценарии определение каждой операции должно встречаться только один раз. Для этого создается буферная таблица, содержащая список всех транслированных на данный момент операций. Если очередная операция в качестве источника использует уже транслированную операцию, определение последней не добавляется в целевой сценарий, лишь используется ее имя.
Аналогичная логика применима для иерархии графических представлений. Для них так же создаётся и используется буферная таблица результатов трансляции нижестоящих представлений.
Что мы будем делать дальше
Сейчас конвертер умеет переводить графические представления в SQL-скрипт. При этом результаты работы полученных сценариев сходятся с результатами работы исходных представлений.
Это, конечно, замечательно, но мы планируем создавать рендеры на другие диалекты SQL. Для этого придётся сделать парсинг SQL-скриптов (п. 3.c).
К сожалению, с наступлением всем известных событий вопрос применения SQreamDB отошёл на второй план, поэтому необходимость в соответствующем рендере снизилась.
Однако нет худа без добра: мы сможем использовать текущие наработки в грядущих проектах импортозамещения. Возможно, рендер на PostgreSQL будет более актуален.
Время покажет.
Благодарности
Огромное спасибо моим коллегам, Ольге и Олегу, за кардинальную переработку и улучшение текста статьи.