tMap – Docs for ESB 5.x

tMap

1. Drop two tFileInputDelimited components,
   tMap and three tFileOutputDelimited components onto the design workspace.

2. Rename the two tFileInputDelimited
   components as Cars and Owners,
   either by double-clicking the label in the design workspace or via the
   View tab of the Component view.

3. Connect the two input components to tMap
   using Row > Main connections and label the connections as
   Cars_data and Owners_data
   respectively.

4. Connect tMap to the three output
   components using Row > New Output (Main) connections and name the output
   connections as Insured, Reject_NoInsur and Reject_OwnerID
   respectively.

   

1. Double-click the tFileInputDelimited
   component labelled Cars to display its Basic settings view.

   

2. Select Repository from the Property type list and select the component’s
   schema, cars in this scenario, from the [Repository Content] dialog box. The rest fields
   are automatically filled.

3. Double-click the component labelled Owners and repeat
   the setting operation. Select the appropriate metadata entry,
   owners in this scenario.

   Note

   In this scenario, the input schemas are stored in the Metadata node of the Repository tree view for easy retrieval. For further
   information regarding metadata creation in the Repository, see
   Talend Studio User Guide.

4. Double-click the tMap component to open
   the Map Editor.

   Note that the input area is already filled with the defined input tables
   and that the top table is the main input table, and the respective row
   connection labels are displayed on the top bar of the table.

5. Create a join between the two tables on the ID_Owner
   column by simply dropping the ID_Owner column from the
   Cars_data table onto the
   ID_Owner column in the
   Owners_data table.

6. Define this join as an inner join by clicking the tMap settings button, clicking in the Value field for Join Model,
   clicking the small button that appears in the field, and selecting Inner Join from the [Options] dialog box.

   

7. Drag all the columns of the Cars_data table to the
   Insured table.

8. Drag the ID_Owner,
   Registration, and ID_Reseller
   columns of the Cars_data table and the
   Name column of the Owners_data
   table to the Reject_NoInsur table.

9. Drag all the columns of the Cars_data table to the
   Reject_OwnerID table.

   For more information regarding data mapping, see Talend Studio
   User Guide.

10. Click the plus arrow button at the top of the Insured
    table to add a filter row.

    Drag the ID_Insurance column of the
    Owners_data table to the filter condition area and
    enter the formula meaning ‘not undefined’: Owners_data.ID_Insurance !=
null.

    With this filter, the Insured table will gather all
    the records that include an insurance ID.

    

11. Click the tMap settings button at the top
    of the Reject_NoInsur table and set Catch output reject to true to define the table as a standard reject output flow to
    gather the records that do not include an insurance ID.

    

12. Click the tMap settings button at the top
    of the Reject_OwnerID table and set Catch lookup inner join reject to true so that this output table will gather the
    records from the Cars_data flow with missing or
    unmatched owner IDs.

    

    Click OK to validate the mappings and
    close the Map Editor.

13. Double-click each of the output components, one after the other, to define
    their properties. If you want a new file to be created, browse to the
    destination output folder, and type in a file name including the
    extension.

    

    Select the Include header check box to
    reuse the column labels from the schema as header row in the output
    file.

1. Press Ctrl + S to save your Job.

2. Press F6 to run the Job.

   The output files are created, which contain the relevant data as defined.

   

   For examples of how to use dynamic
   schemas with tMap, see:

   • Scenario 4: Writing dynamic columns from a MySQL database to an output file.

   • Scenario 4: Writing dynamic columns from a source file to a MySQL database.

1. Drop a tFileInputDelimited component and
   a tFileOutputDelimited component to the
   design workspace, and label the components as Resellers
   and No_Reseller_ID respectively.

2. Connect it to the Mapper using a Row >
   Main connection, and label the
   connection as Resellers_data.

3. Connect the tMap component to the new
   tFileOutputDelimited component by using
   the Row connection named
   Reject_ResellerID.

   

1. Double-click the Resellers component to display its
   Basic settings view.

   

2. Select Repository from the Property type list and select the component’s
   schema, resellers in this scenario, from the [Repository Content] dialog box. The rest fields
   are automatically filled.

   Note

   In this scenario, the input schemas are stored in the Metadata node of the Repository tree view for easy retrieval. For further
   information regarding metadata creation in the Repository, see
   Talend Studio User Guide.

3. Double-click the tMap component to open
   the Map Editor.

   Note that the schema of the new input component is already added in the
   Input area.

