Всем привет! Меня зовут Николай Когель, я главный инженер по разработке Управления технологий MIS Департамента ИТ-блока «Финансы» в Сбере.
Сейчас в Сбере существует несколько крупных систем, в которых происходит построение управленческой отчётности и расчёт финансового результата. Как правило, это предполагает обработку огромных массивов исторических данных нетривиальной структуры из различных систем, загружаемых в аналитическое хранилище данных. По этой причине хранилище данных строится на основе MPP-систем, а с недавнего времени в Сбере наряду с Teradata появилась альтернатива в виде GreenPlum.
Поскольку расчёт финреза по кредитным продуктам насыщен бизнес-логикой, включая расчёт финансовых показателей на посделочном уровне с учётом продуктовой специфики, а также аллокацию полученного финансового результата по множеству аналитик на больших объёмах данных, даже на Teradata нередко встает вопрос отказоустойчивости и много ресурсов тратится на различного рода оптимизацию.
Целью описываемого в статье проекта было построение процесса расчёта финансового результата по продуктовой области «Гарантии ЮЛ» на GreenPlum с целью разгрузки инфраструктуры на Teradata и попытки построения более устойчивого процесса расчетов. На выходе необходимо было получить витрину с требуемым набором финансовых индикаторов и обеспечить выгрузку данных в базу данных SQL для построения OLAP-куба.
Преимущества использования GreenPlum
Программное решение реализуется поверх PostgreSQL, что позволяет строить и масштабировать систему на стандартном серверном оборудовании. В свою очередь Teradata — это аппаратно-программный комплекс, где программное обеспечение поставляется вендором вместе с оборудованием.
Поддержка оборудования при использовании GreenPlum требует стандартных компетенций, можно обойтись без специфического обучения инженеров и администраторов.
GreenPlum является MPP-системой, а так как для построения управленческой отчётности в Сбере требуется обработка огромных объёмов данных, такая архитектура отлично подходит для подобных задач. Legacy-решение также построено на MPP-архитектуре (Teradata), поэтому с точки зрения solution-архитектуры и разработки при переносе бизнес-логики и структур данных с MPP-системы на MPP-систему можно немного сэкономить ресурсы на проработку трансформаций структур и программного кода.
В процессе развития платформы MIS часто возникают потребности в разработке унифицированных и стандартизированных подходов к решению типовых задач (логирование, обработка историчных таблиц, применение управленческих корректировок и пр.) Это приводит к появлению фреймворков, важной частью которых является гибкость по отношению к входным данным и параметрам. GreenPlum со своей стороны предоставляет богатые возможности по использованию динамического SQL.
Как я уже писал ранее, задача состояла не только в непосредственно миграции программного кода, структур и бизнес-логики, но и в адаптации имеющегося ежемесячного расчёта к ежедневному формату.
Виды отчётности
Расскажу немного подробнее о формате ежедневной и ежемесячной отчётности.
Ежемесячная управленческая отчётность привязана к закрытию финансового месяца и отражает набор финансовых индикаторов, атрибутов и метрик, необходимых для принятия бизнесом управленческих решений по определённому направлению или предметной области.
С её помощью можно рассчитать, например, мотивацию продуктовых подразделений, нагрузку на территориальную сеть продаж, оценить финансовый результат от сделок в разрезах клиентских сегментов и многое другое.
Привязка к закрытию месяца логична, так как все подразделения к этому моменту подготавливают и сверяют данные в учётных системах, реплики которых являются для MIS источниками.
Ежедневная отчётность решает схожую с ежемесячной задачу, но предъявляет дополнительное требование к оперативности поставки данных. Конечно, приходится управлять рисками, относящимися к неполноте ежедневных данных, несвоевременным выгрузкам в хранилище, ошибкам в учётных системах, которые обычно исправляются перед закрытием месяца и т. д.
Однако ежедневная отчётность позволяет бизнесу гораздо оперативнее проводить анализ по критически важным показателям, своевременно реагировать на изменение их динамики и быстрее получать обратную связь по принятым решениям, в том числе по итогам закрытия месяца.
Поэтому для решения основной задачи к технической части переноса объектов и заложенной в процедуры расчёта промежуточных узлов тракта бизнес-логики необходимо добавить аналитическую проработку возможности ежедневного расчёта и пересчёта за любое количество прошедших дней.
Подготовка и процесс реализации проекта
Рассмотрим подход к хранению историчности записей в таблицах фактов. В аналитическом хранилище Сбера, в частности платформе MIS, таблицы фактов обычно представлены в вертикальном виде. Например, таблица для хранения метрик по договору может выглядеть вот так:
agrmnt_id | metric_id | val_dc | val_dc_lcl | start_dt | end_dt
В этой таблице:
agrmnt_id — идентификатор договора;
metric_id — идентификатор метрики;
val_dc и val_dc_lcl — сумма по метрике в валюте договора и в рублях;
start_dt и end_dt — дата начала записи и дата окончания записи.
Метрика в данном контексте — это какой-либо существенный финансовый показатель, базовый (с источника) или производный (рассчитанный внутри тракта). Примерами могут служить такие метрики, как «остаток на счёте», «резервы на возможные потери» или «количество дней просрочки».
При таком подходе к хранению историчности фактов отдельных действий по адаптации структур к ежедневному расчёту не требуется, однако необходима проверка, что все они корректно укладываются из источника и в принципе существуют за каждый день.
Также на кластере GreenPlum требовалось организовать ежедневную перекладку данных из реплик систем-источников.
Чтобы выяснить, какие именно данные необходимо переносить на GreenPlum, мы провели аудит зависимостей узлов тракта друг от друга, чтобы выйти на полный объём требуемых данных источника. Частично эта задача решалась имеющейся спецификацией, частично — анализом кода процедур расчёта тракта на Teradata.
Чтобы сократить объём передаваемых по сети данных, на Teradata была предусмотрена буферная область, куда загружались срезы таблиц источника за требуемый период расчёта (например, за вчерашний день), а также отфильтровывались только те данные, которые относятся непосредственно к предметной области «Гарантии ЮЛ», так как таблицы реплики источника содержат информацию по гораздо большему набору банковских продуктов.
Перекладка выполнялась с помощью собственного инструмента на Kotlin с настроенными JDBC-соединениями для чтения с Teradata и записи на GreenPlum посредством команды COPY. Для дополнительного ускорения мы реализовали перекладку нескольких таблиц одновременно в параллельных потоках.
Для реализации требования передачи готовой витрины в базу данных SQL для дальнейшего построения OLAP-куба расширили функционал перекладчика, добавили возможность использовать JDBC-драйвер и вставку с помощью BulkCopy.
Так как к этому моменту мы уже провели аналитическую проработку всех зависимостей между промежуточными узлами расчёта, далее мы разработали и развернули на среде GreenPlum набор DDL-скриптов всех необходимых структур.
Так как Teradata, как и GreenPlum, представляет собой MPP-систему, изменения DDL при миграции были незначительны и состояли только из типов полей и проверки корректности ключа распределения. Причём во всех таблицах была включена компрессия и флаг appendonly=true, применяющий модель хранения Append-Optimized. На этом флаге остановимся подробнее.
В GreenPlum есть несколько моделей хранения таблиц. Рассмотрим Heap и Append-Optimized подходы:
Heap — это подход хранения по умолчанию, унаследованный от PostgreSQL. Как сказано в официальной документации, он отлично подходит для систем с OLTP-нагрузкой, где данные в таблицах часто модифицируются после создания, причём обновляются и удаляются точечно (построчно);
Append-Optimized-подход создан для денормализованных таблиц фактов, где вставка данных происходит пачками и данные редко изменяются построчно. Для DWH с его OLAP-нагрузкой этот способ организации таблиц подходит лучше всего.
Дополнительно к указанным выше преимуществам Append-Optimized-таблиц эта модель хранения поддерживает несколько алгоритмов сжатия для уменьшения занимаемого пространства в СХД.
Также для небольших справочников установили ключ распределения DISTRIBUTED REPLICATED, который полностью реплицирует таблицу на все сегменты кластера GreenPlum, что позволяет оптимизировать план и сократить накладные расходы на перераспределение данных между сегментами в процессе выполнения запроса за счёт небольших расходов на хранение реплики таблицы на СХД.
Получив на среде GreenPlum необходимые данные и развернув DDL-структуры, наша команда разработки приступила к переносу бизнес-логики на PL/pgSQL, реализованной ранее на процедурном расширении Teradata.
Как упоминалось ранее, было логичным реализовать набор фреймворков для решения типовых задач: логирования, обработки историчности при вставке в таблицы фактов и применения управленческих корректировок.
Остановлюсь немного подробнее на каждом из этих компонентов тракта.
Логирование — необходимая часть любой информационной системы, благодаря которой можно понять, как долго шёл расчёт процедуры и с каким статусом расчёт завершился. Если в процессе выполнения произошла ошибка, логирование позволяет понять, в каком именно месте процедуры и уровне вложенности что-то произошло.
Как пример возможной организации логирования можно использовать таблицу следующего формата:
ts | sess_id | tp | src | step | msg
В этой таблице:
ts (timestamp) — дата/время вставки записи в таблицу логов;
sess_id — идентификатор сессии, под которой работала PL/pgSQL-функция (результат функции pg_backend_pid() );
tp — флаг типа сообщения в логе (например 'E' — error, ошибка при выполнении, 'I' — info, информационное сообщение, 'S' и 'F' — start и finish соответственно, по ним удобно вычислять время выполнения функции при выполнении мониторинга);
src — наименование PL/pgSQL-функции, из которой пришло сообщение в лог;
step — шаг выполнения функции;
msg — сообщение, дополнительная информация.
Для эффективного использования логирования мы организовали следующий формат оформления функций расчёта тракта на GreenPlum:
в секции DECLARE заводится служебная переменная _step, в которой будет вестись текущий шаг выполнения функции, устанавливается значение '000';
сразу после секции DECLARE в начале секции BEGIN выполняется вставка записи в лог с tp = 'S' и src = *наименование функции*;
по ходу функции после каждого логического этапа происходит переприсваивание переменной _step для обновления текущего шага – '001', '002' и т. д.;
если в функции собирается любой динамический SQL, для отладки и последующего разбора возможных дефектов в лог записывается информационное сообщение с tp = 'I' и msq = *текст сконструированного динамического запроса* и соответствующим шагом в поле step;
в конце функции обязательно происходит вставка строки в лог с tp = 'F' и наименование функции;
также в конце функции обязательна секция EXCEPTION, где происходит обработка любых исключительных ситуаций и формируется вставка в лог строки с tp = 'E', в msg пишется код системной ошибки GreenPlum (его можно получить через get stacked diagnostics) и текущим значением переменной _step в поле лога step.
При таком подходе к организации логирования на основную таблицу логов можно подключить визуальный интерфейс системы мониторинга, через который SQL-запросами можно будет отслеживать статусы расчёта узлов и время, затраченное на их работу.
Повсеместно по тракту расчёта результирующей витрины идут обращения к историческим таблицам фактов, поэтому каждый раз, когда происходит пересчёт за прошлые периоды, необходимо корректно осуществлять вставку/обновление строк, чтобы учесть пересечение периодов и удаление старых неактуальных записей.
Для этих целей мы разработали универсальную вспомогательную функцию укладки данных в таблицы фактов. Сигнатура функции:
p_sdt — дата, с которой будет происходить вставка историчных данных;
p_filter — дополнительные условия, которые накладываются на таблицу-источник и приёмник. Это может быть набор договоров или метрик, по которым необходимо произвести изменение, а остальные строки оставить как есть и т. д.;
p_src_tbl — наименование таблицы-источника, в которой обязательно есть поля start_dt и end_dt — историчность готовой для вставки записи;
p_tgt_tbl — наименование таблицы-приёмника, в которую будет производиться вставка новых записей и исправляться историчность. Обычно это основные или промежуточные таблицы фактов (атрибуты и метрики по договорам, счетам и пр.);
p_key_cols — массив наименований колонок, которые являются ключами в обеих таблицах (они должны совпадать и в источнике, и в приёмнике);
p_val_cols — массив наименований колонок, которые являются значениями в обеих таблицах.
Логика работы:
на входе имеется подготовленная промежуточная таблица, строки которой необходимо корректно поместить в таблицу-приёмник, не нарушая историчности, передвигая даты окончания записей по тем срокам, которые изменились, по соответствующим ключевым полям и удаляя те строки, которых в обновлённой таблице-источнике больше нет;
в таблице-приёмнике в поле end_dt с датой p_std – 1 происходит закрытие всех строк, удовлетворяющих условию p_filter. Таким образом, отсекается период историчности, который не входит в период обновления из таблицы-источника;
удаляются все записи с датой начала start_dt больше p_sdt. Это строки в таблице-приёмнике, которые полностью лежат в периоде пересчёта. Таким образом, если эти строки отсутствуют в новой таблице-источнике за этот период расчёта, то и в таблице-приёмнике они не нужны. Если же присутствуют, возможно, с другими значениями и периодами начала действия, то чуть позже они будут вставлены в таблицу-приёмник;
происходит вставка строк в таблицу-приёмник с соответствующим UPDATE истории на стыке, если по ключевым полям поменялись поля значений.
Данная функция используется повсеместно в расчёте тракта, что позволяет не решать в каждой отдельной функции задачу корректной вставки новой порции историчных данных в таблицы фактов и упрощает разработку.
Управленческие корректировки — это механизм перераспределения финансового результата между различными центрами прибыли в соответствии с бизнес-правилами. Центры прибыли — это разрезы, в рамках которых можно рассматривать значимые финансовые индикаторы, например территориальные подразделения, ответственные за ведение той или иной сделки, сегмент (малый, средний, крупный бизнес), к которому относится клиент по договору.
Существует много бизнес-правил, по которым работает узел корректировок, и для оптимальной организации однотипных операций над объектами широко использовались возможности динамического SQL-языка PL/pgSQL.
Для демонстрации применения динамического SQL в GreenPlum рассмотрим часть фреймворка, которая отвечает за перенос исторических атрибутов по договору на сгенерированный в рамках управленческой корректировки суррогатный договор с другим центром прибыли.
Код
<… >
DECLARE
<… >
_template varchar := ‘’;
_query varchar := ‘’;
<…>
BEGIN
<… >
_template = '
insert into t_adjbuf_agrmnt_attr_h (
agrmnt_id,
attr_id,
val_int,
start_dt,
end_dt
)
with
cte_conf as (
select
t.agrmnt_id, -- id исходного договора
t.out_agrmnt_id, -- id суррогатного договора
t.prd * daterange($1, $2+1) as prd -- результат пересечения периода историчности в таблице маппинга договоров
from <body_conf> t – таблица-источник с маппингом исходного договора на соответствующий ему суррогатный
),
cte_attrs as (
select
c.out_agrmnt_id as agrmnt_id,
t.attr_id, -- id атрибута по договору
t.val_int, -- значение атрибута по договору
daterange(t.start_dt, t.end_dt+1) * c.prd as _prd
from ###_SRC### t -- таблица фактов с атрибутами договора (что будем переносить)
inner join cte_conf c on 1=1
and t.agrmnt_id = c.agrmnt_id
and daterange(t.start_dt, t.end_dt+1) && c.prd -- пересечение периода историчности маппинга договоров из cte_conf c таблицей фактов по атрибутам договора
where 1=1
and t.start_dt <= $2
and t.end_dt >= $1
and (<attr_condition>) -- условия на id атрибутов, если переносить будем не всё
)
select
t.agrmnt_id,
t.attr_id,
t.val_int,
lower(t._prd) as start_dt, -- переход от daterange-типа к нижней границе типа date
coalesce(max(lower(t._prd)) over (partition by t.agrmnt_id, t.attr_id order by lower(t._prd) rows between 1 following and 1 following) - 1, date''9999-12-31'') as end_dt -- корректное закрытие историчности в end_dt
from cte_attrs t;
';
<… >В части кода, приведённой выше, сконструирован динамический SQL-запрос, в котором применением replace-функции можно заменить ###SRC### на таблицу фактов. Причём когда есть несколько таблиц, к которым нужно применить один и тот же INSERT с минимальными изменениями, для более компактной организации кода функции лучше использовать динамический SQL.
Для этого в другую строковую переменную _query вставляем нескольких значений переменной _template, каждую со своим значением ###SRC###. '<body_conf>' заменяется на наименование таблицы с маппингом договоров источников и приёмников. '<attr_condition>' также заменяется на условие из параметров головной функции, которые дополнительно накладываются на таблицу фактов. Если нужны все строки, можно как параметр передать значение '(1=1)', это никак не повлияет на фильтр в запросе. В итоге вызовом execute _query using p_start_dt, p_end_dt; происходит выполнение динамического запроса с подменой $1 и $2 на указанные параметры при execute. В данном случае это даты, за которые вызывается головная функция и вычитываются историчные атрибуты.
Можно заметить, что в запросе используется тип daterange. Это диапазонный тип данных, поддерживаемый в GreenPlum, который позволяет более наглядно осуществлять работу с периодом дат, например пересечение или объединение. Если не использовать его, нужно писать набор сравнений start_dt и end_dt в нескольких таблицах при их соединении, что достаточно объёмно.
Технические особенности миграции
В процессе миграции и начала работы с GreenPlum наша команда разработки столкнулась с некоторыми техническими моментами, которые мы решили отметить как особенности при переносе программного кода.
GreenPlum основан на PostgreSQL 9.5, поэтому не поддерживает stored procedures и, соответственно, автономные транзакции. Это накладывает определенные ограничения на то, как нужно писать код, чтобы сохранять промежуточные результаты расчетов и логи при ошибках.
Teradata поддерживает Global Temporary Tables — временные таблицы, данные в которых существуют только в рамках конкретной сессии, но их DDL создаётся один раз и находится в общем системном словаре. В GreenPlum такой тип таблиц отсутствует, поэтому для промежуточных вычислений использовались стандартные Temporary Tables, которые пересоздавали в начале первой использующей их функции.
Teradata поддерживает конструкцию QUALIFY, которая позволяет в конце выполнения SQL- запроса фильтровать строки результата без дополнительных подзапросов. Особенно это удобно при фильтрации результата применения аналитических функций с сортировкой. На GreenPlum в таких случаях использовали подзапросы.
GreenPlum не поддерживает DML-конструкцию MERGE, нужно заменять её на серию из INSERT и UPDATE.
GreenPlum поддерживает перегрузку функций, однако нужно быть внимательными с дополнительными возможностями по организации кода, которые возникают при расширении или изменении набора параметров существующей функции, чтобы не создать дубль и не получить проблемы при дальнейшей поддержке кодовой базы.
При вставке строк, содержащих символ с ASCII-кодом, равным 0, на GreenPlum возникает ошибка, хотя DML на Teradata успешно вставлял и пропускал такие символы как пустые.
Вывод
Подытоживая всё вышесказанное, могу отметить, что система GreenPlum особо важна для больших компаний, работающих с огромными массивами данных. Из величины массивов проистекает важная потребность: к таким записям нужно иметь возможность не только обращаться, но и производить операции с небольшими затратами времени. Кроме того, благодаря поддержке реляционной модели GreenPlum сохраняет данные без потери их точности и структуры. Всё это, а также возможности по тонкой настройке и масштабированию делают её хорошей альтернативой существующим MPP-системам для построения хранилищ данных в банках и крупных компаниях.
