PDA

View Full Version : The Weekly Kettle Tip: Looking up information



MattCasters
04-14-2006, 06:57 AM
<h2 id='section-CodeBeamer-Looking+up+information'>Looking up information[/b][/size]


Often, when combining multiple data sources in a transformation, you will find yourself in need of a lookup function.
Kettle offers 3 main different types of lookup steps: Database Lookup, Database Join and Stream Lookup.
The question then becomes: which step do I use?

<span style="float:right; width: 20%; border-style: inset;
background: #f0f0f0; font-size: 80%;
padding: 4px; margin-left: 4px;">This step generates SQL like



SELECT returnValue1, ..., returnValue

FROM tableName

WHERE key1 = field1

AND key2 = field2

...


</span>
<h3 id='section-CodeBeamer-Database+Lookup'>Database Lookup[/b][/size]


If you want to do a simple lookup using a primary key, foreign key or an ID you will find that using Database Lookup is the easiest to use and offers a good chance of getting the best possible performance.
Database Lookup allows you to specify the fields to match and the fields you want in return.
Additionally, if you have do do a lot of lookups against a small table, you can turn on caching which will dramatically speed up your transformation.








<span style="float:right; width: 20%; border-style: inset;
background: #f0f0f0; font-size: 80%;
padding: 4px; margin-left: 4px;">You can use SQL like the following to have the same functionality as the Database Lookup step:



SELECT returnValue1, ..., returnValue

FROM tableName

WHERE key1 = ?

AND key2 = ?

;



With 2 parameters specified: field1and field2
</span>

<h3 id='section-CodeBeamer-Database+Join'>Database Join[/b][/size]
On certain occasions, a simple database lookup is simply not enough.
In those cases, you want to be able to specify all kinds of funky database functions, sub-selects, order-by clauses etc.
Also, if you want to filter out all the rows where we didn't find a match, then you need the Database Join step.
This step packs a lot of punch but offers very little help in the creation of the SQL to perform. That is because we could not come up with a GUI that allows you to generate all possible kinds of SQL statements.






<h3 id='section-CodeBeamer-Stream+Lookup'>Stream Lookup[/b][/size]
Finally, the Stream lookup allows you to lookup information that is NOT stored in a relational database.
It offers you the possibility to load rows of data into memory to do lookups with.
That means that you can get data from ANY data source and perform any type of calculation before you look up values with Stream Lookup.
For example, you can read information from a text-file, apply several calculations to the data and then load it into a Stream Lookup step.
This step then can be used to look up information.
<span style="float:right; width: 20%; border-style: inset;
background: #f0f0f0; font-size: 80%;
padding: 4px; margin-left: 4px;">Sometimes Stream Lookup is also used to speed up database lookups by loading the data to lookup in memory. However, it often makes more sense to specify a large number of rows to cache in Database Lookup. Thay way you get a more predictable memory usage.</span>




Join us next week for a new Weekly Kettle Tip and in the mean time, don't hesitate to mail in questions and proposals for tips.


All the best,

Matt