Migrate table from one database to another

One Star

Migrate table from one database to another

Hi there,
Newbie here.
I'm probably not getting one of the fundamental concepts of Talend Open Studio.
What I'm trying to do is migrate a single table from one database (MySQL) to another (PostgreSQL).
Both databases have a table with addresses, say AddressOld and AddressNew. The columns in AddressNew are different from those in AddressOld (different names and different types).
I'm dragging the source and destination table onto the canvas, then link them and drop a tMap on the link. No matter what I do, I am not able to get to a dialog where I can choose which field of AddressOld is to be mapped to which field of AddressNew.
No matter what I do I get either the metadata of AddressOld or AddressNew on both sides in the tMap editor, not AddressOld on the left hand side and AddressNew on the right hand side.
I've watched a tutorial where the Job Templates function is used from the Enterprise Edition, which does not exist in Open Studio. I've also read some threads that say with Open Studio you'd need to do one table at a time, which is perfectly fine with me.
Any help/pointers much appreciated.
regards,
Jan
One Star

Re: Migrate table from one database to another

Hi Jan
Welcome to Talend Community!
No.1: Set up the schema of tMysqlinput. Then link it with tMap.
No.2: Double click on tMap to open the mapping window.
No.3: Click the "+" button at the right side and add a new output(e.g. out1).
No.4: Set up the schema of out1 manually, even though you have created metadata->DB Connection for tPostgresqlOutput.
No.5: Map the columns from the left side to the right side by pressing and dragging one column or multi columns.
No.6: Link tmap with tPostgreSQL and click on 'Sync schema' button. Or make the same schema of out1 with metadata tPostgresqlOutput.
Regards,
Pedro
One Star

Re: Migrate table from one database to another

Hi Pedro,
Thanks for the welcome and the quick response!
I must admit I'm a bit surprised that I - apparently - cannot simply use the schema from the PostgreSQL database but have to manually enter all fields for each table once more?
I only discovered Talend Open Studio today and was initially quite impressed with the functionality it offers. Just for my understanding: it feels a bit odd that a tool with such an impressive feature set misses something basic like this? Unless I'm mistaken, Talend Open Studio is all about visually building very powerful data manipulation processes. Is there really no smarter way to do this?
kind regards,
Jan
One Star

Re: Migrate table from one database to another

Hi Jan
It's not complex to create a job with tMap. Maybe my description can't make it clear without images or videos.
Have you read the tutorial about tMap? http://www.talendforge.org/tutorials/menu.php.
Have you downloaded the document in which you can get instructions and scenarios about tmap.
Regards,
Pedro
One Star

Re: Migrate table from one database to another

Hi Pedro,
Yes, I have read the tutorial and also have the documentation about the components here on my screen.
Creating the job is indeed not complex, but having to enter every column of every table of the destination database manually while that information is readily available in PostgreSQL's schema is quite labour intensive and seems a bit of out character for Talend Open Studio which offers such a rich visual design feature set.
What I was hoping to find is something that allows me to drop the source table and the destination table on the canvas, then connect the corresponding fields from one table to the other, ie. drag ADDRLINE1 from the AddressOld table to AddressLine1 from the AddressNew table (which then defines that the value of ADDRLINE1 should go in to AddressLine1.
There are a great many tables I need to migrate (and none of them 1 to 1 - for each I need custom mappings).
kind regards,
Jan
One Star

Re: Migrate table from one database to another

You can retrieve the schemas from the database by setting up the connection in metadata. Then you can drop the source and destination tables onto designer and connect them to a tMap. If the input and output column names are the same you can use automap.
One Star

Re: Migrate table from one database to another

Hi Janhess,
The column names are different for input and output, so I will have to (hopefully) drag & drop input columns onto output columns to map them.
This is what I've been trying (but failing to do):
1. drop a tMySQLinput on the canvas.
2. drop a tPostgreSQLoutput on the canvas.
3. right click tMySQLinput, row->main, drag & drop onto tPostgreSQLoutput
Q1: "Do you want to get the schema of the target component?"
4. drop a tMap on the row component
5. double click the tMap component
Now I see the columns of the target component in both the left and right panels (because I answered "Yes" to Q1)
If I would have answered "No" to Q1 I would see the columns of the source component in both the left and right panels.
What I would like (expect?) to see is the columns of the source component in the left panel and the columns of the target component in the right panel so that I can then decide which data from the source goes where in the target.
What am I doing wrong here?
kind regards,
Jan
One Star

Re: Migrate table from one database to another

You should connect tMySQLInput to tMap and tMap output to tPostgresSQLOutput. So the process is att tMySQLInput, add tMap. Join input to tmap. Add TPostgressSQLOutput. Join tMap to output and get schema from target. Then you can map your columns in tMap.
One Star

Re: Migrate table from one database to another

Hi Janhess,
Many thanks for that - that works!
kind regards,
Jan
One Star

Re: Migrate table from one database to another

Hello,
How can I tranfer data from mysql database to oracle using talend open studio for data integration.I just need to move only the contents of a table in the mysql database.Please help me.
One Star

Re: Migrate table from one database to another

Same as above but use tOracleOutput.
Moderator

Re: Migrate table from one database to another

Hi,
Have you checked the related forum:https://www.talendforge.org/forum/viewtopic.php?id=18793?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Migrate table from one database to another

hi,
My Requirement is i want to copy one table from oracle 12c database to Ms sql server database in talend.
Thanks,
Shekar
One Star

Re: Migrate table from one database to another

Hi ,
I want Basics in Talend Like tables copy,columns and table creation and load the data from excel sheet.

Thanks,
Shekar
One Star

Re: Migrate table from one database to another

Hi,
I build a job with MySql server in Talend Studio. Could you please tell that how can I export the same job and run the same with oracle database in another machine which doesnt have Talend Studio
Thanks
Moderator

Re: Migrate table from one database to another

Hi,
I build a job with MySql server in Talend Studio. Could you please tell that how can I export the same job and run the same with oracle database

Is your source file from MySql DB? If so, you can migrate your table by creating a job as:tMySqlInput_1--main--tOracleOutput.
The Build Job feature allows you to deploy and execute a Job on any server, independent of Talend Studio(JDK installed).
Please take a look at document about:TalendHelpCenter:How to build Jobs
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Migrate table from one database to another

Hi,
  I have 5 parameter values in a property file. I need the 5th parameter value to be passed to a query. How can i get the value from property file.
Thanks
Moderator

Re: Migrate table from one database to another

Hi,
We usually use tcontextload to load a context from a flow.
You can design your job like:tFileInputxxx(parameter value)-->tcontextload
                                                  onsubjobok
                                             t<DB>Input(pass context.parameter in query)-->output
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.