Создание временных таблиц процедурами ODI.

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

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

В отличие от описанного здесь алгоритма создания временной таблицы через интерфейс, я предпочитаю использовать для этих целей процедуры Oracle Data Integrator.

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

К ограничениям ODI можно отнести:

• невозможность использования таких операторов SQL как Union, Minus, Intersect в интерфейсах;
• для использования специфических операторов SQL языка в интерфейсах, возможно, понадобится модификация одного или более модулей знаний. Если использовать, например, оператор Qualify необходимо однократно, проще сделать это в процедуре.

Особенности СУБД:

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

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

create table TEMP_GROUP
(
NAME VARCHAR2(30),
NUMBER INTEGER,
GROUP VARCHAR2(200),
CODE VARCHAR2(140),
PREFIX VARCHAR2(10),
POSTFIX VARCHAR2(10),
VAR_NAME VARCHAR2(140)
)

Или такой:

create table TEMP_GROUP2 as
select GROUP from Source1
minus
select GROUP from Source2

Далее этот текст необходимо вставить в процедуру, указав используемую технологию и правильно выбрав схему БД.

Обычно использование временных таблиц предполагает три этапа:

1. создание таблицы и ее заполнение;
2. использование, при необходимости, этой таблицы в интерфейсе загрузки;
3. удаление созданной временной таблицы.

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

Итак, создаем процедуру со следующими шагами:

1. удаление временной таблицы;
2. создание временной таблицы;
3. заполнение временной таблицы;
4. сбор статистики по созданной таблице.

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

Создаем опцию процедуры, назовем ее, например, CreateTable. Установим для нее тип Check Box.

Далее, для каждого шага процедуры, кроме первого, устанавливаем признак условного выполнения шага:

Для этого снимаем пометку с Always Execute, и устанавливаем ее напротив опции CreateTable.

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

Подготовка закончена. Далее используем созданную нами процедуру в пакете два раза:

1. перед выполнением интерфейса, чтобы создать таблицу.
2. после выполнения интерфейса, с опцией CreateTable установленной в No, чтобы временную таблицу удалить.

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