Работа с Oracle Data Integrator (ODI): прямой доступ к метаданным

1. Зачем анализировать ODI через SQL-запросы

Работая с Oracle Data Integrator (ODI), мы ценим его графический интерфейс за автоматизацию рутины и удобство разработки. Однако, когда проект масштабируется до десятков пакетов и сотен сущностей, GUI перестает быть оптимальным инструментом для отслеживания потоков данных, глубокого анализа и аудита зависимостей.

В таких случаях ключом к эффективности становится прямое взаимодействие с метаданными ODI через SQL-запросы к его репозиториям. Эта статья посвящена именно этому – практической работе со структурой репозиториев ODI и детальному разбору SQL-запроса для построения потоков данных.

Мы разберем:

1. Архитектуру репозиториев ODI: Master и Work.

2. Ключевые таблицы метаданных, их структуру и взаимосвязи.

3. Практический пример SQL-запроса.

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

Примечание:

Все примеры и запросы в данной статье основаны на Oracle Data Integrator Studio версии 12.2.1.4.201001 с версией репозитория 05.02.02.07. Структура метаданных может изменяться между версиями ODI. Также следует учесть, что схемы репозиториев (ODI_WORK, ODI_MASTER) могут называться иначе.

2. Репозиторий

Ключ к пониманию ODI лежит в его архитектуре, разделенной на репозитории.

Архитектура ODI предполагает наличие одного Master репозитория и одного или нескольких Work репозиториев. В некоторых случаях (например, при географически распределенной разработке) и Master репозиториев может быть несколько.

Давайте разберем чем отличается Master от Work репозитория.

Master Repository

Хранит глобальные данные, не привязанные к конкретному проекту. В основном это информация со вкладки Topology.

Хранит обычно общую информацию:

• Топология (SNP_TOPOLOGY, SNP_CONNECT, SNP_PSCHEMA, SNP_CONTEXT).

• Пользователи (SNP_USER).

• Языки программирования (SNP_LANG).

Зачем нужен: Администрирование сред, безопасности и управление логическими и физическими подключениями.

Ниже на изображении отмечены ключевые сущности топологии и соответствующие им таблицы в репозитории:

Пример запроса:

select pc.I_context, pc.I_lschema, pc.I_pschema, c.context_name, p.EXT_NAME, l.LSCHEMA_NAME, t.TECH_INT_NAME, t2.TECH_INT_NAME   from ODI_MASTER.SNP_PSCHEMA_CONT  pc   join ODI_MASTER.snp_context c on (c.I_context=pc.I_context)   join ODI_MASTER.SNP_LSCHEMA l on (l.i_lschema = pc.i_lschema)   join ODI_MASTER.SNP_PSCHEMA p on (p.I_PSCHEMA = pc.I_PSCHEMA)   join ODI_MASTER.SNP_TECHNO t on (t.I_TECHNO = l.I_TECHNO)   join ODI_MASTER.SNP_CONNECT cn on (cn.I_CONNECT = p.I_CONNECT)   join ODI_MASTER.SNP_TECHNO t2 on (t2.I_TECHNO = cn.I_TECHNO)  where c.context_name = 'DEV' and p.EXT_NAME = 'Cluster DWH Hive.default' and l.LSCHEMA_NAME = 'default'

Work Repository

Хранимую информацию можно разделить на 2 части, которые хранятся в одной схеме. Первая обычно называется «Work» и хранит данные со вкладки Operator (runtime данные), а вторая – «Designer» и хранит данные со вкладки Designer, т.е. хранится все, что связано с процессом разработки. Такое разделение Work и Design помогает ориентироваться в официальной документации и быстрее находить нужные объекты в интерфейсе.

Что хранит Work:

• Логи выполнения, запуски, статусы и шаги выполнения (SNP_SESSION, SNP_SESS_TASK, SNP_STEP_LOG).

• Переменные, которые используются во время выполнения в сценарии (SNP_VAR_SESS, SNP_VAR_SCEN)

• Сценарии и их шаги (SNP_SCEN, SNP_SB_STEP, SNP_SB_TASK)

• Модули Знаний / Knowledge Modules (SNP_TRT)

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

Что хранит Designer:

• Проекты и папки (SNP_PROJECT, SNP_FOLDER)

• Пакеты, маппинги (SNP_PACKAGE, SNP_MAPPING)

