Mapping data sources using inner join
In this example, there are two data sources: a text file named in.txt and a table named person in a Mysql database. The data of each data source are read as follows:
in.txt
id;name;sex
1;Shong;boy
2;Ross;boy
3;Sabrina;girl
4;Elisa;girl
user_id | name | country | |
---|---|---|---|
1 | Shong | CN | Shong@talend.com |
4 | Elisa | FR | Elisa@talend.com |
This example uses a tMap component to:
- read the data from the in.txt file as a main flow,
- do an inner join with the data from the person table based on the
id
column from the file and theuser_id
column from the table.
The join will get rows, including the external columns from the lookup table,
if the id
already exists in the table, and will reject rows if the
id
does not exist in the table.
id | name | sex | |
---|---|---|---|
1 | Shong | boy | Shong@talend.com |
4 | Elisa | girl | Elisa@talend.com |
id | name | sex |
---|---|---|
2 | Ross | boy |
3 | Sabrina | girl |
Configuring the components
In this Job, you will perform an inner join between the two incoming data flow, generate two output tables, one table for the matched rows, and another for the unmatched rows.
This Job uses the following components:
- a tFileInputDelimited component to read data from in.txt file,
- a tMysqlInputDelimited component to read data from person table,
- a tMap component to perform an inner join and generate two output tables,
- and two tLogRow components to print output data on the console.
Doing an inner join with the tMap component
Double-click the tMap component to open its editor.
Executing the Job
The results show that all matched rows are output into the first tLogRow and all unmatched rows are output into the second tLogRow.