In order to search for data in a database, Kettle offers several options. The simplest situation is the one in which you need to get one or more columns from a single database table. In this recipe, you will learn how to do this by using the Database lookup step.
We will work with the Steel Wheels sample data. If you don't know about it, refer to Chapter 1, Working with Databases. Suppose that you want to look for products that match a given search term and whose prices are below a given value. This recipe shows you how to do this.
Carry out the following steps:
- Create a new transformation.
- Drop a Data Grid step (Input category). Open it. Under the Meta tab, add two
Stringitems:prodandmax_price. Then, complete the Data tab, as shown in the following screenshot:
- Add a User Defined Java Expression step.
- Use that step to add a String named
like_statement. As Java expression, type"%"+prod+"%". - Add a Database lookup step. You will find it in the Lookup category of steps.
- Double-click on the step. As Connection select (or create if it doesn't exist) the connection to the
sampledatadatabase. As Lookup table type or browse forPRODUCTS. - Fill the upper and lower grid, as shown in the following screenshot:
- Close the Database lookup configuration window and do a preview on this step. You will see the following screen:
The Database lookup step allows you to look for values in a database table. To perform that search, you need to specify at least the following:
- The database connection and the database table you want to search
- The conditions for the search
- The fields to retrieve from the table
The upper grid is where you specify the conditions. Each row in the grid represents a comparison between a column in the table and a field in your stream, by using one of the provided comparators.
Take a look at the upper grid in the recipe. The conditions you entered were:
BUYPRICE < max_price and PRODUCTNAME LIKE like_statement
If we take, for example, the first row in the dataset, the condition can be restated as:
BUYPRICE<90 and PRODUCTNAME LIKE %Aston Martin%
That's exactly the search you wanted to perform for that row: look for the records where the column BUYPRICE was less than 90 and the column PRODUCTNAME contained Aston Martin.
As the result of the database lookup, you can retrieve any number of columns. Each database column you enter in the lower grid will become a new field in your dataset. You can rename them (this is particularly useful if you already have a field with the same name) and supply a default value if no record is found in the search.
In the recipe, you added three fields: PRODUCTNAME, PRODUCTSCALE, and BUYPRICE. By default, for PRODUCTNAME you typed not available. In the final preview, you can see that description in the second row. This means that there were no products with Ford Falcon as part of their description and price lower than 70.
The recipe showed the minimal setting of the Database lookup step. The step has more options that can be useful as explained in the following subsections.
When you perform a search with the Database lookup step, you expect that the search returns a row. If the data you are looking for doesn't exist in the table then the lookup fails. If this happens, the fields you added in the lower grid are added to the dataset anyway with null values or with the default values, if you provided them. That is the default behavior. However, you have two more options if you don't like that, which are as follows:
- If the data should be present, then the failure of the lookup is considered an error. In that case, you can handle the error. You can, for example, send the rows that cause the failure to a different stream as in this example:
Tip
Remember that you can capture errors by right-clicking on the Database lookup step, selecting the Define error handling... option and configuring the Step error handling settings window properly. At least, you have to check the Enable the error handling? option and select the Write to log step as the Target step.
- If you do this, the rows for which the lookup fails go directly to the stream that captures the error, in this case, the Write to log step.
- If the rows are useless without the fields that you are looking for, then you can discard them. You do that by checking the Do not pass the row if the lookup fails option. This way, only the rows for which the lookup succeeds will pass to the next step.
The Database lookup step is meant to retrieve just one row of the table for each row in your dataset. If the search finds more than one row, the following two things may happen:
- If you check the Fail on multiple results? option, the rows for which the lookup retrieves more than one row will cause the step to fail. In that case, in the Logging tab window, you will see an error similar to the following:
... - Database lookup (fail on multiple res.).0 ERROR... Because of an error, this step can't continue: ... - Database lookup (fail on multiple res.).0 ERROR... : Only 1 row was expected as a result of a lookup, and at least 2 were found!
- Then you decide if you leave the transformation or capture the error.
- If you don't check the Fail on multiple results? option, the step will return the first row it encounters. You can decide which one to return by specifying the order. You do that by typing an order clause in the Order by textbox. In the
sampledatadatabase, there are three products that meet the conditions for theCorvetterow. If, for Order by, you typePRODUCTSCALE DESC, PRODUCTNAME, then you will get1958 Chevy Corvette Limited Edition, which is the first product after ordering the three found products by the specified criterion.
If instead of taking some of those actions, you realize that you need all the resulting rows, you should take another approach: replace the Database lookup step with a Database join or a Dynamic SQL row step. For recipes explaining these steps, see the following See also section.
If instead of looking for a row you want to determine if the row doesn't exist, the procedure is much the same. You configure the Database lookup step to look for those rows. Then you capture the error, as depicted in the following diagram:
In this case, the stream that you use for capturing the error becomes your main stream. The rows that didn't fail will be discarded and the rows for which the lookup failed go directly to the main stream for further treatment.
- The recipe named Looking for values in a database (with complex conditions or multiples tables involved) in this chapter. This recipe explains a more elaborate way of searching for data in a database.
- The recipe named Looking for values in a database with extreme flexibility in this chapter. As implied by its name, this recipe explains how to search for data in a database almost without restrictions.