Tag Archives: graph parameter

The Beauty of Inline CTL Expressions

Leave a reply

Have you ever wondered how to write data records to a file with current date in its name? Then I’ve got a brand new solution for you! Since version 2.8, CloverETL supports inline CTL expressions within graph attributes and parameters. What does that mean? It means that you can use attributes and parameters containing CTL expressions which are evaluated dynamically at run-time!

Do you recall referencing graph parameters using the ${PARAM} syntax? Using inline CTL expressions is even simpler! Let’s assume you’d like to store some data records to a unique file every day, e.g. orders_2009-09-16.dat. Changing the file name every day might be pretty annoying. Well, it’s much more pleasant to use an inline CTL expression. Take UniversalDataWriter for example and simply set its fileURL attribute to something like this:

${DATAOUT_DIR}/orders_`date2str(today(), "yyyy-MM-dd")`.dat

Notice the `date2str(today(), "yyyy-MM-dd")` part — that’s an inline CTL expression. Yes, that’s all you need to do! Just enclose your CTL expression within back quotes and place it anywhere you like. Except CTL transformations of course, that would be meaningless. ;-)

The good news is that you can reference graph parameters from inline CTL expressions. Such graph parameters might again contain CTL expressions. And so forth… Assuming you defined parameters FIRST_NAME and LAST_NAME, defining another parameter, let’s say FULL_NAME, in the following way is perfectly valid:

`substring('${FIRST_NAME}', 0, 1)`. ${LAST_NAME}

Beware, two adjacent back quotes are treated as an empty CTL expression and always evaluated to an empty string. You might also ask how to use back quotes within inline CTL expressions. Well, it’s pretty straightforward, just escape them using a back slash, i.e. `. (Both these features work since version 2.8.1.)

By default, evaluation of inline CTL expressions is turned on. If you want to turn this feature off for any reason, you can simply do so by setting the GraphProperties.EXPRESSION_EVALUATION_ENABLED configuration property to false.

We hope you’ll find this brand new feature useful! You can let us know where and why you use it by leaving a comment to this post.

Hidden Features: Environment Variables in CloverETL Transformation

Leave a reply

Using environment variables

“Environment variable is named value that can affect the way running process will behave on a computer.”

In daily praxis we usually use environment variables with different syntax depending on operation system. On UNIX-like systems we use them with the syntax: $variable_name, on DOS and Windows systems the syntax is: %variable_name%. To list the variables on UNIX-like system we can use env shell command, on DOS and Windows systems set cmd command. You can find more general information on environment variables at en.wikipedie.org.
But enough of general information. Now how we can use environment variables in CloverETL transformation? It’s very simple, you can use it in the same way as you routinely use graph parameters. So if you want to add username of the user under whom the transformation is running to your processed data, it’s nothing more than adding a new field to metadata and write following in CTL (Clover Transformation Language):

function transform(){
...
$0.username := '${USER}'; //UNIX-like systems
OR
$0.username := '${USERNAME}'; //DOS and Windows systems
...
}

But be careful, value of environment variables can contain “bad characters” (,") that have to be escaped by ‘‘ in CloverETL. The safest way to use env variables in CloverETL is to enclose them in quotation marks  ‘'‘.

Overwriting environment variables & priority of parameter definitions

Often it’s very helpful to use environment variables inside CloverETL transformation. But sometimes you want to define your own graph parameter with the same name as the existing environment variable has. And you may ask the question: “Is it possible?”. I answer: “Yes, it is :-) .” Because there is a hierarchy of graph parameter definitions:

  1. Parameter from external parameter file specified at the start of graph execution by -cfg option
  2. Parameter defined at the start of graph execution by -P option
  3. Parameter from external parameter file that is linked to the graph during the graph development
  4. Internal graph parameter
  5. Environment variable

Parameter definitions from the list are sorted by priority (highest to lowest). So if you have internal graph parameter with the same name as the environment variable, the value from internal parameter is always used in CloverETL.