AnalyticDB:Analyzers for full-text indexes

Choose an analyzer

Default analyzer by cluster version:

• Clusters earlier than V3.1.4.15: AliNLP analyzer

• Clusters V3.1.4.15 or later: IK analyzer

To check your cluster's minor version, see How do I view the minor version of a cluster?

Specify an analyzer

FULLTEXT INDEX idx_name(`column_name`) [ WITH ANALYZER analyzer_name ] [ WITH DICT tbl_dict_name];

CREATE TABLE `tbl_fulltext_demo` (
  `id` int,
  `content` varchar,
  `content_alinlp` varchar,
  `content_ik` varchar,
  `content_standard` varchar,
  `content_ngram` varchar,
  `content_edge_ngram` varchar,
  FULLTEXT INDEX fidx_c(`content`),                                           -- Default analyzer
  FULLTEXT INDEX fidx_alinlp(`content_alinlp`) WITH ANALYZER alinlp,
  FULLTEXT INDEX fidx_ik(`content_ik`) WITH ANALYZER ik,
  FULLTEXT INDEX fidx_standard(`content_standard`) WITH ANALYZER standard,
  FULLTEXT INDEX fidx_ngram(`content_ngram`) WITH ANALYZER ngram,
  FULLTEXT INDEX fidx_edge_ngram(`content_edge_ngram`) WITH ANALYZER edge_ngram,
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id);

Preview tokenization results

Before committing to an analyzer, test how it tokenizes your text. Each analyzer has a dedicated test function that returns the token list for a given input string.

Prefix /*+ mode=two_phase*/ to all tokenization test queries, or they will not execute correctly.

The following examples use the same input — 'Hello world' — across all analyzers so you can compare their output directly:

Usage:

/*+ mode=two_phase*/ SELECT fulltext_ik_test('Hello world');

[hello, world, or]

The Pattern analyzer does not support SQL-based tokenization testing.

AliNLP analyzer

Best for Chinese and multilingual text. The AliNLP analyzer is developed by Alibaba Cloud and DAMO Academy using natural language processing (NLP) technology. It tokenizes consecutive natural-language text into meaningful segments and supports custom dictionaries for user-defined entities and stop words.

Supported languages: Chinese, English, Indonesian, Malay, Thai, Vietnamese, French, Spanish

• Tokenization result for English text:

  /*+ mode=two_phase*/ SELECT fulltext_alinlp_test('Hello world');

  Result:

  [hello,  , world]

The following examples show the tokenization results with the default configurations:

IK analyzer

Best for Chinese text. The IK analyzer is an open-source, lightweight Chinese analyzer. It supports two segmentation modes — coarse-grained and fine-grained — and accepts custom dictionaries for entities and stop words.

• Tokenization result for English text:

  /*+ mode=two_phase*/ SELECT fulltext_ik_test('Hello world');

  Result:

  [hello, world, or]

The following examples show the tokenization results with the default configurations:

Standard analyzer

Best for English text. The Standard analyzer applies language-specific rules: for English, it lowercases text and removes stop words and punctuation before tokenizing; for Chinese, it splits text into individual characters. It supports custom dictionaries.

• Tokenization result for English text:

  /*+ mode=two_phase*/ SELECT fulltext_standard_test('Hello world');

  Result:

  [hello, world]

The following examples show the tokenization results with the default configurations:

Ngram analyzer

Best for fuzzy substring search. The Ngram analyzer breaks text into all possible substrings of a fixed length, making it effective for partial-match queries. It supports custom dictionaries.

• Tokenization result for English text:

  /*+ mode=two_phase*/ SELECT fulltext_ngram_test('Hello world');

  Result:

  [he, el, ll, lo, o ,  w, wo, or, rl, ld]

The following examples show the tokenization results with the default configurations:

Edge_ngram analyzer

Best for prefix search and autocomplete (search-as-you-type). The Edge_ngram analyzer generates prefix tokens of increasing length — for example, h, then he — making it ideal for matching words from the beginning. It supports custom dictionaries.

Tip: Edge_ngram works well when the search term appears at the start of words. For fuzzy substring matching anywhere in the text, use the Ngram analyzer instead.

• Results of English text tokenization.

  /*+ mode=two_phase*/ SELECT fulltext_edge_ngram_test('Hello world');

  Result:

  [h, he]

The following examples show the tokenization results with the default configurations:

Pattern analyzer

Best for custom delimiters. The Pattern analyzer tokenizes text by splitting on a regular expression pattern you define. It does not support custom dictionaries or SQL-based tokenization testing.

FULLTEXT INDEX fidx_name(`column_name`) WITH ANALYZER pattern_tokenizer("Custom_rule") [ WITH DICT `tbl_dict_name` ];

Manage analyzer configuration

show adb_config key <analyzer_param>;

show adb_config key FULLTEXT_NGRAM_TOKEN_SIZE;

SELECT `key`, `value`, `update_time`
FROM INFORMATION_SCHEMA.kepler_meta_configs
WHERE key = '<analyzer_param>';

SELECT `key`, `value`, `update_time`
FROM INFORMATION_SCHEMA.kepler_meta_configs
WHERE key = 'FULLTEXT_NGRAM_TOKEN_SIZE';

set adb_config <analyzer_param>=<value>;

set adb_config FULLTEXT_NGRAM_TOKEN_SIZE=3;