Scripting for ODI Repository

If you need to create a quick script that manipulates ODI Repository objects, Groovy is the programming language of choice. You can run Groovy scripts from within ODI Studio, which offers two major advantages:

Direct access to the odiInstance object: This object represents the current ODI Repository connection, eliminating the need for manual initialisation.
Automatic library linking: ODI Studio handles the linking of ODI and Oracle JDBC libraries for you.

However, if you need to schedule your script or trigger it externally from ODI Studio, you will have to create it as a standalone script. This approach is also often more suitable for complex, multi-module applications.

Creating a standalone ODI Repository access script is straightforward, and it is not limited to Groovy. You can use any programming language that can interact with the ODI Java SDK.

The source code presented in this blog post can be found in this GitHub repository: https://github.com/RittmanMead/groovy-vs-scala-odi-repo-access

Going Standalone

In this blog post, I will demonstrate a simple standalone script in both Groovy and Scala, highlighting the benefits of using strongly typed languages even for smaller tasks.

The scripts in both programming languages will:

Connect to the ODI Repository;
Open a Mapping from a specified Project and Folder;
Analyse the content of the Mapping.

Connection Parameters

Let us begin by listing the ODI connection parameters, as we will need to establish the connection manually in a standalone script.

Groovy:

Map<String,String> odiAccessConfig = [
        masterRepositoryJdbcUrl: "jdbc:oracle:thin:@192.168.1.47:1521:ORCL",
        masterRepositoryJdbcDriver: "oracle.jdbc.OracleDriver",
        masterRepositoryJdbcUser: "prod_odi_repo",
        masterRepositoryJdbcPassword: "oracle",
        workRepositoryName: "WORKREP",
        odiUserName: "SUPERVISOR",
        odiUserPassword: "SUPERVISOR"
]

In our Groovy script, we are using a Map to store the connection values. While it is common to let Groovy infer the data type, here it is explicitly declared as Map<String, String> for clarity.

Note that attempting to access a non-existent value in a Map will result in a runtime error, as a compiler cannot catch such errors.

Scala:

    object odiAccessConfig {
        val masterRepositoryJdbcUrl: String = "jdbc:oracle:thin:@192.168.1.47:1521:ORCL"
        val masterRepositoryJdbcDriver: String = "oracle.jdbc.OracleDriver"
        val masterRepositoryJdbcUser: String = "prod_odi_repo"
        val masterRepositoryJdbcPassword: String = "oracle"
        val workRepositoryName: String = "WORKREP"
        val odiUserName: String = "SUPERVISOR"
        val odiUserPassword: String = "SUPERVISOR"
    }

In Scala, we are using an Object to encapsulate the connection attributes. While a Map could be used, an Object offers stronger type safety. Referencing a non-existent attribute will produce a compile-time error, often highlighted by IDEs like IntelliJ IDEA during code writing.

While Groovy also supports singleton objects, they aren't as elegant or concise as Scala Objects in this context.

Next, we will create an additional set of attributes to specify the ODI Project Code, Folder name, and Mapping name for analysis. Here is the Scala object:

    object mappingSearch {
        val projectCode: String = "DEMO"
        val folderName: String = "SQL_to_ODI"
        val mappingName: String = "SQL_to_ODI___Expressions_3"
    }

Connecting to ODI

The next crucial step is establishing an ODI Repository connection and creating the odiInstance object.

In the Groovy script, the connection process is encapsulated in a function to facilitate potential adaptation for use within ODI Studio:

OdiInstance getOdiInstance(odiAccessConfig) {
  MasterRepositoryDbInfo masterRepoDbInfo = new MasterRepositoryDbInfo(
          odiAccessConfig.masterRepositoryJdbcUrl,
          odiAccessConfig.masterRepositoryJdbcDriver,
          odiAccessConfig.masterRepositoryJdbcUser,
          odiAccessConfig.masterRepositoryJdbcPassword.toCharArray(),
          new PoolingAttributes()
  )

  WorkRepositoryDbInfo workRepoDbInfo = new WorkRepositoryDbInfo(
          odiAccessConfig.workRepositoryName,
          new PoolingAttributes()
  )

  OdiInstance instance = OdiInstance.createInstance(new OdiInstanceConfig(masterRepoDbInfo, workRepoDbInfo))

  Authentication auth  = instance.getSecurityManager().createAuthentication(
          odiAccessConfig.odiUserName,
          odiAccessConfig.odiUserPassword.toCharArray()
  )

  instance.getSecurityManager().setCurrentThreadAuthentication(auth)

  instance
}

