Page Comparison

Here is a quick tip on how to move data from one database to another, a simple proposition thanks to Kettle being the awesome ETL tool that it is!

We are going to move the Pentaho demo sample data from HSQLDb to MySQL. I have attached the Kettle transformation used to move the data. We are not going to go through the tedious details fo the transformation, but rather demonstrate the use of it so you can take it away and put it in your "Pentaho toolbox". Let's dig right in.

...

Before you start, be sure you have the following resources.

• Pentaho Pre-configured Installation Demo Data
• Kettle, latest version 2.3.0
• MySQL Server, version 5.0+ Community Edition or other DB that supports "if"
• MySQL Connector/J, JDBC Driver for MySQL, version 3.1.2
• Kettle transformation used to move the data (Thanks, Nic Guzaldo)

Step By Step

1. Unzip the Pentaho demo data to a working directory on your machine. Start the database using the start_hypersonic.bat|.sh file in the demo data root directory.
2. Unzip Kettle to a working directory on your machine. Start Kettle's Spoon application using the Spoon.bat|.sh file in the Kettle root directory.
3. Download the attached transformation to your working directory and load it into Spoon using the File|Open menu options in Spoon. You should see a transformation similar to this:
   

Image Added

1. This transform will access the data in the DEPARTMENT_MANAGERS and QUADRANT_ACTUALS tables in the demo HSQLDb tables, and copy it into a new database named "sampledata" in MySQL. We have the HSQLDb database up and running, but we need to create a few things on the MySQL side before we run the transformation.
2. First, log in to your MySQL server, and create a database named "sampledata". Grant access to pentaho_user (password "password") to administer (create tables, insert data) this new database.
3. Next, in Spoon, from the Transformation menu at the top of the screen, click the menu item Get SQL. You should be prompted with a dialog that has two create table statements in it. This is the SQL to create the tables in MySQL for you.
4. From the dialog, select Execute SQL. You should now have a DEPARTMENT_MANAGERS and QUADRANT_ACTUALS table in your MySQL sampledata database.
5. In Spoon, from the Transformation menu at the top of the screen, click the menu item Run. This executes the transformation, which copies the data from the HSQLDb to the MySQL database.   

...

If you have any troubles with this tip, or you just want to send some feedback, please post to Pentaho forums at http://forums.pentaho.orglogin and add your comments here.

kind regards,
Gretchen Moran