• Модели данных, переменные (SNP_MODEL, SNP_VAR)

Зачем нужен: Анализ кода, поиск зависимостей, рефакторинг, документирование проектов.

На скриншоте показано соответствие объектов интерфейса, таких как проекты и модели, таблицам в work репозитории:

3. Главные объекты репозитория ODI и их структура

Все объекты в ODI связаны и образуют иерархию. Понимание этих связей — ключ к написанию осмысленных запросов.

Например, маппинг (SNP_MAPPING) или пакет (SNP_PACKAGE) всегда находится в папке (SNP_FOLDER), которая принадлежит проекту (SNP_PROJECT).

Когда генерируется сценарий (SNP_SCEN) из маппинга, он становится готовым к выполнению кодом. Каждый его запуск создает сессию (SNP_SESSION), которая состоит из шагов (SNP_SESS_STEP) и задач (SNP_SESS_TASK).

Общие элементы многих таблиц:

• SNP_<OBJECT> — основные таблицы

• OGG_<OBJECT> — таблицы Oracle Golden Gate

• <OBJECT>_NO или I_<OBJECT> — уникальный идентификатор объекта. Например, I_MAPPING для маппингов или SESS_NO для сессий.

• <OBJECT>_NAME — Название объекта, текстовый идентификатор.
  Как правило, это внутреннее имя объекта в репозитории. В то время как оно часто совпадает с отображаемым, для некоторых объектов имя в GUI хранится в других полях. Например, для физической схемы — EXT_NAME.

• Поле GLOBAL_ID — Уникальный глобальный идентификатор объекта.

Вот несколько примеров:

4. Запрос для анализа потоков данных

Этот запрос позволит построить карту потоков данных, которые «протекают» через маппинги. Он не только определяет таблицы-источники и таблицы-цели для всех маппингов, но и обогащает эти данные информацией о физическом расположении – вплоть до JDBC URL серверов, на которых эти таблицы находятся.

Код запроса

