AkbarAhmed.com

Engineering Leadership

Introduction

Creating a DB connection from Kettle to MySQL involves creating a MySQL use who can access the DB in question, installing the JDBC driver, and creating a connection.

Install the MySQL JDBC driver

Download the MySQL JDBC driver from http://dev.mysql.com/downloads/connector/j/.

Login to mysql.com, then click Download.

cd ~/Downloads
tar -xzf mysql-connector-java-5.1.20.tar.gz
cd mysql-connector-java-5.1.20
cp mysql-connector-java-5.1.20-bin.jar ~/bin/data-integration/libext/

Create a MySQL user

In this post, I am going to create a connection to the Sakila DB.

mysql -u root -p

At the MySQL command prompt, enter the following (replace ‘password’ with your password):

mysql> GRANT ALL ON sakila.* TO akbar@localhost IDENTIFIED BY 'password';

Create the DB connection in Kettle

cd ~/bin/data-integration
./spoon.sh
  1. Click the New in the PDI toolbar.
  2. Click Database connection.
  3. Enter information similar to what’s shown below:
  4. PDI Database Connection

  5. Click Test.
  6. Click OK.

In Explorer in the left pane of PDI, right-click on the Sakila database connection, click Explore.

You should now be able to view the tables in the Sakila database.

4 thoughts on “Create a DB connection to MySQL in Kettle

  1. Kelvin Kang says:

    Hi Akbar,
    Thanks so much for your series of entries on configuring Pentaho and Data Integration. Do you have any tips on configuring a Postgres server. The scenario I’m trying to address is enabling SSL so I can connect to a Heroku database. According to what I’ve read, I need to be able to pass the parameters through the connection but haven’t found any good documentation on how to get it done.

    Thanks,
    Kelvin

  2. kelvinkang says:

    Hi Akbar,

    Thanks so much for your posts on installing Pentaho. Do you have any tips on how to configure Pentaho for a Postgres connection with SSL? From what I can gather, I need to set ssl = true and sslfactory = org.postgresql.ssl.NonValidatingFactory but I’m unsure as to how to do that using Spoon. Any idea how to do that?

    Thanks,
    Kelvin

    1. akbarsahmed says:

      Hi Kelvin,

      Unfortunately, I have not written anything on Postgres + SLL with Pentaho.

      Akbar

  3. Pushkar Jois says:

    Thanx it helped 🙂

Leave a Reply to Pushkar Jois Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: