Migrating data from one mysql database to another using talend

Question

The goal is to migrate data from one mysql database to another, my concern is to define a text file that contains the parameters that defines the source database (host, port, databasename, etc.) and also the file parameters of the output database.

Actually, what I use is the following schema:

tContextLoad_1 -> tFileInputDelimited_1-> tMysqlInput_1 (db_source) -> tMap->
tMysqlInput_2 (db_destination)

depending on the problem that has occurred is:

1- The tContextLoad_1 text file content change is not supported and the pointing is always on the first database "source database".

2- When I create a second tContextLoad_2 can not be connected by a (but) or (trigger-On subject Ok) with tFileOutputDelimited_1 that linked with tMysqlInput_2 (db_destination), how to define a file that contains the definition parameters of a base output data (host, databasename, identifier, password, etc.) ?

score 0 · Answer 1 · answered Feb 19 '18 at 05:31

You can use a configuration file like following,

<connections>
  <connection>
    <host></host>
    <port></port>
    <database></database>
    <table></table> 
    <user></user>
    </password></password>
  </connection>

  <connection>
    <host></host>
    <port></port>
    <database></database>
    <table></table> 
    <user></user>
    </password></password>
  </connection>
</connections>

And then you can iterate on connection tag to fetch the DB details and then assign them to variables and use it.

So the flow would be like, tFileInputXML-->SetGlobalVar-->tMySQLConnection-->tMySQLInput-->tMap-->tMySQLOutput

score 0 · Answer 2 · answered Feb 20 '18 at 09:53

You can either define a single file, but with different parameter names for the source connection and target connection, or define 2 files : one for the source and one for the target, in that case your parameters can have the same name :

Single context file with different parameter names:

tFileInputDelimited_1 (context file) -- tContextLoad_1 | OnSubjobOk | Source(host1,db1,user1,pwd1) -- target(host2,db2,user2,pwd2)

Different context files having the same parameter names:

I'm sure you'll agree with me that the 1st solution is simpler, and more efficient.

Your context files could be like this : key=value

host=myHost
db=myDB
user=myUser

Migrating data from one mysql database to another using talend

2 Answers2