topic Re: How generate new sequence id while creating new row? in Talend Studio

How generate new sequence id while creating new row?

Anonymous — Sat, 16 Nov 2024 09:42:36 GMT

Hi All, I'm using Talend Big Data for pulling data from ALM to mongoDB. Before that, i store the domain, project & release in one table with unique levelID incremented by 1 using this Numeric.sequence("s1",1000,1) . First time it is working fine like below, Domain | Project | Release | LevelID D1 | P1 | R1 | 1000 D1 | P1 | R2 | 1001 D2 | P1 | R1 | 1002 D3 | P1 | Default | 1003 D3 | P1 | R1 | 1004 D3 | P1 | R2 | 1005 D4 | P1 | R1 | 1006 Then, before executing the job second time i have added one more release in ALM (D2,P1,R2). Now i executed the job & one more record insert into the DB. but the problem is new record not yet inserted at the end of table. It is inserted after this record (D2 | P1 | R1 | 1002) with levelID - 1003. Like below table. Domain | Project | Release | LevelID D1 | P1 | R1 | 1000 D1 | P1 | R2 | 1001 D2 | P1 | R1 | 1002 D2 | P1 | R2 | 1003 D3 | P1 | Default | 1004 D3 | P1 | R1 | 1005 D3 | P1 | R2 | 1006 D4 | P1 | R1 | 1007 New record replace the levelID with existing & after the new record row, all row levelID's are incremented by 1. I want to insert the new release with levelID at the end of table, if existing table have 6 record means new record should insert as a 7th one. How to generate the sequence levelID at the end of table?

Re: How generate new sequence id while creating new row?

cterenzi — Fri, 02 Jun 2017 00:29:00 GMT

As you've no doubt deduced, sequences do not persist across jobs. At the start of the job, you would need to query the table for the last ID, increment by 1, and put that value into a variable. Use the variable as the start value for your sequence instead of 1000.

Re: How generate new sequence id while creating new row?

Anonymous — Mon, 04 Dec 2017 13:37:13 GMT

Hi @cterenzi,

Your answer is informative.

But don't we have any component which stores the last sequence value generated and increment by 1. Similar to Sequence Generator transformation in Informatica.

Regards,

Keerthi

Re: How generate new sequence id while creating new row?

krengan21 — Mon, 04 Dec 2017 14:28:28 GMT

Hi ,

You can get the last value of id by having as a lookup table and in the input component in the limit column have 1 as the value and use sort by desc by this way u can get the maxvalue and in the numericsequence("s1",id_column+1,1) will get you the sequence which you want

Re: How generate new sequence id while creating new row?

Anonymous — Mon, 04 Dec 2017 15:49:49 GMT

Hi @krengan21

Thanks for your response.

But it degrades the performance if I do lookup on the table to fetch max value.

Also I want to generate the sequence id for same target table from multiple jobs which might also run in parallel.

Eg: In informatica, we can have reusable SequenceGenerator which can be called from different mappings at a time to generate SEQID for same target and still generates unique values.

Regards,

Keerthi

Re: How generate new sequence id while creating new row?

Anonymous — Fri, 08 Dec 2017 09:47:09 GMT

Can anyone from Talend Team suggest an alternative for the issue please

Regards

Keerthi