About Talend

Talend Open Studio operates as a code generator allowing data transformation scripts and underlying programs to be generated either in Java (OR) Perl. Its GUI is made of a metadata repository and a graphical designer. The metadata repository contains the definitions and configuration for each job. The information in the metadata repository is used by all of the components of Talend Open Studio.

Course Details : http://talend-training.blogspot.in/2013/04/talend-training-course-details.html

Make one Value as Context Variable in Talend

My Incremental Load Logic using context variable(with maxid) in Talend


I faced one problem for making my value as variable. below is the scenario.


I am doing my incremental load job and this will be based on  one auto incremented column.  I need to get the data from my source based on last inserted id in my target database ( After full load execution ). For this I need to get the max value for that id from target database and then i need to use that value in my select statement  with greater than condition in Source Database.


To Resolve this I have created Context variable . 
Then get the  max id from source database  and stored in variable.
Used this variable in my Select Statement for getting only Delta(new) data


Steps:
  • Created contextvariable 
  • Created MysqlInput job to query for max row as maxidfield
  • Created tJavaRow job  to assign the variable  context.setProperty("contextvariable",row1.maxidfield)
  • Then Run, MySqlInput job with an amended query:  "SELECT ... FROM .... where id > "+(String)((String)context.getProperty("contextvariable"))

