В этой статье хотелось бы рассмотреть такой вопрос - как частичное обновление больших объемов данных в таблицах, которые активно используются пользователями на чтение. Задача является типовой, и с ней сталкивается каждый инженер данных. При этом не важно на какой ступеньке своей карьерной лестницы вы находитесь, Junior или Senior, такие задачи будут.

Что мы имеем:

  • Огромная витрина с данными - SALES, содержащая множество атрибутов по продажам в абстрактной системе.

  • Существует ETL-процесс, не важно на какой технологии, который подготавливает SALES_INCREMENT для витрины на основании ряда источников, которые могут поставлять данные по одной продаже в разные периоды времени с задержками до недели-месяца (период инкремента).

  • Каждая итерация инкремента содержит полные актуальные данные за несколько полных месяцев назад + новые данные. Скажем 500млн+ строк.

  • Имеются аналитические системы, которые постоянно обращаются к данным SALES для чтения. Их природа также не важна. Это могут быть прямые запросы из отчетов, запросы для промежуточной агрегации и формирования более узких витрин данных. Главное, что нужно понимать - данные постоянно читаются. И даже ночью у вас нет спасения, чтобы спокойно провести пересчет.

Задача: Обновить данные в таблице SALES на основании SALES_INCREMENT таким образом, чтобы вам не пришлось объясняться перед разгневанными пользователями и коллегами за зависания запросов или испорченные данные в смежных витринах.

Попытки решить без партицирования

На ум сразу приходит стандартный алгоритм: Insert для новых строк, update для старых и задача решена.

Но, например в Oracle, таким подходом можем уложить запросы пользователей по snapshot too old в случае, если запросы долгие, а наше обновление переполнит журнал и приведет к его скидыванию в архив. И не важно какими порциями вы фиксируете данные. Про БД, где нет версионности, вообще молчу. Система просто встанет из-за блокировки таблиц на чтение.

Кроме этого - новые строки мы вставим легкими запросами, а вот старые нам придется обновлять через merge или иными методами, нагружая систему еще и выборками данных, помимо их вставки. Во избежание merge и сложной логики можно удалять обновляемые строки и просто заменять их новым. Использовать принятый в DWH delete + insert. Но тут раздуваем undo/redo и рискуем надолго уйти в Downtime если система по какой то пр��чине начнет откатывать транзакцию. Понятное дело, что опытные разработчики модифицируют данную схему, чтобы все работало. Вплоть до отключения журналов. Но это долго, сложно, дорого по ресурсам и иногда эффективность достигается путем отказа от ACID (Привет от хинтов или связки drop table + rename tmp_table to table)

В общем писать потоковую загрузку с insert/merge/update/delete можно. И так даже делают. И это работает. Но наша цель найти более эффективный и современный способ.

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

ClickHouse

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

Все что нужно сделать для эффективного решения вопросов обновления - прибегнуть к стандартной DDL операции:

alter table sales replace partition (202308) from sales_increment

В данном случае вы замените все данные в SALES за август 2023 года (ну при условии что идет партицирование по месяцам) копией данных из SALES_INCREMENT. В SALES_INCREMENT данные останутся - далее вы можете делать с ними что хотите. Чаще всего таблицу просто удаляют после копирования всех партиций до следующей итерации ETL.

Иногда может возникнуть необходимость дописать данные, не удаляя весь август. Тогда используем move partition:

alter table sales move partition (202308) from sales_increment

В данном случае мы переместим данные из SALES_INCREMENT и допишем их в SALES. В SALES_INCREMENT данные пропадут.

Ну а для автоматизации процесса, можно загнать копирование в цикл по всем доступным партициям инкрементальной таблицы.

select distinct partition from system.parts where table = 'sales_increment'

Вообще в ClickHouse много инструментов для менеджмента данных на уровне партиций. Более подробно про них можно прочитать в документации к БД: ClickHouse. Работа с партициями

Oracle

Я не могу не остановиться на данной СУБД, так как это мой хлеб. И в какой то момент времени мне подкинули идею провернуть трюк с партициями и в нашей БД. Однако все оказалось не так просто как в ClickHouse, но реализовать технологию после пары приседанй удалось.

В чем трудность подобного фокуса в Оракл? Да в том, что данная СУБД поддерживает обмен партициями в двух режимах:

  • Из непартицированной таблицы в партицированную

  • Из партицированной таблицы в непартицированную

По своим архитектурным или иным соображениям не реализовали в Oracle решение по обмену между двумя партицированными таблицами.

