随着数字时代的发展，数据的来源和生成方式越来越广泛，其中也包含大量的文本数据。我们通常选择使用数据库/数据仓库存储这些数据，但是将其中的文本数据中有价值的信息提取出来并进行高效分析，往往需要涉及多个数据处理系统配合来实现，用户的使用门槛通常较高、维护成本较大。

本文从阿里云用户使用云原生数据仓库AnalyticDB PostgreSQL版（以下简称ADB PG）的实际体验出发，介绍ADB PG如何实现“一站式全文检索”业务，并详细阐述ADB PG使用的优势技术，最后提供对应业务案例分析。

一站式全文检索业务痛点

通常在我们在使用数据仓库进行文本数据的加工和分析业务时，离不开数据仓库的数据实时写入、全文检索及任务调度等能力。当时如何使用一套数仓系统完成上述所有功能，往往面临以下几个挑战：

• 如何使用全文检索功能，数据仓库内核的全文检索功能足够全面吗？部分数据仓库在全文检索功能上的的缺失，导致用户往往需要对文本数据做大量开发后才能将数据导入数据仓库；
• 如何调度大批量的全文检索、文本数据加工任务？任务调度依赖数据仓库内核的SQL标准支持能力，以及强大的外部工具支持；
• 如何保证全文检索的性能？全文检索涉及大量的文本数据，而数据仓库在处理文本数据时性能往往不如数字类型的数据；
• 是否具有灵活的配置变更能力等。

ADB PG同时具备完善的全文检索和数据加工能力，能够较好地解决上述问题。下图展示了ADB PG一站式全文检索业务的流程。后文我们也将分享其中的重点技术。

全文检索

全文检索一般是指数据库将自然语言文本转换为可被查询数据的能力。例如，在数据库中存储的文本中找到特定的查询词并将它们按照出现的次数排序，就是一种典型的全文检索应用。

大部分数据库都提供对文本查询的基本功能，我们可以在查询中使用LIKE等表达式查找搜索文本，但是这些方法在现代数据库业务中缺少很多必要的功能：

• 数据库常用的表达式查询方法无法处理派生词等语法，例如英文单词satisfy和它的第三人称形式satisfies。我们如果使用satisfy作为关键词查询，查询结果可能遗漏satisfies，这不是全文检索所期望看到的结果。当然我们可以使用表达式OR去同时匹配satisfy和satisfies，但是这样做非常低效率且容易出错（某些单词存在大量的派生词）；
• 无法对根据匹配结果进行有效地排序，当查询结果较多时，筛选结果将变得非常低效；
• 查询性能慢，无法建立有效的索引，导致查询需要遍历完整的文本数据。

接下来我们介绍ADB PG如何实现全文检索能力。

全文检索的基本功能

ADB PG使用PostgresSQL内核，提供完善的全文检索（Full Text Search）功能，通过文本的预计算提供快速的查询性能。其中预计算主要包含以下几步：

1. 将文本解析为符号。通过符号将文本词语分类为不同的类型，例如数字、形容词、副词等，不同类型的符号可以做不同的操作处理。PG内核使用默认的parser（解析器）进行符号解析，并提供自定义parser的能力用于解析不同语言文本；
2. 将符号转换为词语。相比较符号，词语经过了归一化（normalized）操作，将单词的不同形式进行了合并（例如上文提到的单词satisfy和satisfies），让全文检索功能可以根据语义高效检索。PG内核使用dictionaries（词典）进行这一步工作，同样提供了自定义dictionaries功能；
3. 优化词语存储，高效查询。例如，PG内核提供tsvector(text search vector)数据类型，将文本解析转换为带有词语信息的有序数据，并通过tsquery(text search query)语法对这类数据进行查询，实现高效的全文检索。

tsvector

tsvector用于存放一系列去重（distinct）的词语和它们的顺序、位置等信息，使用PG提供的to_tsvector方法可以自动完成文本至tsvector的转换。我们以英文语句’a fat cat jumped on a mat and ate two fat rats’为例：

postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats');
                          to_tsvector
---------------------------------------------------------------
 'ate':9 'cat':3 'fat':2,11 'jump':4 'mat':7 'rat':12 'two':10
(1 row)

可以看到tsvector的结果包含了一系列的词语，并按照单词的顺序进行了排序；同时每个单词后面跟随了其在语句中的位置信息，如 ‘fat’:2,11 表示 ‘fat’ 在语句的第2和第11个位置；此外tsvector结果省略了连接词（and, on），并对部分单词进行了归一化的处理（jumped过去式归一化为jump）。

