Data Migration

[Anonymous]

Hi everybody,

i have a problem (this is only a synthesis). I need to split data from a single mysql table (lar) to 2 tables (Base_User, Base_LegalEntity) and this is sample.

The problem is that Base_Users table have a key (autoincrement) and this key referred to Base_LegalEntity so, when data will stored from Lar to Base_Users I need of this Key and store it on Base_LegalEntity table with the other information.

How do it?

Example:

Look at the attached image: the problem is that "ID" of "Legal" must be the same value generated as autoincrement in the "ID" of User during job migration.

Thanks all.

[Anonymous]

Easy.

1) Load the data into Base_User first. The auto increment key will be generated for you.

2) Read the contents of the Base_User table and load it to the Base_LegalEntity table

[Anonymous]

It's not the solution.
Look at the image, I need to split some attributes of a row in Base_ User and other attributes in Base_LegalEntity.
If I do as you suggest me, I cannot load other data of "row1" of the same record because there is any relations. I need to do this job rows per rows

[Anonymous]

Sorry, I didn't see that. It is still relatively easy though.

Your database is responsible for your auto increment key of the User table. Therefore in order to get that, the User data needs to be loaded first. However, I notice that not all of your fields are being used. I also notice your input table has an "Id" field. So I would recommend the following....

1) Load your User table first as before, but add the source table's Id column to an empty row.

2) Read that table back into Talend and join it (using a tMap) to your source table.

3) Use the data combined in step 2 to write to your Legal table

4) Remove the source "Id" field data from your User table

It sounds long winded, but since you are relying on your DB for the auto increment (which is the best thing to do), you do not really many choices here. 

[Anonymous]

Thank you, I would like to avoid it (it will be the last solution). Is there any other solution (with the use of other Talend components also) to do that? I am not a Talend expert unfortunately.

[Anonymous]

The problem you have is that the database is controlling your keys. While in the tMap (where your image is showing) you have absolutely no way of predicting your Ids with certainty. Of course, there is another way you could approach this if you are more familiar with SQL. You could create a procedure in your database to insert your data to the User table, retrieve the key and then insert the other data into the Legal table with the key generated in the insert. Your procedure can be called via Talend where could supply all of the data in one go. This might be a way to go for you as it will make the Talend job less complex, but you will need to handle the logic in the DB.

I guess you *could* try and handle the sequence in Talend, switch off the autoincrement functionality during the insert and then swicth it back on again after the insert.....but that sounds like a bit too much of a faff to be honest. 

[vapukov]

Why not use sequence (Talend sequence) as generated auto incremented ID?

as I can see, it MySQL, MySQL definitely  allow this,

but as well most of databases allow disable check and allow insert into auto-inceremented column

something like this :

[Anonymous]

I cannot do that because it's a live migration and i should change all applications to manage the autoincrement value to avoid problem with DB cluster.
@rhall I prefer to avoid the use of SQL Store Procedure. I know it, but it's more easy to use a temporary column (as you have suggested me).

[vapukov]

it always compromise - each solution have and pluses and minuses

I mean more simple way - if You can arrange down-time, You are just need stop other application for short time:

• You do not need change no one of other application
• You just need start Your "Talend ID" out of the current range, after finish MySQL arrange auto increment to new value

I don't know how big You table and how powerful Your server, so it could be fast, could be not. 

[Aukema · Accepted solution]

Not the fastest solution, but I usually solve this as follows:

tdbConnection

   |

onSubjOk

  |

tdbInput -- FlowToIterate -- tMap -- tdbOutput1 (Base_user)                -- onComponentOk -- tdbLastInsertId -- tSetGlobalVar  

                                                    -- tHashOut (Base_LegalEntity)                                                   |--- OnSubJobOk -- tHashIn -- tMap-- tdbOutput2 (Base_LegalEntity)

                                                                                                                                                                        |--> onSubjobOk --> tdbCommit

Subjob1

First make a FlowToIterate so Every record is inserted seperately

Then map the input to the desired output. 

Insert the Base_User in the table and the Base_LegalEntitiy in a Hash

Subjob2

then "on component Ok" on the inserted record get the latest inserted ID and put it in a global Variable

Subjob3

then Read from the Hash, use a tMap to get the newly inserted ID from the globalVar

Insert into the Base_LegalEntity table.

If all successful, do a commit

Note: Dont forget to clear the Hash after reading

Note2: Dont commit&close