4. Create a join between the main input flow and the new input flow by
   dropping the ID_Reseller column of the
   Cars_data table to the
   ID_Reseller column of the
   Resellers_data table.

5. Click the tMap settings button at the top
   of the Resellers_data table and set Join Model to Inner
   Join.

   

6. Drag all the columns except ID_Reseller of the
   Resellers_data table to the main output table,
   Insured.

   

   Note

   When two inner joins are defined, you either need to define two
   different inner join reject tables to differentiate the two rejections
   or, if there is only one inner join reject output, both inner join
   rejections will be stored in the same output.

7. Click the [+] button at the top of the
   output area to add a new output table, and name this new output table
   Reject_ResellerID.

8. Drag all the columns of the Cars_data table to the
   Reject_ResellerID table.

9. Click the tMap settings button and select
   Catch lookup inner join reject to
   true to define this new output table as
   an inner join reject output.

   If the defined inner join cannot be established, the information about
   the relevant cars will be gathered through this output flow.

   

10. Now apply filters on the two Inner Join reject outputs, in order for to
    distinguish the two types of rejection.

    In the first Inner Join output table, Reject_OwnerID,
    click the plus arrow button to add a filter line and fill it with the
    following formula to gather only owner ID related rejection:
    Owners_data.ID_Owner==null

11. In the second Inner Join output table,
    Reject_ResellerID, repeat the same operation using
    the following formula: Resellers_data.ID_Reseller==null

    

    Click OK to validate the map settings and
    close the Mapper Editor.

12. Double-click the No_Reseller_ID component to display
    its Basic settings view.

    

    Specify the output file path and select the Include
    Header check box, and leave the other parameters as they
    are.

13. To demonstrate the work of the Mapper, in this example, remove reseller
    IDs 5 and 8 from the input file
    Resellers.csv.

1. Press Ctrl + S to save your Job.

2. Press F6 to run the Job.

   The four output files are all created in the specified folder, containing
   information as defined. The output file
   No_Reseller_ID.csv contains the
   cars information related to reseller IDs
   5 and 8, which are missing in
   the input file Resellers.csv.

   

   For examples of how to use dynamic
   schemas with tMap, see:

   • Scenario 4: Writing dynamic columns from a MySQL database to an output file.

   • Scenario 4: Writing dynamic columns from a source file to a MySQL database.

1. Drop three tFileInputDelimited
   components, a tMap component, and two
   tFileOutputDelimited components from
   the Palette onto the design workspace, and
   label them to best describe their functions.

2. Connect the input components to the tMap
   using Row > Main connections.

   Pay attention to the file you connect first as it will automatically be
   set as Main flow, and all the other
   connections will be Lookup flows. In this
   example, the connection for the input component Owners
   is the Main flow.

   

1. Define the properties of each input components in the respective Basic settings view. Define the properties of
   Owners.

   

2. Select Repository from the Property type list and select the component’s
   schema, owners in this scenario, from the [Repository Content] dialog box. The rest fields
   are automatically filled.

   Note

   In this scenario, the input schemas are stored in the Metadata node of the Repository tree view for easy retrieval. For further
   information regarding metadata creation in the Repository, see
   Talend Studio User Guide.

   In the same way, set the properties of the other input components:
   Cars and Resellers. These two
   Lookup flows will fill in secondary
   (lookup) tables in the input area of the Map
   Editor.

3. Then double-click the tMap component to
   launch the Map Editor and define the
   mappings and filters.

   Set an explicit join between the Main
   flow Owner and the Lookup flow Cars by dropping the
   ID_Owner column of the Owners
   table to the ID_Owner column of the
   Cars table.

   The explicit join is displayed along with a hash key.

   

4. In the Expr. Key field of the
   Make column, type in a filter. In this use case,
   simply type in "BMW" as the search is focused on the owners of
   this particular make.

   

5. Implement a cascading join between the two lookup tables
   Cars and Resellers on the
   ID_Reseller column in order to retrieve resellers
   information.

6. As you want to reject the null values into a separate table and exclude
   them from the standard output, click the tMap
   settings button and set Join
   Model to Inner Join in each
   of the Lookup tables.

   

7. In the tMap settings, you can set Match
   Model to Unique match,
   First match, or All matches. In this use case, the All
   matches option is selected. Thus if several matches are found
   in the Inner Join, rows matching the explicit join as well as the filter,
   all of them will be added to the output flow (either in rejection or the
   regular output).

   Note

   The Unique match option functions as
   a Last match. The First match and All
   matches options function as named.

