If you want to work with a database, either read, write, view data, etc, in Pentaho the first thing you will have to do is to create a connection with that database. This blog will teach you how to do this. So, let’s start.
Getting ready
In order to set up the connection, you will require to know the connection settings. At least you will need the following:
- Host name: Domain name or IP address of the database server.
- Database name: The schema or other database identifier.
- Port number: The port the database connects to. Each database has its own default port.
- Username: The username to access the database.
- Password: The password to access the database.
It’s also recommended that we also have access to the database at the moment of creating a connection.
How to setup connection
First, open the Spoon and create a new transformation. Then,
- Select the View option that appears in the upper-left corner of the screen, right-click on the Database connections option, and select New. The Database Connection dialog window appears.
- Provide Connection Name.
- Under Connection Type, select the database engine that matches your DBMS.
- Fill all the required details in the Settings options and give the connection a name by typing it in the Connection Name: textbox.
- Your window should look like the following:
6. Press the Test button. A message should appear informing you that the connection to your database is OK.
If you receive an error message instead, you should review the entered data, as well as the availability of the website server. The server may be down, or it may not be available on your machine.
We have to specify these parameter values in the kettle.properties file which can be found in the $HOME/.kettle directory. Take a look at the sample kettle.properties file.
hostName=qa.host.com databaseName=test portNumber=3306 dbUser=test dbPassword=test123
In a production environment, just change the parameter values in the kettle.properties file.
Now question is how it works…?
A database connection is a definition that allows us to access a website with a kettle. With the data we provide, Kettle can strengthen the actual connection to the website and perform various functions related to the database. Once we have defined the database connection, we will be able to access that data and create contradictory SQL statements: create schema objects such as tables, create SELECT statements, change lines, and so on.
In this blog, we created a link to the Database link tree. We can also create a connection by pressing a new button … in the Activation window for any action related to the site change or job placement. Alternatively, there is also a wizard available in the Tools menu or by pressing the F3 button.
Whichever method we choose, a Settings window, such as the one we saw in the recipe, appears, allowing us to define a connection.
Conclusion
A database connection can only be created with a transformation or an opened job. Therefore, in this blog, we have successfully created a database connection.
If you want to read about the basics of Pentaho, you can follow this blog. If you wanna read more about the Pentaho database connection visit this.