tsvector将文本完成了预计算和转换，接下来我们需要tsquery来查询分析tsvector。

tsquery

tsquery用于存放查询tsvector的词语，PG同样提供to_tsquery方法将文本转换为tsquery，结合tsvector及全文检索操作符，就可以完成全文检索查询。

例如，我们可以使用 @@ 操作符查找tsvector中是否包含tsquery的词语：

postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cat');
 ?column?
----------
 t
(1 row)
postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cats');
 ?column?
----------
 t
(1 row)

从查询结果中可以看到，示例语句对词语’cat’的查询结果为t，即true表明查询匹配；同时对词语’cats’的查询结果也为t，因为’cats’是’cat’的复数，语言语义上也满足匹配。

tsquery支持Boolean操作符 & (AND)、| (OR) 和 ! (NOT)，可以方便地构建组合条件的检索查询：

postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cat | dog');
 ?column?
----------
 t
(1 row)
postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cat & dog');
 ?column?
----------
 f
(1 row)

距离搜索

使用全文检索时，我们不仅仅想查找文本中是否包含某个词，还希望在短语、词组的基础上做进一步分析。PG的全文检索tsquery方法，支持短语搜索符，其中N为整数，表示指定词语之间的距离。例如我们想要查找文本是否存在’cat’后跟随’jump’的短语，则可以使用<1>操作符查找：

postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cat<1>jump');
 ?column?
----------
 t
(1 row)

进一步，查找特定的词语组合，也可以用距离搜索方法实现：

postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cat<2>mat');
 ?column?
----------
 f
(1 row)
postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cat<4>mat');
 ?column?
----------
 t
(1 row)

ADB PG在PG全文检索功能基础上，结合社区能力对全文检索进行了深度开发，进一步支持了全文检索-范围距离搜索符，其中M同N，表示词语之间距离，指定词语之间距离在N至M之间的范围内。例如查找文本中是否含有’cat’和’mat’间距离小于等于5的短语，则可以使用<1,5>搜索：

postgres=# select to_tsvector('a fat cat jumped on a mat and ate two fat rats') @@ to_tsquery('cat<1,5>mat');
 ?column?
----------
 t
(1 row)

中文全文检索功能

在部分数据库业务中，通常会存放大量中文文本信息，例如用户的评价表单、地址信息等。对中文文本的分析同样需要全文检索功能来实现。但是在中文中词语是最小语素单位，书写时并不像英语会在词之间用空格分开，这就导致如果使用PG的默认全文检索引擎，难以得到符合中文语义的分词结果。例如使用PG tsvector默认方法分词中文语句，得到的结果明显无法满足分词需求：

postgres=# select to_tsvector('你好，这是一条中文测试文本');
            to_tsvector
-----------------------------------
 '你好':1 '这是一条中文测试文本':2
(1 row)

SCWS(Simple Chinese Word Segmentation，简易中文分词系统)，是一套基于词频词典的开源中文分词引擎，它能将一整段的中文文本基本正确地切分成词。SCWS使用C语言开发，可以直接作为动态链接库接入应用程序，结合PG良好的代码扩展能力，我们可以在PG中使用SCWS实现中文分词功能。

中文分词：zhparser插件

zhparser插件是一个基于SCWS能力开发的PG中文分词插件，在兼容PG已有全文检索能力的基础上，提供丰富的功能配置选项，同时也提供用户自定义词典功能。

在ADB PG中，zhparser插件已完成默认安装，用户可以根据中文分词需求自定义配置zhparser，例如创建一个名为”zh_cn”的中文分词解析器并配置分词策略：

--- 创建分词解析器
CREATE TEXT SEARCH CONFIGURATION zh_cn (PARSER = zhparser);
--- 添加名词（n）、动词（v）、形容词（a）、成语（i）、叹词（e）和习用语（l）、自定义（x）分词策略
ALTER TEXT SEARCH CONFIGURATION zh_cn ADD MAPPING FOR n,v,a,i,e,l,x WITH simple;

更多的插件使用方法可以参考：https://help.aliyun.com/document_detail/209458.html

完成基本配置后，我们可以使用中文分词能力开发中文检索业务，使用示例如下：

postgres=# select to_tsvector('zh_cn','你好，这是一条中文测试文本');
                 to_tsvector
----------------------------------------------
 '中文':3 '你好':1 '文本':5 '测试':4 '这是':2
(1 row)

同样，我们也可以使用tsquery结合zhparser进行文本搜索：

postgres=# select to_tsvector('zh_cn','你好，这是一条中文测试文本') @@ to_tsquery('zh_cn','中文<1,3>文本');
 ?column?
