Looking up data that is stored in a database is a particular case of the process that we use for looking up data in general. We have one or more fields that are the keys for looking up data in a secondary source of data. However, when that secondary source is a database, the way we look through it is different.

To learn how to look up data in a database table, we will start with a file containing information about injured people. The file looks like the following code:

person_id;injury_type;injury_date
153;elbow;2007-07-09
186;fingers;2007-07-15
198;shoulder;2007-07-15
213;elbow;2007-07-16
378;elbow;2007-07-24
391;lower-back;2007-07-29
400;elbow;2007-07-21
407;lower-back;2007-07-15
...

We have the person_id fields and we want to get the names of the people that they correspond to, which can be found in the display_names table in the sports database. We can do this by going through the following steps:

1. Create a new transformation.
2. With a Text file input step, read the injuries.txt file.

The display_names table contains more than just the names of people. It has a field named entity_type that has the value persons. So to look up the values, we will need a composed key made up of the person_id and the entity_type fields. In order to look up the proper value, we need that constant value in our main stream.

3. After the Text file input step, add an Add constants step. This step is located under the Transform folder.

4. Double-click the step and configure it as shown in the following screenshot:

5. Run a preview. You will see the following:

6. From the Lookup folder, drag and drop a Database lookup step. Create a hop from the previous step toward this one.

7. Double-click the step and configure it as shown in the following screenshot:

8. Run a preview. You will see the names of the people added to your stream, as shown in the following screenshot:

As you can see, the configuration window in a Database lookup is quite similar to the configuration window in a Stream lookup. In the upper grid, you specify the conditions for looking up, and in the lower grid, you list the fields that you want to get from the table should the lookup succeed.