[resolved] How to use UUID keys with MySQL in Talend

Seven Stars

[resolved] How to use UUID keys with MySQL in Talend

There are two different ways you can generate UUIDs with Talend to use as keys in MySQL. One of which is the all java method, the other uses MySQL itself to generate the UUIDs.
First, the all java method:
As of Java 1.5, Sun has incorporated a UUID class within the standard edition API. This class, java.util.UUID contains a static method to generate a random UUID. So, for instance, UUID.randomUUID() will generate a new UUID. In a single line of code, you can have a UUID in String form that you can use in the database. For example, UUID.randomUUID().toString() will do this. In Talend there are several ways you can utilize this, which varies depending upon your needs. Below you'll find a couple of examples that show ways to do this.
One way you can utilize a UUID, is to place it in the global map. This can be done with a tSetGlobalVar component. This is shown in the image named setglobalvalue.JPG. From there, it can be retrieved by calling globalMap.get("UUID").toString(). This is shown in the image named gettingfromglobalmap.JPG. In the example job I created, I use a Variable in a tMap to do this, you can also do so in the mapped schema. The benefit of placing it into a variable field is that you can use it in more than one output row without having to write the same globalMap statement more than once, this is evidenced in the image named MappingTheData.JPG. From there, you can use tMySQLOutput components to place them into the database.
In my example job, I have two tables in the database. Both of which have a UUID as the primary key. One of them has a foreign key dependency on the UUID in the other table. The job in its entirety is in the image UUIDWholeJob.JPG.
Next, getting a UUID from the database:
Getting a UUID from MySQL is a simple query "SELECT UUID() UUID." That presents a problem as it will only get one UUID from the database and we need one for each row in the flow. At this point, if we take our row generator and turn its output flow into an iterate, we can grab a UUID from MySQL for each of the rows. This has the caveat of placing the values into the globalMap, which means that for our output from the tMap, we'll need to grab them back out of the globalMap. The schema and query for the tMySQLInput that I use to grab the UUID from the database is shown in image GettingFromMySQL.JPG. Lastly, we have to get the data back out of the globalMap to use in the tMap, how to do this is shown in the mysqltMap.JPG

Accepted Solutions
Seven Stars

Re: [resolved] How to use UUID keys with MySQL in Talend

You aren't going to have more than one UUID because you're only pulling one record out of the postgresql input, what you need to do is disconnect both of the inputs from the tMap. That tFileInputDelimited should be your main row going into the tMap. Depending upon how you're getting the UUID, you'll do one of a couple of things in the tMap. If you're pulling the UUID from the database, this will be your lookup row. Within the tMap, on the lookup row, there is a green arrow pointing to the right with a drop down arrow to the right of it. Click this and select "Reload at each row." This setting is shown in reloadUUID.PNG, which is attached. The whole job looks quite different now as shown in the image named wholeJobUUID.PNG. The other mechanism is generating the UUID directly in the output as shown in my original post by placing a java.util.UUID.randomUUID().toString() call in the field.
Hope that answers your questions.

All Replies
One Star

Re: [resolved] How to use UUID keys with MySQL in Talend

Thank you so much for the instructions.
After playing with the latest Talend Open Studio 3.2.0,
we discovered the tMap relation looks like the attached screen shot.
This is very handy for someone who wants to use MySQL functions.
One Star

Re: [resolved] How to use UUID keys with MySQL in Talend

In your picture : UUIDJobWhole.JPG there are 2 connection : OnSubJobOK and OnSubJobError but what looks like the SubJob ?
One Star

Re: [resolved] How to use UUID keys with MySQL in Talend

In your picture : UUIDJobWhole.JPG there are 2 connection : OnSubJobOK and OnSubJobError but what looks like the SubJob ?

I find the answer of my question... it was so easy Smiley Happy
but I don't understand how generate UUID for each rows. I generate UUID but it's the same for all the job... I work on Postgre for OpenBravo
I upload a picture of my job.
One Star

Re: [resolved] How to use UUID keys with MySQL in Talend

Help!!! Smiley Happy
Seven Stars

Re: [resolved] How to use UUID keys with MySQL in Talend

You aren't going to have more than one UUID because you're only pulling one record out of the postgresql input, what you need to do is disconnect both of the inputs from the tMap. That tFileInputDelimited should be your main row going into the tMap. Depending upon how you're getting the UUID, you'll do one of a couple of things in the tMap. If you're pulling the UUID from the database, this will be your lookup row. Within the tMap, on the lookup row, there is a green arrow pointing to the right with a drop down arrow to the right of it. Click this and select "Reload at each row." This setting is shown in reloadUUID.PNG, which is attached. The whole job looks quite different now as shown in the image named wholeJobUUID.PNG. The other mechanism is generating the UUID directly in the output as shown in my original post by placing a java.util.UUID.randomUUID().toString() call in the field.
Hope that answers your questions.
One Star

Re: [resolved] How to use UUID keys with MySQL in Talend

Many thks for your help !!!! Smiley Happy It's running ! Smiley Very Happy
I begin in Talend, it's not always easy...
One Star

Re: [resolved] How to use UUID keys with MySQL in Talend

One Star

Re: [resolved] How to use UUID keys with MySQL in Talend

many thanks, i managed finally to resolve my problem base on the explication with the guid generated from the mysql Smiley Very Happy