----------
 t
(1 row)

自定义词库

zhparser提供中文自定义词库功能，如果默认的词库满足不了分词的结果需求，可以更新自定义词库并实时优化查询结果。zhparser的系统表zhparser.zhprs_custom_word是一个面向用户的自定义词典表，用户只需要更新该系统表即可实现自定义词语。zhparser.zhprs_custom_word的表结构如下：

Table "zhparser.zhprs_custom_word"
 Column |       Type       | Collation | Nullable |        Default
--------+------------------+-----------+----------+-----------------------
 word   | text             |           | not null |
 tf     | double precision |           |          | '1'::double precision
 idf    | double precision |           |          | '1'::double precision
 attr   | character(1)     |           |          | '@'::bpchar
Indexes:
    "zhprs_custom_word_pkey" PRIMARY KEY, btree (word)
Check constraints:
    "zhprs_custom_word_attr_check" CHECK (attr = '@'::bpchar OR attr = '!'::bpchar)

其中word列为自定义词语；tf、idf列用于设置自定义词语权重，参考TF-IDF(term frequency–inverse document frequency，词频-逆文本频率指数)；attr列为自定义词语的分词/停止词属性。

在ADB PG中，自定义词库是数据库级别的，存放于每个数据节点对应数据库的数据目录下。下面展示如何在ADB PG中使用自定义词库功能。

以前文示例语句’你好，这是一条中文测试文本’为例，如果我们期望’测试’和’文本’不要拆分为两个词语，而是以’测试文本’作为一个单独的分词，那么只需要在zhparser.zhprs_custom_word系统表中插入对应分词，重载后即可生效：

postgres=# insert into zhparser.zhprs_custom_word values('测试文本');
INSERT 0 1
postgres=# select sync_zhprs_custom_word();     --加载自定义分词
 sync_zhprs_custom_word
------------------------
(1 row)
postgres=# q --重新建立连接
postgres=# select to_tsvector('zh_cn','你好，这是一条中文测试文本');
               to_tsvector
-----------------------------------------
 '中文':3 '你好':1 '测试文本':4 '这是':2
(1 row)

自定义词库也支持停止词功能，例如我们不希望词语’这是’单独作为一个分词，同样可以在自定义词库中插入对应的词语和控制符停止特定分词：

postgres=# insert into zhparser.zhprs_custom_word(word, attr) values('这是','!');
INSERT 0 1
postgres=# select sync_zhprs_custom_word();
 sync_zhprs_custom_word
------------------------
(1 row)
postgres=# q --重新建立连接
postgres=# select to_tsvector('zh_cn','你好，这是一条中文测试文本');
              to_tsvector
---------------------------------------
 '中文':3 '你好':1 '是':2 '测试文本':4
(1 row)

全文检索索引

全文检索查询业务可能涉及到大量的文本数据，合理使用索引可以有效提升查询性能。倒排索引是一种存放了数据和位置关系的数据结构，在数据系统中通常被用于处理大量文本的检索问题。倒排索引如何提升文本的检索性能呢？下面我们通过一个例子来解释。

现在存在一张数据表（Document），存放了一系列的文本（Text），同时每条文本都有一个对应的编号（ID），这张表的结构如下：

当我们想查找出所有包含”中文”这个词语的文本，在这个数据结构下需要逐条检索Text的全部内容。当数据量大时，做这一步查询将会有非常大的代价。而通过建立倒排索引可以解决此问题，其索引结构包含每条文本中的词语，及词语对应出现的文本位置。一个可能的倒排索引数据结构如下：

通过这个数据结构，我们查找所有包含”中文”的文本将非常简单，可根据索引信息直接定位文本ID，避免了大量的文本数据扫描。

在ADB PG中，提供GIN（Generalized Inverted Index, 通用倒排索引）功能，可以有效提升tsvector类型的查询性能。（具体的性能提升效果见后文案例）

CREATE INDEX text_idx ON document USING GIN (to_tsvector('zh_cn',text));

存储过程

存储过程（SQL stored procedures），通常指数据库中存放在一起的一系列SQL。用户在使用存储过程时，可以指定存储过程的名字并使用参数（parameters），并在合适的时候调用存储过程实现相同或者不同的业务。

存储过程的特性

ADB PG的内核版本升级至PG 12，可以较好地支持存储过程能力。通过合理地使用存储过程，我们在业务开发中可以获得以下收益：

