cassandra summit 2014: fuzzy entity matching at scale
DESCRIPTION
Presenter: Ken Krugler, President of Scale Unlimited Early Warning has information on hundreds of millions of people and companies. When a person wants to open a new bank account, they need to be able to accurately find similar entities in this large dataset, to provide a risk assessment. Using the combination of Cassandra & Solr via DSE, they can quickly find and evaluate all reasonable candidates.TRANSCRIPT
Ken Krugler | President, Scale Unlimited
Fuzzy Entity Matching
whoami•Ken Krugler, Scale Unlimited - Nevada City, CA
•Consulting on big data (workflows, search, etc)
•Training for Hadoop, Cascading, Solr & Cassandra
The Problem
Should I Trust You?•When opening a bank account...
•...what is the applicant's risk?
!
•Key is matching person...
•...to other account info
Matching people•I have some information you've provided
•I need to match against ALL bank data
•But banks won't exchange their customer info
•So what can we do?
Early Warning Services•Owned by the top 5 US banks
•Gets data from 800+ financial institutions
•So they have details on most US bank accounts
Fuzzy Matching
What's a fuzzy match?•Match everything that's equivalent
!
!
•Match nothing that's different
≅
≇
Why is it hard?•Lots of gray areas in fuzzy matching
!
•Can't use exact key join
•So no easy lookup using C* row key
•Often computationally intensive
≟
Matching People•I've got information on lots of people
•I'm being asked about a specific person
•How to quickly find all good matches?
•Not doing batch matching ≟
What's a Good Match?•Comparing field values between records
•Are these two people the same?Name Bob Bogus Robert Bogus
Address 220 3rd Ave 220 3rd AvenueCity Seattle SeattleState WA WAZIP 98104-2608 98104
What about now?•Normalization becomes critical
•How to focus on the important features?Name Bob Bogus Robert H. Bogus
Address Apt 102, 220 3rd Ave 3220 3rd Avenue SouthCity Seattle SeattleState Washington WAZIP 98104
How do you calc similarity?•Calculate degree of similarity for each field (0 -> 1.0)
•Give each field a weight (these sum to 1.0)
•Score is sum(fieldN sim * fieldN weight)
•So score is 0 (nothing in common) to 1.0 (exact dup)
Does that scale?•For a given person being matched...
•You need to compare to every other person
•Works for a few thousand people
•Doesn't scale for 100s of millions of people
Search to the Rescue
Search is (fast) similarity•Find N most similar docs to this doc (my query)
•Each doc has multi-dimensional feature vector
•Each feature (dimension) is a unique word
•Feature weight is TF * IDF
Cosine Similarity•Each document has a term vector
•E.g. three unique words x, y, z
•Weight is TF*IDF of each word
•Calc cosine of angle between 2 vectors
•That is the similarity score
Cosine sim ≢ match sim•Doesn't have same level of sophistication
•So throw a bigger net to find candidates
•e.g. get top N*X, assuming at most X matches
•Then do match similarity calc on this (small) set
So two-step process
Match0.900.500.100.85
...
Query: name=“Bob Bogus”^3
and ssn=“222447777”^10
and dob=“19600723”^5
Solr
Index
Name SSN DOB
Bob Bogus 222447777 19610603
Robert Bogus 193618919 19600723
Bob Smith 479385821 19600723
Sam Stealthy 222447777 19930523
Name SSN DOB
Bob Bogus 222447777 19600723
... ... ...
How do you pick N?•Can be small, if match sim ≈ search sim
•If N is too big, it's inefficient
•If N is too small, you miss matches
•Tune search to mimic match sim
•Right tradeoff depends on use case
What is Solr?•Enterprise search system, build on top of Lucene
•Open source project at Apache Software Foundation
•Scales to billions of documents
•Highly configurable & customizable
•Integrated with Cassandra in DSE
Solr Schema•Defines set of fields in a document
•Direct one-to-one mapping with Cassandra columns
•Fields can be defined with synonyms, etc., etc.<fields> <field name="key" type="string" indexed="true" stored="true" /> <field name="name" type="text" indexed="true" stored="true" /> </fields>
DSE Search with Solr
What is DSE with Solr? •DSE-specific enhancement to Cassandra
•Keeps a Solr index in sync with a C* table
•Indexes distributed to all nodes C* & Solr
C* & Solr
C* & Solr
C* Table
S* Index
C* Table
S* Index
C* Table
S* Index
Handy replication & failover •Implementation leverages C* replication
•So you get load balancing, reliability, scalability
•You can replicate from a regular C* DC to Solr DCC* & Solr
C* & Solr
C* & Solr
C* C*
C*
Solr DC C* DC
Who builds the index? •In background
•Much slower than C* updates
•Uses existing secondary index hook
Secondary Index Hook
Distribute to indexing queues
Indexing Queue
Logical Rows
Read C* storage row
max_solr_concurrency_per_core
Create one Solr docper entry
Apply FieldInputTransformer Update Solr
back_pressure_threshold_per_core
How fast is it? •Writing 170M records ≈ 2.5 hours
•8 node DSE 4.0 cluster, 8 1TB SSDs on each
•This is indexing during writes
•About 15% of index available when writes finish
•Complete index takes another 12 hours
System Overview
ETL Hadoop Workflow•Extract, transform, load
•Built using Cascading API
•Parse data, simple normalization
•Other transformations happen in Solr
Cassandra ingress•Reduce tasks in Hadoop talk to C* cluster
•Using DataStax Java driver for Cassandra
•Bottleneck is Solr indexing
•Inserts get throttled when this falls behind
•But total time less than with deferred indexing
Architectural DiagramC* + Solr
C* + Solr
C* + Solr
Hadoop
Cluster
Entity Matcher API
Ingest performance•For max performance, write without reads
•But how to avoid creating duplicate entries?
•Set the row key to the hash of searchable fields
•Accept "near duplicates" in search results
•Possible to push some Solr load into workflow
Summary
Key points to remember•This is for ad hoc requests, not batch deduplication
•Use search to reduce candidate set, then match
•Pain is in normalization, matching logic
•DSE + Solr simplifies architecture & adds goodness
More questions?•Feel free to contact me
•http://www.scaleunlimited.com/contact/
•Get training on DSE with Solr
•http://www.datastax.com/what-we-offer/products-services/training