Search SQL:用于全文检索的SQL扩展（一）

一些实现全文检索功能的产品例如Elasticsearch以及旧版本的Transwarp Search，只提供了API的交互接口，用户在检索时必须掌握REST API、query的写法，甚至是只有在对Apache Lucene底层技术比较熟悉的情况下才能写出高效的查询条件，使用成本比较高，运维起来也比较麻烦。

为了解决这一问题，我们实现了Search SQL，支持通过更为通用的数据查询语言SQL使用Transwarp Search，进行全文检索，很大程度上提高了Transwarp Search的易用性，减少应用开发成本。

实现步骤

通过Search SQL实现全文检索的过程分为两步：

• 文本分词

  第一步是为文本指定分词器，Transwarp Search将根据指定分词器对待查询文本进行分词，生成倒排索引，对文本数据进行标准化，使文本分词后的每个单词都可以查询。这一步通过SQL语句在建内表时对列指定分词器实现。

• 查询条件分词

  第二步是进行查询，将查询条件根据与倒排索引相同的分词器分词，以同样的标准进行规范，再与倒排索引相匹配，返回符合条件的记录。这一步中的检索语义通过CONTAINS语法实现。

制定分词器

Search SQL支持用SQL语句在建内表时对列指定分词器，对中文列指定分词器与英文分词列的语法在指定语言类型和分词器时有细微的差别。

建表时指定分词器的语法如下：

CREATE TABLE <tableName> (

    <id> STRING,   

<column> STRING <WITH|APPEND> ANALYZER 'ZH'|'EN' <ANALYZER_NAME>

......

)STORED AS ES  

[WITH SHARD NUMBER <m>]   

[REPLICATION <n>];

加粗部分表示对于对应字段的分词器设置：

• <WITH|APPEND> ：指定分词列的关键字。当该列只需要检索语义时，用WITH关键字；当该列在检索语义的基础上还需要精确查询时，用APPEND关键字。

• 'ZH'|'EN'：用于指明语言类型为中文或是英文，'ZH'表示中文，'EN'表示英文。

• <ANALYZER_NAME>：用于指定分词器，根据前一个参数的不同，该参数有不同可选值。中文支持的分词器有 ik 和 mmseg；英文支持的分词器有standard 和 english。

其中，WITH SHARD NUMBER和REPLICATION分别用于指定分片和副本数量，与本文内容关系不大，因此不在此处详述。

示例

创建表news_analyze_zh，并为其字段content指明为中文分词器ik，分片数10，副本数1，实现的语句如下。

create table news_analyze_zh(

   key1 string,

   content string append analyzer 'ZH' 'ik'  

)stored as ES

with shard number 10

replication 1;

CONTAINS语法

完成建表并对列指定分词器实现被查询文本的标准化后，可通过CONTAINS语法对查询条件进行分词，实现分词检索，具体语法如下：

CONTAINS(

   [schema.]column,

   '<text_query>'   

)

• [schema.]column ：指定查询的分词列。
  

• text_query ：检索表达式，注意必须引用在单引号内''。

示例

若需从表news_analyze_zh的content字段中精确查询含有‘大数据’字样的文本，应该通过如下语句实现：

select * from news_analyze_zh where contains(content, '大数据');

以上为Search SQL中分词检索最基础的用法，为了精简查询结果，实现更丰富的检索语义，CONTAINS语法支持使用NEAR和FUZZY操作符。

NEAR 操作符

NEAR操作符应用于CONTAINS检索语法的基础上，限制所查询单词的间隔宽度，从而提高查询结果的相关性。该操作符的具体使用规则如下：

CONTAINS(<column>, 'NEAR((token1, token2[,token3,...]), slop[, in_order])');

NEAR语法涉及3个相关参数：

• token：表示查询所匹配的词，可以提供多个。注意，token所代表的单词不允许被分词，且必须为分词后的倒排索引中存在的单词，否则查询无意义。

• slop：表示两个token之间最多允许间隔多少其他token。

• in_order：可选项，表示是否按顺序依次匹配token，类型为boolean。默认值为false，表示不按照顺序匹配，即token1可以出现在token2之后；若为true，将不会返回token1出现在token2之后的结果。

示例

假设目前需要查询带有‘大数据’和‘应用’字样的文本，二者之间最多允许相隔一个单词，而且必须按照顺序出现，应该通过如下语句实现：

select * from news_analyze_zh where contains(content, 'near((大数据,应用),1,false)');

FUZZY 操作符

FUZZY 操作符是针对短语搜索设计，同样是在基础分词检索的基础上，查询相似的短语，做模糊查询。该操作符的具体使用规则如下：

CONTAINS(<column>, 'fuzzy(phrase, fuzziness)')

共包含2个相关参数：

• phrase：先经过指定分词器分词，得到多个token，查询结果包含分词后的所有token。

• fuzziness：最大编辑距离（Levenshtein距离）。目标内容中所有与短语相应的token，在经过若干次编辑操作（删除、相邻交换）后，其相对位置与将phrase 进行分词后所有token的相对位置一致。每进行一次编辑操作编辑距离都会增加1，最终将返回编辑距离未超过fuzziness的结果。

fuzzy 操作符的具体执行过程如下图：

示例

假设目前需要查询带有‘大数据应用’字样的短语以及‘大数据...应用’、‘应用...大数据’等类似字样的文本，可以通过如下语句实现：

select * from news_analyze_zh where contains(content, 'fuzzy(大数据应用,5)');

优势总结

当从大量的文本数据查询短语或单词时，标准SQL只能通过形如like %word%的语句来实现查询，然而like操作符计算量较大，处理速度较慢，且SQL只能对检索提供较为单一的语义。相较于传统的模糊查询，使用Search SQL进行检索不仅可以提升查询性能，还定义了多种模糊查询的语法。

• 查询性能更优

  like %word% 的算法复杂度是O(n)，然而分词查询的复杂度只有 O(log(n)) ，其查询性能优于前者。

• 检索语义更丰富

  like %word%只提供一种单一的语义，无法表达对指定间隔内的两个短语的查询。相反，Search SQL检索语义在此方面定义了丰富的语法，通过NEAR和FUZZY操作符描述不同场景下的检索语义。

在下一篇文章中，我们将通过具体的示例介绍本文介绍语法的实现效果。

---

往期原创文章

大数据开放实验室由星环信息科技（上海）有限公司运营，专门致力于大数据技术的研究和传播。若转载请在文章开头明显注明“文章来源于微信订阅号——大数据开放实验室”，并保留作者和账号介绍。