• 存储过程整合了一些列SQL，并分隔不同业务的SQL。这种特性使得存储过程易于维护，极大地提升数据库开发者的效率；
• 调用存储过程非常简单，数据库开发者可以高效地在不同业务场景中复用存储过程；
• 不同的存储过程可以赋予不同的用户权限，帮助提升数据库使用的安全性。

ADB PG存储过程语法与SQL标准基本相同，如下：

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

存储过程与函数的区别

在ADB PG 6.0版本中，函数（Function）可以实现大部分的存储过程功能，我们通常建议用户使用函数来实现存储过程业务，但是存储过程仍然是许多ADB PG用户、及PostgresSQL从业者们期待已久的功能，具体原因主要为：

• 存储过程使用CREATE PROCEDURE语法，并通过CALL调用，符合SQL语法标准，减少用户从其它支持存储过程数据库的业务迁移至ADB PG的工作量；
• 存储过程支持内部开启事务块，进行事务提交(Commit)或者事务回滚(Rollback)，而函数无此功能，只能整体提交或回滚一个事务；
• 存储过程无类似Function的返回值，但是可以通过output参数获取返回结果。

分布式数据仓库

云原生数据仓库AnalyticDB PostgreSQL版（以下简称ADB PG）基于开源项目PostgreSQL和GreenPlum构建，并于2022年推出了AnalyticDB PostgreSQL版7.0版本，PostgreSQL内核版本升级至PG 12，在功能、性能、企业级能力和安全能力等方面都更具有竞争力。

ADB PG采用大规模并行处理（Massively Parallel Processing, MPP）架构，提供灵活的扩展性及高效的数据分析性能；同时支持分布式事务，提供高可用的数据库服务。ADB PG的架构如下图所示：

ADB PG由协调节点和计算节点两大组件构成，协调节点负责全局事务管理，全局元数据存储，SQL解析，重写，执行计划生成及计划自适应优化、计算调度等功能；计算节点主要包含执行引擎和存储引擎，其中执行引擎既支持Greenplum/PostgreSQL功能强大的原生引擎，又支持数据分析场景性能优化的自研向量化引擎，多态化存储引擎则支持本地行存堆表、列存压缩表，和外部表，以及基于存储计算分离架构下的云原生表。协调节点和计算节点通过双副本保障高可用，同时通过水平和垂直扩展提供计算和存储资源的线性扩容。

通过使用ADB PG分布式数据仓库架构，用户可以灵活选择配置计算节点和存储节点，以满足对数据加工和分析性能的资源需求。

案例：ADB PG助力彩数实现全文检索加工及分析

彩数（上海）商务咨询有限公司是韩国三星集团旗下第一企划公司全资控股中国子公司，上海市专精特新企业，主要业务是从社交媒体、新闻和电子商务网站采集分析数据，包括社交数据分析，电商数据分析，问卷分析，埋点数据分析等，基于此为国际大型企业用户提供实时性营销咨询报告。典型用户有韩国现代，宝马，三星，葛兰素史克，拜耳等。

接来下我们以彩数的业务场景做示例案例，展示ADB PG如何实现一站式全文检索实时分析业务，该示例的业务背景为：某产品销售平台经过长时间经营，存在大量产品A的使用评价历史数据，同时每日还会不断收到该产品新的评价数据，也称为每日增量数据。现在该平台希望将每日新评价与历史评价信息写入ADB PG，进行数据加工并从多维度分析客户对产品的评价。

• 注意，实际的业务场景可能比本文案例的复杂许多，本案例仅做示范用。

数据写入/同步

案例中，我们定义产品A的用户评论信息表为product_customer_reply，表结构设计如下：

CREATE TABLE product_customer_reply (
  customer_id   INTEGER,        -- 用户ID
  gender        INTEGER,        -- 性别
  age           INTEGER,        -- 年龄
  ---
  --- 可包含用户的相关信息
  ---
  reply_time    TIMESTAMP,  -- 评论时间
  reply         TEXT                -- 评论内容
) DISTRIBUTED BY(customer_id);

如果业务数据是已经处理好的格式话数据文件，可以通过COPY命令批量加载数据，例如使用如下命令，指定分隔符加载数据文件至ADB PG：

COPY product_customer_reply FROM '/path/localfile' DELIMITER as '|';

增量数据部分，我们同样可以使用COPY方法批量攒批数据加载，也可以结合应用程序使用ADB PG client-SDK攒批写入，具体可参考：https://help.aliyun.com/document_detail/126644.html

此外，如果业务数据已使用TP数据库，那么可以通过DTS服务进行表结构/全量数据同步，也可以配置增量同步实时更新数据。

全文检索

