August 15, 2023

Advanced mapping with lookup reload at each row – Docs for ESB 6.x

Advanced mapping with lookup reload at each row

The following scenario describes a Job that retrieves people details from a lookup
database, based on a join on the age. The main flow source data is read from a MySQL
database table called people_age that contains people details such
as numeric id, alphanumeric first name and last name and numeric age. The people age is
either 40 or 60. The number of records in this table is intentionally restricted.

The reference or lookup information is also stored in a MySQL database table called
large_data_volume. This lookup table contains a number of
records including the city where people from the main flow have been to. For the sake of
clarity, the number of records is restricted but, in a normal use, the usefulness of the
feature described in the example below is more obvious for very large reference data

To optimize performance, a database connection component is used in the beginning of
the Job to open the connection to the lookup database table in order not to do that
every time we want to load a row from the lookup table.

An Expression Filter is applied to this lookup source flow, in order to select only
data from people whose age is equal to 60 or 40. This way only the relevant rows from
the lookup database table are loaded for each row from the main flow.

Therefore this Job shows how, from a limited number of main flow rows, the lookup join
can be optimized to load only results matching the expression key.


Generally speaking, as the lookup loading is performed for each main flow row,
this option is mainly interesting when a limited number of rows is processed in the
main flow while a large number of reference rows are to be looked up to.

The join is solved on the age field. Then, using the relevant
loading option in the tMap component editor, the lookup
database information is loaded for each main flow incoming row.

For this Job, the metadata has been prepared for the source and connection components.
For more information on how to set up the DB connection schema metadata, see the
relevant section in the
Talend Studio User Guide

This Job is formed with five components, four database components and a mapping

Document get from Talend
Thank you for watching.
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x