Friday 20 December 2013

context variable in Talend ETL, Executing Talend job from command prompt - Begineers Example

Hi Guys,

This post will tech you about
1) Understanding and usage of context variables
2) Executing Talend job from command line by passing context variables.

References :
Vikram's post :
http://www.vikramtakkar.com/2013/01/understand-context-variables-with.html

Diethard Steiner post:
http://diethardsteiner.blogspot.in/2012/02/talend-open-studio-how-to-set-up.html

Talend Tutorials:
http://www.talendforge.org/tutorials/tutorial.php?idTuto=34

Environment : 
1) TOS 5.1.3
2) Windows 7 OS - Command prompt.

Example Scenario:
Dynamically printing any name that you pass from context variable on console.
This will give you the first basic idea of using context variables in Talend ETL thus you can implement the same concept for other requirements in your project

What is context variable ? Context variables are user defined variable provided by Talend Open studio whose value can be changed at run time.

We can provide the values of the context variables at run time which allows jobs to be executed in different ways with different parameters.

Example:

Find the steps below... For components detailed description, follow the Talend online Tutorials here at https://help.talend.com/display/HOME/Welcome

PART - I
1) Dragging and dropping components  to be use in this job
2) creating context variables
3) Make use of created context variable in "tFixedFlowInput" component.
4) Components usage for this example
5) Executing the job
6) Exporting job and executing from command prompt. 
PART - II
1) Exporting Talend JOB
2) Executing the job from command prompt by passing different parameters(i.e., different context variables).


PART - I
Step 1 : Components in JOB

Drag and drop tFixedFlowInput, tLogRow and tBufferOutput components on to the design area from palette. 

Make "Main" connections in the sequence of components as shown in below figure.
 Way of giving connections 
Right click on 1st component -> Click Row -> Click Main and then connect to 2nd component
In the same way make a Main connection from 2nd component to 3rd component as shown in above figure. 

Step 2:Creating context variable:

There are multiple ways of creating context variables.(As per my knowledge up to this blog written date).  

One of the ways which I am describing here is to create context variable for particular job.. 
Find how image below which are yellow right marked..

The "X" right mark is another way of creating context variables  from repository - This post is not discussing this point rather discussing about above bold text. 


Every job has it's own context variables to create.. 

Context(Job_name) options area :
It'll have 3 sections 
1) Variables 2) Values as tree and 3) Values as tables
also various options to add, delete, move up , move down the context variables as shown in above figure 

Variables :
1) Click on + Symbol and give the name for your context variable ( In this example context variable name is "name"
2) Set what type of context variable in your case ( here it is "String"Type)

Note that when you give name, the Script code generates automatically. 

Values as tree:
Give default values that you want to pass from context variable .. In this example I given it as "Sadakar".. at the time of execution from command prompt you can pass any other name lets say "TalendLearner"..
and the job will take this parameter value and give the same name back on console.


Context variables are user defined variable provided by Talend Open studio whose value can be changed at run time. We can provide the values of the context variables at runtime which allows jobs to be executed in different ways with different parameters. For Example, we can define different context variables for Production & Test and based on the environment the job is running it will set the variable values. - See more at: http://www.vikramtakkar.com/2013/01/understand-context-variables-with.html#sthash.rtPUSPQE.dpuf

Context variables are user defined variable provided by Talend Open studio whose value can be changed at run time. We can provide the values of the context variables at runtime which allows jobs to be executed in different ways with different parameters. For Example, we can define different context variables for Production & Test and based on the environment the job is running it will set the variable values. - See more at: http://www.vikramtakkar.com/2013/01/understand-context-variables-with.html#sthash.rtPUSPQE.dpuf
Context variables are user defined variable provided by Talend Open studio whose value can be changed at run time. We can provide the values of the context variables at runtime which allows jobs to be executed in different ways with different parameters. For Example, we can define different context variables for Production & Test and based on the environment the job is running it will set the variable values. - See more at: http://www.vikramtakkar.com/2013/01/understand-context-variables-with.html#sthash.rtPUSPQE.dpufContext variables are user defined variable provided by Talend Open studio whose value can be changed at run time. We can provide the values of the context variables at runtime which allows jobs to be executed in different ways with different parameters. For Example, we can define different context variables for Production & Test and based on the environment the job is running it will set the variable values. - See more at: http://www.vikramtakkar.com/2013/01/understand-context-variables-with.html#sthash.rtPUSPQE.dpufContext variables are user defined variable provided by Talend Open studio whose value can be changed at run time. We can provide the values of the context variables at runtime which allows jobs to be executed in different ways with different parameters. For Example, we can define different context variables for Production & Test and based on the environment the job is running it will set the variable values. - See more at: http://www.vikramtakkar.com/2013/01/understand-context-variables-with.html#sthash.rtPUSPQE.dpufContext variables are user defined variable provided by Talend Open studio whose value can be changed at run time. We can provide the values of the context variables at runtime which allows jobs to be executed in different ways with different parameters. For Example, we can define different context variables for Production & Test and based on the environment the job is running it will set the variable values. - See more at: http://www.vikramtakkar.com/2013/01/understand-context-variables-with.html#sthash.rtPUSPQE.dpuf
Values as Table:
This will display the values in the form of table.. just for showing all the variables in a neat format.