使用全文检索功能前，首先要对中文分词进行配置。ADB PG默认对中文分词做了基本配置，一般情况下用户可以直接使用中文分词功能即可，当然用户应结合业务对中文分词进行定制化配置。例如，本案例中我们期望中文分词能将产品名，品牌名这些非默认分词加入自定义词库：

-- 添加自定义分词
INSERT INTO zhparser.zhprs_custom_word VALUES('产品A');
INSERT INTO zhparser.zhprs_custom_word VALUES('品牌A');

随着业务的增长，业务数据量增加、分词数量增加都有可能使得全文检索查询执行速度变慢。例如，以下是一个查询筛选所有评论中，对产品A好评并有再次购买的潜在客户：

SELECT count(*) from product_customer_reply where to_tsvector('zh_cn', reply) @@ to_tsquery('zh_cn','产品A<1,10>购买') AND to_tsvector('zh_cn', reply)  @@ to_tsquery('zh_cn','产品A<1,10>好');

当数据量增长后，该查询耗时为：

SELECT count(*) FROM product_customer_reply WHERE reply_ts @@ to_tsquery('zh_cn','产品A<1,10>购买') AND reply_ts @@ to_tsquery('zh_cn','产品A<1,10>好');
 count
--------
 428571
(1 row)
Time: 7625.684 ms (00:07.626)

此时我们建立GIN索引加快全文检索，对reply列查询的速度：

CREATE INDEX on  product_customer_reply USING GIN (to_tsvector('zh_cn',reply));

再次查询，可以看到查询时间显著下降：

SELECT count(*) FROM product_customer_reply WHERE to_tsvector('zh_cn', reply) @@ to_tsquery('zh_cn','产品A<1,10>购买') AND to_tsvector('zh_cn', reply)  @@ to_tsquery('zh_cn','产品A<1,10>好');
 count
--------
 428571
(1 row)
Time: 4539.930 ms (00:04.540)

除了对文本列建立倒排索引，我们也可以提前对reply列完成tsvector的建立，减少全文检索的查询计算工作量，例如建立类型为tsvector的reply_ts列，存放reply列的分词数据：

ALTER TABLE product_customer_reply ADD COLUMN reply_ts tsvector;

同样对于reply_ts建立GIN索引：

CREATE INDEX ON product_customer_reply USING GIN (reply_ts);

上述查询对应修改：

SELECT count(*) FROM product_customer_reply WHERE reply_ts @@ to_tsquery('zh_cn','产品A’<1,10>‘购买') AND reply_ts @@ to_tsquery('zh_cn','产品A’<1,10>‘好');
 count
--------
 428571
(1 row)
Time: 465.849 ms

可以看到通过合理地配置全文检索、设计表结构、使用索引，显著地提升了案例中全文检索的查询性能。

数据加工

完成全文检索设计后，我们可以批量加工产品的所有评论数据，将文本数据的特征、分组特性提取出来哦用户分析查询。数据加工任务可能涉及到全量数据的大量SQL处理，因此可以使用存储过程来控制加工任务。例如，我们可以建立ts_search_detail表，存放一些列的全文检索查询条件：

CREATE TABLE ts_search_detail (
  search_id       INTEGER,
  ts_search_text  TEXT
) DISTRIBUTED BY(id);

此外建立proc_results表用于存放加工后的结果，这里我们建立了一张经过全文检索分析后的，用户id、性别、年龄信息的明细表：

CREATE TABLE proc_results (
  id        INTEGER, 
  gender    INTEGER,
  age       INTEGER,
  search_id INTEGER
) DISTRIBUTED BY(id);

创建存储过程ts_proc_jobs，逐条加工ts_search_text中不同的全文检索条件，并将结果存放至proc_results：

CREATE OR REPLACE PROCEDURE ts_proc_jobs() 
AS $
DECLARE
  ts_search   record;
  proc_query  text;
BEGIN
  FOR ts_search IN (SELECT ts_search_text, search_id FROM ts_search_detail) LOOP
    proc_query := '';
    proc_query := 'INSERT INTO proc_results (id, gender, age, search_id) 
                   SELECT customer_id, gender, age, '
                   || ts_search.search_id
                   || 'FROM product_customer_reply WHERE '
                   || ts_search.ts_search_text;
    execute(proc_query);
    commit;
    raise notice 'search id % finish', ts_search.search_id;
  END LOOP;
END;
$
LANGUAGE 'plpgsql';

完成加工后的数据，可以根据业务需求进行复杂关联查询分析、全文检索分析等业务，本案例不再详细举例。