实操干货｜如何通过云原生数据仓库实现“一站式全文检索”？

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)

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)

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)

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)

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)

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

--- 创建分词解析器
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;

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

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

                 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)

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)

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

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'
  } ...

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

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

-- 添加自定义分词
INSERT INTO zhparser.zhprs_custom_word VALUES('产品A');
INSERT INTO zhparser.zhprs_custom_word VALUES('品牌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)

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)

ALTER TABLE product_customer_reply ADD COLUMN reply_ts tsvector;

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

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

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

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';