Используем аналитические функции SQL в ODI.

Приветствую.

Сегодня хочу рассказать о том, что давно уже всем известно. Вернее, я так думал, что все, кто работал с ODI об этом давно знают.

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

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

На самом деле, все можно сделать намного проще.

Для начала рассмотрим как именно неправильно ведет себя ODI в случае, если в маппинге мы используем какую-то аналитическую функцию.

Для этого примера я выбрал практически бесполезную идею подсчета разницы между временем обновления записей в таблице SNP_EXP_TXT для каждого набора строк (подробнее о структуре рабочего репозитария ODI). Попробуем сделать временную таблицу содержащую результаты вот такого запроса:

select I_TXT, cast(LAST_DATE - MIN(LAST_DATE) over (partition by I_TXT) as float)
from db_snpw.dbo.SNP_EXP_TXT
order by 1, 2 desc;

Результаты выглядят вот так:

Обратите внимание, что здесь нет необходимости в группировочной функции, а также на тот факт, что в MS SQL Server можно использовать номер колонки в конструкции order by.

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

Последовательность выполнения:
1. Создаем интерфейс, добавляем в качестве источника таблицу SNP_EXP_TXT.
2. Устанавливаем для интерфейса признак того, что промежуточная область (стейдж) не совпадает с местом, где расположена целевая таблица, что является необходимым при создании временной таблицы с помощью интерфейса.

3. Задаем имя временной таблицы, создаем колонки, задаем, при необходимости, тип этих колонок.

4. Вводим нужный SQL код в поле ввода маппинга для нужной колонки.

После этого можно запустить интерфейс и посмотреть результаты. Выполнение интерфейса остановилось на этапе создания временной таблицы, из-за отсутствия прав у пользователя, от имени которого работает ODI, так как у этого пользователя отсутствуют права на создание таблиц в БД tempdb, но нам важно увидеть, какой именно запрос подготовлен, с помощью методов подстановки, для заполнения этой таблицы:

Как видно, ODI сформировал дополнительную директиву на группировку колонки I_TXT, так как встретил в маппинге для колонки Diff вызов функции MIN().

Как же можно вернуть ODI на путь истинный? Один из подходов, возможно, мог бы быть попыткой научить ODI видеть разницу между аналитической функцией MIN(...) over (...) и агрегирующей функций MIN(), именно так, как это делает интерпретатор SQL запроса в СУБД.

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

Второй подход рассматривает эту проблему немного с другой стороны. В частности, если мы в обычном SQL запросе все выбираемые поля агрегируем, то у нас нет необходимости в отдельной конструкции GROUP BY.

Но, скажете вы, нам же реально не нужно группировать выборку по полю I_TXT? Конечно, не нужно, и поэтому мы просто добавим в маппинг для колонки I_TXT некую обманку, используем псевдосуммирование для того, чтобы ODI решил что мы агрегируем по всем выбираем колонкам и не добавлял отдельную группировку:

Смотрим результаты:

Что-ж, все выглядит отлично, и при этом не пришлось менять модули знаний. Сам запрос, если скопировать его из Оператора, тоже без проблем выполняется на БД источнике.

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

Для этого откроем Менеджер Топологий и найдем технологию Microsoft SQL Server. На вкладке Language мы увидим, что данная технология использует язык SQL.

Если затем мы посмотрим на вкладку Languages, найдем и откроем описание языка SQL, которым пользуется ODI, мы увидим, что там присутствует подраздел с описанием агрегатных функций.

К сожалению, мне не удалось создать новый языковой элемент, чтобы использовать описанный выше подход, но не использовать стандартные sum() или max(), в тех случаях, когда нам нужно отменить группировку по некоторой колонке:

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

Продолжение следует...