8. On the output area of the Map Editor,
   click the plus button to add two tables, one for the full matches and the
   other for the rejections.

9. Drag all the columns of the Owners table, the
   Registration, Make and
   Color columns of the Cars
   table, and the ID_Reseller and
   Name_Reseller columns of the
   Resellers table to the main output table.

10. Drag all the columns of the Owners table to the
    reject output table.

11. Click the Filter button at the top of the
    main output table to display the Filter
    expression area.

    Type in a filter statement to narrow down the number of rows loaded in the
    main output flow. In this use case, the statement reads:
    Owners.Children_Nr >= 2 && Owners.Children_Nr <=
6.

12. In the reject output table, click the tMap
    settings button and set the reject types.

    Set Catch output reject to true to collect data about BMW car owners who
    have less than two or more than six children.

    Set Catch lookup inner join reject to
    true to collect data about owners of
    other car makes and owners for whom the reseller information is not
    found.

    

    Click OK to validate the mappings and
    close the Map Editor.

    On the design workspace, right-click the tMap and pull the respective output link to the relevant
    output components.

13. Define the properties of the output components in their respective
    Basic settings view.

    In this use case, simple specify the output file paths and select the
    Include Header check box, and leave the
    other parameters as they are.

    

1. Press Ctrl + S to save your Job.

2. Press F6 to run it.

   The main output file contains the information related to BMW owners who
   have two to six children, and the reject output file contains the
   information about the rest of the car owners.

   

   For examples of how to use dynamic
   schemas with tMap, see:

   • Scenario 4: Writing dynamic columns from a MySQL database to an output file.

   • Scenario 4: Writing dynamic columns from a source file to a MySQL database.

1. Take the same Job as in Scenario 4: Advanced mapping using filters, explicit joins and rejections.

2. Drop a new tFileOutputDelimited component
   from the Palette on the design workspace,
   and name it Rejects_BMW_Mercedes to present its
   functionality.

3. Connect the tMap component to the new
   output component using a Row connection and
   label the connection according to the functionality of the output component.

   This connection label will appear as the name of the new output table in
   the Map Editor.

4. Relabel the existing output connections and output components to reflect
   their functionality.

   The existing output tables in the Map
   Editor will be automatically renamed according to the
   connection labels. In this example, relabel the existing output connections
   BMW_Mercedes_withChildren and
   Owners_Other_Makes respectively.

   

1. Double-click the tMap component to launch
   the Map Editor to change the mappings and
   the filters.

   Note that the output area contains a new, empty output table named
   Rejects_BMW_Mercedes. You can adjust the position
   of the table by selecting it and clicking the Up or Down arrow button at
   the top of the output area.

2. Remove the Expr. key filter
   (“BMW”) from the Cars table in
   the input area.

3. Click the Filters button to display the
   Filter field, and type in a new filter
   to limit the search to BMW or
   Mercedes car makes. The statement reads as follows:
   Cars.Make.equals("BMW") ||
Cars.Make.equals("Mercedes")

   

4. Select all the columns of the main output table and drop them down to the
   new output table.

   Alternatively, you can also drag the corresponding columns from the
   relevant input tables to the new output table.

5. Click the tMap settings button at the top
   of the new output table and set Catch output
   reject to true to collect
   data about BMW and Mercedes owners who have less than two or more than six
   children.

6. In the Owners_Other_Makes table, set Catch lookup inner join reject to true to collect data about owners of other car
   makes and owners for whom the reseller information is not found.

   

7. Click OK to validate the mappings and
   close the Map Editor.

8. Define the properties of the output components in their respective
   Basic settings view.

   In this use case, simple specify the output file paths and select the
   Include Header check box, and leave the
   other parameters as they are.

   

1. Press Ctrl + S to save the Job.

2. Press F6 to run it.

   The output files contain content of the main output flow shows that the
   filtered rows have correctly been passed on.

   

   For examples of how to use dynamic
   schemas with tMap, see:

   • Scenario 4: Writing dynamic columns from a MySQL database to an output file.

   • Scenario 4: Writing dynamic columns from a source file to a MySQL database.

1. Drop the DB Connection under the Metadata
   node of the Repository to the design
   workspace. In this example, the source table is called
   people_age.

2. Select tMysqlInput from the list that
   pops up when dropping the component.

   

