ApsaraDB for SelectDB:Custom tokenization

Character filter

A character filter preprocesses input text before tokenization. Use it to normalize raw input — for example, replacing punctuation with spaces, or stripping HTML tags before indexing web content.

CREATE INVERTED INDEX CHAR_FILTER IF NOT EXISTS x_char_filter
PROPERTIES (
  "type" = "char_replace"
  -- For other parameters, see below
);

Supported types:

• `char_replace` — replaces specified characters with a target character before tokenization.

  Parameter	Description	Default
  char_filter_pattern	List of characters to replace	—
  char_filter_replacement	Replacement character	Space

• `html_strip` — strips HTML tags from input text before tokenization.

Tokenizer

CREATE INVERTED INDEX TOKENIZER IF NOT EXISTS x_tokenizer
PROPERTIES (
  "type" = "standard"
);

Choose a tokenizer based on your use case:

Token filter

CREATE INVERTED INDEX TOKEN_FILTER IF NOT EXISTS x_token_filter
PROPERTIES (
  "type" = "word_delimiter"
);

• `word_delimiter` — splits tokens at word boundaries and normalizes them.

  Default behavior:

  • Splits at non-alphanumeric characters: Super-Duper → Super, Duper

  • Strips leading and trailing delimiters: XL---42+'Autocoder' → XL, 42, Autocoder

  • Splits at case transitions: PowerShot → Power, Shot

  • Splits at letter-to-digit transitions: XL500 → XL, 500

  • Removes English possessive 's: Neil's → Neil

  Optional parameters:

  Parameter	Default	Description
  generate_number_parts	true	Emit numeric subwords
  generate_word_parts	true	Emit alpha subwords
  protected_words	—	Words to exclude from splitting
  split_on_case_change	true	Split at case transitions
  split_on_numerics	true	Split at digit-letter transitions
  stem_english_possessive	true	Remove trailing 's
  type_table	—	Custom character-type mappings. Maps non-alphanumeric characters to a type to prevent delimiter treatment. Example: ["+ => ALPHA", "- => ALPHA"]. Supported types: ALPHA, ALPHANUM, DIGIT, LOWER, SUBWORD_DELIM, UPPER

• `asciifolding` — converts non-ASCII characters to ASCII equivalents (for example, é → e).

• `lowercase` — converts all tokens to lowercase.

• `stop` — removes stop words from the token stream.

• `pinyin` (Pinyin filter) — converts Chinese characters to Pinyin after tokenization. Accepts the same parameters as the Pinyin tokenizer above.

Analyzer

An analyzer combines one tokenizer with one or more token filters, executed in order.

CREATE INVERTED INDEX ANALYZER IF NOT EXISTS x_analyzer
PROPERTIES (
  "tokenizer" = "x_tokenizer",            -- exactly one tokenizer
  "token_filter" = "x_filter1, x_filter2" -- one or more token filters, run in order
);

Example 1: Phone number prefix search with edge n-gram

Use the edge n-gram tokenizer to index phone numbers so users can search by any prefix.

Components used:

• Tokenizer: edge_ngram with token_chars = digit, min_gram = 3, max_gram = 10

• Analyzer: edge_ngram_phone_number

CREATE INVERTED INDEX TOKENIZER IF NOT EXISTS edge_ngram_phone_number_tokenizer
PROPERTIES
(
    "type" = "edge_ngram",
    "min_gram" = "3",
    "max_gram" = "10",
    "token_chars" = "digit"
);

CREATE INVERTED INDEX ANALYZER IF NOT EXISTS edge_ngram_phone_number
PROPERTIES
(
    "tokenizer" = "edge_ngram_phone_number_tokenizer"
);

CREATE TABLE tbl (
    `a` bigint NOT NULL AUTO_INCREMENT(1),
    `ch` text NULL,
    INDEX idx_ch (`ch`) USING INVERTED PROPERTIES("support_phrase" = "true", "analyzer" = "edge_ngram_phone_number")
) ENGINE=OLAP
DUPLICATE KEY(`a`)
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

select tokenize('13891972631', '"analyzer"="edge_ngram_phone_number"');

Result:

[
  {"token":"138"},
  {"token":"1389"},
  {"token":"13891"},
  {"token":"138919"},
  {"token":"1389197"},
  {"token":"13891972"},
  {"token":"138919726"},
  {"token":"1389197263"}
]

Example 2: Technical log search with standard tokenizer and word_delimiter filter

Use the standard tokenizer with asciifolding, word_delimiter, and lowercase filters for fine-grained tokenization of log messages containing IP addresses, email addresses, and mixed-case identifiers.

Components used:

• Token filter: word_delimiter with split_on_numerics = false, split_on_case_change = false

• Analyzer: lowercase_delimited (standard tokenizer + asciifolding + word_splitter + lowercase)

CREATE INVERTED INDEX TOKEN_FILTER IF NOT EXISTS word_splitter
PROPERTIES
(
    "type" = "word_delimiter",
    "split_on_numerics" = "false",
    "split_on_case_change" = "false"
);

