Use Kettle to Migrate Sample Data to MySQL

July 11, 2006
Contributed by Nic Guzaldo, Pentaho Community Member

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.

Resources Before You Get Started

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

• Pentaho Pre-configured Installation Demo Data
• Kettle, latest version
• 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:
   

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.   

That's all there is to it! Your MySQL database should be populated with the sample data.

Note that the transformation's database connection information makes a few assumptions, not the smallest being that the databases are local, you are logging in to them with the pentaho_user, etc. You can find and tweak these parameters by clicking on a step in the transformation and clicking the edit button next to the connection information. 

If you have any troubles with this tip, or you just want to send some feedback, login and add your comments here.

kind regards,
Gretchen Moran