Getting data from a database by running a query built at runtime

When you work with databases, most of the times you start by writing an SQL statement that gets the data you need. However, there are situations in which you don't know that statement exactly. Maybe the name of the columns to query are in a file, or the name of the columns by which you will sort will come as a parameter from outside the transformation, or the name of the main table to query changes depending on the data stored in it (for example sales2010). PDI allows you to have any part of the SQL statement as a variable so you don't need to know the literal SQL statement text at design time.

Assume the following situation: You have a database with data about books and their authors, and you want to generate a file with a list of titles. Whether to retrieve the data ordered by title or by genre is a choice that you want to postpone until the moment you execute the transformation.

Getting ready

You will need a book database with the structure explained in Appendix, Data Structures.

How to do it...

1. Create a transformation.

2. The column that will define the order of the rows will be a named parameter. So, define a named parameter named ORDER_COLUMN, and put title as its default value.

   Note

   Remember that Named Parameters are defined in the Transformation setting window and their role is the same as the role of any Kettle variable. If you prefer, you can skip this step and define a standard variable for this purpose.

3. Now drag a Table Input step to the canvas. Then create and select the connection to the book's database.

4. In the SQL frame type the following statement:

   SELECT * FROM books ORDER BY ${ORDER_COLUMN}

5. Check the option Replace variables in script? and close the window.

6. Use an Output step like for example a Text file output step, to send the results to a file, save the transformation, and run it.

7. Open the generated file and you will see the books ordered by title.

8. Now try again. Press F9 to run the transformation one more time.

9. This time, change the value of the ORDER_COLUMN parameter typing genre as the new value.

10. Press the Launch button.

11. Open the generated file. This time you will see the titles ordered by genre.

How it works...

You can use Kettle variables in any part of the SELECT statement inside a Table Input step. When the transformation is initialized, PDI replaces the variables by their values provided that the Replace variables in script? option is checked.

In the recipe, the first time you ran the transformation, Kettle replaced the variable ORDER_COLUMN with the word title and the statement executed was:

SELECT * FROM books ORDER BY title

The second time, the variable was replaced by genre and the executed statement was:

SELECT * FROM books ORDER BY genre

Note

As mentioned in the recipe, any predefined Kettle variable can be used instead of a named parameter.

There's more...

You may use variables not only for the ORDER BY clause, but in any part of the statement: table names, columns, and so on. You could even hold the full statement in a variable. Note however that you need to be cautious when implementing this.

Note

A wrong assumption about the metadata generated by those predefined statements can make your transformation crash.

You can also use the same variable more than once in the same statement. This is an advantage of using variables as an alternative to question marks when you need to execute parameterized SELECT statements.

See also

• Getting data from a database by providing parameters. This recipe shows you an alternative way to parameterize a query.