// Uncomment this line when running the code outside of ODI Studio:
OdiInstance odiInstance = getOdiInstance(odiAccessConfig)

Explanation:

Master and Work Repository Configuration: The function sets up connection details for both the Master and Work Repositories.
ODI Instance Creation: An OdiInstance object is created using an OdiInstanceConfig object, encapsulating the repository information.
Authentication: An Authentication object is created and set as the current instance's authentication to enable access to the repository.
Function Return: The function returns the initialized odiInstance object, ready for use in subsequent code.

Scala Equivalent:

lazy val odiInstance: OdiInstance = {
  val masterRepoDbInfo: MasterRepositoryDbInfo = new MasterRepositoryDbInfo(
    odiAccessConfig.masterRepositoryJdbcUrl,
    odiAccessConfig.masterRepositoryJdbcDriver,
    odiAccessConfig.masterRepositoryJdbcUser,
    odiAccessConfig.masterRepositoryJdbcPassword.toCharArray(),
    new PoolingAttributes()
  )

  val workRepoDbInfo: WorkRepositoryDbInfo = new WorkRepositoryDbInfo(
    odiAccessConfig.workRepositoryName,
    new PoolingAttributes()
  )

  val instance: OdiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterRepoDbInfo, workRepoDbInfo))

  val auth: Authentication = instance.getSecurityManager().createAuthentication(
    odiAccessConfig.odiUserName,
    odiAccessConfig.odiUserPassword.toCharArray()
  )

  instance.getSecurityManager().setCurrentThreadAuthentication(auth)

  instance
}

Key Points:

Both languages follow a similar approach to establish the ODI connection.
Scala's lazy val ensures the connection logic is executed only when the odiInstance value is first accessed.

Finders for Repository Search

The subsequent step involves initializing Finder objects to locate the desired Project and Mapping within the ODI repository.

Groovy Implementation:

IOdiProjectFinder projectFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class)
IMappingFinder mappingFinder = (IMappingFinder)odiInstance.getTransactionalEntityManager().getFinder(Mapping.class)

Explanation:

getFinder Method: This method retrieves a Finder object for a specific entity type (e.g., OdiProject or Mapping).
Type Casting: The returned object is cast to the appropriate Finder interface (IOdiProjectFinder or IMappingFinder) to enable its specialized methods for searching and retrieving those entities.

Scala Implementation:

    lazy val projectFinder: IOdiProjectFinder = odiInstance.getTransactionalEntityManager().getFinder(classOf[OdiProject]).asInstanceOf[IOdiProjectFinder]
    lazy val mappingFinder: IMappingFinder = odiInstance.getTransactionalEntityManager().getFinder(classOf[Mapping]).asInstanceOf[IMappingFinder]

Note that passing the class as an argument to the function and type casting look quite different in both languages.

Task: Search and Analyse

It is time to delve into the core task of finding and analysing the specified Mapping. The steps involved are:

Locate the Project: Retrieve the Project using its Project Code.
Filter Project Folders: Obtain a list of Project Folders and filter it to locate the desired Folder by name.
Find the Mapping: Within the identified Folder, retrieve the Mapping using its Mapping Name.
Extract and Analyze Components:
- Extract all Components from the Mapping.
- For Filter Components, display the Filter expression.
- For Expression Components, display the number of Attributes.
- List all other Components, providing their type and name.

Crucially, we will incorporate robust error handling to prevent Java exceptions and ensure script resilience. The approaches to error handling in Groovy and Scala will be quite different.

First, the complete code for both Groovy and Scala before we go through them line by line.

Groovy:

// analyse mapping
List<String> analyseMapping(projectFinder, mappingFinder, mappingSearch) {

  OdiProject foundProject = projectFinder.findByCode(mappingSearch.projectCode)

  if (foundProject) {
    OdiFolder foundFolder = foundProject.getFolders().find { OdiFolder f -> f.name == mappingSearch.folderName }

    if (foundFolder) {
      Mapping foundMapping = mappingFinder.findByName(foundFolder, mappingSearch.mappingName)

      if (foundMapping) {
        List<IMapComponent> mappingComponentsToAnalyse = foundMapping.getAllComponents()

        List<String> mappingComponents = mappingComponentsToAnalyse.collect { IMapComponent comp ->
          if (comp instanceof FilterComponent) {
            "Filter Component ${((FilterComponent)comp).name} has filtering expression ${((FilterComponent)comp).filterConditionText}."
          } else if (comp instanceof ExpressionComponent) {
            "Expression Component ${((ExpressionComponent)comp).name} has ${((ExpressionComponent)comp).attributes.size} attributes."
          } else {
            "Mapping Component ${comp.name} is of type ${comp.typeName}."
          }
        }

        return mappingComponents
      } else throw new Exception("Error: Mapping named '${mappingSearch.mappingName}' not found in Folder '${mappingSearch.folderName}'.")
    } else throw new Exception("Error: Folder named '${mappingSearch.folderName}' not found in Project '${mappingSearch.projectCode}'.")
  } else throw new Exception("Error: Project with code '${mappingSearch.projectCode}' not found.")
}