Но мы же с вами понимаем, что подобное ограничение никак не мешает нам совершить обмен. Просто придется добавить промежуточную непартицированную таблицу и перегонять содержимое через нее. Пр��ступим.

Нам потребуется документация на exchange partition: Oracle. Работа с партициями

Первое, что нужно сделать - это поместить данные из исходной партицированной таблицы в промежуточную непартицированную sales_increment_staging:

alter table sales_increment exchange partition SYS_XXX
 with table sales_increment_staging without validation

Хочу обратить внимание, что without validation критично ускоряет процесс переключения, а так как мы все таки размышляем с позиции DWH, то считаем, что данные уже миллион раз проверены на целостность, и делать это еще раз - пустая трата ресурсов и времени. Однако, если с этим будут проблемы - всегда можно убрать директиву.

Следующим шагом перемещаем данные из промежуточной таблицы в целевую

alter table sales exchange partition SYS_YYY
 with table sales_increment_staging without validation

Происходит полная замена данных в целевой таблице. Потому стоит помнить, что если мы применяем подобный подход в Oracle, то партиция инкремента должна содержать полные данные. Формально БД на уровне метаданных превращает таблицу в партицию. А старую партицию удаляет (также на уровне метаданных).

Выглядит это примерно так:

  • Сегмент данных промежуточной таблицы SALES_INCREMENT_STAGING заменяет сегмент с партицией в таблице SALES

  • Старый сегмент с партицией SALES остается в БД. Т.е пока живут запросы чтения, начавшиеся до фиксации переключения партиций, данный сегмент остается видимым ими по требованиям уровня изоляции Read Commited

Хочу обратить отдельное внимание на следствие данного алгоритма - версионность данных Oracle работает при использовании данной DDL операции. И все запросы смогут дочитать данные, которые были в таблице SALES до того как их подменили. А новые запросы увидят уже новую партицию. Данный вопрос подробно не рассматривается нигде, но мы вынуждены были провести тесты, чтобы убедиться что система переноса безопасна.

Также из алгоритма вытекает требование - осторожно относиться к создаваемой таблице. Она должна иметь такие же настройки табличного пространства, что и те таблицы, с которыми мы производим обмен. С версии 12.2 появился лайфхак, который позволит нам избавиться от головной боли при создании SATGING таблиц:

create table sales_increment_staging for exchange with table sales_increment

Одной очень полезной функцией оказалась возможность менять партиции по значению. Для этого нужно использовать конструкцию partition for. В данном случае можно будет подменить партицию по значению, а не по имени партиции. Т.е если вы знаете, что в таблице sales_increment_staging находится август 2023 года, то просто передайте значение ключа партицирования в качестве параметра.

alter table sales exchange partition for (date'2023-08-01')
 with table sales_increment_staging without validation

Еще одним отличием обмена партициями по имени и по значению является тот факт, что обмен по имени требует наличия партиции с этим именем в целевой таблице, а по значению - не требует. Ну при условии того, что включено автосоздание партиций.

Однако все СУБД требуют соблюдения ряда правил, для того, чтобы технология работала:

  • Одинаковая структура таблиц - для всех СУБД. Это логичное требование

  • Одинаковые ключи партицирования для случаев с СУБД, где доступен обмен между двумя партицированными таблицами

  • Одинаковая политика хранения таблиц на диске (для ClickHouse)

  • И прочее, о чем можно прочитать в документации к конкретной СУБД

Что же мы получили:

  • Быстрое и простое обновление данных в целевых таблицах, не вынуждающее нас писать сложный код.

  • Почти все затраты на утилизацию и блокировки ложатся на стандартные механизмы обеспечения согласованности данных СУБД, так как мы работаем со стандартным DDL.

  • Все UNDO/REDO пишутся для таблицы инкремента, а не для основной таблицы. Таким образом в том же Oracle, мы защищаем себя от ошибки snapshot too old на длительных выборках по SALES.

  • Мы вообще отвязываемся от логики ETL. Т.е даже если поменяется код, наша логика обновления всегда будет работать. Нужно только актуализировать структуры таблиц.

  • Нам не нужно будет ограничивать ETL по объему, опасаясь долгого обновления основной таблицы. Операция обмена партициями быстра в любой БД и на самых больших объемах.

  • Мы можем варьировать периоды инкремента. Грузить месяц несколько раз в день. Полгода по ночам. Год раз в месяц. Все зависит от бизнеса и частоты обновления данных в тот или иной период в прошлом.