Повинуясь всеобщей тенденции и следуя духу времени, мы в Норникеле переводим расчёты нашего хранилища на уровень БД. Так мы ускоряем обработку данных и формирование отчётности, да и система работает стабильнее.

Но пока мы оптимизируем и переносим "классический" код в базу, запрашиваемые заказчиком алгоритмы усложняются, а обрабатываемых данных становится больше. Чтобы склонить чашу весов на нашу сторону в этом извечном противостоянии мы решили применить новый метод!

Приветствую! Я – эксперт команды поддержки и развития корпоративного хранилища данных. В 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 шага:

1. Получить xml указанной CV.

2. Конвертировать xml в структуры внутренних переменных конвертера.

3. Преобразовать полученную структуру в целевой язык, разделяя компоненты на категории исходных языков: графический элемент, формула на языке Calculation Engine, формула на SQL-скрипте.

4. Повторить первые три шага для графических элементов, представляющих собой вложенные CV.

Разберём каждый шаг подробнее.

1. Как получить xml указанной CV

На то, что графические CV хранятся в БД в виде xml-структур, указывает нам кнопка "Display 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(<expression>, <search_value>, <result> [, <search_value>, <result> [...] ] [, <default_result>]), или string(arg) в to_varchar(<value>). Некоторые функции не имеют прямого соответствия в SQL, но хорошо обходятся операторами. Например, CE-функция match(arg, cmp) реализуется оператором like и двумя функциями replace() для замены масок * на % и ? на _.

c) Трансляция SQL-скрипта

С этим всё просто: передаём его 1-к-1 в целевое утверждение. Конечно, для перевода в Sqreqm DB или любой другой диалект потребуется парсинг текущего скрипта, но на данном этапе обойдёмся без этого.

4. Особенности рекурсивной развёртки нижестоящих CV

Порой в ходе рекурсивного обхода структуры операций графического представления одна и та же операция используется в качестве источника для нескольких вышестоящих операций. В целевом сценарии определение каждой операции должно встречаться только один раз. Для этого создается буферная таблица, содержащая список всех транслированных на данный момент операций. Если очередная операция в качестве источника использует уже транслированную операцию, определение последней не добавляется в целевой сценарий, лишь используется ее имя.

Аналогичная логика применима для иерархии графических представлений. Для них так же создаётся и используется буферная таблица результатов трансляции нижестоящих представлений.

Что мы будем делать дальше

Сейчас конвертер умеет переводить графические представления в SQL-скрипт. При этом результаты работы полученных сценариев сходятся с результатами работы исходных представлений.

Это, конечно, замечательно, но мы планируем создавать рендеры на другие диалекты SQL. Для этого придётся сделать парсинг SQL-скриптов (п. 3.c).

К сожалению, с наступлением всем известных событий вопрос применения SQreamDB отошёл на второй план, поэтому необходимость в соответствующем рендере снизилась.

Однако нет худа без добра: мы сможем использовать текущие наработки в грядущих проектах импортозамещения. Возможно, рендер на PostgreSQL будет более актуален.

Время покажет.