topic Re: Important expressions in tFilterRow and tMap in Talend Studio

Important expressions in tFilterRow and tMap

vladika — Sat, 16 Nov 2024 13:24:27 GMT

Hi everyone,
I'm an extreme newbie with Talend + java so apologiez in advance... But I just entered the desperate phase..
I have a desire to switch from usual query tool to ETL. I'm used to mySQL syntax, and in order to make that move, I need a replacement for the most important functions I use regularly:
Date manipulation:
datediff(), year(), month(), week()
String manipulation:
substring(), substring_index(), concatenate()
Conditional expression:
case...when...then...else...end
Operators:
like, in (), between...and...
I decided to translate one query to a Talend job with output in console (tLogRow) as a good learning example. Query contains: join, multiple where conditions including scalar select subquery in where clause and aggregation. And I 've been stuck on the first tFilterRow for two days...Reason? Lack of knowledge of Java, because I'm not able to get the syntax for advanced mode of tFilterRow. I guess the same syntax shares tMap component. The trouble is that I am not able to find any source of documentation about the code that I can write inside. I downloaded Java language specification, but I can't seem to find anything inside..
For example, I need to translate this mySQL condition:
year(table1.entry_date) = 2010,
(date is in the format yyyy-mm-dd)
From what I picked browsing the forum it should look like this:
TalendDate.compareDate(input_row.entry_date, ????, "yyyy")
But, If I put ???? = 2010 I get an error that I'm using the integer instead of date datatype. If I put ???? = "2010" I get the error that I'm using the string instead of date datatype... So then, how do you quote a literal of "date" datatype ?
And more generaly can anyone point me to some source of documentation where I have listed all java methods I can use inside tFilterRow and tMap...
Big thanx!

Re: Important expressions in tFilterRow and tMap

Anonymous — Thu, 03 Jun 2010 11:35:31 GMT

hi,
this is just a suggestion from someone who has been in this position.
create an empty job and add tRowGenerator, add a column to it; select this column and you will find on the lower portion of this screen under 'Function Parameter' a columns called values click on it and a ellipsis (...) will appear, click on it and you will find all function available to you.
i am sure there are better ways to seeing this but this one works..
Nicolas

Re: Important expressions in tFilterRow and tMap

vladika — Thu, 03 Jun 2010 15:35:32 GMT

Thanx nic,
but I am getting the following error message when I click on ellipsis in the field "Value" within tab "Function parameters" (tRowGenerator component):
Unhandled event loop exception
XPCOM error -2147467259
If I choose not to close the workbench after the error message and click on ellipsis again, Expression Builder opens but all portions of the window are just empty (Categories, Functions, Help)

Re: Important expressions in tFilterRow and tMap

vladika — Fri, 04 Jun 2010 10:43:25 GMT

Ok, now I see the source code of all the methods under Repository / Code / Routines / system.
And I tried to change my condition under tFilterRow / Advance Mode into something trivial. I just took out the example from the TalendDate.compareDate method:
TalendDate.compareDate(2008/11/24, 2008/11/24 ,"yyyy/MM/dd") == 0
And I am still getting the error, that my first two arguments are of type int ?

Re: Important expressions in tFilterRow and tMap

Anonymous — Sun, 06 Jun 2010 17:44:16 GMT

hi vladika
why don't try ELT Talend solution.
Have a look at on demand webinars and in your case look for ELT: High Performance Loading for your Data Warehouse.
I think it give you some solution.
regards
laurent

Re: Important expressions in tFilterRow and tMap

vladika — Mon, 07 Jun 2010 10:17:57 GMT

Thanx kzone! I'll take a look at it for sure. Just by glancing through the documentation (TOS_componenets_RG) I already see that this is exactly what I'm looking for
In the meantime I found the solution, so I'm posting it for all the other newbiez like me. So if you want to convert the mySQL syntax for the condition year(entry_date) = 2010 into Talend, by using only built in functions (java project) this is one way to do it:
TalendDate.diffDate(input_row.entry_date, TalendDate.parseDate("dd-MM-yyyy","01-01-2010"), "yyyy") == 0
But, before this make sure that your "date pattern" in "Edit Schema" window for the column entry_date (in my case) is the same like the format you're having in the DB table. For example, date format in mySQL is "yyyy-MM-dd", but Talend takes the default date pattern from the OS locale date/time format settings, which in case of Ubuntu / US English locale settings is "dd-MM-yyyy"..So it has to be changed.

Re: Important expressions in tFilterRow and tMap

Anonymous — Wed, 09 Jun 2010 08:28:59 GMT

maybe this> TalendDate.parseDate("yyyy", input_row.entry_date ).equals( "2010" )
*BUT*
you should avoid doing this type of filtering in Talend when loading data from a Database,
and instead load only the data you need by using SQL WHERE clauses in your statements
Nicolas