Complete the migration of the entire database with a kettle set of processes

Demand:
1. Do you encounter the need to migrate all the tables and data in the MySQL database to Oracle.
2. Are you still using kettle to repeat the painting: Table input-table output, create TABLE, and worry.

Here's a general set of database migration processes for you.

Technical guidance:
At the beginning of the implementation, a similar (samples\jobs\process all tables) was found in the example provided by kettle.
Through the related transformation, finally achieve the goal.

To achieve process anatomy:
The whole process is divided into: 2 job,4 trans.
Trans plug-ins used: Table input, field selection, copying records to results, getting records from results, setting variables, customizing Java scripts, table output.
1. Big job.


2. The Source Library table name to be migrated is obtained and set to the result set for use by the following job.

Attention:

For MySQL, the SQL statement for table input above is show tables, but the result of the statement query contains the view, so the next steps will give an error when inserting data into the table queried by the statement.

If there is a view in the library that you want to migrate, you can use the following statement to query all tables that need to be migrated:

Select table_name from INFORMATION_SCHEMA. TABLES where Table_schema = ' database name ' and Table_type = ' base TABLE '

3. Configure the child job to execute the child job once for each previous record (that is, each table)


4. Below is the child job.


5. Gets the table name in the record and sets it to the variable.


6. Read the result information of the current table and create a table in the target library ( This is the difficulty ).

Because you only need to fetch the structure information for fetching the table, add the where 1=2 after the SQL.

The following code creates the target library table.

Public boolean processrow (STEPMETAINTERFACE&NBSP;SMI,&NBSP;STEPDATAINTERFACE&NBSP;SDI)  throws  KettleException{    // First, get a row from the  Default input hop//object[] r = getrow (); Org.pentaho.di.core.database.DatabaseMeta  dbmeta = null;java.util.list list = gettrans (). GetRepository (). ReadDatabases () //3.x All database connection information in the repository with getdatabases (); if (List != null && !list.isempty ()) {for (int  i=0;i<list.size (); i++) {dbmeta =  (Org.pentaho.di.core.database.DatabaseMeta) list.get (i);                          //below is the database connection of the target library, you can modify if ("Mysql_test") as needed. Equalsignorecase (Dbmeta.getname ()) ) {break;}}} if (dbmeta!=null) {org.pentaho.di.core.database.database db=new org.pentaho.di.core.database.database ( Dbmeta); try{Db.connect (); String tablename = getvariable ("tablename"); Logbasic ("Start CREATE TABLE:"  + tablename); Tablename!=null && tablename.trim (). Length () >0) {STRING&NBSP;SQL&NBSP;=&NBSP;DB.GETDDL ( Tablename, data.inputrowmeta)//${tablename}db.execstatement (Sql.replace (";",  "")); LogBasic (sql);}} catch (exception e) {logError ("CREATE TABLE Exception", e);} Finally{db.disconnect ();}} Return false;}

7. Table Data Migration.



8. Almost on the line, I use MySQL to MySQL, Oracle test is no problem but in the test process, found that the source table if there is a blob table, there will be problems, may be due to the table output does not specify the reason for the field, the specific solution, also did not think, there will be time in the perfect.

The whole process is completed under kettle4.3, and the complete process can be downloaded in the attachment.

5.x run to create table structure step error, cause Data.inputrowmeta is empty because the 6th step
Adding a where 1=2 after SQL causes the table input step not to extract the records, and the 5.x structure becomes null.
Solve:
Remove the where 1=2, and then, in the table input the limit line is set to 1, can, I pro-Test.

Kettle Database Migration example. RAR 115 Network Tray gift code: 5LBD26PC02PJ

Complete the migration of the entire database with a kettle set of processes