If you intend to work with a database, either reading, writing, looking up data, and so on, the first thing you will have to do is to create a connection to that database. This recipe will teach you how to do this.
In order to create the connection, you will need 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 recommended that you also have access to the database at the moment of creating a connection.
Open Spoon and create a new transformation.
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.
Under Connection Type, select the database engine that matches your DBMS.
Fill 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:
Press the Test button. A message should appear informing you that the connection to your database is OK.
A database connection is the definition that allows you to access a database from Kettle. With the data you provide, Kettle can instantiate real database connections and perform the different operations related to databases. Once you define a database connection, you will be able to access that database and execute arbitrary SQL statements: create schema objects like tables, execute SELECT statements, modify rows, and so on.
In this recipe you created the connection from the Database connections tree. You may also create a connection by pressing the New... button in the Configuration window of any database-related step in a transformation or job entry in a job. Alternatively, there is also a wizard accessible from the Tools menu or by pressing the F3 key.
Whichever method you choose, a Settings window, like the one you saw in the recipe, shows up, allowing you to define the connection. This task includes the following:
A database connection can only be created with a transformation or an opened job. Therefore, in the recipe you were asked to create a transformation. The same could have been achieved by creating a job instead.
The recipe showed the simplest way to create a database connection. However, there is more to know about creating database connections.
If you intend to use the same database in more than one transformation and/or job, it's recommended that you share the connection. You do this by right-clicking on the database connection under the Database connections tree and clicking on Share. This way the database connection will be available to be used in all transformations and jobs. Shared database connections are recognized because they appear in bold. As an example, take a look at the following sample screenshot:
The databases books and sampledata are shared; the others are not.
The information about shared connections is saved in a file named shared.xml located in the Kettle home directory.
No matter what Kettle storage method is used (repository or files), you can share connections. If you are working with the file method, namely ktr and kjb files, the information about shared connections are not only saved in the shared.xml file, but also saved as part of the transformation or job files even if they don't use the connections.
Instead of typing fixed values in the database connection definition, it's worth using variables. Variables live in either of the two places: in the kettle.properties file, which lives in the Kettle home directory, or within the transformation or job as a named parameter. For example, instead of typing localhost as the hostname, you can define a variable named HOST_NAME, and as the host name, type its variable notation as ${HOST_NAME} or %%HOST_NAME%%. If you decide to move the database from the local machine to a server, you just have to change the value of the variable and don't need to modify the transformations or jobs that use the connection.
Tip
To edit variables stored in the kettle.properties file, just open the kettle.properties editor, which can be found under Edit | Edit the kettle.properties file.
This is especially useful when it's time to move your jobs and transformations between different environments: development, test, and so on.
The recipe showed you how to provide the general properties needed to create a connection. You may need to specify additional options; for example, a preferred schema name, or supply some parameters to be used when the connection is initialized. In order to do that, look for those options in the extra tab windows under the General tab of the Database Connection window.
Kettle offers built-in support for a vast set of database engines. The list includes commercial databases (such as Oracle), open source (such as PostgreSQL), traditional row-oriented databases (such as MS SQL Server), modern column-oriented databases (such as Infobright), disk-storage based databases (such as Informix), and in-memory databases (such as HyperSQL). However, it can happen that you want to connect to a database that is not in that list. In that case, you might still create a connection to that database. First of all, you have to get a JDBC driver for that DBMS. For Kettle versions previous to 5.0, copy the JAR file containing the driver to the libext/JDBC directory inside the Kettle installation directory. For versions after 5.0, copy the JAR file containing the driver to the lib directory. Then create the connection. For databases not directly supported, choose the Generic database connection type. In the Settings frame, specify the connection string (which should be explained along with JDBC), the driver class name, and the username and password. In order to find the values for these settings, you will have to refer to the driver documentation.
If you are not sure that the database connection will be accessible when a job or transformation runs from outside Spoon, you might precede all database-related operations with a Check DB connection job entry. The entry will return true or false depending on the result of checking one or more connections.