RapidFuzz for DuckDB 2.0: best-match extraction and Unicode-aware scores

The RapidFuzz extension brings the RapidFuzz C++ library to DuckDB. Its DuckDB 2.0 release goes from 37 functions to 57 and changes two things existing queries will notice, covered under Upgrading.

Find the best match

rapidfuzz_extract_one returns the best candidate from a list, like Python’s rapidfuzz.process.extractOne:

SELECT rapidfuzz_extract_one('cowboys',
    ['New York Jets', 'New York Giants', 'Dallas Cowboys']) AS best;
-- {'choice': Dallas Cowboys, 'score': 83.07692307692308, 'choice_index': 3}

rapidfuzz_extract returns the top top_k matches (default 5, NULL for all). Both rank by WRatio unless you pass another scorer, such as 'token_sort_ratio' or 'jaro_winkler_normalized_similarity'.

To match a table against a reference list, pass the list as a subquery:

CREATE TABLE suppliers(name) AS VALUES
    ('Acme Corporation'), ('Globex Corporation'), ('Initech'),
    ('Umbrella Corporation'), ('Wayne Enterprises');

CREATE TABLE invoices(vendor) AS VALUES
    ('ACME corp.'), ('Globex Corporation Inc'), ('initech'),
    ('Umbrela Corp'), ('Stark Industries');

SELECT vendor,
       rapidfuzz_extract_one(vendor, (SELECT list(name) FROM suppliers)).choice AS best_guess
FROM invoices;

Two are wrong. Matching is case-sensitive, so ACME corp. lost to Globex, and Stark Industries has no supplier but still got the closest one. Lowercase both sides, set score_cutoff, and use choice_index to recover the original spelling:

WITH choices AS (
    SELECT list(lower(name) ORDER BY name) AS keys,
           list(name ORDER BY name) AS names
    FROM suppliers
)
SELECT vendor,
       names[m.choice_index] AS supplier,
       round(m.score, 1) AS score
FROM (
    SELECT vendor, names,
           rapidfuzz_extract_one(lower(vendor), keys, score_cutoff := 80) AS m
    FROM invoices, choices
);

NULL choices are skipped but keep their position, so choice_index stays correct. Ties keep list order.

Count characters, not bytes

Previous releases scored UTF-8 bytes, so é counted as two characters and 日本語 as nine. Non-ASCII strings are now decoded to code points first; ASCII strings are unaffected.

SELECT rapidfuzz_indel_distance('café', 'cafe') AS indel,
       rapidfuzz_osa_distance('café', 'cafe') AS osa,
       rapidfuzz_hamming_distance('日本語', '日本人') AS hamming,
       rapidfuzz_ratio('naïve café', 'naive cafe') AS ratio;

The new values match Python’s rapidfuzz, so thresholds tuned in a notebook carry over.

Options

Every scorer takes score_cutoff. Besides filtering, it lets RapidFuzz stop early on pairs that can’t qualify. Distances above the cutoff return cutoff + 1; similarities below it return 0:

SELECT vendor FROM invoices
WHERE rapidfuzz_levenshtein_distance(lower(vendor), 'initech', score_cutoff := 2) <= 2;

Options are keyword-only and must be constants:

With replace_cost := 2, kitten is 5 from sitting instead of 3. With unequal insert and delete costs, argument order matters.

See where strings differ

rapidfuzz_levenshtein_opcodes returns the edits as ranges:

SELECT op.operation,
       substr('kitten', op.s1_start + 1, op.s1_end - op.s1_start) AS from_text,
       substr('sitting', op.s2_start + 1, op.s2_end - op.s2_start) AS to_text
FROM (SELECT unnest(rapidfuzz_levenshtein_opcodes('kitten', 'sitting')) AS op);

rapidfuzz_levenshtein_editops lists one entry per edit, and Indel versions of both exist. Positions are 0-based character offsets, as in Python.

rapidfuzz_partial_ratio_alignment finds where a short string best fits in a longer one:

SELECT substr(note, a.s1_start + 1, a.s1_end - a.s1_start) AS matched,
       round(a.score, 1) AS score
FROM (
    SELECT 'Order #4471: shipped via Federal Expres' AS note,
           rapidfuzz_partial_ratio_alignment(note, 'Federal Express') AS a
);
-- Federal Expres, 96.6

New metrics

• Levenshtein, with the edit costs above. Earlier releases didn’t have it.
• Damerau-Levenshtein, which counts an adjacent swap as one edit: recieve is 1 from receive, not 2.
• Ratios: rapidfuzz_wratio, rapidfuzz_qratio, rapidfuzz_token_ratio, rapidfuzz_partial_token_ratio, rapidfuzz_partial_token_sort_ratio.

Every function now has a description and example in duckdb_functions().

Faster against a constant

When one argument is constant, the extension now uses RapidFuzz’s cached scorers, which preprocess that string once instead of per row. Strings are also no longer copied before scoring.

sum(f(s, 'customer 424242 smith jones')) over 5 million strings, one thread on an Apple M3, best of three:

DuckDB’s built-in levenshtein and jaccard ran within 5% on both DuckDB versions, so the gain is the extension’s. On the same data, rapidfuzz_levenshtein_distance took 0.42 s against 4.3 s for the built-in levenshtein, with identical results.

Upgrading

• Non-ASCII scores change. Expect lower distances and higher similarities for accented, non-Latin or emoji text. Recheck thresholds tuned on old values.
• Integer metrics return BIGINT. _distance and _similarity for Levenshtein, Damerau-Levenshtein, OSA, Indel, LCSseq, Hamming, Prefix and Postfix were DOUBLE. Normalized scores, Jaro, Jaro-Winkler and ratios are still DOUBLE.

rapidfuzz-cpp is pinned to 3.3.4, which fixes WRatio when one string is exactly eight times longer than the other and an out-of-bounds read in edit-operation handling. See the changelog for everything else.

Try it

Install from the Query Farm extension repository:

SET allow_extension_repositories = 'allowed';

CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
    WITH PREFIX 'https://duckdb-extensions.query.farm';

INSTALL rapidfuzz FROM query_farm;
LOAD rapidfuzz FROM query_farm;

Already installed? Use FORCE INSTALL rapidfuzz FROM query_farm in a fresh session. If a score differs from Python’s rapidfuzz for the same strings, open an issue.