topic Re: How to execute on every 100 rows of data from DB? in Talend Studio

How to execute on every 100 rows of data from DB?

Anonymous — Tue, 06 Aug 2019 11:51:15 GMT

Hi,

I am pretty sure talend should be able to do this task relatively easily, but I am not sure the best way to go about it. I have 100,000 rows of data, but an tSOAP I am calling can only take 100 rows of data per tSOAP call. I would like to execute an tSOAP call on 100 rows each time until I have looped through the full 100,000 row data set. Any advice/recommended components on going about this is much appreciated.

Thanks,

Pratap.

Re: How to execute on every 100 rows of data from DB?

Anonymous — Wed, 07 Aug 2019 11:46:07 GMT

Any Update please

Re: How to execute on every 100 rows of data from DB?

JR1 — Thu, 08 Aug 2019 20:39:33 GMT

Not really sure what you have as an input but here I will assume it is a csv file. Set your job up like this (assuming you have a header in your input file):

tFileInputDelimited (pointing at your file just to count the records - no outputs) 
-- OnSubjobOK -->
tLoop (From: 0 To: ((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")), Step: 100)
-- Iterate --> 
tFileInputDelimited (pointing at your file again but this time with Header: 1 + ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")) and Limit: 100)
-- Main --> 
tSOAP (or whatever you want to do after this)

This will split up your input into batches of 100. If you are using a different kind of input (e.g. an Oracle table) the counting of the records and querying the table will look different of course.

Let us know if this helps.

Re: How to execute on every 100 rows of data from DB?

Anonymous — Fri, 09 Aug 2019 07:35:21 GMT

Thanks
It seems like, working but i have 220 custoemrs in .csv file, but when every do the iteration with 100, then 300 customers are processed, i hope, duplicate records also processing? How we can fix this issue

Re: How to execute on every 100 rows of data from DB?

JR1 — Fri, 09 Aug 2019 07:58:17 GMT

Have you tried it? If you set the limit to 100 and there are only 20 records left from where you start (by setting the header value), it will only process those 20 records. I do not really know what you mean with duplicates.

Re: How to execute on every 100 rows of data from DB?

Anonymous — Fri, 09 Aug 2019 08:55:48 GMT

Yes, i tried, but getting some of customer coming again into flow ,
Below implementation where i did,
tFileInputDelimited (pointing at your file just to count the records - no outputs) - Just file location given, i didn't given any header and limit here, is it do any thing other then file
-- OnSubjobOK -->
tLoop (From: 0 To: ((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")), Step: 100)
-- Iterate -->
tFileInputDelimited (pointing at your file again but this time with Header: 1 + ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")) and Limit: 100)- same i did, i didn't done anything other then above
-- Main -->
tSOAP (or whatever you want to do after this)-used

Re: How to execute on every 100 rows of data from DB?

JR1 — Fri, 09 Aug 2019 09:32:38 GMT

Now I get what you mean with duplicates. You will have to look into the calculations to make sure that a single record is not processed in another batch again. I do not know if your file has any headers or footers in it. Please try to understand the calculations done here and try to adapt them to your needs. Unfortunately, I cannot present you with a perfect solution here.

Re: How to execute on every 100 rows of data from DB?

Anonymous — Fri, 09 Aug 2019 10:19:24 GMT

here what is happening, i have file with values
123451
123456
124578
124574
125485
till 1000 rows
Ourput: 123451,123456,124578,124574,125485.. 100rows
123456,124578,124574,125485.. 100rows
124578,124574,125485.. 100rows
124574,125485.. 100rows
like that displaying, next row start with previous row 2nd id/customer, so its sending duplicate ids
Suggest if have any solution.

Re: How to execute on every 100 rows of data from DB?

JR1 — Fri, 09 Aug 2019 10:40:59 GMT

There is something wrong with the "header" value in the second file component. Looking at this, I would assume that you have chosen ((Integer)globalMap.get("tLoop_1_CURRENT_ITERATION"))) instead of ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))). Correct?

Re: How to execute on every 100 rows of data from DB?

Anonymous — Fri, 09 Aug 2019 11:02:31 GMT

Yes, Exactly, its working fine now.Thanks

Re: How to execute on every 100 rows of data from DB?

Anonymous — Fri, 09 Aug 2019 11:48:08 GMT

Could you please let us know that how we can loop from DB instead of tFileInputDelimited (pointing at your file just to count the records)

Re: How to execute on every 100 rows of data from DB?

elkhounds — Fri, 09 Aug 2019 18:44:07 GMT

I have a similar use case, except I need to execute (or Chunk) every 100 rows from an Oracle DB to a tSOAP call.

Thanks

Re: How to execute on every 100 rows of data from DB?

JR1 — Fri, 09 Aug 2019 21:48:01 GMT

For Oracle DBs, you would have to get the row count first by using a tDBRow component and performing a "SELECT COUNT(*) FROM XXX WHERE YYY". Use a tFlowToIterate component after that and store the only result record (there can be only one) via a tSetGlobalVar in a global variable.

Instead of the second tFileInputXXX component, you would use a tDBInput component and for Oracle, you would use a statement like the following:

SELECT A, B, C, D FROM XXX WHERE YYY AND ROWNUM > " + String.valueOf(((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")))) + " AND ROWNUM < " + String.valueOf(((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))) +100)

I think PostgreSQL and MySQL (and others) do not have the ROWNUM pseudo-column - you will have to find your own way here...

Re: How to execute on every 100 rows of data from DB?

Anonymous — Wed, 08 Apr 2020 09:12:28 GMT

I am trying to do the same thing in sql server. I am able to get trest call out for one row. In that case i am using value from 1 to row count with step as 1 in tloop component and the output of tdbinput am writing as global variable in tjava. But am not able to do the same in batch of 10. I am able to do till tdbinput by putting where condtion as id >=" + (Integer)globalMap.get("tLoop_1_CURRENT_VALUE") + "and id <= " + (Integer)globalMap.get("tLoop_1_CURRENT_VALUE") +10 . But not able to proceed after that. I tried titeratetoflow with value ((String)globalMap.get("tDBinput_4_QUERY")).

Below is my job:

new_job_issue.jpg

Re: How to execute on every 100 rows of data from DB?

Anonymous — Wed, 08 Apr 2020 21:09:41 GMT

Is someone online and can help me...thanks