AnalyticDB:ColourDataがフルテキスト検索、データ処理、およびデータ分析を実装できるように、AnalyticDB for PostgreSQLを使用する

背景情報

ColourData上海ビジネスコンサルティング株式会社. は、SamsungグループのCheilWorldwideの完全所有の中国子会社です。 ColourDataは、ソーシャルメディア、ニュース、eコマースウェブサイトからのソーシャルメディアデータ、eコマースデータ、アンケートデータ、イベント追跡データなどのデータの収集と分析に専念しています。 ColourDataはこのデータを使用して、世界の大規模企業向けのリアルタイムのマーケティングおよびコンサルティングレポートを提供します。 ColourDataの典型的なユーザーは、韓国ヒュンダイ、BMW、サムスン、グラクソスミスクライン、バイエルです。

ColourDataのデータによると、eコマースプラットフォームは、製品aに関する大量の履歴コメントデータを蓄積し、製品に関する新しいコメントデータを毎日受信します。 プラットフォームは、データ処理と多次元分析のために、履歴データと毎日の増分データをAnalyticDB for PostgreSQLに書き込みたいと考えています。

データの書き込みまたは同期

次のステートメントを実行して、product_customer_replyという名前のテーブルを作成し、コメントデータを製品aに格納します。

CREATE TABLE product_customer_reply (
  customer_id   INTEGER,		-- User ID
  gender        INTEGER,		-- Gender
  age           INTEGER,		-- Age
  ---
  --- Other information about the user 
  ---
  reply_time    TIMESTAMP, 	-- Comment time
  reply         TEXT				-- Comment text
) DISTRIBUTED BY(customer_id);

ビジネスデータがフォーマットされたデータファイルに保存されている場合、COPY文を実行してデータをロードできます。 たとえば、次のステートメントを実行し、区切り文字を指定してデータファイルをAnalyticDB For PostgreSQLにロードします。

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

COPYステートメントを実行するか、AnalyticDB for PostgreSQLクライアントSDKを使用して、増分データをバッチロードできます。 詳細については、「AnalyticDB For PostgreSQL Client SDKを使用したデータの書き込み」をご参照ください。

ビジネスデータがオンライントランザクション処理 (OLTP) データベースに保存されている場合、data Transmission Service (DTS) を使用してスキーマ同期または完全データ同期を実行できます。 DTSを使用して増分データ同期を実行し、データをリアルタイムで更新することもできます。

フルテキスト検索

データに中国語の単語が含まれている場合は、全文検索を使用する前に中国語の単語セグメンテーションを設定する必要があります。 デフォルトでは、AnalyticDB for PostgreSQLは中国語の単語セグメンテーションの基本的な設定を提供します。 デフォルトの設定を使用するか、ビジネス要件に基づいて設定をカスタマイズできます。 たとえば、次のステートメントを実行して、製品AとブランドAをカスタム辞書に追加します。

-- Add custom words.
INSERT INTO zhparser.zhprs_custom_word VALUES('Product A');
INSERT INTO zhparser.zhprs_custom_word VALUES('Brand A');

ビジネスデータの量が増え、カスタム辞書に追加される単語が増えると、全文検索の実行が遅くなる可能性があります。 たとえば、次のステートメントを実行して、製品Aに良いコメントを付け、製品Aを再度購入したいユーザーにクエリを実行します。

SELECT count(*) FROM product_customer_reply WHERE to_tsvector('zh_cn', reply) @@ to_tsquery('zh_cn','Product A<1,10>Purchase') AND to_tsvector('zh_cn', reply)  @@ to_tsquery('zh_cn','Product A<1,10>Good');

ビジネスデータの量が増加すると、クエリを完了するのに長い時間が必要になります。

SELECT count(*) FROM product_customer_reply WHERE reply_ts @@ to_tsquery('zh_cn','Product A<1,10>Purchase') AND reply_ts @@ to_tsquery('zh_cn','Product A<1,10>Good');
 count
--------
 428571
(1 row)

Time: 7625.684 ms (00:07.626)

次のいずれかの方法を使用して、クエリを高速化できます。

• 方法1: 応答列のGINインデックスを作成して、応答列の全文検索を高速化します。

  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','Product A<1,10>Purchase') AND to_tsvector('zh_cn', reply)  @@ to_tsquery('zh_cn','Product A<1,10>Good');
   count
  --------
   428571
  (1 row)
  
  Time: 4539.930 ms (00:04.540)

• 方法2: 応答列にTSVECTORタイプの列を作成して、フルテキスト検索のクエリおよびコンピューティング操作を削減します。 たとえば、次のステートメントを実行して、reply_tsという名前のTSVECTOR型の列を作成し、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','Product A'<1,10>'Purchase') AND reply_ts @@ to_tsquery('zh_cn','Product A'<1,10>'Good');
   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列の全文検索条件を1つずつ処理し、処理結果を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';

データ処理後、ビジネス要件に基づいて、複雑な関連クエリと分析、およびフルテキスト検索分析を実行できます。

関連ドキュメント

• フルテキスト検索

• ストアドプロシージャ