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.
To demonstrate the Talend tMap Lookup Match model, we used the Employees and Department text files.
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.
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.
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 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.
Let me run the Talend tMap lookup match job and see the output.
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.
Let me change the Match model to First Match and run the Talend tMap Lookup Match Model job to see the result.
The below tMap lookup image has selected the first matching records for the DeptID 2 and 6 Department name.
This time we change the tMap Match model option to all matches.
From the below Lookup match model image, it has shown all the matching department names for the department ID.
If you change the Talend tMap Match Model to All rows, Talend will display two table’s Cartesian products.
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.
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.
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.
