Talend Open Studio data migration with MySQL on parent-child relation

Question

I am using Talend Open Studio for data migration as I am upgrading my existing application architecture to a new one. I am using MySQL in both the applications but with different schema. I have migrated data successfully between single tables but while I am transferring data from a single table to a parent-child relationship table with a foreign key constraint, the data transfer is tremendously slow. For e.g. I am migrating my Cities table to Cities and Citiesi18n and below is the schema for them:

My old schema :

CITIES (
  id   
  city_name
  status
  created_at
)

The newly created schema where I need to migrate the data :

CITIES (
  id   
  status
  created_at
)

CITIESI18N (
  id           
  lang_code
  name
  fk_city_id      (// foreign key of cities table)
)

Below are the snapshots from my Talend jobs:

And here is the tmap configuration :

Now when I transfer the data without the foreign key the result are super fast. See below :

But the same when I transfer with a foreign key, my transfer is super slow :

(Note: I have taken province table for example as it is similar to cities table)

I think with Foreign key constraint it must be indexing the columns while transferring the data making it slower, but I am not sure. Is there any way I can fix this as I have a lot of tables similar to this which needs to be migrated. I am just curious to know the reason.

Try to remove the key from the id columns of the tmap and check if it works. — Neo, Mar 16 '19 at 07:08
MySQL create indexes for foreign keys for InnoDB tables only. is your tables InnoDB? plus check any other cases from - https://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically — a_vlad, Mar 22 '19 at 09:20

score 0 · Answer 1 · answered Mar 19 '19 at 09:41

0

I don't know why you have this behaviour : you can try to redirect 'provience_i18n' to a tHashOutput (cache component), then link to a subjob with tHashInput (refering to your tHashOutput)-->tMySQLOutput. You'll have 2 subjobs, one for each insertion.

answered Mar 19 '19 at 09:41

Corentin

2,442
1
17
28

score 0 · Answer 2 · answered Jun 18 '19 at 11:28

You are loading data to the parent & child at the same time. Using one tmap. When you are inserting foreign key in the second table, there is also insertion being made in the foreign/parent table. What you could alternatively do is: Load the data in the main CITIES table first, then onSubJobOk, load into child/CITIESI18N table. It would be faster. Let me know if it works.

Talend Open Studio data migration with MySQL on parent-child relation

2 Answers2