topic Re: Is there any unique row number generator in Talend Studio

Is there any unique row number generator

Anonymous — Sat, 16 Nov 2024 13:32:54 GMT

Hi all,
I want to load the data from dimension tables to fact table. In this case i have to load the unique row number in fact table . Its like as a sequence generator in ETL- Inofrmatica . How can i do ? plz expecting ur reply
thanks
guru

Re: Is there any unique row number generator

Anonymous — Tue, 16 Feb 2010 20:56:13 GMT

There is the tAddSequence component in the Misc category. It adds a unique row number at the and of the flow. Give that a try.

Re: Is there any unique row number generator

alevy — Fri, 05 Mar 2010 05:04:20 GMT

That's not a standard component. In tMap, you can have a field populated with Numeric.sequence("<sequence name>",<seed value>,<increment step>).

Re: Is there any unique row number generator

_AnonymousUser — Tue, 26 Feb 2013 00:44:48 GMT

how can i keep track of highest sequence value from my previous job execution?
i don't want to start the sequence to start from min value each time on each job execution.

Re: Is there any unique row number generator

Anonymous — Tue, 26 Feb 2013 02:28:07 GMT

If you have a Database SEQUENCE for example it exist in ORACLE you can reuse the Oracle Sequence using the Additional Columns fucntion of the Advanced TAB of your DatabaseOutput component.
You name the COLUMN where you want to put the SEQUENCE; you use the Position Replace; and then you use the proper expression in the SQL expression (for example my_seq.nextval).
Another scenario would be to have a connector that RETRIEVE your SEQ first; then you assign the VALUE to a a Context variable and you start from there a Talend sequence using the routine Numeric.sequence.
There is so many ways for doing it.
Which one are you looking forward; the one which stick with the DB; or the one which is less DB activity consuming etc...
Best regards.

Re: Is there any unique row number generator

_AnonymousUser — Tue, 26 Feb 2013 05:46:21 GMT

Hi cantoine,
i could run my job successfully when i had Oracle Table as my final table.
how can i achieve same results if i want to write into a delimited file.
i dont want to use second approach mentioned in your previous reply.
thank you for your quick response.

Re: Is there any unique row number generator

Anonymous — Tue, 26 Feb 2013 13:43:49 GMT

If I understand you want to Incremental a SEQUENCE to store in a FILE, right ?
This sequence value is coming from an Oracle Table, right ?
The only way of doing it is to call the current SEQUENCE VALUE from a tOracleInput at the begining of your JOB; and then use this value as a ContextVariable to start your Numeric.Sequence routine in your tMap; then this values will be written in your FILES.
In the meantime if you need to refresh the Sequence at the end of your PROCESS; you will have to call a tOracleROW and perform the query to update your SEQUENCE value with the current Numeric.Sequence or do a + File_NUMBER_OF_ROWS from the Global Variable.
Hope it helps.

Re: Is there any unique row number generator

_AnonymousUser — Tue, 26 Feb 2013 22:32:46 GMT

cantoine,
thank you for your guidance. i created my job as per the design. Also i used below sql in tOracleRow componenent to have the sequence recreated in DB after completion of my processing logic.

"begin
execute immediate 'drop sequence customer_ID';
execute immediate 'CREATE SEQUENCE customer_ID
START WITH "+ Integer.valueOf(Integer.parseInt((String)globalMap.get("currVal"))) +"
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER';
end;
"