Talend tMap Lookup

This Talend section discusses the Talend tMap Lookup Model, Lookup Match Model, and Store temp data options. In general, when we have only one match record in the lookup table, there is no point in changing the Talend tMap Lookup Match Model. However, when we have multiple matches in the lookup table, we must specify which one to select for the particular ID. The Match Model option completely controls it.

From the below Department table text file, this file has multiple entries for DeptID 2 and 6.

Text File 0

We used the Employees and Department text files to demonstrate the Talend tMap Lookup Match model.

Text Input 1

First, we added the tMap to the job designer and connect the Employees Main input row to tMap. Next, we added the Department Main row to tMap, and it will automatically convert to the lookup table.

Talend tMap Lookup 2

Next, we added the tLogRow, connected the new output of tMap to it, and named it the Lookup_Output. I suggest you refer to the Talend tJoin and tMap Joins articles to understand the same.

new Output Name window 3

The following screenshot shows the final Talend tMap Lookup Match model job. Please double-click on the tMap to open the tMap schema editor.

Talend tMap Lookup 4

Talend tMap Lookup Match Model Example

First, we connected the Key or common columns of the Employee and Department tables, i.e., DeptID. Next, we dragged all the columns to the Lookup_output.

Match Model 5

Let me run the Talend tMap lookup match job and see the output.

Talend tMap Lookup 6

From the above screenshot, you can see that DeptID 2 has loaded Developer. For DeptID 6, it loaded Sales as the department name.

Please click on the Setting tab and click the Match Model’s browse button to open available options. The setting will decide the Matching records that the tMap lookup has to select. As you can see, it has four options, and the default one is a Unique match. So, the default Unique match model chooses the last match from the lookup table.

Options - All Rows, Unique, First, and All Matches 7

Let me change the Match model to First Match and run the Talend tMap Lookup Match Model job to see the result.

Talend tMap Lookup 8

The below tMap lookup image has selected the first matching records for the DeptID 2 and 6 Department name.

Talend tMap Lookup 9

This time we change the tMap Match model option to all matches.

Talend tMap Lookup 10

From the below Lookup match model image, it has shown all the matching department names for the department ID.

Talend tMap Lookup 11

If you change the Talend tMap Match Model to All rows, Talend will display two table’s Cartesian products.

Talend tMap Lookup 12

Talend tMap Lookup Model Settings

Click the browse button beside the tMap lookup model settings will show the available Talend lookup model options.

  • Load Once: All the records available in the lookup table will load at the beginning, and tMap lookup uses those records.
  • Reload at each row: This look table will load and process each record from the Main row.
  • Reload at each row (Cache): Same as the above, but it will process from the cache.
Talend tMap Lookup 13

Similarly, tMap has one more option called Store temp data. By changing this from default False to True, you can store the lookup table in the local file system, and it will act as the temp data.

Talend tMap Lookup 14

If you enabling the Store temp data option, then you have to choose the Temp data Directory path to save this Temp data. I suggest you try this option when you are processing millions of records.

Choose Folder 15