Working With The tMap Component In Real-Time Examples

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.