The first recipes in the chapter showed you how to look for additional information in a database. There are still many other sources of information. You may need to look in property files, in Excel files, in text files, and so on. Kettle allows you to look for data coming from all those sources with the Stream lookup step.
In this example, you have information about books coming from an Excel file and you need to complete this dataset by looking up the author's data and genre description, which are in external sources. In this case, the author's information is inside a text file and the genres are in a fixed predefined list.
For doing this recipe, you will need the following:
lastname, firstname, nationality
, and id_author
. The following are sample lines of this file:"lastname","firstname","nationality","id_author" "Larsson","Stieg","Swedish","A00001" "King","Stephen","American","A00002" "Hiaasen","Carl ","American","A00003" "Handler","Chelsea ","American","A00004" "Ingraham","Laura ","American","A00005"
title, id_author, price, id_title
, and id_genre
as shown in the following screenshot:You can also download sample files from the book's website.
Carry out the following steps:
books.xls
file under the File tab and click on the Add button. Populate the grid under the Fields tab by clicking on the Get fields from header row button. authors.txt
file and click on the Add button. Type
, as the Separator under the Content tab and finally, populate the Fields tab grid by clicking on the Get Fields button. String
items: id_genre
and genre
. Then, complete the Data tab as shown in the following screenshot: id_genre
under the Field column and id
under LookupField. genre
Field, of String
Type. Add Unknown
as the Default value.The Stream Lookup step is the step that you should use to look for additional information in other sources. In this recipe, the main data comes from an Excel file with book titles and also, the identification for their author and genres. You used two Stream Lookup steps to look up for each title, the author's information, and the genre description respectively.
In the first Stream Lookup step the purpose is to look for the author's data; in this step, you configure the Lookup step listbox pointing to the Text file input step, which is where the author's data is coming from.
In the upper grid named The key(s) to lookup the values(s), you have to specify the fields used to join both data sources. In this case, those fields are id_author
and id
.
The second grid titled Specify the fields to retrieve is to declare the fields to add to the main dataset. You have typed the fields: firstname, lastname
, and nationality
.
The last Stream Lookup step was created in order to retrieve the genre description that matches the genre identification in the main dataset. Here the key values are the fields id_genre
and id
and the only data to retrieve is the field genre
. Here you entered Unknown
as the Default value. This means that if the Stream Lookup step doesn't find a matching genre for a row the genre description will be set to Unknown
.
The following sections provide you with some alternatives to the use of the Stream Lookup step.
The Stream Lookup step compares the fields with an equal operator. There are a couple of situations where this may not be what you need.
If you are not sure about the similarity of the values between the fields that you are comparing, a comparison by equal may fail. In that case, you could use the Fuzzy match step, which allows you to search for similar values.
If you need to compare using other operators, for example<=
, then you should also look for an alternative step. One possible approach would be to use the Join Rows (Cartesian product) to retrieve all the data and filter the rows from that step with a Filter rows or a Java Filter step afterward.
Keep in mind that this option might increase the number of records you are processing, slowing down your ETL process significantly.
Another option would be to transfer the source data to a database table and then lookup in the database. This approach takes a little more effort, but it has its advantages. You have more flexible ways for looking up data in a database compared with looking up in a stream. Besides, for big datasets, you can also gain performance as explained in the next subsection.
For big datasets, looking up in plain files with a Stream Lookup step will definitely slow down your transformation. As a workaround to speed things up, you should consider moving the data to a database table before looking up in it. The main advantage of this approach is that you can cache data, which makes the lookup task faster.
What if your ETL project doesn't involve database tasks? For these temporary lookup tables, you may want to use an in-memory database such as HSQLDB or H2.
For a practical example of this, take a look at the following blog post by Slawomir Chodnicki:
The second Stream Lookup step in the recipe only returns a simple description and has a short list of possible values. In these cases, you can replace this step with a Value Mapper from the Transform category.
You should complete the step, as shown in the following screenshot:
In the Source value column, you define the possible values for the id_genre
field, and in the Target value column, you define their descriptions. Also, in the Default upon non-matching textbox, you can enter a default value to be returned for the rows with invalid genre identification.