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

完成加工後的資料，可以根據業務需求進行複雜關聯查詢分析、全文檢索索引分析等業務。

相關文檔

• 全文檢索索引

• 預存程序