RethinkDB does not have a built-in distance metric like Levenshtein edit distance, but it is fairly easy to update rows in place. I found some examples of people attempting to build regular expressions to match specific Levenshtein edit distances, but this does not seem like a high performance approach, especially for finding things more than one character apart.<\/p>\n
To start, you must first install the Levenstein library for Python:<\/p>\n
\npip install python-levenshtein\n<\/pre>\nHaving done that, you can load your database:<\/p>\n
\nimport Levenshtein\n\nimport rethinkdb as r\nr.connect(\"localhost\", 28015).repl()\n\ndb = r.db(\"search\")\ntable = db.table(\"industries\")\n<\/pre>\nOnce you have the table, it’s relatively easy to get a single test row, modify it, and upload it to the database. Remember to add .run() to the end of the query. Once you do this, you can filter to rows that contain the column you’re interested in (in this case, you should get one row back from the last query). The reason that you can update a document you downloaded previously is that RethinkDB automatically pulls down the document’s ID.<\/p>\n
\ndoc = [x for x in table.limit(1).run()]\n\ndoc['Title_Query_Distance'] = \\\n Levenshtein.ratio(doc['search_query'], doc['Title'])\ndoc['Title_Description_Distance'] = \\\n Levenshtein.ratio(doc['search_query'], doc['Description'])\n\ntable.update(doc).run()\n\n[x for x in table.filter('Title_Query_Distance').run()]\n<\/pre>\nHaving done all this, you can run this query for each record in the database (if you’re splitting the work up, you may wish to do this only on rows with the value not populated):<\/p>\n
\ndef updateDoc(table, doc):\n doc['Title_Query_Distance'] = \\\n Levenshtein.ratio(doc['search_query'], doc['Title'])\n doc['Title_Description_Distance'] = \\\n Levenshtein.ratio(doc['search_query'], doc['Description'])\n table.update(doc).run()\n\n# this query takes about ten seconds on a 31k row table, with no indexes\ntoUpdate = \\\n [doc for doc in \\\n table.filter(r.row.has_fields(\"Title_Query_Distance\").not_()).run()]\n\n# this query can take hours (30k row table)\n[updateDoc(table, doc) for doc in toUpdate]\n<\/pre>\nOnce we’ve done this, it’s a good practice to do some diagnostic queries, to verify everything updated as expected (these two should return matching counts)<\/p>\n
\ntable.filter(lambda x: x['Title_Query_Distance']).count().run()\ntable.count().run()\n<\/pre>\n","protected":false},"excerpt":{"rendered":"RethinkDB does not have a built-in distance metric like Levenshtein edit distance, but it is fairly easy to update rows in place. I found some examples of people attempting to build regular expressions to match specific Levenshtein edit distances, but this does not seem like a high performance approach, especially for finding things more than … <\/p>\n