Changing the database connection at runtime

Sometimes you have several databases with exactly the same structure serving different purposes. These are some situations:

• A database for the information that is being updated daily and one or more databases for historical data.

• A different database for each branch of your business.

• A database for your sandbox, a second database for the staging area, and a third database fulfilling the production server purpose.

In any of those situations, it's likely that you need access to one or the other depending on certain conditions, or you may even have to access all of them one after the other. Not only that, the number of databases may not be fixed; it may change over time (for example, when a new branch is opened).

Suppose you face the second scenario: Your company has several branches, and the sales for each branch are stored in a different database. The database structure is the same for all branches; the only difference is that each of them holds different data. Now you want to generate a file with the total sales for the current year in every branch.

Getting ready

Download the material for this recipe. You will find a sample file with database connections to three branches. It looks like this:

branch,host,database
0001 (headquarters),localhost,sales2010
0002,183.43.2.33,sales
0003,233.22.1.97,sales

If you intend to run the transformation, modify the file so it points to real databases.

How to do it...

1. Create a transformation that reads the file with connection data and copy the rows to results.

2. Create a second transformation, and define the following named parameters: BRANCH, HOST_NAME, and DATABASE_NAME.

3. Create a database connection. Choose the proper Connection Type:, and fill the Settings data. Type a value for the Port Number:, the User Name:, and the Password. As Host Name: type ${HOST_NAME}, and as Database Name: type ${DATABASE_NAME}.

4. Use a Table Input step for getting the total sales from the database. Use the connection just defined.

5. Use a Text file output step for sending the sales summary to a text file. Don't forget to check the option Append under the Content tab of the setting window.

6. Create a job with two Transformation job entries, linked one after the other.

7. Use the first entry to call the first transformation you created, and the second entry to call the second transformation. The job looks like this:

   

8. Double-click the second transformation entry, select the Advanced tab, and check the Copy previous results to parameters? and the Execute for every input row? checkboxes.

9. Select the Parameters tab and fill it as shown:

   

10. Save both transformations. Save the job, and run it.

11. Open the text file generated. It should have one line with sales information for each database in the file with the list of databases.

How it works...

If you have to connect to several databases, and you don't know in advance which or how many databases you will have to connect to, you can't rely on a connection with fixed values, or variables defined in a single place as for example in the kettle.properties file. In those situations, the best you could do is to define a connection with variables, and set the values for the variables at runtime.

In the recipe, you created a text file with a summary sales line for each database in a list.

The transformation that wrote the sales line used a connection with variables defined as named parameters. This means that whoever calls the transformation has to provide the proper values.

The main job loops on the list of database connections. For each row in that list, it calls the transformation copying the values from the file to the parameters in the transformation. In other words, each time the transformation runs, the named parameters are instantiated with the values coming from the file.

There's more...

In the recipe, you changed the host and the name of the database. You could have parameterized any of the values that made up a database connection, for example the user and password.

See also

• Connecting to a database. This recipe explains how to connect to a database by using variables.

• Executing a transformation once for every row in a dataset in Chapter 7, Executing and Reusing Jobs and Transformations. With this recipe you will understand better the way the loop over the database connection works.