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

How to execute on every 100 rows of data?

Anonymous — Wed, 05 Jun 2013 17:54:37 GMT

Hey,
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 API I am calling can only take 100 rows of data per API call.
I would like to execute an API 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,
Brian

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

alevy — Thu, 06 Jun 2013 04:44:23 GMT

How are you constructing the API call and passing the data - in tJavaRow?

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

Anonymous — Thu, 06 Jun 2013 23:01:42 GMT

I am using the tSoap component to perform the API call.
I am thinking use tFlowToIterate --- something that counts to 100 --- tIterateToFlow (some how batch it into groups of 100) and then execute off each batch

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

alevy — Fri, 07 Jun 2013 04:59:18 GMT

I think the main problem is how to aggregate the 100 rows of data to pass in one tSOAP. If you can do that, then calling the tSOAP only for every 100th row is quite easy e.g.
(construct your SOAP call aggregating the 100 rows of data) --> tFilterRow advanced: Numeric.sequence("s1",1,1)%100==0 --> tSOAP --> (reset your SOAP call construct to start with the next set of 100 rows)
Don't forget OnSubjobOK --> tSOAP for the remaining data rows in excess of a multiple of 100.

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

Anonymous — Fri, 07 Jun 2013 17:31:28 GMT

Yeah, I am trying to figure out how to batch the data every 100 rows, but I like the idea of calling tSoap every 100th row.

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

Anonymous — Fri, 07 Jun 2013 17:33:16 GMT

I am thinking about creating a temp table or temp file and number the rows. Then use tFilter and only allows rows with numbers 100 or less to be sent, the rest get sent back to the temp file renumbered.
Loop until the temp file/table is empty.

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

Anonymous — Wed, 10 Sep 2014 15:49:06 GMT

Hi All, Just wondered if this ever got resolved as I am facing exactly the same problem. Thanks, Ash.

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

Anonymous — Wed, 10 Sep 2014 16:00:04 GMT

I would use a tMap with a filter and a sequence. Ever if the sequence value is a integer multiplier of 100 the filter opens.

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

Anonymous — Wed, 10 Sep 2014 16:21:14 GMT

@jlolling, thanks, for getting back to me so fast!
Sorry to be a pain but would you mind elaborating a little more, im still fairly new to the tool. So inside the tMap, have a filter in there?

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

Anonymous — Wed, 10 Sep 2014 16:44:27 GMT

please read documentation about Talend & filter in tmap
https://help.talend.com/search/all?query=tMap+operation&content-lang=en
regards
laurent

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

Anonymous — Wed, 10 Sep 2014 20:30:38 GMT

In the tMap use this expression as filter

(Numeric.sequence("index",1,1) % 100) == 0

If you need to filter also on other places in your job (even child or parent jobs) take care you give the sequence different names because they are static.

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

Anonymous — Thu, 11 Sep 2014 09:54:18 GMT

Ahh i see what youre saying, but Im not sure this solves my problem. I can only get it to process every 100th record alone, not all in between?
I'm trying to create an XML document to be processed when its 100 element large, once it hits that send the data off to the external service then create the next document with another 100 rows in until there arent anymore rows to process. Is there a way to achieve this?

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

Anonymous — Thu, 11 Sep 2014 10:01:12 GMT

I have an idea, please check if you can implement this.
- Create a sequence (rowID) for incoming rows using sequence generator
- Create a filter to filter records having rowID > 100 and write it to file
- Create subjob and process the file like first step above
- Once the file is processed, move the file and push to archive folder
- and repeat the process again till you have a file
Thanks
Vaibhav

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

Anonymous — Thu, 11 Sep 2014 10:08:41 GMT

@Vaibhav, thanks for the response. I was really trying / hoping to stay away from saving files (as i know there is a split functionality in tAdvancedFileOutputXML), do you know if there are any other ways to keep it all in short term memory without saving to disk?

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

Anonymous — Thu, 11 Sep 2014 10:37:12 GMT

Then, Why not to use buffer/hash components?

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

Anonymous — Thu, 11 Sep 2014 10:44:09 GMT

Okay, i could try. Ive only used these (tBufferOutput) before for passing data up to parent jobs though, how would i use this to pass data at the right time within the same job?

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

Anonymous — Thu, 11 Sep 2014 11:08:55 GMT

Check following, it would be useful - one option
https://help.talend.com/pages/viewpage.action?pageId=3969155#technical-thashinput-scenario_speed

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

Anonymous — Thu, 11 Sep 2014 12:02:51 GMT

I have had a look here, I have tried something similar but couldnt get it to work correctly. I have already pulled all of the data out of the database, I want to loop through each of those records and on record 100 do something. Id rather not make a call to the DB every time I loop. Im really struggling to understand how to do this in Talend without it being really complicated 😞

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

Anonymous — Thu, 11 Sep 2014 12:16:01 GMT

Hi
Take a look at this topic, hope it could give you a hint.
Best regards
Shong

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

Anonymous — Thu, 11 Sep 2014 12:22:19 GMT

Before starting to create a small poc for you, can you pl show your existing job design?