1. Зачем анализировать ODI через SQL-запросы
Работая с Oracle Data Integrator (ODI), мы ценим его графический интерфейс за автоматизацию рутины и удобство разработки. Однако, когда проект масштабируется до десятков пакетов и сотен сущностей, GUI перестает быть оптимальным инструментом для отслеживания потоков данных, глубокого анализа и аудита зависимостей.
В таких случаях ключом к эффективности становится прямое взаимодействие с метаданными ODI через SQL-запросы к его репозиториям. Эта статья посвящена именно этому – практической работе со структурой репозиториев ODI и детальному разбору SQL-запроса для построения потоков данных.
Мы разберем:
Архитектуру репозиториев ODI: Master и Work.
Ключевые таблицы метаданных, их структуру и взаимосвязи.
Практический пример 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 — Уникальный глобальный идентификатор объекта.
Вот несколько примеров:
Объект | Таблица | ID | Имя | Ключевые связи |
Проекты | SNP_PROJECT | I_PROJECT | PROJECT_NAME | |
Папки | SNP_FOLDER | I_FOLDER | FOLDER_NAME | I_PROJECT (к проекту), PAR_I_FOLDER (к родительской папке) |
Маппинги | SNP_MAPPING | I_MAPPING | NAME | I_FOLDER (к папке) |
Сценарии | SNP_SCEN | SCEN_NO | SCEN_NAME | Может содержать I_MAPPING, I_PACKAGE и т.д., в зависимости от источника |
Сессии | SNP_SESSION | SESS_NO | SESS_NAME | SCEN_NO (к сценарию), I_CONTEXT (к контексту) |
Задачи сессии | SNP_SESS_TASK | I_SESS_TASK | TASK_NAME1 | SESS_NO (к сессии) |
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. Полезные материалы
Статья посвящена работе с метаданными репозитория — https://www.clearpeaks.com/odi-11g-repository-metadata-tips-tricks/ . Это хороший ресурс для понимания структуры репозитория, поэтому он выделен в отдельный пункт.
Другие запросы
Набор полезных 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/
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://forums.oracle.com/ords/apexds/domain/dev-community?tags=oracle-data-integrator