// output and exit
try {
  List<String> mappingAnalysis = analyseMapping(projectFinder, mappingFinder, mappingSearch)
  println("-= ${mappingSearch.mappingName} Mapping Components =-\n\t${mappingAnalysis.join('\n\t')} ")
  System.exit(0)
} catch (Exception e) {
  println(e.message)
  System.exit(1)
}

Scala:

    val foundProject: Option[OdiProject] = Option(projectFinder.findByCode(mappingSearch.projectCode))

    val mappingAnalysis: Either[String, Vector[String]] = foundProject match {
        case None => Left(s"Error: Project with code '${mappingSearch.projectCode}' not found.")
        case Some(project) => {
            val foundFolder: Option[OdiFolder] = project.getFolders.asScala.toVector.find(_.getName == mappingSearch.folderName)

            foundFolder match {
                case None => Left(s"Error: Folder named '${mappingSearch.folderName}' not found in Project '${mappingSearch.projectCode}'.")
                case Some(folder) => {
                    val foundMapping: Option[Mapping] = Option(mappingFinder.findByName(folder, mappingSearch.mappingName))

                    foundMapping match {
                        case None => Left(s"Error: Mapping named '${mappingSearch.mappingName}' not found in Folder '${mappingSearch.folderName}'.")
                        case Some(mapping) => {
                            val mappingComponentsToAnalyse: Vector[IMapComponent] = mapping.getAllComponents.asScala.toVector
                            val mappingComponents: Vector[String] = mappingComponentsToAnalyse.map {
                                _ match {
                                    case filterComp: FilterComponent => s"Filter Component ${filterComp.getName} has filtering expression ${filterComp.getFilterConditionText}."
                                    case exprComp: ExpressionComponent => s"Expression Component ${exprComp.getName} has ${exprComp.getAttributes.size()} attributes."
                                    case otherComp => s"Mapping Component ${otherComp.getName} is of type ${otherComp.getTypeName}."
                                }
                            }
                            Right(mappingComponents)
                        }
                    }
                }
            }
        }
    }

    // output and exit
    mappingAnalysis match {
        case Left(errorMessage) => {
            println(errorMessage)
            sys.exit(1)
        }
        case Right(mappingComponents) => {
            println(s"-= ${mappingSearch.mappingName} Mapping Components =-\n\t${mappingComponents.mkString("\n\t")}")
            sys.exit(0)
        }
    }

Searching for the Project

Both scripts start by searching for the Project using its code:

Groovy:

OdiProject foundProject = projectFinder.findByCode(mappingSearch.projectCode)

Scala:

val foundProject: Option[OdiProject] = Option(projectFinder.findByCode(mappingSearch.projectCode))

Key Points:

findByCode Function: This function attempts to locate a Project based on its code, returning a Project object or null if not found.
Handling Null Values: Groovy directly assigns the result to foundProject, while Scala wraps it in the Option monad to elegantly handle potential null values. This is a great way to avoid the dreaded Java null pointer exception.

Project not found error handling approaches in both languages:

Groovy:

if (foundProject) {
    // Process the found Project
} else {
    throw new Exception("Error: Project with code '${mappingSearch.projectCode}' not found.")
}

Employs a conditional statement to check for a non-null foundProject.
Throws an exception if the Project is not found, indicating an error condition.

Scala:

val mappingAnalysis: Either[String, Vector[String]] = foundProject match {
    case None => Left(s"Error: Project with code '${mappingSearch.projectCode}' not found.")
    case Some(project) => {
        // Process the found Project, return analysis as a Vector of Strings
    }
}

Leverages pattern matching on the Option monad to handle both the None and Some(project) cases.
Uses the Either monad to encapsulate either an error message Left(errorString) or the mapping analysis output Right(analysisVector), avoiding explicit exception throwing.

Summary:

Both languages effectively handle Project retrieval and potential errors.
Groovy relies on traditional conditional checks and exception throwing.
Scala takes a functional approach with monads, pattern matching, and error handling without exceptions.

