topic Re: Process NUM along with from and to column values every i'th record in Talend Studio

Process NUM along with from and to column values every i'th record

Anonymous — Tue, 19 Mar 2019 15:49:04 GMT

Hi,

I wanted output like below:

1,1,1000000
2,1000000,2000000
3,2000000,3000000
4,3000000

Using tDBInput and tJavaFlex I am able to generate the below:

1,1,1000000 2,1000000,2000000 3,2000000,3000000.

How to get the last row.

tDBInput:

"select id from

(select id, row_number() over (order by id) as RowNumber from table) expr1
where mod(expr1.RowNumber, 1000000) = 0
"

tJavaFlex:
Start code:
// start part of your Java code
globalMap.put("startID", "1");
int cntProcess=1;

Main code:
row2.processID = String.valueOf(cntProcess);
row2.startID = (String)globalMap.get("startID");
row2.endID = row1.maxID.toString();
globalMap.put("startID", row1.maxID.toString());
cntProcess++;

End code:
row2.startID = row1.maxID.toString();
row2.endID = row1.maxID.toString();
row2.threadID = String.valueOf(cntProcess);

I wanted this to dynamically prepare the query using >= and <, like below:

sub-process 1 : select * from table where id >= 1 and id < 1000000;

sub-process 2 : select * from table where id >= 1000000 and id < 2000000;

sub-process 3 : select * from table where id >= 2000000 and id < 3000000;

sub-process 4 : select * from table where id >= 3000000;

How to achieve this, as I wanted to run the sub-process independently.

Thanks.

Re: Process NUM along with from and to column values every i'th record

akumar2301 — Thu, 21 Mar 2019 12:40:35 GMT

Your request is little confusing.

can you confirm your INPUT is

1,1,1000000
2,1000000,2000000
3,2000000,3000000
4,3000000

Expected Output :

you want to run below sql for each record record in input(Iteration )

select * from table where id >= column2 and id < column3;

Re: Process NUM along with from and to column values every i'th record

Anonymous — Thu, 21 Mar 2019 16:24:06 GMT

Hi Abhishek,

Sorry if it was confusing.

I wanted to develop a generic ELT job to process all the rows in a table. This job should process splitting the rows based on primary key in multiple iterations.

For example if I have records whose primary key value range from 1 to 3012543, I wanted to process every 1 million rows in a iteration.

Which means there would be 4 iterations.

1st iteration - select where clause will have id >=1 and id < 1000000

2nd iteration - select where clause will have id >=1000000 and id < 2000000

3rd iteration - select where clause will have id >=2000000 and id < 3000000

4th iteration - select where clause will have id >=3000000

How to have a generic tELTOracleMap for all the iterations?

Hope this is clear.

Thanks.

Re: Process NUM along with from and to column values every i'th record

akumar2301 — Thu, 21 Mar 2019 20:14:38 GMT

ok its clear.
Wanted to know why using tELTOracleMap ?
What do you need to do after you select data from table?