Four Stars

How generate new sequence id while creating new row?

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?
5 REPLIES
Ten Stars

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

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.

Five Stars

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

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

Six Stars

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

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 

Five Stars

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

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

 

Five Stars

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

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

Regards

Keerthi