Friday, April 29, 2016

CloverETL User Defined CTL Functions

Are there custom functions or functionality that you use over and over again and are sick of copying/pasting the code into a CTL Editor? Did you know that you can save a function to use within any transformation? 

Example: You have a set of multiple delimited files where there could be any number of delimiters. Your goal is to split the contents by all delimiters for further processing, but what if the input files contain different delimiters?   

Input 1:

Input 2: 

As you can tell by the input sources, there’s not an easy way to split these records apart. Now, add 10000 lines to each input with different delimiters. How can you create something that you can reuse for each input row? The answer is to create a custom CTL function in CloverETL and reuse that function for processing each input record. I have created the splitStringWithMultipleDelims CTL function which will return a list of strings after the input is split by the delimiters that are passed into the function. I will not go into the logic of the function as that’s not what’s important here.

How can we reuse this logic to process each input record? You can call this function in any of the transformation editors where you can write CTL code by following this example:

The important items here are the import statement which points to your CTL function definition file, the function call with the appropriate input values, and the function return value. The CTL compiler will complain if you have not setup your function correctly; just as it would if you have incorrectly used any pre-defined function in CTL.

For visual purposes, you can see the entire solution below (note, I am using CloverETL 4.1.x Corporate Server). If you have any questions about my implementation or the topic, please do not hesitate to comment below and/or private message me.