使用SQL，用CockroachDB进行模糊匹配的实验

今天你吸猫了么
293 阅读6分钟

最近的一条推文启发了我，通过结合CockroachDB的一些现有能力来解决模糊匹配的需求。请注意推文中提到的关键功能。

  • 相似但不相等的体育赛事名称：一个常见的模式。用户往往会在输入字段中打错数据，而且数据并不总是正确的。尽管如此，我们还是希望能够返回最接近的匹配。
  • 我宁愿使用这个内置的功能，而不是支付整个ES集群的费用，并增加维护的开销。这是我在过去几个月里第二次听到这种看法。ES是一个全功能的搜索引擎，提供了一个很好的体验，但是，对于这个目的来说，将是过度的，需要额外的时间和费用来部署和操作。

我发现这个练习的有趣之处在于，这种将事件通过外部系统路由到数据库的配置模式具有巨大的潜力，远远超过你将在下面看到的模糊匹配的例子。似乎开发实践已经从把逻辑埋在数据库中发展到用最适用于问题的语言来编码；微服务的出现与这一趋势相一致。这一观察，再加上能够在特定的列族上定义一个变化反馈的新功能，让我相信我们会在CockroachDB上看到这种模式的一些非常有趣的应用。

现在，我将离开我的演讲台，进入演示环节。

Fuzzy matching twitter question

演示背景

首先，CockroachDB不是PostgreSQL的分支，所以你不能简单地 "栓 "上Postgres的常规扩展，比如pg_trgm ，也就是推文中提到的模块。但这只是一个小障碍，事实上，它提供了一个机会来展示CockroachDBEnterprise Changefeeds（又称 "CDC"）的一个新功能：在特定的列族上配置一个changefeed的能力。这个功能在我使用的v22.1.0-beta.1 ，这个版本应该在2022年5月中旬就能普遍使用。

模糊匹配实验

希望的状态

我们对有关运动队的数据进行INSERT、UPDATE、DELETE，并希望根据我们可能拼错名字的查询来检索这些数据。我承认我误读了这条推文，把它理解为与运动队的名字有关，而不是与体育赛事的名字有关，所以我在这里展示的是使用队名数据，但我认为它也很容易适用于赛事名称，只要你能获得这些数据。

如果我们设想需求是由应用程序需要执行这种类型的匹配而产生的，我们可以建立一个简单的REST应用程序，它将。

  1. 提供一个webhook端点，让CockroachDB的变化feeds能够发送事件。
  2. 提供一个用于搜索的REST端点，返回最接近的前N个队名匹配的排名列表。

表的DDL

这是该表的DDL。FAMILY...部分使changefeed只根据FAMILY f1 ，忽略由此产生的对FAMILY f2 的变化，这正是我们需要的。

CREATE TABLE teams
(
  id UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID()
  , name TEXT NOT NULL
  , grams TEXT[]
  , FAMILY f1 (id, name)
  , FAMILY f2 (grams)
);

这里是grams 列上的倒置索引。

CREATE INDEX ON teams USING GIN (grams);

REST应用程序。CDC/索引

现在，我们需要REST应用程序。我使用Python来做这个，因为它很简洁，很容易启动和运行，而且Flask模块对REST应用非常好。而且，我有一些代码片段，我可以很容易地重复使用。整个Python脚本在这里，但我将专注于以下有趣的方面。

  • 从字符串生成n-grams。
def get_ngrams(s, n=3):
  return [s[i:i+n] for i in range(len(s) - n+1)]

对于输入la galaxy ，返回值是['la ', 'a g', ' ga', 'gal', 'ala', 'lax', 'axy'] 。n 的默认值是3，这与pg_trgm 的工作方式相一致。

有几件事需要指出。

  1. 文本是由一个单独的Python函数小写的。这在信息检索应用中很常见，因为比较通常是不考虑大小写的。
  2. ngrams显示了从左到右在字符串中滑动窗口的结果，产生了3个字符的序列，可以跨越单词之间的空间。后者是相邻单词的因素，所以短语的评分是适当的。
  • 变化反馈的Webhook端点。
@app.route('/cdc', methods = ['POST', 'GET'])
def cdc_webhook():
  obj = request.get_json(force=True)
  print("CDC: " + json.dumps(obj)) # DEBUG
  for o in obj["payload"]:
    if o["after"] is None:
      pass # Nothing to be done here
    else:
      pk = o["after"]["id"]
      name = o["after"]["name"]
      index_string(pk, name)
  return "OK", 200

Changefeed将发送一个HTTP POST到这个/cdc 端点，通过下面的SQL语句进行配置。当这种情况发生时，JSON可以通过调用request.get_json(force=True) ，该JSON包含id 和name 列的当前值，从teams 表。

  • 这些值被传递给index_string(pk, name) 函数，该函数只是更新teams 表的grams 列。
