One Star

What is the best way to generate unique ID numbers

I want to generate random but unique ID numbers in a certain format for insertion in a database - what is the recommended procedure to achieve this?
18 REPLIES
One Star

Re: What is the best way to generate unique ID numbers

depends on the format and the rules you need for that
Best way, escpecially with databases is to let the database do that job -> auto-increment
Assuming that this is not helping you with your problem you could use the talend routine Numeric.sequence and editing the result accordingly to any rule or format you need to match
Lordan
One Star

Re: What is the best way to generate unique ID numbers

Thank you. Basically we want to generate SugarCRM IDs which must be unique and on this format:
208ae41d-6f4c-4500-bf28-49cfaa77c9aa
As you can see they are actually hexadecimal, however I am sure using only 0-9 would suffice as long as the ID generated is unique within the system.
When does the numeric sequence reset itself, every time the job runs or when Talend starts?
One Star

Re: What is the best way to generate unique ID numbers

depends on your ToS version
but you can easily decide when it has to be restarted, use the method Numeric.resetSequence(String seqName, int startValue)
Usage:
Numeric.sequence("mySeq", 1, 1) -> sequence name = mySeq, start value 1, step 1
-> Numeric.resetSequence("mySeq", 1)
But I fear it would be complicated to use that for your needed format, better write a routine doing that for you
One Star

Re: What is the best way to generate unique ID numbers

Even better - it turns out that there is actually a MySQL function called UUID() which generates exactly this type of ID!
One Star

Re: What is the best way to generate unique ID numbers

perfect :-)
One Star

Re: What is the best way to generate unique ID numbers

Smiley Happy Now just how to tell Talend to utilize this MySQL function with an expression within tMap - is that even possible?
One Star

Re: What is the best way to generate unique ID numbers

I don't know the syntax right now but use a tMysqlRow, so you can write the query yourself including function calls
the value in your data flow put as a input link in that row component so you can access the value.
If the data flow from the tMap is named row1 and the needed column Id just enter in the query
..." + row1.Id + "....
One Star

Re: What is the best way to generate unique ID numbers

Thank you - is it not possible to call this function in a tMap expression? I need to do other magic such as combining two address fields into one etc, so I will need a tMap anyway.
One Star

Re: What is the best way to generate unique ID numbers

Unfortunately, in the job I have created now (see screenshot), the outcome of the last_insert_id is 0 and not the result of a UUID() function call.
FYI: After the screenshot was taken the query has been edited to have quotes around it so it now reads:
"INSERT INTO leads (id, account_name)
VALUES(UUID(),'TESTTESTTEST')"
One Star

Re: What is the best way to generate unique ID numbers

Is the column ID the PK for that table?
Is that represented in the schema too?
One Star

Re: What is the best way to generate unique ID numbers

Is the column ID the PK for that table?
Is that represented in the schema too?

Yes, absolutely.
One Star

Re: What is the best way to generate unique ID numbers

Let me repeat my other question as well - is it not possible to call the UUID() MySQL function in a tMap expression rather than through an explicit INSERT query?
One Star

Re: What is the best way to generate unique ID numbers

As far as Primary Key goes, it is indeed PK in the database which I am trying to write to, but the ID field does not exist in the Excel file I am reading from. And when I add it in the schema on the database side (of the Input connection) I get an error message saying that the schemas do not match. Do I need to use a tMap in order to have non-matching schemas?
One Star

Re: What is the best way to generate unique ID numbers

I added an ID field (key, not nullable) before the INSERT MySQL component ("leads") and that got rid of the error message, but still the resulting value of last_insert_id is 0.
Please see screenshot of where the id gets added in a tMap.
Seventeen Stars

Re: What is the best way to generate unique ID numbers

hi,
have a look at this post ...
it could help you ... I hope Smiley Happy
regards
laurent
One Star

Re: What is the best way to generate unique ID numbers

hi,
have a look at this post ...
it could help you ... I hope Smiley Happy
regards
laurent

That looks perfect, thank you very much!
One Star

Re: What is the best way to generate unique ID numbers

I want to generate random UUID numbers in a certain format for insertion in a database - what is the recommended procedure to achieve this?
I have been ask to use routines but it does not work Smiley Sad Theres a field in file called id and i wan to generate random uuid for every row to put it in my database
Four Stars

Re: What is the best way to generate unique ID numbers

 Hi , 
Any One Can elaborate on this 
 I am Reading data from Mssql data base table ,its has UniqueIdentiifer Field, I want to Insert This uniqueidentifer filed to Target Table
 How to Insert UniqueIdentifer Filed to Target Table Through tmap or mssqloutput
 
Please help on this