Загрузка изменений из двух источников в одну ДТФ

В статье о соединении диапазонных таблиц фактов (ДТФ) я рассмотрел запрос, формирующий из двух ДТФ корректный набор диапазонов с фактами из обеих таблиц.

Заметим, что

1. составные первичные ключи в двух таблицах, включающие дату начала диапазона, могут занимать не меньше, а, возможно, и больше места, чем столбцы фактов,
2. запрос, соединяющий миллионы (десятки, сотни миллионов) строк из двух ДТФ в любом случае будет ресурсоемким.

Если такой запрос требуется выполнять часто, то оказывается выгодно хранить факты, даже загружаемые из разных источников, не в двух, а в одной ДТФ.

Загрузка изменений за прошлые даты в ДТФ

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

Но время от времени возникает задача добавления в непустую ДТФ новых или измененных данных за прошлые даты, и упомянутый алгоритм для этого не годится. Поэтому рассмотрим универсальный алгоритм заполнения ДТФ из источника, откуда поступают данные как на текущую дату, так и на прошлые даты — если данные за прошлую дату изменились.

Интеграция систем посредством шины данных

Ранее я писал о том, как мы интегрировали системы через шину данных. Этот пост продолжает тему интеграции систем через шину данных.

Дано: несколько систем и шина данных, реализующая обмен сообщениями через отдельные каналы (топики) по принципу publish - subscribe.

Цель: интегрировать системы посредством сообщений об изменениях, передаваемых и получаемых в/из шины данных.

Соединение диапазонных таблиц фактов

В прошлый раз я описал компактную форму хранения нерегулярно меняющихся данных в диапазонной таблице фактов (ДТФ) на основе периодических снэпшотов.

Речь идет о таблице фактов в широком смысле:

"Каждая таблица с составным ключом из нескольких внешних ключей – это таблица фактов." Ральф Кимбалл

Такая таблица не обязательно принадлежит презентационному слою хранилища данных, но на законных основаниях может жить в промежуточном слое, который предоставляет данные для витрин. (Кстати, такая таблица в терминах Data Vault — сателлит линка.)

Компактное представление истории нерегулярно меняющихся данных

Тема данного поста навеяна чтением Ральфа Кимбалла, который выделяет три вида таблиц фактов при построении хранилищ данных: транзакционные, снэпшотные и накопительные. В первых каждой строке соответствует отдельная транзакция (бизнес-операция, факт снятия показаний, зарегистрированное уникальное событие), загружаемая в хранилище данных из системы-источника. Вторые формруются из периодически загружаемых моментальных снимков (снэпшотов), где каждый снэпшот содержит множество строк. Третьи допускают многократное обновление строк, поскольку их строки описывают некие эволюционирующие сущности, например, заказы с этапами жизненного цикла или продажи за месяц при ежедневном обновлении.

Поговорим о снэпшотных таблицах фактов. И начнем с определения контекста.

Как мы интегрировали системы через шину данных

В торговой сети "Бубль-Гум" десяток взаимодействующих информационных систем, а в качестве шины данных у нас Apache Kafka.

Когда начинался проект по интеграции систем через шину данных, мне виделось так: обмен данными между всеми системами выполняется через топики шины данных с помощью сообщений, абстрагированных от специфики систем. Это модельные топики, так как контент и структура их сообщений соответствует модели данных предметной области и не зависит от особенностей систем-источников и систем-приемников. (По сути, модельные топики — это дата-продукты, контракты которых почти не меняются со временем, и потому они предпочтительны для интеграции систем. Отдельные системы со временем могут заменяться другими, но до тех пор, пока они верны модельным топикам, такие замены никак не сказываются на работе других интегрированных систем.)

Еще раз о ловле и обработке изменений в реляционной БД

Часто возникает задача получения изменений в таблице БД за период для их обработки. Существуют такие основные варианты решения:

1. маркирование каждой измененной записи тем или иным образом,
2. регистрация каждого изменения в отдельной таблице с помощью триггера (см. подробнее Захват и обработка изменений в БД...),
3. получение изменений за период с помощью разности с более ранним снэпшотом (см. подробнее Передача изменений между БД: подход в духе KISS).

Если у вас есть выбор, какой из вариантов реализовать, то сделать этот выбор правильно помогут ответы на следующие вопросы:

JSON или CSV для обмена данными между БД?

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

Например, в системе управления заказами клиентов есть логическая сущность заказ, состоящая из "шапки" и строк заказа. В реляционной БД она представляется двумя таблицами.

Моделирование данных (о терминах и смыслах)

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

Борхес, "Аналитический язык Джона Уилкинса"

Модели данных делятся на

а) концептуальные,
б) реляционные,
в) корпоративные,
г) "сущность-связь",
д) предметной области,
е) незавершенные,
ж) приснившиеся архитектору данных за пять минут до пробуждения.

Ну, вы поняли?

Виды данных в информационных системах

Неоднократно встречал деление данных в информационных системах (далее ИС) на

1. справочники,
2. мастер-данные,
3. документы,
4. метаданные.

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