You have done with creating context variable.. now you need to make use of this context variable

Step 3: 
Making use of created context variable in "fFixedFlowInput" component. 


Now, I am going to describe how you can make use of context variable from tFixedFlowInput 
(This a scenario ...)

IDEA is :
Pass any name(a group of characters - in simple lets say a string)  from "tFixedFlowInput"  with the help of created context variable and display back the same name on the console when execute the job from command prompt. 


1) Click on "tFixedFlowInput" component
2) Click on Component to get it's properties
3) Click on Edit Schema
4) Click on + sign and give Column name( Any name.. it is not mandatory to give the same name as context variable) and close it.
5) Give value for the name ( you can type or press Ctrl+Space and then select the context variable).

Find the image below for flow of steps. 

You are done with the make use of context variable which you created in step 3 in "tFieFixedFlow" component. 
i.e., dynamically you are passing name from "tFileFixedFlow" component to get the same value on the console. 

Step 4: other components to view the output
tLogrow: 
Displays data or results in the Run console.

Detailed description of this component is 

tBufferOutput:
This component collects data in a buffer in order to access it later via webservice for example.

Detailed description of this component is 


Step 5: Save your job and run it
Sample output


PART - II
Step 1: Exporting Talend JOB
1) Right click on your job
2) Click on Export job
3) Give the destination for exporting by giving zip file name.
4) Click on finish. 


Step 2: Executing Talend JOB from command prompt by passing the parameters.
1) Open command prompt if you use window (This example is based on windows 7 OS).
2)  Unzip the exported file and open the open the project using command prompt

Find the image below


Change your directory from the command prompt till you find .bat file of project.
i.e., 
For this example it would be 
D:\>Cd C:\Users\helical002\Desktop\test_param_0.1\test_param_0.1\test_param>

Hold on here.....

Open the .bat file in text editor(Notepad++ is preferrable)... You can find a line(s) code starting with "java"......
for example
java -Xms256M -Xmx1024M -cp classpath.jar; sadakar_project.test_param_0_1.test_param --context=Default %* 

Execute this command from the opened directory.
i.e., 

Complete command execution example

C:\Users\helical002\Desktop\test_param_0.1\test_param_0.1\test_param>java -Xms25
6M -Xmx1024M -cp classpath.jar; bitach.test_param_0_1.test_param --context=Defau
lt %*


Sample output once you execute the above command for this example
.-------.
|tLogRow_1|
|=-----=|
|name   |
|=-----=|
|Sadakar|
'-------'


Now, Give different names as input from the above command.

Syntax for passing parameters :

--context_param param_1_name=param_1_value param_2_name=param_2_value


In this example lets pass a(differs from default) parameter .. lets say "context_variable_quick_understanding"



C:\Users\helical002\Desktop\test_param_0.1\test_param_0.1\test_param>java -Xms25
6M -Xmx1024M -cp classpath.jar; sadakar_project.test_param_0_1.test_param --context_param
 name=context_variable_quick_understanding
.------------------------------------.
|             tLogRow_1              |
|=----------------------------------=|
|name                                |
|=----------------------------------=|
|context_variable_quick_understanding|
'------------------------------------'


That's it... you have done with learning context variables - passing them from command prompt..

NOTE :
When you export your job it will create Default.properties file in your file/folder system.
For example naviage in the folder structure where you can find properties file ( usually it is under contexts folder
C:\Users\sadakar002\Desktop\test_param_0.1\test_param_0.1\test_param\bitach\test_param_0_1\contexts




( Thanks for writing up of "Vikram" , "Dither" on the same for making me to write up this as a quick learning)....


Sadakar
BI developer


I believe in "Learning never exhausts the mind"











Tuesday 10 December 2013

Converting string to date in Talend

Date Expression in Talend

Converting string to date:
 

Relational.ISNULL(row1.Checkdate)==true?
null:new SimpleDateFormat("MM-dd-yyyy hh:mm:ss").parse(row1.Checkdate)



Source :
See the difference b/w small and capital

http://javatechniques.com/blog/dateformat-and-simpledateformat-examples/