topic Count of Inserted rows in Talend Studio

Count of Inserted rows

Anonymous — Wed, 20 Mar 2013 04:54:22 GMT

Hi All,
I have a job which reads more than 5000 records daily. I want to create a log table which captures job status ( OK/FAILED) , No of Rows Inserted , No of Rows Updated , No of Rows Deleted.
So I can be able to identify the no of rows inserted daily.
Kindly assist how to do this.

Thanks,
Saty.

Re: Count of Inserted rows

Anonymous — Wed, 20 Mar 2013 09:27:49 GMT

Hi,
When inserted item is chosen, there will be a global variale such as ((Integer)globalMap.get("tOracleRow_1_NB_LINE_INSERTED")) will be avaible, which counts the total number of records have been inserted into database.
Press ctrl+space to access all the global variables
See my screenshot
Best regards
Sabrina

Re: Count of Inserted rows

Anonymous — Tue, 26 Mar 2013 10:41:59 GMT

Thanks Sabrina,

I tried to use that parameter in my job. But I'm getting null in my output. Kindly go through my screenshots.

Kindly assist.

Thanks,
Saty.

Re: Count of Inserted rows

janhess — Tue, 26 Mar 2013 11:16:00 GMT

You need to make sure the log process is running after the other processes - use an on subjob ok.

Re: Count of Inserted rows

Anonymous — Tue, 26 Mar 2013 11:31:00 GMT

You need to make sure the log process is running after the other processes - use an on subjob ok.

Hi Janhess,
Once the main job execution is done and Job status is captured by tAssert components. And then Log Process works.
So it is the last process to run in the job. No need to use on Subjob Ok for Log Process I guess.
Thanks,
Saty.

Re: Count of Inserted rows

janhess — Tue, 26 Mar 2013 13:46:05 GMT

Shouldn't you be using tMySQLOutput1 for the stats?

Re: Count of Inserted rows

Anonymous — Wed, 27 Mar 2013 04:37:14 GMT

Shouldn't you be using tMySQLOutput1 for the stats?

Hi Janhess,
I'm not clear. Are u asking me whether I'm using these functions using tMySQLOutput component.? If so, Yes. I'm using it to call these functions in tMap ( in Log Process ) component.

Thanks,
Saty.

Re: Count of Inserted rows

janhess — Wed, 27 Mar 2013 09:12:24 GMT

No the tMySQLOutput you reference in your rule is 2. Looking at your picture I thought it should be 1.

Re: Count of Inserted rows

Anonymous — Wed, 27 Mar 2013 11:26:01 GMT

No the tMySQLOutput you reference in your rule is 2. Looking at your picture I thought it should be 1.

Thanks Janhess. I did not observe that. I've changed to 1 and now I got it. I've got values under
' TOTAL_NO_OF_ROWS ' and ' NO_OF_ROWS_INSERTED '. Because I'm only inserting the data.
I've changed the job as in Pic 1. And I have used two queries in tMySqlRow component. One Update and one Delete query. U can see the properties of tMySqlRow component in Pic 2.
Now I want to capture deleted count and updated count.
I hope I've explained clearly. Kindly assist.
Thanks,
Saty.

Re: Count of Inserted rows

Anonymous — Wed, 27 Mar 2013 13:30:03 GMT

Hi Saty
There is no global variable like (Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED") available if you use tMysqlRow to execute the statements, if you want to count the number of rows are deleted or updated, you have to use tMysqlOutput to do it. To do:
1. Query the records from table with tMysqlInput, and link it to a tMap.
2. On tMap, define two output table, one for deleting, and another one for updating, set the filter expression of deleting output as:
row1.id<2000
set the fitler expression of updating output as:
row1.id>5000
3. Link each output to a tMysqlOutput component and select corresponding action 'delete'/'update'.
The job design looks like:
tMysqlInput--main--tMap--out1--tMysqlOutput_1(for deleting)
--out2--tMysqlOutput_2(for updating)
Then, you can use the global variables on other subjob.
(Integer)globalMap.get("tMysqlOutput_1_NB_LINE_DELETED")
(Integer)globalMap.get("tMysqlOutput_2_NB_LINE_UPDATED")
Shong

Re: Count of Inserted rows

Anonymous — Thu, 28 Mar 2013 05:01:01 GMT

There is no global variable like (Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED") available if you use tMysqlRow to execute the statements, if you want to count the number of rows are deleted or updated, you have to use tMysqlOutput to do it.

Thanks Shong.Your reply is very helpful. I've understood the usage of these variables.

Re: Count of Inserted rows

_AnonymousUser — Tue, 09 Jul 2013 13:15:17 GMT

hi,
is it possible to use function inet_aton before inserting data into a mysql database on a col ,if possible can anyone show how to use it and the component used.................

Re: Count of Inserted rows

Anonymous — Wed, 10 Jul 2013 04:18:48 GMT

Hi banu,

is it possible to use function inet_aton before inserting data into a mysql database on a col ,if possible can anyone show how to use it and the component used.................

Could you please elaborate your case with an example with input and expected output values, which helps us to understand your requirement much better.
Best regards
Sabrina

Re: Count of Inserted rows

_AnonymousUser — Wed, 10 Jul 2013 06:37:58 GMT

Hi ,
the data file contains ip address like 10.58.25.26 in a column ,in the table for this column i have given the data type as integer . The inet_aton function normally converts ip address to integer value based on some logic .
Since i want the address to be converted into integer i want to know which component can be used to perform this operation while inserting into database table... is it possible

Re: Count of Inserted rows

Anonymous — Wed, 10 Jul 2013 06:52:33 GMT

hi,
is it possible to use function inet_aton before inserting data into a mysql database on a col ,if possible can anyone show how to use it and the component used.................

Hi
inet_aton is a function of Mysql database, you can't call a SQL function in a Java expression, as a workaround, use a tMysqlRow to execute a query for inserting like this:

"insert into person values ("+row1.id+", INET_ATON('"+row1.ip+"'))"

Shong

Re: Count of Inserted rows

_AnonymousUser — Wed, 10 Jul 2013 07:40:54 GMT

Hi,

Thank u ... and il check on user routines ....

Re: Count of Inserted rows

Anonymous — Wed, 11 May 2016 12:42:23 GMT

Hi id4dsc,
Thank you for ur post.
i got my query resolved with the help of ur post.
Thanks and Regards,
Anand Yadav