1. справочники,
2. ресурсы,
3. конфигурации,
4. операции,
5. транзакции,
6. деривативы,
7. метаданные.

Опишу эти виды данных неформально, но доходчиво.

Оконные функции aka over-функции

Если бы у нас были только агрегатные функции, мы были бы принуждены постоянно делать выбор: видеть детальные данные или агрегированные? Аналитические функции, известные также как оконные функции или over-функции, позволяют одновременно получить и то и другое. С их помощью можно видеть данные, агрегированные по множеству строк, рядом с детальными данными.

Обычные, или скалярные, функции принимают на вход значения столбцов одной — текущей — строки курсора. Агрегатные функции принимают на вход значения столбцов всех строк курсора, определяемых where и group by (или их отсутствием). Оконные функции принимают на вход значения столбцов множества строк, определяемого в предложении over и зависящего от текущей строки.

Рассмотрим оконные функции на примере СУБД PosgreSQL 15.

Пробелы и острова, или Gaps and islands. Часть II

Это продолжение поста Пробелы и острова, или Gaps and islands. Часть I

Напомню, что задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".

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

Рассмотрим решение задачи "пробелы и острова" на хронологической последовательности данных.

Пробелы и острова, или Gaps and islands. Часть I

Задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".

С наступленим эры оконных функций (они же аналитические, они же over-функции) задачи вида "пробелы и острова" решаются с помощью этих функций.

Примеры ниже с таблицей t1 взяты мной из статьи Solving Gaps and Islands with Enhanced Window Functions by Itzik Ben-Gan и адаптированы для выполнения на PostgreSQL.

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

Периодические таблицы-2 (не химия)

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

select *
from ttt
where current_date between first_date and last_date
;

Подход естественный и очевидный. В самом деле, январь длится с 1 по 31, включительно. Неделя длится с понедельника по воскресенье, включительно. Акция в магазине проходит с 10 по 15 апреля, включительно.

Однако! Магазин работает с 10 до 20 часов, исключая 20 часов. Обеденный перерыв длится с 13 до 14 часов, исключая 14 часов, а с 14 до 14:30 проходит совещание – исключая 14:30.

Имитация ассоциативных массивов в PL/pgSQL

В языке PL/pgSQL нет ассоциативных массивов, которые позволяют получить значение по ключу. И это досадно. Но ладно.

Первый способ имитировать ассоциативный массив

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

1. найти индекс элемента первого массива, содержащего значение ключа,
2. по найденому индексу получить значение из второго массива.

Десять лет спустя, или Установка PostgreSQL на Windows

10 лет назад я написал первый пост в мой блог – про установку Oracle XE 11gR2 на Windows. Сколько воды утекло...

В 2022 комания Oracle удалила мой аккаунт, которым я пользовался лет двадцать, после того как Россия попала под экспортные ограничения. (Как в том анекдоте, где опытный кадровик помогает молодому коллеге справиться к горой резюме от кандидатов, которые тому нужно просмотреть. Половину резюме – сразу в мусорную корзину со словами "Лузеры нам не нужны!") Но жизнь продолжается.

Неизвестное vs Отсутствующее, или Двуликий NULL

Когда-то я уже писал о "многоликом NULL", см. Часть I и Часть II. Сегодняшний пост в дополнение и в развитие этой темы.

Я неоднократно встречал утверждение, что NULL в SQL нужно понимать как "неизвестно что". С этим согласуется поведение операторов сравнения при сравнении двух NULL (команды выполнены в PostgreSQL 15):

select 1
where null = null
   or null != null
   or null < null
   or null <= null
   or null >= null
   or null > null
;

no rows

dbang! Утилиты для работы с БД

Написанные на Python, утилиты dbang позволяют существенно сэкономить усилия при решении типичных задач, связанных с БД:

• ddiff.py - выполняет запросы к двум БД и формирует отчет о расхождениях;
• dtest.py - выполняет запросы к БД и формирует отчет о найденных проблемах;
• dget.py - выгружает данные из БД в файлы csv, xlsx или html;
• dput.py - загружает данные из файлов csv или xlsx в таблицы БД;
• hedwig.py - на основе файлов создает и отправляет е-мейл.

Утилиты могут работать с СУБД Oracle, PostgreSQL, SQLite и MySQL.

delta revisited. Работа с изменениями первичного ключа

Ранее я описал механизм delta для регистрации изменений в таблицах БД Oracle, позволяющий обрабатывать изменения независимо нескольким клиентам.

Долгое время все решения на базе механизма delta делались для базовых таблиц с неизменными первичными ключами.

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

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

delta revisited. Работа с обычной таблицей изменений

Ранее я описал механизм delta для регистрации изменений в таблицах БД Oracle, позволяющий обрабатывать изменения независимо нескольким клиентам.

Работа описанного механизма опирается на таблицу изменений с rowdependencies. Что делает его зависимым от СУБД Oracle. А в один прекрасный день возникает желание использовать хорошо зарекомендовавший себя механизм в СУБД PostgreSQL или другой СУБД с триггерами.

Можно ли использовать более универсальный подход при реализации таблицы изменений вместо таблицы с rowdependencies?