31 comments:

  1. Can you please explain this with screen shots. My scenario is little bit similar. Instead of Max Id i am trying to get max date modified from target , pass it to variable and filter my source based on variable. Please help

    ReplyDelete
  2. Hi Sravanthi,

    I sent you the sample job in mail. plz check and let me know if you have any issues.

    thanks,
    Raja K

    ReplyDelete
    Replies
    1. Hi Raja - Can you pls explain the same with screenshots - i am loading 10 tables from an XML file. My scenario- capturing the Table name the job is loading, create_date(data from an xml file) - so pls do send me the same as soon as possibl.
      i would like to know how and where to set the value to the Global context variable created. - Thanks in advance

      Delete
    2. Hi,

      My scenario is also similar. I need to only select recordds where the lastmodifieddate or the records > the last run date of the job (set each time the job runs). Could you please send the sample job to aldous.chris@gmail.com.

      Thanks in advance!

      Chris

      Delete
    3. Hello Chris,

      Am newbie to Talend. Here is my scenario, I have a Parent Job which will internally invoke 3 Job-lets. And the Job-lets is having file parsing, which I have configured the file details in metadata. So now the path of the file in metadata, I should pass through a global context variable which I will run and get it from my Parent Job. So please let me know how can I pass a global context variable value to meta data(File XML). Thank you in advance.

      Kind REgards,
      Neeraja.

      Delete
    4. Hello Raja,

      Am newbie to Talend. Here is my scenario, I have a Parent Job which will internally invoke 3 Job-lets. And the Job-lets is having file parsing, which I have configured the file details in metadata. So now the path of the file in metadata, I should pass through a global context variable which I will run and get it from my Parent Job. So please let me know how can I pass a global context variable value to meta data(File XML). Thank you in advance.

      Kind REgards,
      Neeraja.

      Delete
    5. Hi Neeraja,

      You can pass the file path as context variable to the child jobs.

      1. if you are using joblets then you don't need to pass any parameters. because joblet can use all context variables of the parent job where the this job let is being used..

      2. IF you are using mutiple(like parent and child) jobs then you can pass the context variable to the subjob.
      1. declare the context variable inside each job with same name
      2. For the child job(trunjob) component you can select trasmit whole context option. then this parent job context variable values will be assigned to child job.
      3. then use the context varaible instead of hardcoded file name by changing property type to build in from repository

      Thanks,
      Raja K

      Delete
    6. Hi Raja,
      Thank you for quick reply. Yes, I have done all that options and am able to pass global context variable into my child jobs. However, the issue is in one of that child job I have an XML to parse. SO that XML file properties I have configured in meta-data(FileXML), so now how can I pass the context variable value into that meta-data(File XML)?? Thank you.

      Delete
    7. Hi Neeraja,

      You can do this by changing the "Proper type" from Repository to Built In the (component tab of ) tFileinputXML component .

      thanks,
      Raja K

      Delete
    8. Hi Raja,

      Yes, am using the same functionality to import the file properties from Metadata i.e., Built in. But my question is, in bulit in(Metadata), while configuring it will ask for file path. So my requirement is, I have pass that file path from context variable. So pls let me know how can I pass the context variable to Metadata

      Delete
  3. HI Raja,

    I am new to talend. I want to know how to do incremental loading as our source data is coming in daily basis. can you please send me one sample example to "torafi.sk@gmail.com"
    Thanks in advance

    ReplyDelete
    Replies
    1. Hi,

      If you don't mind shooting me the same sample example.

      aashikhan05@hotmail.com

      Thanks,
      Asher.

      Delete
  4. iam new to talend will you please send me the job(incremental_loading) to my mail_id
    bietl.kiran@gmail.com

    Thanks in advance

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Can you please explain this with screen shots. My scenario is little bit similar. Instead of Max Id i am trying to get max date modified from target , pass it to variable and filter my source based on variable. Please help

    ReplyDelete
  7. Hi Friends,

    I am not able to add screen shots in the comments. please send me your email ids..then i can forward the job with screen shots.

    Please find the steps below.


    You can do this with tFlowtoIterate component.

    step1: take tmysqlinput component and write your max date query there.(ex: select max(date) from mytable )


    step2: take tFlowtoIterate component and map it from tmysqlinput------->tflowtoIterate(flowtoIterate will give the input column as variable)


    step3: connect this tflowtoIterate component tmssqlinput job (tmssqlinput).(iterate flow)


    setp4: you can use the maxdate variable you mssqlinput by clicking on ctrl+space and select the respective variable.






    NOTE: while doing date comparisons use Talend date compare function instead of using < and > .

    ReplyDelete
  8. Hi Friends,

    Thank you for your comments. I have forwarded the screen shot to the people who mentioned their email ids. please let me know i miss any one.

    thanks,
    Raja K

    ReplyDelete
  9. Could you please send me the email as well @kumarkamal.dse@gmail.com Thanks

    ReplyDelete
  10. Hi Raja,

    Please send the mail to me as well with screen shots and sample job @chakarabat@gmail.com

    Thanks,
    Chakravarthy

    ReplyDelete
  11. Hi Raj,
    Could you please send me the same.
    Email Id: sadiqmaniyar@yahoo.com

    Thank you,
    Sadiq

    ReplyDelete
  12. Hi Raja,
    Could you please send me the same.
    jeganbaskar@gmail.com

    ReplyDelete
  13. Hi Raja,
    Could you please send me the same.
    jeganbaskar@gmail.com

    ReplyDelete
  14. Hi Raja

    Could you please send me the screen shot for above job.

    ashu100ashu@gmail.com

    ReplyDelete
  15. pls send me too as well.
    raajag@gmail.com

    ReplyDelete
  16. Plz send send me the same
    erankitkhanduri@gmail.com

    regards

    ReplyDelete
  17. Could you please send me the screen shot for above job.

    praba.talend@gmail.com

    ReplyDelete
  18. Could you please send me the screen shot for above job.

    praba.talend@gmail.com

    ReplyDelete
  19. Hi Raja,

    I am new to Talend and I have a similar issue, a little, bit different.

    I have a csv file with SalesCode (1004,1002,1003,1100,..)
    I have to dynamically pass this salescode to a query string in my xml

    select Id,AccountDes,Name from Product where SalesCode='+SalesCode+)

    Please can you help here.
    I am in desperate need of this help

    my email address is veesam99@hotmail.com

    Thank You
    Rav

    ReplyDelete
  20. Hi Raja,
    Please send me screen shots satish.sat1507@gmail.com thanks in advance.
    Regards
    satish

    ReplyDelete
  21. Hi Raja,

    Could you please send me the screen shot for above job.

    hareesh.pr93@gmail.com

    ReplyDelete
  22. please send me the mail for me
    avinash.r@prowesstics.com

    ReplyDelete