Introduction
In this article, we will learn about the use of tMap components with a real-time example. We mainly focused on the Match Model, Join Model and catch inner join rejects settings. As we assume you have already had experience on connections and basic talend design.
To learn more about on tMap component and its benefits, read my previous article Mapping Component (tMap).
tMap Examples
In this job, we are going to join multiple data from different sources (here files) into a single data (table) using tMap component. Input files are read by tFileInputDelimited component and output data is printed by tLogRow component. Here student data are taken as an example.
Input data
In this example, we take the input data from the 3 text files using tFileInputDelimited component.
- Input Files
- Primary.txt
- Mark.txt
- Parent.txt
The below properties won't affect the output data. These properties are used to improve performance.
- Lookup Mode
- Store temp data
- Schema Type
| Property | Value | Description |
| Lookup Model | Load once | Load once option first loads all the lookup table data at once in the system before starting of the process between Main table and Lookup table. |
| Reload at each row | Load all the records of a lookup flow for each record of the main flow | |
| Reload at each row (cache) | Same as reload at each row but this model not be used as store temp data as true | |
| Store temp data | False | Lookup data is stored in the system. |
| True | Lookup data is stored in the extra disk. | |
| Schema Type | Built-in | Schema defined directly in the tMap |
| Repository | Schema taken from the metadata(repository) |
Example 1
In this example, we are going to check the different type of Match Model with same input data.
For this example, we have created the below job.
tMap Editor
Below are the tMap editor settings for the above job.
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Lookup Model | Load once | Load once option first loads all the lookup table data at once in the system before starting of the process between Main table and Lookup table. |
| Join Model | Left Outer Join | The output data have all the main table data, primary key matched lookup table data and if primary key value is not in the lookup table then the lookup table columns have null value. |
| Store temp data | False | Lookup data is stored in the system. |
| Output Panel - tMap settings | ||
| Catch output reject | False | Output table reject is not catch in the another output table. |
| Catch lookup inner join reject | False | Reject for main table is not catch in output table. |
| Schema Type | False | Selecting the schema from the built-in or repository(metadata). |
Scenario 1
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Match Model | Unique match | Unique match option gives the last matching record in the lookup table. |
Output
- In the Mark_details table Id column have same 111 value in 2 rows. As we are using the unique match in match model, it takes the last match of 111 values in the Id column.
- As we are using the left outer join, It brings all the values from primary_detail table, matched values from mark_detail table and null values for not matched columns (Here Id 114 is not in the mark_detail table).
Scenario 2
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Match Model | First match | First match option gives the first matching record in the lookup table. |
Output
- In the Mark_details table Id column have same 111 value in 2 rows. As we are using the first match in match model, it takes the first match of 111 values in the Id column.
Scenario 3
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Match Model | All Match | All match option gives all matching record in the lookup table. |
Output
- In the Mark_details table Id column have same 111 value in 2 rows. As we are using the All match in match model, it takes the all match of 111 values in the Id column.
Example 2
In this example, we are going to check the different type of Join Model and Catch lookup inner join reject with same input data.
For this example, we have created the below job.
tMap Editor
Below are the tMap editor settings for the above job. All the settings are same for following scenarios, only change in Join Model and Catch lookup inner join reject
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Lookup Model | Load once | Load once option first loads all the lookup table data at once in the system before starting the process between Main table and Lookup table. |
| Match Model | Unique match | Unique match option gives the last matching record in the lookup table. |
| Store temp data | False | Lookup data is stored in the system. |
| Output Panel - tMap settings | ||
| Catch output reject | False | Output table reject is not caught in another output table. |
| Schema Type | False | Selecting the schema from the built-in or repository(metadata). |
Scenario 1
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Join Model | Left Outer Join | The output data have all the main table data, primary key matched lookup table data and if primary key value is not in the lookup table then the lookup table columns have null value. |
| Output Panel - tMap settings | ||
| Catch lookup inner join reject | True | Reject for main table catch in output table. |
Output
No rows are returned because as we use left outer join there is no reject from main table in output.
Scenario 2
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Join Model | Inner Join | The output data have all the main table data, primary key matched lookup table data and if primary key value is not in the lookup table then the lookup table columns have null value. |
| Output Panel - tMap settings | ||
| Catch lookup inner join reject | False | Reject for main table is not catch in output table. |
Output
Inner Join gives the primary key match values between the primary_ detail table, Mark_detail table and Parent_detail table.
Scenario 3
| Property | Value | Description |
| Input Panel - tMap settings | ||
| Join Model | Inner Join | The output data have all the main table data, primary key matched lookup table data and if primary key value is not in the lookup table then the lookup table columns have null value. |
| Output Panel - tMap settings | ||
| Catch lookup inner join reject | True | Catch the main table rejects from the inter join between main and lookup tables. |
Output
Inner Join gives the primary key match values between the primary_ detail table, Mark_detail table and Parent_detail table. As Catch lookup inner join reject is true, it will give the reject from the primary_detail tables after inner join is applied.
Summary
In this article, you have learned about the how tMap component works on the various scenarios with examples.
Join the conversation! Your thoughts help the community grow.