12.08.2025 06:46
ElizavetaRyumshina 0 150 Источник

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 – Уникальный глобальный идентификатор объекта.

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

Объект

Таблица

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. Полезные материалы

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

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

  3. oracle.com

Комментарии (0)