Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Pentaho Data Integration 4 Cookbook

You're reading from   Pentaho Data Integration 4 Cookbook Over 70 recipes to solve ETL problems using Pentaho Kettle

Arrow left icon
Product type Paperback
Published in Jun 2011
Publisher Packt
ISBN-13 9781849515245
Length 352 pages
Edition 1st Edition
Tools
Arrow right icon
Toc

Table of Contents (17) Chapters Close

Pentaho Data Integration 4 Cookbook
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
1. Working with Databases FREE CHAPTER 2. Reading and Writing Files 3. Manipulating XML Structures 4. File Management 5. Looking for Data 6. Understanding Data Flows 7. Executing and Reusing Jobs and Transformations 8. Integrating Kettle and the Pentaho Suite 9. Getting the Most Out of Kettle Data Structures Index

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

  1. Open Spoon and create a new transformation.

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

  3. Under Connection Type, select the database engine that matches your DBMS.

  4. Fill the Settings options and give the connection a name by typing it in the Connection Name: textbox. Your window should look like this:

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

You have been reading a chapter from
Pentaho Data Integration 4 Cookbook
Published in: Jun 2011
Publisher: Packt
ISBN-13: 9781849515245
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime