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
- Click the New in the PDI toolbar.
- Click Database connection.
- Enter information similar to what’s shown below:
- Click Test.
- 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.
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
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
Hi Kelvin,
Unfortunately, I have not written anything on Postgres + SLL with Pentaho.
Akbar
Thanx it helped 🙂