Scenario 2: Comparing columns and grouping in the output flow duplicate records that have the same functional key - 6.3

Talend Components Reference Guide

EnrichVersion
6.3
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

This second scenario describes a Job that aims at:

  • generating a functional key using one algorithm on one of the input columns, DoB as described in scenario 1.

  • matching two input columns using the Jaro-Winkler algorithm.

  • grouping the output columns by the generated functional key to optimize the matching operation and compare only the records that have the same blocking value, functional key in this scenario. For more information on grouping output columns and using blocking values, see tMatchGroup.

Setting up the Job

  1. Drop the following components from the Palette onto the design workspace: tRowGenerator, tGenKey and tMatchGroup.

  2. Connect all the components together using the Main link.

Configuring the data input

  1. Double-click tRowGenerator to define its schema as follows:

    The tRowGenerator component will generate an input data flow that has three columns: Firstname, Lastname, and DoB (date of birth). This data may have problems such as duplication, first or last names spelled differently or wrongly, different information for the same customer, etc.

  2. Click OK to validate the settings and close the dialog box, and accept to propagate the changes when prompted.

Configuring key generation

  1. Double-click tGenKey to display the Basic settings view and define the component properties.

    You can click and import blocking keys from the match rules created with the VSR algorithm and tested in the Profiling perspective of Talend Studio and use them in your Job. Otherwise, define the blocking key parameters as described in the below steps.

  2. Under the Algorithm table, click the [+] button to add a row in this table.

  3. On the column column, click the newly added row and select from the list the column you want to process using an algorithm. In this example, select DoB.

  4. On the algorithm column, click the newly added row and select from the list the algorithm you want to apply to the corresponding column. In this example, select substring(a,b).

  5. Click in the value column and enter the value for the selected algorithm, when needed. In this scenario, type in 6;10.

    The substring(a,b) algorithm allows you to extract the characters from a string, between two specified indices, and to return the new substring. First character is at index 0. In this scenario, for a given DoB "21-01-1995", 6;10 will return only the year of birth, that is to say "1995" which is the substring from the 7th to the 10th character.

    In this example, we want to generate a functional key that holds the last four characters of the date of birth, which correspond to the year of birth, for each of the data rows and we do not want to define any extra options on these columns.

    You can select the Show help check box to display instructions on how to set algorithms/options parameters.

    Once you have defined the tGenKey properties, you can display a statistical view of these parameters. To do so:

  6. Right-click on the tGenKey component and select View Key Profile in the contextual menu.

    The View Key Profile editor displays, allowing you to visualize statistics regarding the number of blocks and to adapt the parameters according to the results you want to get.

    Note

    When you are processing a large amount of data and when this component is used to partition data in order to use them in a matching component (such as tRecordMatching or tMatchGroup), it is preferable to have a limited number of rows in one block. An amount of about 50 rows per block is considered optimal, but it depends on the number of fields to compare, the total number of rows and the time considered acceptable for data processing.

    From the key editor, you can:

    • edit the Limit of rows used to calculate the statistics.

    • click and import blocking keys from the Studio repository and use them in your Job.

    • edit the input column you want to process using an algorithm.

    • edit the parameters of the algorithm you want to apply to input columns.

    Every time you make a modification, you can see its implications by clicking the Refresh button which is located at the top right part of the editor.

  7. Click OK to close the View Key Profile editor.

Configuring the grouping of the output data

  1. Click the tMatchGroup component, and then in its basic settings click the Edit schema button to view the input and output columns and do any modifications in the output schema, if needed.

    In the output schema of this component, there are output standard columns that are read-only. For more information, see tMatchGroup properties.

  2. Click OK to close the dialog box.

  3. Double-click the tMatchGroup component to display its Configuration Wizard and define the component properties.

    If you want to add a fixed output column, MATCHING_DISTANCES, which gives the details of the distance between each column, click the Advanced settings tab and select the Output distance details check box. For more information, see tMatchGroup properties.

  4. In the Key definition table, click the plus button to add to the list the columns on which you want to do the matching operation, FirstName and LastName in this scenario.

  5. Click in the first and second cells of the Matching Function column and select from the list the algorithm(s) to be used for the matching operation, Jaro-Winkler in this example.

  6. Click in the first and second cells of the Weight column and set the numerical weights for each of the columns used as key attributes.

  7. In the Match threshold field, enter the match probability threshold. Two data records match when the probability threshold is above this value.

  8. Click the plus button below the Blocking Selection table to add a line in the table, then click in the line and select from the list the column you want to use as a blocking value, T_GEN_KEY in this example.

    Using a blocking value reduces the number of pairs of records that needs to be examined. The input data is partitioned into exhaustive blocks based on the functional key. This will decrease the number of pairs to compare, as comparison is restricted to record pairs within each block.

  9. Click the Chart button in the top right corner of the wizard to execute the Job in the defined configuration and have the matching results directly in the wizard.

    The matching chart gives a global picture about the duplicates in the analyzed data. The matching table indicates the details of items in each group and colors the groups in accordance with their color in the matching chart.

Configuring the console output

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

  2. In the Mode area, select Table to display the Job execution result in table cells.

Executing the Job

  • Save your Job and press F6 to execute it.

    The output columns include the T_GEN_KEY column that holds the functional key generated by the tGenKey component.

    You can see that all records that have the same functional key are grouped together in different blocks "groups". The identifier for each group is listed in the GID column next to the corresponding record. The number of records in each of the output blocks is listed in the GRP_SIZE column and computed only on the master record. The MASTER column indicates with true/false if the corresponding record is a master record or not a master record. The SCORE column lists the calculated distance between the input record and the master record according to the Jaro-Winkler matching algorithm.

    For an example of creating data partitions based on different blocking keys and using them with multiple tMatchGroup components, see tMatchGroup.