SQLite Linker - OperationalError: no such function: levenshtein

[zhyzhkea]

Hi,

I'm going through the demo notebook but using SQLite linker vs. DuckDB. According to documentation, SQLite backend supports distance_function_at_thresholds, exact_match, and levenshtein_at_thresholds. My attempt to use this setting:

from splink.sqlite.linker import SQLiteLinker
import splink.sqlite.comparison_library as cl
import sqlite3
conn = sqlite3.connect(":memory:")

settings = {
    "link_type": "dedupe_only",
    "unique_id_column_name": "source_customer_id",
    "comparisons": [
        cl.levenshtein_at_thresholds("first_name",[1,2]),
        cl.levenshtein_at_thresholds("last_name",[1,2]),
        #cl.distance_function_at_thresholds("last_name",
        #                                    distance_function_name = 'jaccard',
        #                                    distance_threshold_or_thresholds = [0.9, 0.7]
        #                                  ),
        cl.exact_match("company_name", term_frequency_adjustments=True),
        cl.exact_match("email_address"),
        cl.exact_match("phone"), 
    ],
    "blocking_rules_to_generate_predictions": [
        "l.first_name = r.first_name and l.last_name = r.last_name",
        "l.company_name = r.company_name and l.email_address = r.email_address",
        "l.last_name = r.last_name and l.phone = r.phone" 
    ],
    "retain_matching_columns": True,
    "retain_intermediate_calculation_columns": True,
}

linker = SQLiteLinker(df, connection = conn, settings_dict = settings)
.....
.....
import logging
logging.getLogger("splink").setLevel(logging.DEBUG)
linker.estimate_u_using_random_sampling(max_pairs=1e6)

fails with the error message: SQLite Linker - OperationalError: no such function: levenshtein.

Attempt to use "distance_function_at_thresholds" comparison fails with: AttributeError: 'distance_function_at_thresholds' object has no attribute '_distance_level' at linker initialization.

Please let me know if I'm doing something wrong.

Thanks

[RobinL]

Sorry - this isn't well documented, but sqlite doesn't include the levenstein function so you have to register it:

from rapidfuzz.distance.Levenshtein import distance
conn = sqlite3.connect(":memory:")
conn.create_function("levenshtein", 2, distance)

[zhyzhkea]

Hi Robin,

Thanks for the prompt response. This code fixed the issue with Levenshtein function. Is there any way to set distance_function_at_thresholds to work in SQLite?

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[1], line 16
      7 conn = sqlite3.connect(":memory:")
      8 conn.create_function("levenshtein", 2, distance)
     10 settings = {
     11     "link_type": "dedupe_only",
     12     "unique_id_column_name": "source_customer_id",
     13     "comparisons": [
     14         cl.levenshtein_at_thresholds("firs_tname",[1,2]),
     15         #cl.levenshtein_at_thresholds("last_name",[1,2]),
---> 16         cl.distance_function_at_thresholds("last_name",
     17                                             distance_function_name = 'jaccard',
     18                                             distance_threshold_or_thresholds = [0.9, 0.7]
     19                                           ),
     20         cl.exact_match("company_name", term_frequency_adjustments=True),
     21         cl.exact_match("email_address"),
     22         cl.exact_match("phone"), 
     23     ],
     24     "blocking_rules_to_generate_predictions": [
     25         "l.firs_tname = r.firs_tname and l.last_name = r.last_name",
     26         "l.company_name = r.company_name and l.email_address = r.email_address",
     27         "l.last_name = r.last_name and l.phone = r.phone" 
     28     ],
     29     "retain_matching_columns": True,
     30     "retain_intermediate_calculation_columns": True,
     31 }
     33 linker = SQLiteLinker(df, connection = conn, settings_dict = settings)

File ~/miniconda3/envs/splink3/lib/python3.8/site-packages/splink/comparison_library.py:257, in DistanceFunctionAtThresholdsComparisonBase.__init__(self, col_name, distance_function_name, distance_threshold_or_thresholds, regex_extract, valid_string_regex, set_to_lowercase, higher_is_more_similar, include_exact_match_level, term_frequency_adjustments, m_probability_exact_match, m_probability_or_probabilities_thres, m_probability_else)
    248     level = self._exact_match_level(
    249         col_name,
    250         term_frequency_adjustments=term_frequency_adjustments,
   (...)
    253         m_probability=m_probability_exact_match,
    254     )
    255     comparison_levels.append(level)
--> 257 threshold_comparison_levels = distance_threshold_comparison_levels(
    258     self,
    259     col_name,
    260     distance_function_name,
    261     distance_threshold_or_thresholds,
    262     regex_extract,
    263     set_to_lowercase,
    264     m_probability_or_probabilities_thres,
    265 )
    266 comparison_levels = comparison_levels + threshold_comparison_levels
    268 comparison_levels.append(
    269     self._else_level(m_probability=m_probability_else),
    270 )

File ~/miniconda3/envs/splink3/lib/python3.8/site-packages/splink/comparison_library_utils.py:118, in distance_threshold_comparison_levels(self, col_name, distance_function_name, distance_threshold_or_thresholds, regex_extract, set_to_lowercase, higher_is_more_similar, include_colname_in_charts_label, m_probability_or_probabilities_thres)
    115         kwargs["distance_function_name"] = distance_function_name
    116         kwargs["higher_is_more_similar"] = higher_is_more_similar
--> 118     level = self._distance_level(**kwargs)
    119     threshold_comparison_levels.append(level)
    121 return threshold_comparison_levels

**AttributeError: 'distance_function_at_thresholds' object has no attribute '_distance_level'**

[ADBond]

Is there any way to set distance_function_at_thresholds to work in SQLite?

Have just had a quick check of this and it seems there is a bug preventing this from working currently - this should be fixed by the next release.

In the meantime the comparison level distance_function_level should work okay, so for the time being if you wish to use this with some other distance function you can replace for example:

cl.distance_function_at_thresholds("some_column", "custom_distance_function", [1, 3, 4])

with

import splink.sqlite.comparison_level_library as cll
...
# (in your "comparisons" in your settings dictionary:)
{
    "output_column_name": "some_column",
    "comparison_description": "Some column comparison",
    "comparison_levels": [
        cll.null_level("some_column"),
        cll.exact_match("some_column"),
        cll.distance_function_level("some_column", "custom_distance_function", 1),
        cll.distance_function_level("some_column", "custom_distance_function", 3),
        cll.distance_function_level("some_column", "custom_distance_function", 4),
        cll.else_level(),
    ]
}

See this topic guide on defining comparisons for more details on defining comparisons in this way

[ADBond]

Sorry - this isn't well documented, but sqlite doesn't include the levenstein function so you have to register it:

from rapidfuzz.distance.Levenshtein import distance
conn = sqlite3.connect(":memory:")
conn.create_function("levenshtein", 2, distance)

Apologies - this has been on my to-do list to hook this in to the sqlite linker by default (with an opt-out) so that this works out of-the-box in a similar style to the SparkLinker since I added pre-built Levenshtein options for SQLite. Will sort this out for the next release.

[RobinL]

No worries. FWIW, I've also added notes on how to add udfs to sqlite in the docs here, so that's somewhere we can point people to in future for any functions that aren't used in pre-baked comparisons

[zhyzhkea]

Thanks, the custom level definition worked.