liang jin and chen li n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Selectivity Estimation for Fuzzy String Predicates in Large Data Sets PowerPoint Presentation
Download Presentation
Selectivity Estimation for Fuzzy String Predicates in Large Data Sets

Loading in 2 Seconds...

play fullscreen
1 / 52

Selectivity Estimation for Fuzzy String Predicates in Large Data Sets - PowerPoint PPT Presentation


  • 65 Views
  • Uploaded on

Liang Jin and Chen Li. Selectivity Estimation for Fuzzy String Predicates in Large Data Sets. Supported by NSF CAREER Award IIS-0238586. Example: a movie database. “Find movies starred Schwarrzenger ”?. Find movies with a star “ similar to ” Schwarrzenger.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Selectivity Estimation for Fuzzy String Predicates in Large Data Sets' - rafe


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
example a movie database
Example: a movie database

“Find movies starred Schwarrzenger”?

Find movies with a star “similar to” Schwarrzenger.

queries with fuzzy string predicates
Queries with Fuzzy String Predicates
  • Stars: name similar to “Schwarrzenger”
  • Employees: SSN similar to “430-87-7294”
  • Customers: telephone number similar to “412-0964”
  • Similar to:
    • a domain-specific function
    • returns a similarity value between two strings
  • Example: edit distance
    • Ed(s1,s2): minimum # of operations (insertion, deletion, substitution) to change s1 to s2
    • ed(Tom Hanks,

Ton Hank ) = 2

Database

selectivity estimation problem formulation
Selectivity Estimation: Problem Formulation

star SIMILARTO ’Schwarrzenger’

Input: fuzzy string predicate P(q, δ)

A bag of strings

Output: # of strings s that satisfy dist(s,q) <= δ

why selectivity estimation
Why Selectivity Estimation?

SELECT *

FROM Movies

WHERE star SIMILARTO ’Schwarrzenger’

AND year BETWEEN [1970,1971];

SELECT *

FROM Movies

WHERE star SIMILARTO ’Schwarrzenger’

AND year BETWEEN [1980,1999];

Movies

The optimizer needs to know the selectivity of a predicate to decide a good plan.

rest of the talk
Rest of the talk
  • Motivation: selectivity estimation of fuzzy predicates
  • Our approach: SEPIA
    • Proximity between strings
    • Histograms and estimation algorithm
  • Construction and maintenance of SEPIA
  • Experiments
intuition of sepia
Intuition of SEPIA

Selectivity Estimation of Approximate Predicates

proximity between strings
Proximity between Strings

Edit Distance? Not discriminative enough

edit vector from s1 to s2
Edit Vector from s1 to s2
  • A vector <I, D, S>
    • I: # of insertions
    • D: # of deletions
    • S: # of substitutions

in a sequence of edit operations with their edit distance

global ppd table
Global PPD Table

Proximity Pair Distribution table

selectivity estimation ed lukas 2
Selectivity Estimation: ed(lukas, 2)
  • Do it for all v2 vectors in each cluster, for all clusters
  • Take the sum of these contributions
selectivity estimation for ed q d
Selectivity Estimation for ed(q,d)
  • For each cluster Ci
  • For each v2 in frequency table of Ci
  • Use (v1,v2,d) to lookup PPD
  • Take the sum of these f * N
  • Pruning possible (triangle inequality)
outline
Outline
  • Motivation: selectivity estimation of fuzzy predicates
  • Our approach: SEPIA
    • Proximity between strings
    • Histograms and estimation algorithm
  • Construction and maintenance of SEPIA
  • Experiments
clustering strings
Clustering Strings

Two example algorithms

  • Lexicographic order based.
  • K-Medoids
    • Choose initial pivots
    • Assign strings to its closest pivot
    • Swap a pivot with another string
    • Reassign the strings
number of clusters
Number of Clusters

It affects:

  • Cluster quality
    • Similarity of strings within each cluster
  • Costs:
    • Space
    • Estimation time
constructing frequency tables
Constructing Frequency Tables
  • For each cluster, group strings based on their edit vector from the pivot
  • Count the frequency for each group
constructing ppd table
Constructing PPD Table
  • Get enough samples of string triplets (q,p,s)
  • Propose a few heuristics
    • ALL_RAND
    • CLOSE_RAND
    • CLOSE_LEX
    • CLOSE_UNIQUE
dynamic maintenance frequency table
Dynamic Maintenance: Frequency Table

Take insertion as an example

improving estimation accuracy
Improving Estimation Accuracy
  • A post-processing step to further improve estimation accuracy
  • See paper for details.
outline1
Outline
  • Motivation: selectivity estimation of fuzzy predicates
  • Our approach: SEPIA
    • Proximity between strings
    • Histograms and estimation algorithm
  • Construction and maintenance of SEPIA
  • Experiments
slide26
Data
  • Citeseer:
    • 71K author names
    • Length: [2,20], avg = 12
  • Movie records from UCI KDD repository:
    • 11K movie titles.
    • Length: [3,80], avg = 35
  • Introduced duplicates:
    • 10% of records
    • # of duplicates: [1,20], uniform
  • Final results:
    • Citeseer: 142K author names
    • UCI KDD: 23K movie titles