with PROJECT_FOLDER -- то, где хранится маппинг на вкладке Designer в разделе Projects    as    (select PROJECT_NAME,            PROJECT_NAME || '/' || case              when f2.FOLDER_NAME is not null                   then f2.FOLDER_NAME || '/' || f1.FOLDER_NAME              else f1.FOLDER_NAME            end PathFolder,            nvl(f1.i_folder, f2.i_folder) i_folder            from ODI_WORK.SNP_PROJECT       join ODI_WORK.SNP_FOLDER f1         on (f1.I_PROJECT = SNP_PROJECT.I_PROJECT)       left join ODI_WORK.SNP_FOLDER f2         on (f2.I_PROJECT = f1.PAR_I_FOLDER)),   models as    (select SNP_MODEL.TECH_INT_NAME,            SNP_MODEL.LSCHEMA_NAME,            snp_table.res_name,            COD_MOD,            SNP_MODEL.COD_MOD || '.' || snp_table.res_name as QUALIFIED_NAME,            SNP_MODEL.REV_CONTEXT,            I_TABLE       from ODI_WORK.SNP_MODEL -- Модели. Расположение Designer/Models       join ODI_WORK.SNP_TABLE         on (SNP_TABLE.I_MOD = SNP_MODEL.I_MOD))      select PROJECT_FOLDER.PROJECT_NAME,          PROJECT_FOLDER.PathFolder,          SNP_MAPPING.NAME,          case            when SNP_MAP_CONN_ie.name is not null and SNP_MAP_CONN_os.name is null                 then 'target'            when SNP_MAP_CONN_ie.name is null and SNP_MAP_CONN_os.name is not null                 then 'source'          end src_trg,          nvl(models_rf.TECH_INT_NAME, models_qn.TECH_INT_NAME) as TECH_INT_NAME,          nvl(models_rf.LSCHEMA_NAME, models_qn.LSCHEMA_NAME) as LSCHEMA_NAME,          SNP_PSCHEMA.EXT_NAME as PSCHEMA_NAME,          nvl(models_rf.res_name, models_qn.res_name) as res_name,          SNP_CONTEXT.CONTEXT_NAME,          SNP_CONNECT.CON_NAME,          SNP_CONNECT.DSERV_NAME,          case SNP_CONNECT.CONNECT_TYPE            when 'D' then 'User Defined'            when 'R' then 'Repository'            when 'H' then 'Hadoop'            when 'P' then 'Pig'            when 'I' then 'Hive'            when 'B' then 'HBase'            when 'K' then 'Kafka'            when 'S' then 'Spark Python'            else SNP_CONNECT.CONNECT_TYPE          end as Data_Server,          TO_CHAR(DBMS_LOB.SUBSTR(SNP_MTXT.FULL_TXT)) as URL      from ODI_WORK.SNP_MAPPING     inner join PROJECT_FOLDER       on (PROJECT_FOLDER.I_FOLDER = SNP_MAPPING.I_FOLDER)     inner join ODI_WORK.SNP_MAP_COMP       on (SNP_MAP_COMP.I_OWNER_MAPPING = SNP_MAPPING.I_MAPPING)     inner join ODI_WORK.SNP_MAP_REF       on (SNP_MAP_REF.I_MAP_REF = SNP_MAP_COMP.I_MAP_REF)     left join models models_rf       on (SNP_MAP_REF.I_REF_ID is not null and models_rf.I_TABLE = SNP_MAP_REF.I_REF_ID)     left join models models_qn       on (models_rf.I_TABLE is null and models_qn.QUALIFIED_NAME = SNP_MAP_REF.QUALIFIED_NAME)        left join ODI_WORK.SNP_MAP_CP SNP_MAP_CP_i       on (SNP_MAP_CP_i.I_OWNER_MAP_COMP = SNP_MAP_COMP.I_MAP_COMP and          SNP_MAP_CP_i.DIRECTION = 'I')     left join ODI_WORK.SNP_MAP_CONN SNP_MAP_CONN_ie       on (SNP_MAP_CONN_ie.I_END_MAP_CP = SNP_MAP_CP_i.I_MAP_CP)        left join ODI_WORK.SNP_MAP_CP SNP_MAP_CP_o       on (SNP_MAP_CP_o.I_OWNER_MAP_COMP = SNP_MAP_COMP.I_MAP_COMP and          SNP_MAP_CP_o.DIRECTION = 'O')     left join ODI_WORK.SNP_MAP_CONN SNP_MAP_CONN_os       on (SNP_MAP_CONN_os.I_START_MAP_CP = SNP_MAP_CP_o.I_MAP_CP)        left join ODI_MASTER.SNP_CONTEXT       on (SNP_CONTEXT.context_code = nvl(models_rf.REV_CONTEXT, models_qn.REV_CONTEXT)) -- в норме быть значение из планировщика с которым запускается маппинг        left join ODI_MASTER.SNP_LSCHEMA       ON (SNP_LSCHEMA.LSCHEMA_NAME = nvl(models_rf.LSCHEMA_NAME, models_qn.LSCHEMA_NAME))        left join ODI_MASTER.SNP_PSCHEMA_CONT       ON (SNP_PSCHEMA_CONT.I_CONTEXT = SNP_CONTEXT.I_CONTEXT AND          SNP_PSCHEMA_CONT.I_LSCHEMA = SNP_LSCHEMA.I_LSCHEMA)     left join ODI_MASTER.SNP_PSCHEMA       ON (SNP_PSCHEMA.I_PSCHEMA = SNP_PSCHEMA_CONT.I_PSCHEMA)     left join ODI_MASTER.SNP_CONNECT       ON (SNP_CONNECT.I_CONNECT = SNP_PSCHEMA.I_CONNECT)     left join ODI_MASTER.SNP_MTXT       ON (SNP_MTXT.I_TXT = SNP_CONNECT.I_TXT_JAVA_URL AND SNP_MTXT.I_TXT_ORIG = 6)     where SNP_MAPPING.name = 'map_Smpl_fromHive_toOracle_overSqoop' -- находим нужный маппинг          and SNP_MAP_COMP.TYPE_NAME in( 'DATASTORE', 'FILE')-- нам нужны только таблицы          and not (SNP_MAP_CONN_ie.name is not null and SNP_MAP_CONN_os.name is not null) -- исключаем промежуточные таблицы src->TMP->trg    order by 1,2,3;  

Диаграмма запроса

Детальный разбор запроса

Давайте разберем его по частям на примере маппинга map_Smpl_fromHive_toOracle_overSqoop:

Результат работы запроса в файле и на скриншоте (разбит на две части):