def index_string(pk, content):
  ng = tokenize(content)
  stmt = text("UPDATE teams SET grams = :grams WHERE id = :pk").bindparams(grams=ng, pk=pk)
  run_statement(stmt)

我不会在这里讨论run_statement(stmt) 函数，而是让感兴趣的读者参考代码本身。

  • 这里是我们需要运行的SQL语句，以配置changefeed，将所有这些联系起来。
CREATE CHANGEFEED FOR TABLE teams FAMILY "f1"
INTO 'webhook-https://localhost:18080/cdc?insecure_tls_skip_verify=true'
WITH updated, full_table_name, topic_in_value;

请注意，这个操作需要企业许可证，但在单节点的 "演示 "模式下也可以使用（见下文）。

REST应用程序：搜索/模糊匹配

REST客户端可以通过与此相当的操作来检索给定团队名称的模糊匹配（这个例子是在我的MacBook上运行的；base64 命令在这里与在Linux上的工作方式不同；pretty_print_json.py ）。

$ name="PA Galuxy"; time curl -k -s https://localhost:18080/search/$( echo -n $name | base64 )/5 | pretty_print_json.py
[
  {
    "name": "LA Galaxy",
    "pk": "15e240a7-d1db-4b77-b454-c895a11610bf",
    "score": "42.8571"
  },
  {
    "name": "LA Galaxy II",
    "pk": "85b5b97a-9a6e-4cef-b63c-7cbe123eca07",
    "score": "10.7143"
  },
  {
    "name": "LA Giltinis",
    "pk": "82b96763-6836-4ab8-84ad-1864a1f3e16d",
    "score": "4.7619"
  },
  {
    "name": "Tampa Mayhem",
    "pk": "6f5fd5e0-6654-4385-9bd0-c191f4f1c5b4",
    "score": "3.5714"
  },
  {
    "name": "Tampa Tarpons",
    "pk": "1851c8e9-77e8-456b-a197-6aaed971942a",
    "score": "2.8571"
  }
]

real 0m0.196s
user 0m0.063s
sys 0m0.043s

在进入/search 端点的Python代码之前，以下内容值得一提。

  1. 我故意把球队的名字拼错了；"LA Galaxy "才是我想要的名字。
  2. 尽管初始字符，即 "PA Galuxy "中的 "P "不正确，但结果还是正确的。这就是基于n-gram的匹配的基本特征之一--你不依赖于字符串中的前导字符来匹配。

接下来是这个交互的Python部分。

@app.route("/search//")
def do_search(q_base_64, limit):
  query_str = decode(q_base_64)
  logging.info("Query: {}".format(query_str))
  ng = tokenize(query_str)
  logging.info("Query (n-grams): {}".format(ng))
  sql = """
  WITH qbool AS
  (
    SELECT id, grams, 1 + ABS(ARRAY_LENGTH(grams, 1) - ARRAY_LENGTH(CAST(:ngrams AS TEXT[]), 1)) delta
    FROM teams
    WHERE grams && CAST(:ngrams AS TEXT[])
  ), qscore AS
  (
    SELECT id, COUNT(*) n FROM
    (
      SELECT id, UNNEST(grams) FROM qbool
      INTERSECT
      SELECT id, UNNEST(CAST(:ngrams AS TEXT[])) FROM qbool
    )
    GROUP BY id
  )
  SELECT qbool.id, t.name, 100*n/delta score
  FROM qbool, qscore, teams t
  WHERE qbool.id = qscore.id AND t.id = qbool.id
  ORDER BY score DESC
  LIMIT :max_rows;
  """
  stmt = text(sql).bindparams(ngrams=ng, max_rows=limit)
  rv = []
  for row in run_statement(stmt, True, False):
    pk = str(row[0])
    name = str(row[1])
    score = float(row[2]/len(ng))
    d = {}
    (d["pk"], d["name"], d["score"]) = (pk, name, '{:.4f}'.format(score))
    rv.append(d)
  return Response(json.dumps(rv), status=200, mimetype="application/json")

这里有相当多的事情发生，主要是在SQL表达式中。我将尽我所能来叙述。

  • 有两个常见的表表达式（CTE），它们处理不同的方面
  • qbool 处理布尔性质：这条记录是否匹配？它还提供了一个额外的评分输入，即提供的查询字符串的长度与 列中的实际值的差异。例如，这被用来提高包含 "LA Galaxy "的行与包含 "LA Galaxy II "的行的分数。grams
  • 查询谓词，WHERE grams && CAST(:ngrams AS TEXT[]) ，包含了&& （数组重叠）操作。理想情况下，这个操作会使用GIN索引；这个拉动请求解决了这个问题，已经被合并，所以目前可以在主网上使用。它将被包含在未来的版本中--敬请关注。
  • qscore 在这个过程中， ，根据匹配行和查询之间重叠的n-grams的数量来确定一个分数。qbool
  • 最后，这些CTE的结果与teams 表中的name 列相结合，生成一个有序的结果，以JSON格式返回给客户端（见上面的例子）。

