Connecting to a database
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.
Getting ready
In order to create the connection, you will need to know the connection settings. At least you will need:
Host Name: Domain name or IP address of the database server.
Port Number
User Name
Password
It's recommended that you also have access to the database at the moment of creating the connection.
How to do it...
Open Spoon and create a new transformation.
Select the View option that appears in the upper-left corner of the screen, right-click 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 the Settings options and give the connection a name by typing it in the Connection Name: textbox. Your window should look like this:
Press the Test button. A message should appear informing you that the connection to your database is OK.
Note
If you get an error message instead, you should recheck the data entered, as well as the availability of the database server. The server might be down, or it might not be reachable from your machine.
How it works...
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 with 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 F3.
Whichever the method you choose, a setting window like the one you saw in the recipe shows up allowing you to define the connection. This task includes:
Selecting a database engine (Connection type:)
Selecting the access method (Access:)
Note
Native (JDBC) is recommended but you can also use a predefined ODBC data source, a JNDI data source, or an Oracle OCI connection.
Providing the Host Name or IP
Entering the User Name and Password for accessing the database.
A database connection can only be created with a transformation or a job opened. Therefore, in the recipe you were asked to create a transformation. The same could have been achieved by creating a job instead.
There's more...
The recipe showed the simplest way to create a database connection. However, there is more to know about creating database connections.
Avoiding creating the same database connection over and over again
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 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 are 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 the Kettle storage method (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.
Note
You can avoid saving all the connection data as part of your transformations and jobs by selecting the option Only save used connections to XML? in the Kettle options window.
Avoiding modifying jobs and transformations every time a connection changes
Instead of typing fixed values in the database connection definition, it's worth using variables. For example, instead of typing localhost as the hostname, you can define a variable named HOST_NAME and as 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.
This is especially useful when it's time to move your jobs and transformations between different environments: development, test, and so on.
Specifying advanced connection properties
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.
Connecting to a database not supported by Kettle
Kettle offers built-in support for a vast set of database engines. The list includes both commercial databases (such as Oracle) and open source (such as PostgreSQL), traditional row-oriented databases (such as MS SQL Server) and modern column-oriented databases (such as Infobright), disk-storage based databases (such as Informix) and in-memory databases (such as HSQLDB). 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. Copy the jar file containing the driver to the libext/JDBC directory inside the Kettle installation directory. Then, create the connection. In this case, as connection type choose Generic database. 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.
Checking the database connection at run-time
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.
