В этой статье хотелось бы рассмотреть такой вопрос - как частичное обновление больших объемов данных в таблицах, которые активно используются пользователями на чтение. Задача является типовой, и с ней сталкивается каждый инженер данных. При этом не важно на какой ступеньке своей карьерной лестницы вы находитесь, 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 много инструментов для менеджмента данных на уровне партиций. Более подробно про них можно прочитать в документации к БД: https://clickhouse.com/docs/en/sql-reference/statements/alter/partition

Oracle

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

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

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

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

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

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

Нам потребуется документация на exchange partition: https://docs.oracle.com/en/database/oracle/oracle-database/23/vldbg/maintenance-partition-tables-indexes.html#GUID-E08650B4-06B1-43F9-91B0-FBF685A3B848

Первое, что нужно сделать - это поместить данные из исходной партицированной таблицы в промежуточную непартицированную 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 по объему, опасаясь долгого обновления основной таблицы. Операция обмена партициями быстра в любой БД и на самых больших объемах.

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