Text Joins in an RDBMS for Web Data Integration

1
Text Joins in an RDBMS for Web Data Integration

• Luis Gravano
• Panagiotis G. Ipeirotis

Nick Koudas Divesh Srivastava
Columbia University
ATT Labs - Research
2
Why Text Joins?
Problem Same entity has multiple textual
representations.
3
Matching Text Attributes
Need for a similarity metric!
Many desirable properties

• Match entries with typing mistakes
• Microsoft Windpws XP vs. Microsoft Windows XP
• Match entries with abbreviated information
• Zurich International Airport vs. Zurich Intl.
  Airport
• Match entries with different formatting
  conventions
• Dept. of Computer Science vs. Computer Science
  Dept.
• and combinations thereof

4
Matching Text Attributes using Edit Distance
Edit Distance Character insertions, deletions,
and modifications to transform one string to the
other
Good for spelling errors, short word insertions
and deletions Problems word order variations,
long word insertions and deletions
Approximate String Joins VLDB 2001
5
Matching Text Attributes using Cosine Similarity
Similar entries should share infrequent tokens
Different token choices result in similarity
metrics with different properties
6
Using Words and Cosine Similarity
Using words as tokens
Infrequent token (high weight)

• Split each entry into words
• Similar entries share infrequent words

Common token (low weight)

• Good for word order variations and common word
  insert./del.
• Computer Science Dept. Dept. of Computer
  Science
• Problems with misspellings
• Biotechnology Department ? Bioteknology Dept.

WHIRL W.Cohen, SIGMOD98
7
Using q-grams and Cosine Similarity
Using q-grams as tokens

• Split each string into small substrings of length
  q (q-grams)
• Similar entries share many, infrequent q-grams

Handles naturally misspellings, word order
variations, and insertions and deletions of
common or short words
8
Problem
For two entries t1, t2 0 Similarity 1
Problem that we address Given two relations,
report all pairs with cosine similarity above
threshold f
9
Computing Text Joins in an RDBMS
R1
R2
10
Sampling Step for Text Joins
Similarity S weight(token, t1) weight(token,
t2)

• Similarity is a sum of products
• Products cannot be high when weight is small
• Can (safely) drop low weights from RiWeights
  (adapted from Cohen Lewis, SODA97 for
  efficient execution inside an RDBMS)

RiWeights
RiSample
Eliminates low similarity pairs (e.g., EUROAFT
INC with HATRONIC INC)
11
Sampling-Based Text Joins in SQL
R1Weights
R2Sample
R1
12
Experimental Setup

• 40,000 entries from ATT customer database, split
  into R1 (26,000 entries) and R2 (14,000 entries)
• Tokenizations
• Words
• Q-grams, q2 q3

• Methods compared
• Variations of sample-based joins
• Baseline in SQL
• WHIRL SIGMOD98, adapted for handling q-grams

13
Metrics

• Execute the (approximate) join for similarity gt
  f
• Precision (measures accuracy)
• Fraction of the pairs in the answer with real
  similarity gt f
• Recall (measures completeness)
• Fraction of the pairs with real similarity gt f
  that are also in the answer
• Execution time

14
Comparing WHIRL and Sample-based Joins

• Sample-based Joins Good recall across similarity
  thresholds
• WHIRL Very low recall (almost 0 recall for
  thresholds below 0.7)

15
Changing Sample Size

• Increased sample size ? Better recall,
  precision
• Drawback Increased execution time

16
Execution Time

• WHIRL and Sample-based text joins break-even at
  S 64, 128

17
Contributions

• WHIRL Cohen, SIGMOD98 inside an RDBMS
  Scalability, no data exporting/importing
• Different tokens choices
• Words Captures word swaps, deletion of common
  words
• Q-grams All the above, plus spelling mistakes,
  but slower

SQL statements tested in MS SQL Server and
available for download at http//www.cs.columbia.
edu/pirot/DataCleaning/
18
Questions?