Sometimes you have several databases with exactly the same structure serving different purposes. These are some situations:
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.
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.
BRANCH, HOST_NAME
, and DATABASE_NAME
. ${HOST_NAME}
, and as Database Name: type ${DATABASE_NAME}
.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.
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.