setting
Setting
  • Test bed
    • PC: 2.4G P4, 1.2GB RAM, Windows XP
    • Visual C++ compiler
  • Query workload:
    • Strings from the data
    • String not in the data
    • Results similar
  • Quality measurements
    • Relative error: (fest – freal) / freal
    • Absolute relative error : |fest – freal | / freal
quartile distribution of relative errors
Quartile distribution of relative errors

Data set 1. CLOSE_RAND; 1000 clusters

dynamic maintenance
Dynamic Maintenance

More results in the paper:

  • Extension to other similarity functions
  • More experimental results
related work
Related Work
  • Traditional histograms
  • Selectivity estimation for predicates with wildcards: star LIKE “%Hanks%”
  • Answering fuzzy predicates efficiently (another talk in this conference)
conclusions
Conclusions
  • Important to support queries with fuzzy string predicates
  • SEPIA: provides accurate selectivity estimation
    • Structures can be efficiently constructed and maintained.
    • Extendable to various similarity measurements

The Flamingo Project :http://www.ics.uci.edu/~flamingo/

Q&A?

why do we care

Errors in databases:

    • Data is not clean
    • Especially true in data integration and cleansing

Relation S

Relation R

Star

Star

Keanu Reeves

Keanu Reeves

Samuel Jackson

Samuel L. Jackson

Why do we care?

Schwarzenegger

Schwarzenegger

Samuel Jackson

Samuel L. Jackson

  • Errors in queries
    • User doesn’t remember a string exactly
    • User types a wrong string
size of histograms
Size of histograms
  • Data set 1
  • 1000 clusters
  • PPD table: 5MB
  • Frequency tables: 200KB
constructing ppd table1
Constructing PPD table
  • We want to generate enough sample triplets to cover as many (v1, v2) pairs as possible.
  • We also want to control the cost of generating the samples and calculation.
  • Heuristics
    • ALL_RAND
    • CLOSE_RAND
    • CLOSE_LEX
    • CLOSE_UNIQUE
populating ppd table
Populating PPD Table

CLOSE_RAND is used

number of clusters con t
Number of Clusters (con’t)

Number of clusters grows with the size of the dataset

Fixed number of clusters

extension to other similarity functions
Extension to other similarity functions
  • SEPIA: a general framework for selectivity estimation for fuzzy string predicates.
  • Key issue in extensions: proximity between strings
    • Too specific?
    • Too general?
  • Example: Jaccard coefficient distance
    • Proximity between two strings s1 and s2.
    • G(s,n) is the n-gram set for string s.

< |G(s1, n) ^ G(s2, n)|, |G(s1, n) v G(s2, n)|, ed(s1, s2) >

research issues
Research Issues
  • Deciding similarity functions
    • Domain specific
  • Query processing
    • Answering a query with fuzzy predicates efficiently
  • Query optimization
    • Selectivity estimation
queries with fuzzy string predicates1
Queries with Fuzzy String Predicates
  • Stars: name similar to “Schwarrzenger”
  • Employees: SSN similar to “430-87-7294”
  • Customers: telephone number similar to “412-0964”

Database

  • Similar to:
    • a domain-specific function
    • returns a similarity value between two strings
  • Examples:
    • Edit distance: ed(Schwarrzenger, Schwarzenegger)=2
    • Cosine similarity
    • Jaccard coefficient distance
    • Soundex
why do we care1

Errors in the database:

    • Data often is not clean by itself
    • Especially true in data integration and cleansing

Relation S

Relation R

Star

Star

Keanu Reeves

Keanu Reeves

Samuel Jackson

Samuel L. Jackson

Why do we care?

Schwarzenegger

Schwarzenegger

Samuel Jackson

Samuel L. Jackson

  • Errors in the query
    • The user doesn’t remember a string exactly
    • The user unintentionally types a wrong string
selectivity of fuzzy predicates
Selectivity of Fuzzy Predicates

star SIMILARTO ’Schwarrzenger’

  • Selectivity: # of records satisfying the predicate
example similarity function edit distance
Example Similarity Function: Edit Distance
  • A widely used metric to define string similarity
  • Ed(s1,s2)= minimum # of operations (insertion, deletion, substitution) to change s1 to s2
  • Example:

s1: Tom Hanks

s2: Ton Hank

ed(s1,s2) = 2

using traditional histograms
Using traditional histograms?
  • No “nice” order for strings
  • Lexicographical order?
    • Similar strings could be far from each other: Kammy/Cammy
    • Adjacent strings have different selectivities: Cathy/Catherine
edit vector from s1 to s21
Edit Vector from s1 to s2
  • A vector <I, D, S>
    • I: # of insertions
    • D: # of deletions
    • S: # of substitutions

in a sequence of edit operations with their edit distance

  • Easily computable
  • Not symmetric
  • Not unique, but tend to be (ed <= 3  91% unique)
improving estimation accuracy1
Improving Estimation Accuracy
  • Reasons of estimate errors
    • Miss hits in PPD.
    • Inaccurate percentage entries in PPD.
  • Improvement: use sample fuzzy predicates to analyze their estimation errors
relative error model
Relative-Error Model
  • Use the errors to build a model
  • Use the model to adjust initial estimation
clustering algorithms
Clustering Algorithms

K-Metoids is better