Create a DB connection to MySQL in Kettle

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.

Advertisements

4 Comments

  1. 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

    Reply

  2. 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

    Reply

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s