Essential tool: Use Pentaho for data migration

pentaho download address: https://sourceforge.net/projects/pentaho/files/Data%20Integration/

Unzip pentaho, click under winSpoon.batYou can start.

Create a new database connection. With the database connection, you can migrate data between multiple data sources. Note hereSet the database connection to be shared(Right-click the database connection to set up sharing), otherwise you will be prompted to select the configuration database connection every time.

New oracle database connection:

Insert picture description here

The new mysql database connection is the same, pay attention to the driver package, if the driver class is not found:org.gjt.mm.mysql.Driver, You can check whether the driver package contains this driver class. Add the correct driver package to the directory:~\data-integration\lib under.

Insert picture description here

Use jndi to configure the data source, copy a jdbc.properties file in the directory: ~\data-integration\simple-jndi, add related database connection configuration, such as myslq configuration reference as follows:

mysql/type=javax.sql.DataSource  
mysql/driver=com.mysql.cj.jdbc.Driver
mysql/url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT
mysql/user=test
mysql/password=test
Insert picture description here

New conversion, proceedTable input-table outputFor conversion, double-click the table input to configure, select the database connection, and select the query SQL to be executed. The same is true for the configuration table output, you can edit the related configuration, field cropping, and if the target table does not exist, you can edit the table creation statement (adjustment field, field type). There is no problem to input the table and connect the table output (shift + left mouse button). Finally, just run this transformation, and the original data source data can be migrated to the target data source.

Insert picture description here

Here is just the simplest example of Pentaho. Pentaho has almost all the data ETL functions you can think of, such as automatic jobs, import and export, email sending, scripts, data analysis, etc. It is an indispensable tool.