Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Wednesday, January 5, 2011

Using Expressions and Variables in Talend Maps

The Map -- tMap in Talend Open Studio -- is the workhorse of data conversion and ETL processing.  Occasionally, you'll need to work with a variable or expression.  There are several places in Talend where you can put an expression.

This article is available as a PDF here.

Maps are a productive way to transfer the fields of a source to a target using a graphical tool.  When there's a one-to-one correspondence between source and target fields, the map can be created with a simple drag-and-drop.  However, sometimes the map may require some logic or special processing in certain fields.

Examples of this special processing include

Null handling 
(row1.State==null)?"":row1.State 

Specifying a default value
(row2.New_Region==null)?row1.Region:row2.New_Region  

In tMap, you can put these variables in three places:

  1. In the target field Expression,
  2. As a Var, or
  3. In a tSetGlobalVar
The target field and Var panel locate the variable within the tMap component.  tSetGlobalVar locates the variable and expression out of the tMap component.

IN THE TARGET FIELD

Writing expressions in the target field is a concise way to process a field.  The target field Expression features an Expression Builder.

Expression Defined in Target field


AS A VAR

If your expression needs to be used throughout the map's fields, you can create a variable in the same window.


Notice that the variable is preceded with 'Var'.

USING TSETGLOBALVAR

This step involves adding another component to the canvas.  In addition to tMap, add a tSetGlobalVar component.  Rather than connecting the input Excel connection to the tMap, put the tSetGlobalVar in between.  This will require re-mapping the source and target fields; use the row of the tSetGlobalVar rather than the Excel file.

Double-click on the tSetGlobalVar and add a variable.  Make sure the variable is surrounded with quotes.  My example uses "mystate" and it is set with an expression that handles the null.  In the tMap, you'll use the following syntax in the target field.  The variable is stored in a Java map. Don't forget the cast.


(String)globalMap.get("mystate")



There is a lot of flexibility in setting variables in Talend Open Studio.  Pick the most concise syntax that gives you access to the variable where you need it.

14 comments:

  1. Check this page out for some useful Commons Lang functions that can be used in Talend expressions: http://goo.gl/TlxuL.

    ReplyDelete
  2. This page shows Talend expressions being used in tLoop components: http://goo.gl/py0fz.

    ReplyDelete
  3. Hey Carl!

    I'm a newbie in Talend, so I guess this is a very stupid question (and easy, I think) but I'll give it a shot:
    I'd like to use a "variable" in a SELECT-statement, e.g.:
    SELECT *
    FROM my_table
    WHERE my_table_id > *some_variable*;

    I would place the SELECT in a tOracleInput-component - but how can I have the variable in there? This can't be done with a subselest as the variable comes from an MSSQL-database... I would appreciate any help here! Thanks in advance!
    /Daniel

    ReplyDelete
    Replies
    1. Hi Daniel,

      You have a few options. The first takes advantage of the fact that everything in the query text box is a Java string and can be manipulated accordingly with quotes and the plus operator indicating string concatenation. So "SELECT ... table_id = " + globalMap.get("myvar") would add the value of "myvar" to the SELECT statement passed to Oracle.

      Another option is to use a tMap with the inputs coming from both Oracle and MySQL. That is, a virtual join between the two tables with one serving as a lookup.

      There are a lot of examples on this blog using Talend Java variables. Search for things like "globalMap" and "context".

      Delete
    2. Thanks a lot for your instant and nice help! Will try to get it to work now... :-)

      Delete
  4. Thank you for the input. Very usefull

    ReplyDelete
  5. Be careful while comparing strings from different datasources - CSV, XLS files, databases, etc. I had some headache trying to compare two strings from Excel and MySQL. See screenshot here http://goo.gl/PTvvJ for some explanation.

    ReplyDelete
  6. hi, is it possible to do a split into array in the expression builder. am looking at spliting an excel column that has other values. but this particular column has 4 to 9 (,) delimited values. i wish to turn them to columns. Any help.

    ReplyDelete
    Replies
    1. Hi,

      I would investigate using another Talend component to break apart the column rather than expression builder. This example uses tExtractRegExFields. If you want to apply logic on the broken-apart fields, then involved expression builder.

      http://bekwam.blogspot.com/2013/03/parsing-string-using-talend-open.html

      Delete
  7. hello,

    I would extract a date from a name of file to insert into colmun in a table
    exple

    I have in the file name /stream in the tfileinputdelemited:

    c:/unarchive/AZERT_01-0-03-16-06-00-fght.csv ==>

    I would extrat from this filename date: 16/03/2014 06:00:00 with the tmap using expression to insert the date 16/03/2014 06:00:00

    how can i do this ? thanx for your help aappreciated

    ReplyDelete
  8. Hi expression editor in the tmap should check whether the input fileds are null if it is null it should pass only those records but it didnt eork for me if i gave directly ****==null it not catching the rows.

    ReplyDelete
  9. hello, a question I have an Informix table that has a field of type datetime I want to extract records from this table by filtering on the DataTime to another new table field, for example filter all records with today's date and create a new table with the result.
    Thank you very much for answering, Greetings from Peru.

    ReplyDelete
    Replies
    1. I haven't worked with Informix and Talend yet, but here's a suggested starting point. Use a tInformixInput component to connect and read the source data. Use a tInformixOutput component to connect to and write the output data, creating a table on-the-fly if needed. Regarding the filtering, there is a special purpose tFilterRow component available in Talend, however, I usually end up expanding the SQL used in the tInformixInput component for performance reasons.

      I always place a tMap component between the input and output components for compatibility. If the input and the output are the same schema, then this becomes a trivial step that can be implemented with the Auto Map feature.

      Buena suerte.

      Delete
  10. Hi all,please any one hep how to solve this problem
    source Target should be

    id,salary id,salary
    10,1000 10,1000
    20,3000 20,4000
    30,4000 30,8000
    40,2000 40,10000


    How can i achieve this please help me

    ReplyDelete