with PROJECT_FOLDER as (…)
Этот блок кода собирает полный путь к папке, где хранится маппинг. Запрос объединяет имя проекта (PROJECT_NAME) с именем родительской папки (f2.FOLDER_NAME) и дочерней папки (f1.FOLDER_NAME), если они существуют.
Для маппинга map_Smpl_fromHive_toOracle_overSqoop FOLDER_NAME — «EXAMPLES/Hive», а PROJECT_NAME – «EXAMPLES».

models as (…)
Здесь собирается информацию о моделях данных и таблицах, которые в них содержатся. В нашем случае две таблицы smlp_data_tbl (hive) и SMLP_DATA_TBL (oracle).

select SNP_MODEL.TECH_INT_NAME, SNP_MODEL.LSCHEMA_NAME, snp_table.res_name, COD_MOD,SNP_MODEL.COD_MOD ||'.'||snp_table.res_name as QUALIFIED_NAME , SNP_TABLE.I_TABLE     from ODI_WORK.SNP_MODEL -- Модели. Расположение Designer/Models          join ODI_WORK.SNP_TABLE on (SNP_TABLE.I_MOD = SNP_MODEL.I_MOD)          where (snp_table.res_name = 'smlp_data_tbl'               and SNP_MODEL.COD_MOD ='HADOOP_DEFAULT')           or (snp_table.res_name ='SMLP_DATA_TBL'               and SNP_MODEL.COD_MOD = 'PUB_DS_SAMPLES') 

В основном запросе models подключается дважды (models_rf и models_qn). Это сделано для надежности. Сначала идет попытка соединения по идентификатору (I_REF_ID), так как это наиболее точный способ. Если эта связь не найдена, то запрос пытается найти соответствие по полному квалифицированному имени (QUALIFIED_NAME). Если и после этого информация о модели не найдена, вероятно, она была удалена, и в интерфейсе ODI такой элемент будет помечен как «Deleted».

QUALIFIED_NAME

SNP_MODEL.COD_MOD ||'.'||snp_table.res_name as QUALIFIED_NAME 

Здесь создается уникальное полное имя для каждой таблицы, объединяя код модели и имя ресурса. Это имя используется для связи с маппингами по условию (models.QUALIFIED_NAME = SNP_MAP_REF.QUALIFIED_NAME).

left join ODI_WORK.SNP_MAP_COMP … left join ODI_WORK.SNP_MAP_REF
Данный набор соединений связывают маппинг с его компонентами (SNP_MAP_COMP) и ссылками на объекты (SNP_MAP_REF).

Довольно важным элементов здесь является фильтр SNP_MAP_COMP.TYPE_NAME in (‘DATASTORE’, ‘FILE’). Таблица SNP_MAP_COMP хранит все компоненты, которые можно увидеть на диаграмме маппинга. Для построения потоков данных нас интересуют только те компоненты, которые представляют собой внешние сущности:

• DATASTORE – это таблицы и представления в базах данных, т.е. модели данных.

• FILE – это буквально файлы (CSV, TXT и тд)

Остальные многочисленные типы компонентов, такие как JOIN, FILTER, EXPRESSION, AGGREGATE, представляют собой операции преобразования данных внутри маппинга.
Ещё есть компонент REUSABLEMAPPING. Компонент с таким типом представляет собой вложенный маппинг (reusable mapping). В данном запросе он не обрабатывается отдельно, так как его источники и приемники будут корректно отображены при анализе самого вложенного маппинга как отдельный маппинг.

left join ODI_WORK.SNP_MAP_CP … left join ODI_WORK.SNP_MAP_CONN
Здесь мы присоединяем коннекторы (SNP_MAP_CONN) и их точки (SNP_MAP_CP), чтобы отследить направление потока данных (DIRECTION=’I’ для входящего, DIRECTION=’O’ для исходящего).
Как выглядит содержимое таблицы ODI_WORK.SNP_MAP_CP для маппинга map_Smpl_fromHive_toOracle_overSqoop:

select cp.*, t.name as COMP_TYPE   from ODI_WORK.SNP_MAP_CP cp        left join ODI_WORK.SNP_MAP_CP_ROLE r on (r.I_MAP_CP_ROLE = cp.I_MAP_CP_ROLE)         left join ODI_WORK.SNP_MAP_COMP_TYPE t on (t.I_MAP_COMP_TYPE=r.I_OWNER_COMP_TYPE) where cp.I_map_cp in (199434,199435,199436,199439, 199437, 199438); 

