Talend tMap Lookup

In this Talend section, we discuss the Talend tMap Lookup Model, Lookup Match Model, and the 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 have to specify which one to select for the particular ID. The Match Model option completely controls it.

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

Talend tMap Lookup 0

To demonstrate the Talend tMap Lookup Match model, we used the Employees and Department text files.

Talend tMap Lookup 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 as the Lookup_Output. I suggest you refer the Talend tJoin and tMap Joins articles to understand the same.

Talend tMap Lookup 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.

Talend tMap Lookup 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 for DeptID 2 it 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. It is the setting that will decide the Matching records that the tMap lookup has to select. As you can see, it has four options, and the default one Unique match. So, the default Unique match model chooses the last match from the lookup table.

Talend tMap Lookup 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: For each record from the Main row, this look table will load and process.
  • 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 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.

Talend tMap Lookup 15