topic Re: How to retrieve the row count from a table? in Talend Studio

How to retrieve the row count from a table?

Anonymous — Mon, 21 Jul 2014 10:10:22 GMT

Hi all,
I need to retrieve the row count of a table. How can I do this?
For this I should execute a select query to the database (Oracle in my case).
Can I use the tOracleRow component to get this done? If so, please advice me.
Thanks,
thivanka.

Re: How to retrieve the row count from a table?

Anonymous — Mon, 21 Jul 2014 10:30:10 GMT

Hi,
Do you want to capture no. of insert, update, and delete for a table? If so, you can add a tFlowMeter component between input component and output component, and then using tFlow MeterCatcher to catch the no of records.

For example:
tFileInput--->tFlowMeter--->tMysqlOutput_1
tFlowMeterCatcher---tLogRow
There is one column called count on the schema of tFlowMeterCatcher which counts the no. of records pass by the specify flow.
Let me know if it is OK with you.

Or you want to count the number of rows are inserted/updated/deleted? Global variable, such as:
((Integer)globalMap.get("tOracleOutput_1_NB_LINE_INSERTED"))?

Best regards
Sabrina

Re: How to retrieve the row count from a table?

Anonymous — Mon, 21 Jul 2014 10:33:55 GMT

Hi Sabrina,
Thanks for your quick reply. But, what I need is not the things you mentioned. For example, say that a table
contains 1548 records. I need to get the number of rows in the table to an integer variable or something. So the
value I want is 1548.
Thanks,
thivanka.

Re: How to retrieve the row count from a table?

Anonymous — Mon, 21 Jul 2014 10:40:34 GMT

Hi,

For example, say that a table
contains 1548 records. I need to get the number of rows in the table to an integer variable or something. So the
value I want is 1548.

Do you mean you want to use SQL COUNT() function in talend DB component?
Best regards
Sabrina

Re: How to retrieve the row count from a table?

Anonymous — Mon, 21 Jul 2014 11:05:06 GMT

Hi Sabrina,
Yeah, I need to use the SQL COUNT().

Re: How to retrieve the row count from a table?

Anonymous — Mon, 21 Jul 2014 11:38:11 GMT

Hi Sabrina,
Is there any solution for this?
Thanks,
thivanka.

Re: How to retrieve the row count from a table?

willm1 — Mon, 21 Jul 2014 20:32:40 GMT

I suppose the crude solution to your problem would be to use a tOracleInput with a select count that you iteratetoflow and use in your job... Not very cool, but would work.

Re: How to retrieve the row count from a table?

Anonymous — Tue, 22 Jul 2014 02:46:31 GMT

Yes, but I was thinking whether we can use a tOralcleRow and execute the SQL COUNT() command in it and retrieve the value from it.
Since the tOracleRow returns a ResultSet, I dont know how to get the count from it.

Re: How to retrieve the row count from a table?

Anonymous — Tue, 22 Jul 2014 04:36:39 GMT

Hi,
Sorry for delay.
tXXXRow component is usually used to any type of sql statement excepts select statement, we use txxxInput component to execute a select statement, if you use use tXXXRow to execute a select statement, it returns a record set, and you are required to use a tParseRecordSet component after tXXXRow to parse the record sets.
Best regards
Sabrina

Re: How to retrieve the row count from a table?

Anonymous — Tue, 22 Jul 2014 07:02:07 GMT

Hi Sabrina,
Thanks for the reply. I will try to do this as you suggested.
Thanks,
thivanka.

Re: How to retrieve the row count from a table?

Anonymous — Tue, 22 Jul 2014 07:36:34 GMT

Hi thivanka,
Feel free to let me know if it is OK with you.
Best regards
Sabrina

Re: How to retrieve the row count from a table?

Anonymous — Tue, 22 Jul 2014 07:58:23 GMT

Hi thivanka,
tAggregateRow component could help You.
If your using tmap then create another output link to tAggregateRow and select the required function from Operations Property of it.
Attached screen shots. Please find it.

In other you can also use tFlowmeter.
Thanks & Regards
Dasari

Re: How to retrieve the row count from a table?

_AnonymousUser — Wed, 23 Jul 2014 03:08:47 GMT

Hi Dasari,
Thank you for you support. I will try what you have given and get back to you.
Thanks,
thivanka.

Re: How to retrieve the row count from a table?

Anonymous — Wed, 23 Jul 2014 21:43:33 GMT

IF the table is really huge (say multi million records), one way is to get it is from Oracle data dictionary tables. select num_rows from dba_tables where table_name ='XYZ'. The catch is that stats need to be collected. Again this solution is if the number of rows is so large that select count(1) from XYZ takes 1/2 hour and does not meet your SLAs

Re: How to retrieve the row count from a table?

_AnonymousUser — Thu, 15 Sep 2016 09:08:42 GMT

Hi xdshi,
Hi want to capture unused records from my toraclerow component. How can it be done?
Thanks,
Anuj

Re: How to retrieve the row count from a table?

llatinsky1652125276 — Mon, 16 May 2022 20:10:34 GMT

Hello, Sabrina. I found this post from many years ago and tried to use your suggestion to get the count of rows selected from source (which is SQL Server) by getting the value of ((Integer)globalMap.get("tDBInput_2_NB_LINE"))) and compare it with the rows inserted into destination which is tDBOutputBulkExec_1 Amazon Redshift. I get the former value in my tBufferoutput without a problem but not the latter - this one is always null. Does the tDBOutputBulkExec act differently regarding rows inserted...? Thank you

Re: How to retrieve the row count from a table?

Anonymous — Tue, 17 May 2022 02:29:09 GMT

Hello,

Would you mind posting your job design screenshots on community which will be helpful for us to understand your situation very well?

Best regards

Sabrina

Re: How to retrieve the row count from a table?

llatinsky1652125276 — Wed, 18 May 2022 13:23:19 GMT

Thank you for considering my question, Sabrina. This is the job I am talking about - those are just straight copies from source (SQL Server to Destination - Redshift), and to check that my number of rows extracted from source matches the number of rows inserted into destination, I decided to use global variables to save each count and return it to the calling job for comparison or even use it inside the child job before committing the data to redshift. This is my design: (and I only started with Talend last month so please forgive me if I am off)

SQL Server component:

Re: How to retrieve the row count from a table?

llatinsky1652125276 — Wed, 18 May 2022 13:58:41 GMT

Redshift component:

now capture the output counts in tFixedFlowInput:

and this is my output to parent:

the parent job has to check where any in count not equal the out count and report it or if the child job can compare them there and not commit to redshift when counts are not equal. I have a bunch of tables like that transferring data from sql server to redshift. The parent job right now just printing out the output parameters - messgae_in_1 shows the correct count but messge_out_1 - which should be coming from redshift shows null:

thi is java code for now just to see that the main job is getting the parameters from the child:

this is the run window when I run the main job :

thank you