topic Re: count records in Talend Studio

count records

_AnonymousUser — Sun, 29 Jul 2012 20:55:20 GMT

I have a requirement where if the table has no data then I should not run that specific job. Is there a component that I can use to check for number of records. Like if count(*) = 0 then end the job else complete the run.
Can somebody show me an example?
MASTER JOB
BEGIN->JOB1->JOB2->END
Below is a DEPTJOB
TABLE1->TMAP->TABLE1_DM
if there are no records in DEPT then I want to exit and run only JOB2

Re: count records

alevy — Mon, 30 Jul 2012 01:20:52 GMT

You would have to read the number of rows using t<DB>Input (SELECT COUNT(*) FROM DEPT) passing the result to, say, tSetGlobalVar and then have an If trigger from t<DB>Input where you can test the globalMap variable created to see if it's >0 and so trigger JOB1.

Re: count records

_AnonymousUser — Mon, 30 Jul 2012 02:37:54 GMT

I have 5 seaparate jobs that needs this count(*) check. Is there a way I can create one job that would take table name as input and return me record count ...instead of doing the same thing in each job.
select count(*) from &variablename -> tsetGlobalvar

Re: count records

alevy — Mon, 30 Jul 2012 02:39:42 GMT

Sure. Put that part into a job or joblet using a context variable for the table name.

Re: count records

_AnonymousUser — Tue, 31 Jul 2012 01:11:42 GMT

I created joblet like
INPUT -> toracleinput->tsetglobavar->output
toracleinput - I gave query like this select count(*) as numofrecords from context.tablename
tsetglobavar - key numofrecords values row1.numofrecords

INSIDE JOB1
JOBLET ->run if ->TABLE1->TMAP->TABLE1_DM
where to provide the value for context.tablename
I am really not clear... can anyone provide a sample flow

Re: count records

alevy — Tue, 31 Jul 2012 02:02:31 GMT

Just define it as the default value for the context variable in your job...

Re: count records

_AnonymousUser — Tue, 31 Jul 2012 21:02:33 GMT

context variable is read only inside the job where I dropped the joblet. Unable to reuse the joblet
joblet -runif records >1 -toracleinput -> tmap -toraclebulkoutputexec
Any ideas how to do this? I need to basically reuse the query
"select count(*) from "+ context.table_name

Re: count records

alevy — Wed, 01 Aug 2012 00:23:04 GMT

Each job that uses the joblet needs to have the context variable defined with the relevant table name as its value. I don't understand why you think it can't be re-used.

Re: count records

_AnonymousUser — Wed, 01 Aug 2012 22:41:38 GMT

thanks, I got the joblet working along with context .

Inside the job I have
joblet -> output_1 -> tjavarow - if numRecordCount>0 -> tjava
- if numRecordCount==0->-> tjava

tjavarow
output_row.numRecordCount = input_row.numRecordCount;
run if has errors not sure how to provide the condition inside run if. appreciate ur help
Is there a way I can get run if directly from joblet?

Re: count records

alevy — Thu, 02 Aug 2012 00:02:24 GMT

Unfortunately, joblets do not yet support RunIf triggers directly so I would design it as follows:
joblet:
toracleinput -main-> tsetglobavar -if (Integer)globalMap.get("numrecordcount")>0-> tjava -onsubjobok-> trigger_output_1
-if (Integer)globalMap.get("numrecordcount")==0-> tjava -onsubjobok-> trigger_output_2
Note that the tjavas are empty; they are just place-holders for the if triggers.
job:
joblet -onsubjobok1-> tjava
-onsubjobok2-> tjava
The problem with your approach is that the if is referencing a variable "numRecordCount" that doesn't exist, you would have to use <rowname>.numRecordCount (the rowname being that from the joblet). There is also no need to define output_row.numRecordCount in tjavarow unless there is a flow from tjavarow.