tRecordMatching – Docs for ESB 5.x

tRecordMatching

1. In Eclipse, check out the test.mydistance project
   from svn at:

   http://talendforge.org/svn/top/trunk/test.mydistance/

2. In this project, navigate to the Java class named
   MyDistance.Java: http://talendforge.org/svn/top/trunk/test.mydistance/src/main/java/org/talend/mydistance//.

3. Open this file that has the below code:

   package org.talend.mydistance; import org.talend.dataquality.record.linkage.attribute.AbstractAttributeMatcher; import org.talend.dataquality.record.linkage.constant.AttributeMatcherType; /** * @author scorreia * * Example of Matching distance. */ public class MyDistance extends AbstractAttributeMatcher { /* * (non-Javadoc) * * @see org.talend.dataquality.record.linkage.attribute.IAttributeMatcher#getMatchType() */ @Override public AttributeMatcherType getMatchType() { // a custom implementation should return this type AttributeMatcherType.custom return AttributeMatcherType.CUSTOM; } /* * (non-Javadoc) * * @see org.talend.dataquality.record.linkage.attribute.IAttributeMatcher#getMatching Weight(java.lang.String, * java.lang.String) */ @Override public double getWeight(String arg0, String arg1) { // Here goes the custom implementation of the matching distance between the two given strings. // the algorithm should return a value between 0 and 1. // in this example, we consider that 2 strings match if their first 4 characters are identical // the arguments are not null (the check for nullity is done by the caller) final int max = 4; int nbIdenticalChar = Math.min(max, Math.min(arg0.length(), arg1.length())); for (int c = 0; c < max; c++) { if (arg0.charAt(c) != arg1.charAt(c)) { nbIdenticalChar = c; break; } } return (max - nbIdenticalChar) / ((double) max); } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

   package org.talend.mydistance;   import org.talend.dataquality.record.linkage.attribute.AbstractAttributeMatcher; import org.talend.dataquality.record.linkage.constant.AttributeMatcherType;   /** * @author scorreia * * Example of Matching distance. */ public class MyDistance extends AbstractAttributeMatcher {       /*      * (non-Javadoc)      *      * @see org.talend.dataquality.record.linkage.attribute.IAttributeMatcher#getMatchType()      */     @Override     public AttributeMatcherType getMatchType() {         // a custom implementation should return this type AttributeMatcherType.custom         return AttributeMatcherType.CUSTOM;     }       /*      * (non-Javadoc)      *      * @see org.talend.dataquality.record.linkage.attribute.IAttributeMatcher#getMatching      Weight(java.lang.String,      * java.lang.String)      */     @Override     public double getWeight(String arg0, String arg1) {         // Here goes the custom implementation of the matching distance between the two given strings.         // the algorithm should return a value between 0 and 1.           // in this example, we consider that 2 strings match if their first 4 characters are identical         // the arguments are not null (the check for nullity is done by the caller)         final int max = 4;         int nbIdenticalChar = Math.min(max, Math.min(arg0.length(), arg1.length()));         for (int c = 0; c < max; c++) {             if (arg0.charAt(c) != arg1.charAt(c)) {                 nbIdenticalChar = c;                 break;             }         }         return (max - nbIdenticalChar) / ((double) max);     }   }

4. In this file, type in the class name for the custom algorithm you are
   creating in order to replace the default name. The default name is
   MyDistance and you can find it in the line:
   public class MyDistance implements
IAttributeMatcher.

5. In the place where the default algorithm is in the file, type in the
   algorithm you need to create to replace the default one. The default
   algorithm reads as follows:

   final int max = 4; int nbIdenticalChar = Math.min(max, Math.min(arg0.length(), arg1.length())); for (int c = 0; c < max; c++) { if (arg0.charAt(c) != arg1.charAt(c)) { nbIdenticalChar = c; break; } } return (max - nbIdenticalChar) / ((double) max);

   1 2 3 4 5 6 7 8 9

   final int max = 4;         int nbIdenticalChar = Math.min(max, Math.min(arg0.length(), arg1.length()));         for (int c = 0; c < max; c++) {             if (arg0.charAt(c) != arg1.charAt(c)) {                 nbIdenticalChar = c;                 break;             }         }         return (max - nbIdenticalChar) / ((double) max);

6. Save your modifications.

7. Using Eclipse, export this new .jar file.

1. In the Repository tree view, expand
   Metadata – DB
   Connections where you have stored the main input schemas and
   drop the relevant file onto the design workspace.

   The [Components] dialog box
   appears.

   

2. Select tMysqlInput and click OK to drop the tMysqlInput component onto the workspace.

   The input table used in this scenario is called
   person. It holds several columns including the two
   columns name and email we want to
   do the fuzzy match on. The following capture shows the basic properties of
   the main input component:

   

3. Do the same for the second input table you want to use as a reference,
   customer in this scenario.

   The following capture shows the basic properties of the reference input
   component:

   

4. Drop the following components from the Palette onto the design workspace: tRecordMatching and three tLogRow.

5. Connect the main and reference input components to tRecordMatching using Main
   links. The link between the reference input table and tRecordMatching displays as a Lookup link on the design workspace.

6. Connect tRecordMatching to the three
   tLogRow components using the Matches, Possible
   Matches and Non Matches
   links.

   

1. Double-click tRecordMatching to display
   its Basic settings view and define its
   properties.

   

2. Click the Edit schema button to open a
   dialog box. Here you can define the data you want to pass to the output
   components.

   

   In this example we want to pass to the tRecordMatching component the name and
   email columns from the first tMysqlInput component, and the ref_name
   and ref_email columns from the
   second tMysqlInput component.

   The MATCHING_DISTANCE and the
   MATCHING_WEIGHT columns in the output schema are
   defined by default.

   The MATCHING_WEIGHT column is always between
   0 and 1. It is a global
   distance between sets of columns (defined by the columns to be matched).

   The MATCHING_DISTANCE column will print a distance
   for each of the columns on which we use an algorithm. The results will be
   separated by a vertical bar (pipe).

   Click OK to close the dialog box and
   proceed to the next step.

3. In the Key Definition area of the
   Basic settings view of tRecordMatching, click the plus button to add two
   columns to the list.

4. Select the input columns and the output columns you want to do the fuzzy
   matching on from the Input key attribute
   and Lookup key attribute lists
   respectively.

   In this example, select name and email
   as input attributes and ref-name and
   ref_email as lookup attributes.

   Note

   When you select a date column on which to apply an algorithm or a matching algorithm,
   you can decide what to compare in the date format.

   For example, if you want to only compare the year in the date, in the component schema
   set the type of the date column to Date and then enter
   “yyyy” in the Date
   Pattern field. The component then converts the date format to a string
   according to the pattern defined in the schema before starting a string
   comparison.

5. Click in the Matching type column and
   select from the list q-gram, the method to
   be used on the first column to check the incoming data against the reference
   data.

6. Set the matching type for the second column, Levenshtein in this example.

   The minimum and maximum possible match values are defined in the Advanced settings view. You can change the
   by-default values.

7. In the Confidence Weight column, set a
   numerical weight for each of the columns used as key attributes.

8. Click in the cell of the Handle Null
   column and select the null operator you want to use to handle null
   attributes in the columns.

9. If required, click the plus button below the Blocking Selection table to add one or more lines in the
   table and then click in the line and select from the list the column you
   want to use as a blocking value.

   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 blocking value. This will decrease the number of pairs to compare as
   comparison is restricted to record pairs within each block. Check Scenario 2: Comparing columns and grouping in the output flow duplicate
   records that have the same functional key for a use case of the blocking value.

10. Double-click the first tLogRow component
    to display its Basic settings view, and
    select Table in the Mode area to display the source file and the tRecordMatching results together to be able to
    compare them.

    

11. Do the same for the other two tLogRow
    components.

• Save your Job and press F6 to execute
  it.

  Three output tables are written on the console. The first shows the match
  entries, the second show the possible match entries and the third shows the
  non match entries according to the used matching method in the defined
  columns.

  The figure below illustrates extractions of the three output
  tables.

  

  The first table lists all the names and emails that could be matched with
  identical entries in the reference table. Thus the matching distance and the
  matching weight are equal to “1.0”.

  The second table lists all the names and emails that have a possible match
  in the reference table. The matching distance column prints the distances
  for the name and email columns and
  separate them by a vertical bar.

  The third table lists all the names and emails that do not have match in
  the reference table.

1. On the previous Job, drop the tLibraryLoad component from the Palette to the Design workspace.

2. Delete the tLogRow components named
   possible and none.

3. Connect the tLibraryLoad component to the
   tMysqlInput
   (person) component using a Trigger > On Subjob Ok link.

1. Double-click tLibraryLoad to open its
   Component view.

   

2. Click the […] button and browse to the
   mydistance.jar file.

3. Click Windows>Show
   view… to open the Modules
   view.

4. In the Modules view, click and in the open dialog box, browse to the user-defined
   mydistance.jar file created for this Job.

5. Click Open.

   The user-defined .jar file is imported and listed in
   the Modules view.

   You will get an error message if you try to run the Job without installing
   the external user-defined .jar file.

6. Double-click tRecordMatching to open its
   Component view.

   

7. In the Key Definition table of this view,
   click the name row in the Matching Type column and select custom… from the drop-down list.

8. In the Custom matcher class of this
   name row, type in the path pointing to
   MyDistance.class in the
   mydistance.jar file. In this example, this path is
   org.talend.mydistance.MyDistance.

Note

When you select a date column on which to apply an algorithm or a matching algorithm,
you can decide what to compare in the date format.

For example, if you want to only compare the year in the date, in the component schema
set the type of the date column to Date and then enter
“yyyy” in the Date
Pattern field. The component then converts the date format to a string
according to the pattern defined in the schema before starting a string
comparison.

• Press F6 to run this Job.

  In the Run view, the matched entries are
  identified and listed as follows: