uploadTable

Class FindCorrespondences

java.lang.Object

uploadTable.FindCorrespondences

public class FindCorrespondences
extends java.lang.Object

**************************************************************************************************************************** * This is the Main Class of the DS4DM Preprocessing Component called "CreateCorrespondenceFiles". * * This Software component finds the schema- and instance- correspondences between the tables in the webservice's corpus * **************************************************************************************************************************** About DS4DM: DS4DM stands for 'Data Search for Data Mining'. It is a extension to the RapidMiner software. This extension allows users to extend a data table with additional attributes(=columns) of their choice. E.g. if you have a table with company information uploaded in RapidMiner, you can use the DS4DM-RapidMiner-operators for getting the additional column 'Headquarter location' - ready populated with the correct values. The DS4DM Webservice: The table extension operator of the DS4DM-RapiMiner extension uses a custom-made webservice. This webservice hosts a large corpus of datatables. Whenever a table needs to be extended, the webservice looks for the right tables (in our example: tables with company headquarter information) and returns these tables to the RapidMiner operator. The Search function: The webservice offers two table-search functions: search and extendedSearch. The search function will only find and return tables that have a column with a name similar to the name of the desired extension attribute. In our example: only tables will be found that have a columnname similar to 'Headquarter location'. There might however be tables where this column exists, but has a quite different name e.g 'HQ city'. In the standard search function these tables would not be found. The ExtendedSearch function Therefore the webservice also offers the extendedSearch. Here, there is a first step in which tables are searched for in the same way as in the standard search function - using the similarity of columnnames. There is however a second step in which the list of found tables is extended with additional tables that have the correct column, the column however has an unpractical name. This second step (the extension-step) uses pre-calculated correspondences between the tables in the webservice's table corpus. Schema-Correspondences A schema-correspondence marks two columns from two different tables as containing the same attribute e.g. column1 from table1 and column4 from table3 both contain company sizes. The pre-calculated schema-correspondences between tables in the corpus, allow us - knowing the 'Headquarter location'-columns found in the standard search - to find further, less obvious 'Headquarter location'-columns in other tables. These additional tables, which we found via the schema-correspondences are then added to our list of found tables. ************************************************************************************************************************************* * This Software component finds the schema- and instance- correspondences between the tables in the webservice's corpus * * You run it before you start the webservice, or whenever you would like to use a new corpus of tables. * * This is why it is called a 'Preprocessing Component'. * ************************************************************************************************************************************* Instance-Correspondences We mentioned, that instance correspondences are also created by this software component. An instance-correspondence marks two rows from two different tables as referring to the same real-world-object e.g. row2 from table1 and row11 from table3 both contain information about the company Tesla. The webservice also needs these instance-correspondences to help add the 'Headquarter location'-information to the right company. Operation: This java program can be executed by... * calling its .jar-file via the commandline/terminal. In this case the command should look like this: java -jar CreateCorrespondenceFiles-0.0.1-SNAPSHOT-jar-with-dependencies.jar "path/to/where/the/indexes/are/saved" "path/to/the/csvTables/constituting/the/webservice's/corpus" "path/to/where/the/correspondences/will/be/saved" "Integer blockSize. 5 is a good default value" * running the main-Method of the Main-Class in an java environment with the same four parameters

Author:

Benedikt Kleppmann

Constructor Summary

Constructors

Constructor and Description
FindCorrespondences()

Method Summary

Methods

Modifier and Type	Method and Description
static java.lang.String	formatQueryString(java.lang.String keyColumnString, int maxEditDistance) formatQueryString this small method takes the keyColumnString from one table and prepares it so that it can be used for searching tables with similar key columns.
static de.uni_mannheim.informatik.dws.winter.processing.Processable<de.uni_mannheim.informatik.dws.winter.model.Correspondence<uploadTable.additionalWinterClasses.MatchableTableColumn,uploadTable.additionalWinterClasses.MatchableTableRow>>	getDuplicateBasedSchemaMatches(de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data1, de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data2, java.io.File file1, java.io.File file2, de.uni_mannheim.informatik.dws.winter.processing.Processable<de.uni_mannheim.informatik.dws.winter.model.Correspondence<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn>> instanceCorrespondences, java.lang.String correspondenceFolderPath) getDuplicateBasedSchemaMatches This method uses the previously found instance-matches between two tables to identify the schema-matches between these tables.
static de.uni_mannheim.informatik.dws.winter.processing.Processable<de.uni_mannheim.informatik.dws.winter.model.Correspondence<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn>>	getInstanceMatches(de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data1, de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data2, java.io.File file1, java.io.File file2, java.lang.String correspondenceFolderPath) getInstanceMatches This method finds the instance-matches between a pair of tables and returns these instance matches to a csv-file For finding instance matches, all NxM row combinations between the two tables are considered.
static void	main(java.lang.String[] args) The main Method This is where the program starts.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

FindCorrespondences

public FindCorrespondences()

Method Detail

main

public static void main(java.lang.String[] args)
                 throws java.lang.Exception