where SNP_MAPPING.name = ‘map_Smpl_fromHive_toOracle_overSqoop’) — фильтр для выбора конкретного маппинга по имени из интерфейса.

and not(SNP_MAP_CONN_ie.name is not null and SNP_MAP_CONN_os.name is not null) — это условие исключает промежуточные таблицы, у которых есть как входящие, так и исходящие потоки в рамках одного компонента.

В интерфейсе такая таблица выглядит следующим образом:

case when … then ‘target’ when … then ‘source’ end src_trg
В данной строке с помощью анализа связей в SNP_MAP_CONN (таблица, описывающая потоки данных), запрос определяет, является ли таблица источником (‘source’) или целевой (‘target’).

Считаем, что если есть «input connection point» и нет «output connection point», то это целевая таблица.

А если есть «output connection point» и нет «input connection point», то это источник.

left join ODI_MASTER.SNP_CONTEXT
Эта таблица используется для задания контекста. Для определения контекста используется поле REV_CONTEXT из модели данных. Это контекст, который по умолчанию используется для реверс-инжиниринга.

Важный момент:

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

SNP_LSCHEMA → SNP_PSCHEMA_CONT → SNP_PSCHEMA → SNP_CONNECT — эта цепочка подключений является преобразованием логической схемы в физическую (конкретная схема на сервере) в рамках заданного контекста.

left join ODI_MASTER.SNP_MTXT ON (SNP_MTXT.I_TXT = SNP_CONNECT.I_TXT_JAVA_URL AND SNP_MTXT.I_TXT_ORIG = 6)
В данном подключение извлекается текстовое значение URL. Условие I_TXT_ORIG = 6 — это строго определенный в ODI идентификатор, который говорит, что извлекаемый текст является именно «JDBC URL для сервера данных». Вот несколько примеров других значений:

• 1 (SNP_ACTION.I_TXT_DESC)

• 9 (SNP_VERSION.I_TXT_VERSION_COMMENT)

• 16 (SNP_TECHNO.I_FK_TXT)

• 7 (SNP_CONNECT.I_TXT_JNDI_URL)

• 12 (SNP_CONNECT.I_TXT_WS_BASE_URL)

• 13 (SNP_USER.I_TXT_DETAIL)

В итоге запрос предоставляет не просто логическую карту маппинга, а полную картину потока данных: от расположения в проекте до конкретных URL. Это полезно для анализа сетевых взаимодействий и документирования систем.

5. Заключение

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

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

Освоение прямого доступа к метаданным ODI открывает путь к более глубокому контролю и пониманию ETL-процессов.

6. Полезные материалы

1. Статья посвящена работе с метаданными репозитория — https://www.clearpeaks.com/odi-11g-repository-metadata-tips-tricks/ . Это хороший ресурс для понимания структуры репозитория, поэтому он выделен в отдельный пункт.

2. Другие запросы

   • Набор полезных SQL-запросов к репозиторию ODI. Запросы предназначены для административных задач и управления, например, для поиска сессий с ошибками, анализа запущенных сценариев и получения списков объектов: https://gist.github.com/rmorenobello/0d8add698f70c53020665faf9b813a27

   • В статье приводится и объясняется SQL-запрос, который позволяет извлечь из репозитория точный код (например, SQL), который был выполнен в рамках конкретного шага сессии ODI – https://devepm.com/2020/02/26/odi-hidden-gems-snp-tables-query-to-get-executed-code-example/

   • Статья предоставляет SQL-запросы для получения детальной информации о выполнении планов загрузки, включая статусы отдельных шагов, время их выполнения и последовательность – https://devepm.com/2022/12/06/odi-hidden-gems-snp-tables-query-to-get-load-plan-execution-steps/

3. oracle.com

   • Документация: https://docs.oracle.com/en/middleware/fusion-middleware/data-integrator
     И, в частности, про репозитории: https://docs.oracle.com/middleware/12211/odi/administer-develop/admin_reps.htm

   • Блог: https://blogs.oracle.com/dataintegration/

   • Сообщество: https://forums.oracle.com/ords/apexds/domain/dev-community?tags=oracle-data-integrator