Locating the Project Folder

Both scripts proceed to locate the Project Folder containing the Mapping. Instead of using a Finder, they directly access the list of Folders from the found Project object. (This is to demonstrate the different ways of finding stuff in an ODI repository.)

Groovy:

OdiFolder foundFolder = foundProject.getFolders().find { OdiFolder f -> f.name == mappingSearch.folderName }

Explanation:

Retrieves Folder List: Calls foundProject.getFolders() to obtain the full list of Project Folders.
Searches by Name: Employs the find function with a closure (lambda expression) to filter the list, locating the Folder with the specified name.
Null Handling Required: The find function in Groovy returns the found Folder or null if not found, necessitating explicit null checks.

Scala:

val foundFolder: Option[OdiFolder] = project.getFolders.asScala.toVector.find(_.getName == mappingSearch.folderName)

Explanation:

Converts to Scala Collection: Uses asScala to transform the Java collection returned by getFolders into a native Scala collection.
Converts to Vector: Creates a Vector for efficient operations.
Finds Folder with Option: Applies the find function with a concise lambda expression to locate the Folder, returning the result that wrapped in the Option monad for seamless and safe value-not-found case handling.

Key Differences:

Null Handling: Groovy requires manual null checks, while Scala implicitly handles nulls through the Option monad.
Collection Handling: Scala often prefers native Scala collections over Java collections for better integration with its functional features.
Concise Lambdas: Both languages leverage lambda functions for search but Scala's lambda syntax is more concise, using _ to represent the argument.

Mapping and Component Retrieval

Both scripts now locate the desired Mapping and extract its Components:

For Mapping search we use the Mapping Finder in the same manner we used the Project Finder.

Mapping Component Extraction:

Groovy: List<IMapComponent> mappingComponentsToAnalyse = foundMapping.getAllComponents() - retrieves a collection of Mapping components.
Scala: val mappingComponentsToAnalyse: Vector[IMapComponent] = mapping.getAllComponents.asScala.toVector - retrieves a collection of Mapping components but we prefer to use a native Scala collection.

Key Differences:

Scala converts the Java collection to a native Scala Vector for efficiency and compatibility with functional operations.

Component Analysis

This is where the distinct approaches of Groovy and Scala emerge:

Groovy:

List<String> mappingComponents = mappingComponentsToAnalyse.collect { IMapComponent comp ->
  if (comp instanceof FilterComponent) {
    "Filter Component ${((FilterComponent)comp).name} has filtering expression ${((FilterComponent)comp).filterConditionText}."
  } else if (comp instanceof ExpressionComponent) {
    "Expression Component ${((ExpressionComponent)comp).name} has ${((ExpressionComponent)comp).attributes.size} attributes."
  } else {
    "Mapping Component ${comp.name} is of type ${comp.typeName}."
  }
}

Explanation:

collect Function: Iterates through the Components, applying a closure to each one.
Type Checks with instanceof: Determines the Component type using instanceof.
Explicit Casting: Casts the Component object to the appropriate type for property access.
Conditional Logic: Uses if...else if...else blocks to handle different Component types and generate analysis information.

Scala:

val mappingComponents: Vector[String] = mappingComponentsToAnalyse.map {
  _ match {
    case filterComp: FilterComponent => s"Filter Component ${filterComp.getName} has filtering expression ${filterComp.getFilterConditionText}."
    case exprComp: ExpressionComponent => s"Expression Component ${exprComp.getName} has ${exprComp.getAttributes.size()} attributes."
    case otherComp => s"Mapping Component ${otherComp.getName} is of type ${otherComp.getTypeName}."
  }
}

Explanation:

map Function: Applies a function to each Component.
Pattern Matching: Leverages Scala's powerful pattern matching to elegantly handle different Component types within a single expression.
No Explicit Casting: Scala's pattern matching implicitly casts the Component to the appropriate type, eliminating the need for manual casting.

Summary:

Groovy relies on conditional statements and explicit type casting for Component analysis.
Scala offers a more concise and expressive approach using pattern matching and implicit casting, which is much more elegant than the Groovy code.

Conclusion

While the Java SDK offers broad language compatibility, not all choices are equally suited for every project. While a dynamically typed language like Groovy excels in quick prototyping and scripting due to its flexible nature, larger projects may benefit from the rigor of statically typed languages like Scala and Kotlin.

The full source code presented in this blog post can be found here: https://github.com/RittmanMead/groovy-vs-scala-odi-repo-access.