The main Method This is where the program starts. When calling it four parameters must be passed to it: indexFolderPath (=path/to/where/the/indexes/are/saved), datafilePath (=path/to/the/csvTables/constituting/the/webservice's/corpus), correspondenceFolderPath (=path/to/where/the/correspondences/will/be/saved), blockSize ("5" is a good value to choose for this integer) If these parameters aren't passed. The program ends with an error message. the steps in this function are: 1. open the KeyColumnIndex 2. create the hashmap 'blockedTableCombinations' Looking for correspondences between each of the NxN table-combinations is too computationally expensive. Therefore we first create a list of likely corresponding table pairs - the blockedTableCombinations. 2.1. the KeyColumnIndex contains the key column of each table in the webservice's corpus. In turn we retrieve each of these key columns 2.1.1. for we use the column values from retrieved key column to search in the index for 5 the tables with the most similar column values - the blocking partners for the originally retrieved key column. 2.1.2. we save the so found table-pairs in the blockedTableCombinations-hashmap in such a way that each table-pair appears only once. 3. loop through the table pairs saved in the blockedTableCombinations-hashmap 3.1. for each table pair: find the instance matches 3.2. pass the instance matches on to the duplicateBasedSchemaMatching-method, to also find the schema-matches

Parameters:

args -

Throws:

java.lang.Exception

getInstanceMatches

public static de.uni_mannheim.informatik.dws.winter.processing.Processable<de.uni_mannheim.informatik.dws.winter.model.Correspondence<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn>> getInstanceMatches(de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data1,
                                                                                                                                                                                                                                                          de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data2,
                                                                                                                                                                                                                                                          java.io.File file1,
                                                                                                                                                                                                                                                          java.io.File file2,
                                                                                                                                                                                                                                                          java.lang.String correspondenceFolderPath)
                                                                                                                                                                                                                                                                   throws java.lang.Exception

getInstanceMatches This method finds the instance-matches between a pair of tables and returns these instance matches to a csv-file For finding instance matches, all NxM row combinations between the two tables are considered. Row combinations with a similarity score bigger than 0.7 are considered instance matches. The similarity score for a row combination = 0.5 * KeyColumnSimilarity + 0.5 * NonKeyColumnSimilarity To find out how the KeyColumnSimilarity and the NonKeyColumnSimilarity are calculated, please look at the classes KeyColumnComparatorJaccard.java and NonKeyColumnComparator_withDType.java respectively. If the parameter deternimePrecisionAndRecall is set to true (by default it is false), then following steps are done additionally: 1. read a goldstandard file from memory. This goldstandard file should contain a list of the correct instance correspondences - usually these will have been manually determined by a person. 2. compare the found instance correspondences with the correct instance correspondences from the goldstandard file and calculate the Precision-, Recall- and F1- scores. 3. save these scores to a csv-file

Parameters:

data1 -

data2 -

file1 -

file2 -

correspondenceFolderPath -

deternimePrecisionAndRecall -

fileLocationsForEvaluation -

Returns:

Throws:

java.lang.Exception

getDuplicateBasedSchemaMatches

public static de.uni_mannheim.informatik.dws.winter.processing.Processable<de.uni_mannheim.informatik.dws.winter.model.Correspondence<uploadTable.additionalWinterClasses.MatchableTableColumn,uploadTable.additionalWinterClasses.MatchableTableRow>> getDuplicateBasedSchemaMatches(de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data1,
                                                                                                                                                                                                                                                                      de.uni_mannheim.informatik.dws.winter.model.DataSet<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn> data2,
                                                                                                                                                                                                                                                                      java.io.File file1,
                                                                                                                                                                                                                                                                      java.io.File file2,
                                                                                                                                                                                                                                                                      de.uni_mannheim.informatik.dws.winter.processing.Processable<de.uni_mannheim.informatik.dws.winter.model.Correspondence<uploadTable.additionalWinterClasses.MatchableTableRow,uploadTable.additionalWinterClasses.MatchableTableColumn>> instanceCorrespondences,
                                                                                                                                                                                                                                                                      java.lang.String correspondenceFolderPath)
                                                                                                                                                                                                                                                                               throws java.io.IOException

getDuplicateBasedSchemaMatches This method uses the previously found instance-matches between two tables to identify the schema-matches between these tables. It then saves the found schema-matches to a csv-file. The schema-match finding works as follows: In each table, we only consider the instances that have an instance match to the other table. For each column combination (column1 - column2) between the two tables, we compare the values the left instances have for the column1 with the values the corresponding right instances have for the column2. If the average similarity between these instance values is bigger than 0.7, we consider this column-combination to be a schema-correspondence. (The exact way that two instance values are compared is data type specific. It is specified in the nested method 'compare'.) Once the schema-matches have been found, they are saved to a csv-file.

Parameters:

data1 -

data2 -

file1 -

file2 -

instanceCorrespondences -

correspondenceFolderPath -

Returns:

Throws:

java.io.IOException

formatQueryString

public static java.lang.String formatQueryString(java.lang.String keyColumnString,
                                 int maxEditDistance)

formatQueryString this small method takes the keyColumnString from one table and prepares it so that it can be used for searching tables with similar key columns. The steps are: 1. remove the commas from the keyColumnString 2. split the keyColumnString into a list of words 3. limit the list of words to a length of 1000 4. add '~maxEditDistance' to the end of every word. The maxEditDistance specifies the maximum levenstein distance, that a word may have to be counted as match (by default: maxEditDistance=3)

Parameters:

keyColumnString -

maxEditDistance -

Returns: