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

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

Заметим, что

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оконные функции 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.

Неизвестное 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?

Кейс: изменение значения первичного ключа

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

Вопрос об изменении значения первичного ключа может также возникнуть в контексте интеграции систем.

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

Сможет ли система Б обновить первичный ключ, если она получит сообщение об изменении бизнес-ключа?

ddiff. Эффективное сравнение данных в разных БД

Как вы поступаете, когда нужно сравнить два больших набора данных? Скорее всего, первое, что вы делаете, – проверяете, одинаковое ли количество строк в каждом наборе и одинаковые ли суммы по некоторым столбцам. Можно еще сравнить количество уникальных значений в одних и тех же столбцах в двух наборах, минимальные и максимальные значения столбцов, сумму длин строк в строковых столбцах.

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

Логическая целостность данных, выгружаемых из БД

Задача: передавать данные из одной БД в другую, отсекая ненужные для интеграции данные – слишком старые или логически нерелевантные. При этом вся совокупность данных, выгруженных из БД-источника, должна оставаться логически целостной. Это значит,

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

Очевидно, что если выгружать все строки из выбранного набора таблиц БД, в котором обеспечена ссылочная целостность, то критерий выгрузки (все строки) не требует специальных анализа и реализации, а выгружаемые данные также будут ссылочно целостны.

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

Какого ограничения целостности не хватает в SQL

Константного - когда значение в столбце, присвоенное insert'ом, нельзя изменить update'ом.

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

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

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

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

В связи с этим при вставке или изменении записи нужно проверять, не приведет ли эта операция к появлению в таблице записей с пересекающимися периодами.

Функции c within group в СУБД Oracle

Функции c within group бывают агрегатные и аналитические, а объединяет их то, что для вычисления результата они нуждаются в упорядоченной последовательности входных значений. Именно упорядочивание входных значений и задается с помощью within group (order by ...).

Статистические функции в Oracle 11g, часть II

В части I было показано, как с помощью функций Oracle 11g получить статистические характеристики выборки, а именно:

• центральные тенденции: среднее арифметическое, медиану, моду и середину диапазона,
• характеристики разброса: дисперсию, среднеквадратичное отклонение, межквартильный диапазон и диапазон выборки.

Также с помощью запросов SQL были построены столбцовая диаграмма и гистограмма для номинальных и числовых данных.

Сегодня с помощью функций Oracle поиграем с нормальным распределением.