AnalyticDB PostgreSQL助力彩数实现全文检索加工及分析

背景信息

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

本文案例的业务背景为：某产品销售平台经过长时间经营，存在大量产品A的使用评价历史数据，同时每日还不断收到该产品新的评价数据，也称为每日增量数据。现在该平台希望将每日新评价与历史评价信息写入AnalyticDB PostgreSQL版，进行数据加工并从多维度分析客户对产品的评价。

数据写入或同步

案例中定义产品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命令批量加载数据。例如，使用如下命令指定分隔符加载数据文件至AnalyticDB PostgreSQL版：

\COPY product_customer_reply FROM '</path/localfile>' DELIMITER as '|';

增量数据部分同样可以使用COPY命令批量攒批数据加载，也可以结合应用程序使用AnalyticDB PostgreSQL版 Client SDK攒批写入。详情请参见基于Client SDK数据写入。

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

全文检索

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

-- 添加自定义分词
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)

您可以通过以下方式，加速查询。

• 方式一：对文本列reply创建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';

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

相关文档

• 全文检索

• 存储过程