CREATE INVERTED INDEX ANALYZER IF NOT EXISTS lowercase_delimited
PROPERTIES
(
    "tokenizer" = "standard",
    "token_filter" = "asciifolding, word_splitter, lowercase"
);

CREATE TABLE tbl (
    `a` bigint NOT NULL AUTO_INCREMENT(1),
    `ch` text NULL,
    INDEX idx_ch (`ch`) USING INVERTED PROPERTIES("support_phrase" = "true", "analyzer" = "lowercase_delimited")
) ENGINE=OLAP
DUPLICATE KEY(`a`)
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

select tokenize('The server at IP 192.168.1.15 sent a confirmation to user_123@example.com, requiring a quickResponse before the deadline.', '"analyzer"="lowercase_delimited"');

Result:

[
  {"token":"the"},
  {"token":"server"},
  {"token":"at"},
  {"token":"ip"},
  {"token":"192"},
  {"token":"168"},
  {"token":"1"},
  {"token":"15"},
  {"token":"sent"},
  {"token":"a"},
  {"token":"confirmation"},
  {"token":"to"},
  {"token":"user"},
  {"token":"123"},
  {"token":"example"},
  {"token":"com"},
  {"token":"requiring"},
  {"token":"a"},
  {"token":"quickresponse"},
  {"token":"before"},
  {"token":"the"},
  {"token":"deadline"}
]

Example 3: Case-insensitive exact matching with keyword tokenizer

Use the keyword tokenizer to keep the entire input as one token, then apply asciifolding and lowercase filters for case-insensitive, accent-insensitive exact matching.

Components used:

• Tokenizer: keyword

• Token filters: asciifolding, lowercase

CREATE INVERTED INDEX ANALYZER IF NOT EXISTS keyword_lowercase
PROPERTIES (
"tokenizer" = "keyword",
"token_filter" = "asciifolding, lowercase"
);

CREATE TABLE tbl (
    `a` bigint NOT NULL AUTO_INCREMENT(1),
    `ch` text NULL,
    INDEX idx_ch (`ch`) USING INVERTED PROPERTIES("support_phrase" = "true", "analyzer" = "keyword_lowercase")
) ENGINE=OLAP
DUPLICATE KEY(`a`)
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

select tokenize('hÉllo World', '"analyzer"="keyword_lowercase"');

Result:

[
  {"token":"hello world"}
]

Example 4: Chinese Pinyin search

Use the Pinyin tokenizer to search Chinese names by full Pinyin, first-letter acronyms, and original Chinese characters.

-- Create a Pinyin tokenizer that supports multiple output formats
CREATE INVERTED INDEX TOKENIZER IF NOT EXISTS pinyin_tokenizer
PROPERTIES (
    "type" = "pinyin",
    "keep_first_letter" = "true",
    "keep_full_pinyin" = "true",
    "keep_joined_full_pinyin" = "true",
    "keep_original" = "true",
    "keep_none_chinese" = "true",
    "lowercase" = "true",
    "remove_duplicated_term" = "true"
);

CREATE INVERTED INDEX ANALYZER IF NOT EXISTS pinyin_analyzer
PROPERTIES (
    "tokenizer" = "pinyin_tokenizer"
);

CREATE TABLE contacts (
    id BIGINT NOT NULL AUTO_INCREMENT(1),
    name TEXT NULL,
    INDEX idx_name (name) USING INVERTED PROPERTIES("analyzer" = "pinyin_analyzer", "support_phrase" = "true")
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

INSERT INTO contacts VALUES (1, "Liu Dehua"), (2, "Zhang Xueyou"), (3, "Guo Fucheng");

-- Search by original name, full Pinyin, partial Pinyin, or first-letter acronym
SELECT * FROM contacts WHERE name MATCH 'Liu Dehua';
SELECT * FROM contacts WHERE name MATCH 'liudehua';
SELECT * FROM contacts WHERE name MATCH 'liu';
SELECT * FROM contacts WHERE name MATCH 'ldh';

-- Create a Pinyin filter to apply after the keyword tokenizer
CREATE INVERTED INDEX TOKEN_FILTER IF NOT EXISTS pinyin_filter
PROPERTIES (
    "type" = "pinyin",
    "keep_first_letter" = "true",
    "keep_full_pinyin" = "true",
    "keep_original" = "true",
    "lowercase" = "true"
);

CREATE INVERTED INDEX ANALYZER IF NOT EXISTS keyword_pinyin
PROPERTIES (
    "tokenizer" = "keyword",
    "token_filter" = "pinyin_filter"
);

CREATE TABLE stars (
    id BIGINT NOT NULL AUTO_INCREMENT(1),
    name TEXT NULL,
    INDEX idx_name (name) USING INVERTED PROPERTIES("analyzer" = "keyword_pinyin")
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

INSERT INTO stars VALUES (1, "Liu Dehua"), (2, "Zhang Xueyou"), (3, "LiuDehuaABC");

-- Supports multiple search modes
SELECT * FROM stars WHERE name MATCH 'Liu Dehua';
SELECT * FROM stars WHERE name MATCH 'liu';
SELECT * FROM stars WHERE name MATCH 'ldh';
SELECT * FROM stars WHERE name MATCH 'zxy';