ETL MySQL in Oracle ODI 12c

This article describes how to synchronize data from MySQL to Oracle via ODI.

1. Define the physical architecture

1.1 Creating a new MySQL data server
Topology->physical Architecture->mysql, right-click to select New Data Server and enter the relevant information in the Definition dialog box, such as:

In the JDBC dialog box, enter the following information, such as:

Click Save.
1.2 Creating a physical scenario for MySQL
Topology->physical architecture->mysql-> "Hue_mysql", enter the name and database in the Definition dialog box, click Save.

2. Define Logical architecture

Topology->logical Architecture->mysql, right click to select "New Logical Schema", select the corresponding physical scheme in the Definition dialog box
, click Save.

3. Define MySQL Model

Designer->models->new model, as shown in

Click "Save". Then click on "Reverse Engineer" to synchronize the MySQL table structure.
A copy of the Oracle database table structure has been previously synchronized and is used directly here. If not, you'll need to follow the steps above to create it.

4. Create a project

Designer->projects->new Project, enter a name and click Save.

5. Create a map

Designer->projects->mysql_to_oracle->mapping, right-click to select "New Mapping", then drag the source and target tables to link, such as:

The knowledge module here selects "LKM SQL to SQL (built-in). GLOBAL "as shown in:

Attention:
A, if you encounter a MySQL field type that does not match the field type of Oracle, edit the field type of the source and target tables in model.
B, in the logic options in the map, set the target table's integration type to incremental update, such as:

C, don't forget to click "Save".

6. Verify the mapping

Click on the icon shown in the diagram to verify the error, if the errors are modified according to the actual situation.

ETL MySQL in Oracle ODI 12c