August 15, 2023

Scenario 1: Aggregating table columns and filtering – Docs for ESB 6.x

Scenario 1: Aggregating table columns and filtering

This scenario describes a Job that gathers together several input DB table schemas and
implementing a clause to filter the output using an SQL statement.

  • Drop the following components from the Palette
    onto the design workspace: three tELTMysqlInput
    components, a tELTMysqlMap, and a tELTMysqlOutput. Label these components to best describe
    their functionality.

  • Double-click the first tELTMysqlInput component
    to display its Basic settings view.

  • Select Repository from the Schema list, click the three dot button preceding Edit schema, and select your DB connection and the
    desired schema from the [Repository Content] dialog

    The selected schema name appears in the Default Table
    field automatically.

    In this use case, the DB connection is
    and the schema for the
    first input component is owners.

  • Set the second and third tELTMysqlInput
    components in the same way but select cars and
    resellers respectively as their schema names.


In this use case, all the involved schemas are stored in the Metadata node of the Repository tree
view for easy retrieval. For further information concerning metadata, see
Talend Studio User Guide

You can also select the three input components by dropping the relevant schemas from
the Metadata area onto the design workspace and
double-clicking tELTMysqlInput from the [Components] dialog box. Doing so allows you to skip the
steps of labeling the input components and defining their schemas manually.

  • Connect the three tELTMysqlInput components to
    the tELTMysqlMap component using links named
    following strictly the actual DB table names: owners,
    cars and resellers.

  • Connect the tELTMysqlMap component to the
    tELTMysqlOutput component and name the link
    agg_result, which is the name of the database table you
    will save the aggregation result to.

  • Click the tELTMysqlMap component to display its
    Basic settings view.

  • Select Repository from the Property Type list, and select the same DB connection that you use
    for the input components.

    All the database details are automatically retrieved.

  • Leave all the other settings as they are.

  • Double-click the tELTMysqlMap component to launch
    the ELT Map editor to set up joins between the input tables and define the output

  • Add the input tables by clicking the green plus button at the upper left corner of
    the ELT Map editor and selecting the relevant table names in the [Add a new alias] dialog box.

  • Drop the ID_Owner column from the owners
    table to the corresponding column of the cars table.

  • In the cars table, select the Explicit
    check box in front of the ID_Owner column.

    As the default join type, INNER JOIN is
    displayed on the Join list.

  • Drop the ID_Reseller column from the
    cars table to the corresponding column of the
    resellers table to set up the second join, and define the
    join as an inner join in the same way.

  • Select the columns to be aggregated into the output table,

  • Drop the ID_Owner, Name, and
    ID_Insurance columns from the owners
    table to the output table.

  • Drop the Registration, Make, and
    Color columns from the cars table to
    the output table.

  • Drop the Name_Reseller and City columns
    from the resellers table to the output table.

  • With the relevant columns selected, the mappings are displayed in yellow and the
    joins are displayed in dark violet.

  • Set up a filter in the output table. Click the Add filter
    button on top of the output table to display the Additional clauses expression field, drop the
    City column from the resellers table
    to the expression field, and complete a WHERE clause that reads resellers.City

  • Click the Generated SQL Select query tab to
    display the corresponding SQL statement.

  • Click OK to save the ELT Map settings.

  • Double-click the tELTMysqlOutput component to
    display its Basic settings view.

  • Select an action from the Action on data list as

  • Select Repository as the schema type, and define
    the output schema in the same way as you defined the input schemas. In this use
    case, select agg_result as the output schema, which is the name
    of the database table used to store the mapping result.


You can also use a built-in output schema and retrieve the schema structure from the
preceding component; however, make sure that you specify an existing target table having
the same data structure in your database.

  • Leave all the other settings as they are.

  • Save your Job and press F6 to launch it.

    All selected data is inserted in the agg_result table as
    specified in the SQL statement.


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