如何用CockroachDB进行模糊匹配

下面是一些关于如何复制我上面展示的内容的说明。鉴于我已经做了这些，我可能会遗漏一些步骤，所以请告诉我（GitHub问题可能是最简单的方法）。我将使用我刚刚在谷歌云平台上部署的Ubuntu虚拟机来说明这个问题。

  • 第一步：安装一些前提条件（我喜欢使用psql CLI）。

sudo apt update
sudo apt install postgresql-client-common -y
sudo apt install postgresql-client -y
sudo apt install python3-pip -y
sudo apt install libpq-dev -y
pip3 install -r requirements.txt

  • 第2步：部署最新的CockroachDB二进制文件（我将使用测试版，因为22.1还没有发货）。

$ curl https://binaries.cockroachdb.com/cockroach-v22.1.0-beta.1.linux-amd64.tgz | tar xzvf -

  • 第3步：启动 "演示 "模式（保持这个终端对CLI的开放）。

$ ./cockroach-v22.1.0-beta.1.linux-amd64/cockroach demo

在输出中，你会看到像下面这样的一行，你会在一些情况下使用它。

#     (sql)      
postgresql://demo:demo15932@127.0.0.1:26257/movr?sslmode=require

  • 第4步：在一个新的shell中，克隆这个GitHub repo。

$ git clone https://github.com/mgoddard/hot-fuzz.git

  • 第5步：把这个 repo 作为你当前的工作目录（虚拟机的主机名也是hot-fuzz ）。

$ cd hot-fuzz/
$ ls
LICENSE  README.md  images  prep_teams_data.pl  pretty_print_json.py  trigrams.py

  • 第6步：启动Python Flask REST应用程序。

$ export DB_CONN_STR="postgresql://demo:demo15932@127.0.0.1:26257/movr?sslmode=require"
$ ./trigrams.py

  • 第7步：回到使用CLI的终端，运行DDL，启用，然后设置changefeed。

demo@127.0.0.1:26257/movr> CREATE TABLE teams
(
  id UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID()
  , name TEXT NOT NULL
  , grams TEXT[]
  , FAMILY f1 (id, name)
  , FAMILY f2 (grams)
);
CREATE INDEX ON teams USING GIN (grams);

demo@127.0.0.1:26257/movr> SET CLUSTER SETTING kv.rangefeed.enabled = TRUE;

demo@127.0.0.1:26257/movr> CREATE CHANGEFEED FOR TABLE teams family "f1"
INTO 'webhook-https://localhost:18080/cdc?insecure_tls_skip_verify=true'
WITH updated, full_table_name, topic_in_value;

  • 第8步：在第三个终端，加载数据（Perl脚本包含在这个 repo中）。

$ cd hot-fuzz/
$ DB_CONN_STR="postgresql://demo:demo15932@127.0.0.1:26257/movr?sslmode=require"
$ curl -s https://en.wikipedia.org/wiki/List_of_professional_sports_teams_in_the_United_States_and_Canada | ./prep_teams_data.pl | psql $DB_CONN_STR

  • 第9步：最后，试试那个/搜索终端。

$ name="PA Galuxy"; time curl -k -s https://localhost:18080/search/$( echo -n $name | base64 )/5 | ./pretty_print_json.py
[
  {
    "name": "LA Galaxy",
    "pk": "73b541c1-e3d4-4f1d-8598-bedc11200f03",
    "score": "42.8571"
  },
  {
    "name": "LA Galaxy II",
    "pk": "3a3b0e44-68cc-45da-aa72-9467e46f0458",
    "score": "10.7143"
  },
  {
    "name": "LA Galaxy II",
    "pk": "9303e054-d6ad-4793-b904-9e3eeba28ff9",
    "score": "10.7143"
  },
  {
    "name": "LA Giltinis",
    "pk": "9b8bfbb4-1ac6-477d-aaed-79daf1183f02",
    "score": "4.7619"
  },
  {
    "name": "Tampa Mayhem",
    "pk": "f566f12f-7f8d-4ae6-b04b-832a5f9025c5",
    "score": "3.5714"
  }
]

real 0m0.049s
user 0m0.052s
sys 0m0.008s

如果你想

鸣谢

作者希望感谢以下人士提供的宝贵意见，使这篇博文成为可能。

  • 丹-凯利（Dan Kelly），感谢他在推特上提到激发了关注n-grams的活动。
  • @schrepfler冯小刚，在关于n-grams和模糊匹配的推文中标记了@CockroachDB 。
  • Aaron Zinger，为即将到来的对列族的changefeed支持提供了提示。
  • Rebecca Taft，负责推动&&操作符的索引加速的PR。
  • Rajiv Sharma，感谢他提供了该PR，并在昨天对其进行了最后的润色，使其能够被合并。
  • Jordan Lewis，负责CockroachDB的核心部分的三叉戟工作。

参考文献

avatar
文章
阅读
粉丝