3. Drop the lookup DB connection table from the Metadata node to the design workspace selecting tMysqlInput from the list that pops up. In this
   Job, the lookup is called large_data_volume.

4. The same way, drop the DB connection from the Metadata node to the design workspace selecting tMysqlConnection from the list that pops up. This
   component creates a permanent connection to the lookup database table in
   order not to do that every time we want to load a row from the lookup
   table.

5. Then pick the tMap component from the
   Processing family, and the tMysqlOutput and tMysqlCommit components from the Database family in the Palette
   to the right hand side of the editor.

6. Now connect all the components together. To do so, right-click the
   tMysqlInput component corresponding to
   the people table and drag the link towards tMap.

7. Release the link over the tMap component,
   the main row flow is automatically set up.

8. Rename the Main row link to
   people, to identify more easily the main flow
   data.

9. Perform the same operation to connect the lookup table
   (large_data_volume) to the tMap component and the tMap
   to the tMysqlOutput component.

10. A dialog box prompts for a name to the output link. In this example, the
    output flow is named: people_mixandmatch.

11. Rename also the lookup row connection link to
    large_volume, to help identify the reference data
    flow.

12. Connect tMysqlConnection to tMysqlInput using the trigger link OnSubjobOk.

13. Connect the tMysqlInput component to the
    tMysqlCommit component using the
    trigger link OnSubjobOk.

    

1. Double-click the tMap component to open
   the graphical mapping editor.

   

