topic Re: How to capture # of rows from source,inserted,updated,deleted? in Talend Studio

How to capture # of rows from source,inserted,updated,deleted?

rp2018 — Sat, 16 Nov 2024 07:27:11 GMT

Need to capture following information to a Sql Server table for each of my Talend jobs.

Have a Talend job whose source is Sql Server db and destination is also a Sql Server db. Using tmap to compare then doing inserts,updates and deletes How can I capture what is my jobname,jobstarttime,jobendtime,source qty,insert qty,update qty and delete qty?

This is my job design:

tMSSqlInput(Lookup)

tMSSqlinput--> tMap--------------tMSSqlOutput(Insert new rows)

tMSSqlOutput(Update rows) tMSSqlOutput(Delete rows)

Re: How to capture # of rows from source,inserted,updated,deleted?

TRF — Wed, 17 Oct 2018 03:58:30 GMT

Start date is not directly available as a variable but you can set a global after the tPreJob with the following TalendDate.getDate("yyyy-MM-dd HH:mm:ss").
Same for end date after a tPostJob.
For the rest, see global variables created for each component such as NB_LINE_INSERTED, NB_LINE_UPDATED, NB_LINE_DELETED and NB_LINE.
You get the value after the component has finished to process the rows with the following syntax:
((Integer) globalMap.get(" tMSSqlOutput_1_NB_LINE_INSERTED"))
Except for NB_LINE, if the row count exceed 9999, the returned value is 0. In this case you need to store the concerned rows into a tHashOutput and get the number of rows from tHashOutput_1_NB_LINE.
To get the job name, just refer to the standard variable jobname where you want.

Re: How to capture # of rows from source,inserted,updated,deleted?

Anonymous — Wed, 17 Oct 2018 05:18:26 GMT

In all DI jobs, the Start time, End time and duration can also be derived using tChronometerStart and tChronometerStop component.

For Big data jobs, please follow the suggestions given by TRF.

Here is the snippet of a DI job for your convenience.

Content of tJava:

long ST = ((Long)globalMap.get("tChronometerStart_1_STARTTIME"));
long ET = ((Long)globalMap.get("tChronometerStop_1_STOPTIME"));

Date Start_Time_1=new Date(ST);
Date End_Time_1=new Date(ET);

SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String Start_Time = df1.format(Start_Time_1);
String End_Time = df1.format(End_Time_1);

System.out.println("\n ++++++++++++++++++++++++ Job Summary ++++++++++++++++++++++++ \n");

System.out.println("\n Job Name : " + jobName);

System.out.println("\n Start Time : " + Start_Time);

System.out.println("\n End Time : " + End_Time);

System.out.println("\n Job Duration : " + ((Long)globalMap.get("tChronometerStop_1_DURATION")) + " milliseconds");

System.out.println("\n Input Record Count : " + ((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")));

System.out.println("\n ++++++++++++++++++++++++ Job Summary End ++++++++++++++++++++++++ \n");

Output:

Hope this helps!!!!

Regards,

Ragu

Re: How to capture # of rows from source,inserted,updated,deleted?

rp2018 — Wed, 17 Oct 2018 14:49:01 GMT

This is exactly what I was looking for. Many Thanks!!

Re: How to capture # of rows from source,inserted,updated,deleted?

rp2018 — Wed, 17 Oct 2018 19:41:36 GMT

How do you pass the variables information to the next component from tJava? I am able to see all of the results in the run window, but unable to capture it to tMSSqloutput to insert rows to a log table.

Re: How to capture # of rows from source,inserted,updated,deleted?

Anonymous — Wed, 17 Oct 2018 20:16:48 GMT

Start date and end dates are derived along with the formats. so assign it to a context variable with string data type. use those context variables in tMap and connect it to tMSSqloutput.

Duration, no of records are from global map, so you can use it directly in tMap.

Re: How to capture # of rows from source,inserted,updated,deleted?

rp2018 — Wed, 17 Oct 2018 21:33:03 GMT

When I link from tJava to tMap, I'm not getting any fields. Please advise.