Shulou(Shulou.com)06/01 Report--

Oracle multi-form batch synchronization data = "MYSQL

Scene

Multiple Oracle tables, automatically synchronize data to mysql through polling.

Avoid each Oracle table data = "mysql makes a transformation, resulting in a large number of transformations, which are difficult to manage and adjust."

Mainly through kettle variables, loops, automatic matching of field names, and Oracle, mysql data dictionary to achieve.

Initial completion of Oracle table = "initialization synchronization of mysql, subsequent addition of incremental synchronization, DDL synchronization, exception handling operations."

Basic steps

1. Find out which tables are under the test library in mysql and output them to the result record.

two。 Take one row at a time from the result record and set the variable

3. For each use of variable values, go to the oracle data source to generate the corresponding table input (generated by variables)

4. Assign the variable to the table name of the table output component

5. The other contents of the table output are not determined, because the table name and fields are the same as the source-side oracle

6. Loop each "output to result record" and insert each oracle table data into mysql

Overall process

Step 1:Tables in mysql (Job)

Step 2:insert into mysql (Job)

Step 2.1 set tablename (tranfomation)

Step 2.2:insert data into mysql (tranfomation)

Follow-up questions:

How to do incremental data migration

1. For tables with time fields on the oracle side

two。 Record the system time for each migration

3. The increment time is "Last system time and" this time

The name of each table time field may be different.

So maintain a table time field record (table name, time field name)

4. Each time the table input is generated, the time field name is obtained by referring to the way the table name variable is generated, which is used in the sql of the table input.

5. The output side of the table will be more complicated, including delete, insert, update and so on.

L Table synchronization without time field or serial number

How to do DDL synchronization:

1. Get data dictionaries for Oracle and mysql

two。 Compare table names with field names

3. Compare field data types

4. Note the data type mapping (such as oracle's varchar2 "=" MySQL's varchar,oracle 's data "=" MySQL's timestamp)

5. Synchronization of tables and fields on MySQL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.