2. The Output table (that was created
   automatically when you linked the tMap to
   the tMySQLOutput will be formed by the
   matching rows from the lookup flow (large_data_volume)
   and the main flow (people_age).

   Select the main flow rows that are to be passed on to the output and drag
   them over to paste them in the Output table (to the right hand side of the
   mapping editor).

   In this example, the selection from the main flow include the following
   fields: id, first_name,
   last_Name and age.

   From the lookup table, the following column is selected:
   city.

   Drop the selected columns from the input tables
   (people and large_volume) to
   the output table.

3. Now set up the join between the main and lookup flows.

   Select the age column of the main flow table (on top)
   and drag it towards the age column of the lookup flow
   table (large_volume in this example).

   A key icon appears next to the linked expression on the lookup table. The
   join is now established.

4. Click the tMap settings button, click the
   three-dot button corresponding to Lookup
   Model, and select the Reload at each
   row option from the [Options] dialog box in order to reload the lookup for each
   row being processed.

   

5. In the same way, set Match Model to
   All matches in the Lookup table, in
   order to gather all instances of age matches in the
   output flow.

6. Now implement the filtering, based on the age column,
   in the Lookup table. The GlobalMapKey field
   is automatically created when you selected the Reload
   at each row option. Indeed you can use this expression to
   dynamically filter the reference data in order to load only the relevant
   information when joining with the main flow.

   As mentioned in the introduction of the scenario, the main flow data
   contains only people whose age is either 40 or 60. To avoid the pain of
   loading all lookup rows, including ages that are different from 40 and 60,
   you can use the main flow age as global variable to feed the lookup
   filtering.

   

7. Drop the Age column from the main flow table to the
   Expr. field of the lookup table.

8. Then in the globalMap Key field, put in
   the variable name, using the expression. In this example, it reads:
   "people.Age"

   Click OK to save the mapping setting and
   go back to the design workspace.

9. To finalize the implementation of the dynamic filtering of the lookup
   flow, you need now to add a WHERE clause in the query of the database
   input.

   

10. At the end of the Query field, following
    the Select statement, type in the following WHERE clause:
    WHERE AGE
='"+((Integer)globalMap.get("people.Age"))+"'"

11. Make sure that the type corresponds to the column used as variable. In
    this use case, Age is of Integer
    type. And use the variable the way you set in the globalMap key field of the map editor.

12. Double-click the tMysqloutput component
    to define its properties.

    

13. Select the Use an existing connection
    check box to leverage the created DB connection.

    Define the target table name and relevant DB actions.

1. Press Ctrl + S to save the Job.

2. Click the Run tab at the bottom of the
   design workspace, to display the Job execution tab.

3. From the Debug Run view, click the
   Traces Debug button to view the data
   processing progress.

   For more comfort, you can maximize the Job design view while executing by
   simply double-clicking on the Job name tab.

   

   The lookup data is reloaded for each of the main flow’s rows,
   corresponding to the age constraint. All age matches
   are retrieved in the lookup rows and grouped together in the output
   flow.

   Therefore if you check out the data contained in the newly created
   people_mixandmatch table, you will find all the
   age duplicates corresponding to different
   individuals whose age equals to 60 or 40 and the city where they have been
   to.

   

   For examples of how to use dynamic
   schemas with tMap, see:

   • Scenario 4: Writing dynamic columns from a MySQL database to an output file.

   • Scenario 4: Writing dynamic columns from a source file to a MySQL database.

1. In the Repository tree view, click
   Metadata > File delimited. Drag and drop the
   customers metadata onto the workspace.

   The customers metadata contains information about
   customers, such as their ID, their name or their address, etc.

   For more information about centralizing metadata, see Talend Studio
   User Guide.

2. In the dialog box that asks you to choose which component type you want to
   use, select tFileInputDelimited and click
   OK.

3. Drop the states metadata onto the design workspace.
   Select the same component in the dialog box and click OK.

   The states metadata contains the ID of the state,
   and its name.

4. Drop a tMap and two tLogRow components from the Palette onto the design workspace.

5. Connect the customers component to the tMap, using a Row >
   Main connection.

6. Connect the states component to the tMap, using a Row >
   Main connection. This flow will automatically be defined as
   Lookup.

   

1. Double-click the tMap component to open
   the Map Editor.

   Drop the idState column from the main input table to
   the idState column of the lookup table to create a
   join.

   Click the tMap settings button and set
   Join Model to Inner Join.

2. Click the Property Settings button at the
   top of the input area to open the [Property
   Settings] dialog box, and clear the Die
   on error check box in order to handle the execution errors.

   The ErrorReject table is automatically
   created.

   

3. Select the id, idState,
   RegTime and RegisterTime in
   the input table and drag them to the ErrorReject table.

   

4. Click the [+] button at the top right of
   the editor to add an output table. In the dialog box that opens, select
   New output. In the field next to it,
   type in the name of the table, out1. Click OK.

5. Drag the following columns from the input tables to the
   out1 table: id,
   CustomerName, idState, and
   LabelState.

   Add two columns, RegTime and
   RegisterTime, to the end of the
   out1 table and set their date formats:
   "dd/MM/yyyy HH:mm" and "yyyy-MM-dd
HH:mm:ss.SSS" respectively.

6. Click in the Expression field for the
   RegTime column, and press Ctrl+Space to display the auto-completion list. Find and
   double-click TalendDate.parseDate. Change the pattern to
   ("dd/MM/yyyy HH:mm",row1.RegTime).

7. Do the same thing for the RegisterTime column, but
   change the pattern to ("yyyy-MM-dd
HH:mm:ss.SSS",row1.RegisterTime).

   

8. Click the [+] button at the top of the
   output area to add an output table. In the dialog box that opens, select
   Create join table from, choose
   Out1, and name it rejectInner.
   Click OK.

9. Click the tMap settings button and set
   Catch lookup inner join reject to
   true in order to handle rejects.

10. Drag the id, CustomerName, and
    idState columns from the input tables to the
    corresponding columns of the rejectInner table.

    Click in the Expression field for the
    LabelState column, and type in
    "UNKNOWN".

11. Click in the Expression field for the
    RegTime column, press Ctrl+Space, and select TalendDate.parseDate. Change the
    pattern to ("dd/MM/yyyy HH:mm",row1.RegTime).

12. Click in the Expression field for the
    RegisterTime column, press Ctrl+Space, and select TalendDate.parseDate, but change
    the pattern to ("yyyy-MM-dd
HH:mm:ss.SSS",row1.RegisterTime).

    If the data from row1 has a wrong pattern, it will
    be returned by the ErrorReject flow.

    

    Click OK to validate the changes and
    close the editor.

13. Double-click the first tLogRow component
    to display its Component view.

    Click Sync columns to retrieve the
    schema structure from the mapper if needed.

    In the Mode area, select Table.

    Do the same thing with the second tLogRow.

1. Press Ctrl + S to save your Job.

2. Press F6 to execute it.

   The Run console displays the main out
   flow and the ErrorReject flow. The main output flow unites both valid data
   and inner join rejects, while the ErrorReject flow contains the error
   information about rows with unparseable date formats.

   

   For examples of how to use dynamic
   schemas with tMap, see:

   • Scenario 4: Writing dynamic columns from a MySQL database to an output file.

   • Scenario 4: Writing dynamic columns from a source file to a MySQL database.