topic Re: [resolved] Migration of Oracle Tables Data to MySQL in Talend Studio

[resolved] Migration of Oracle Tables Data to MySQL

Anonymous — Sat, 16 Nov 2024 11:36:42 GMT

Hi,
How can I use talend to extract data from Oracle and import the data into a MySQL database?
Also, I have the Oracle metadata structure, but not the MySQL equivalent, can talend create the table in the MySQL database as well?

As an example, here is the oracle metadata of my table:
SQL> desc reptest;
Name Null? Type
----------------------------------------- -------- ----------------------------
DT DATE
NO NUMBER

Can talend create the proper table equivalent on the MySQL side and import the data in that table?
See here, the MySQL equivalent:
mysql> desc reptest;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| DT | datetime | YES | | NULL | |
| NO | decimal(65,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+

We currently have around 100 Oracle tables that needs to be extracted daily and imported in a MySQL Database. I am testing talend and if talend can make that happen, we could start using talend for other needs as well.

Thanks,
Steeve

Re: [resolved] Migration of Oracle Tables Data to MySQL

Anonymous — Wed, 18 Jun 2014 21:41:12 GMT

The simplest way is to use the DataIntegration edition.
I suggest you start with creating metadata for both databases (Oracle and MySQL).
You should create in the Metadata section minimum 2 connections and retrieve all schema information.
Now you have all your tables from Oracle and (probably none for your MySQL) at the finger tip.
It depends of the relations ship between the tables in which way and order you have to import the tables.
You can put a Oracle table in your job with drag&drop a table from the Metadata. You create the necessary output components for MySQL also with drag&drop of the MySQL connection to your job.
For one of these pairs define in the tMysqlOutput the table name and set as Table action "Create if not exists" and connect the tOracleInput with the tMysqlOutput.
This is fairly a very straight forward design but should work. If you have to change values or datatypes put a tMap into the flow (also by drag & drop from the palette directly onto the flow) and learn to work with the tMap (Help Center!)

Re: [resolved] Migration of Oracle Tables Data to MySQL

Anonymous — Thu, 19 Jun 2014 17:05:58 GMT

Hi,
Thanks for your help! Works like a charm. I tested the migration of my data with 2 Oracle tables and it works as expected and creates the table on the MySQL side and push the data. This is sweet! Not to mention that I have the ability to transform that data in the process, this is awesome!
Question for my test above with the 2 tables. When I start the job, data from the tables are exported one at a time, so when the first table is done, the second table is then being processed. Is there a way to export the data for both tables at the same time in the same job?
Is there a way to schedule the job to start at a certain time of the day with an interval, like once per day?
Regards,
Steeve

Re: [resolved] Migration of Oracle Tables Data to MySQL

Anonymous — Thu, 19 Jun 2014 18:40:22 GMT

Hi,
I downloaded the TalendOpenStudio User Guide and was able to get the info on how to run stuff in parallel.
The documentation also mention the ability to Schedule jobs. But the document specify that this feature is deprecated but still available.
What is the non-deprecated recommend way of scheduling jobs?
Thanks,
Steeve

Re: [resolved] Migration of Oracle Tables Data to MySQL

Anonymous — Thu, 19 Jun 2014 20:42:22 GMT

If you want to stay at the Open Studio edition I would recommend you use a job scheduler like JobScheduler from SOS Berlin:
http://www.sos-berlin.com/modules/cjaycontent/index.php?id=osource_scheduler_introduction_en.htm
This scheduler is proven to be stable and reliable. You export your Jobs with the Studio (right click on a job and choose "Build Job". Export the jobs as standalone application and give it to the JobScheduler.
I have used this combination for large companies and it works very well.

Re: [resolved] Migration of Oracle Tables Data to MySQL

Anonymous — Mon, 23 Jun 2014 04:49:49 GMT

Hi,
In Talend Enterprise Subscription, you only need to create one subjob for all tables due to the 'dynamic schema' feature.
For details, please see the related forum https://community.talend.com/t5/Design-and-Development/complete-migration-from-oracle-to-sql-server/td-p/106789.
Best regards
Sabrina