• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CREATE TEXT SEARCH CONFIGURATION

CREATE TEXT SEARCH CONFIGURATION

Function

CREATE TEXT SEARCH CONFIGURATION creates a text search configuration. A text search configuration specifies a text search parser that can divide a string into tokens, plus dictionaries that can be used to determine which tokens are of interest for searching.

If only the parser is specified, then the new text search configuration initially has no mappings from token types to dictionaries, and therefore will ignore all words. Subsequent ALTER TEXT SEARCH CONFIGURATION commands must be used to create mappings to make the configuration useful. If COPY option is specified, the parser, mapping and configuration option of text search configuration is copied automatically.

If the schema name is given, the text search configuration will be created in the specified schema. Otherwise, the configuration is created in the current schema.

Defining the user of text search configuration as its owner.

Precautions

  • PARSER and COPY options are mutually exclusive, because when an existing configuration is copied, its parser selection is copied too.
  • If only the parser is specified, then the new text search configuration initially has no mappings from token types to dictionaries, and therefore will ignore all words.

Syntax

CREATE TEXT SEARCH CONFIGURATION name 
    ( PARSER = parser_name | COPY = source_config )
    [ WITH ( {configuration_option = value} [, ...] )];

Parameter Description

  • name

    Specifies the name of the text search configuration to be created. Specifies the name can be schema-qualified.

  • parser_name

    Specifies the name of the text search parser to use for this configuration.

  • source_config

    Specifies the name of an existing text search configuration to copy.

  • configuration_option

    Specifies the configuration parameter of text search configuration is mainly for the parser executed by parser_name or contained by source_config.

    Value range: Supporting default, ngram. and zhparser parser. The parse of default type has no corresponding configuration_option, the configuration_option of ngram and zhparser parser is shown in Table 1.
    Table 1 Configuration parameters of ngram and zhparser parsers

    Parser

    Parameters for adding an account

    Description

    Value Range

    ngram

    gram_size

    The length of word segmentation

    Integer, 1 to 4

    Default value: 2

    punctuation_ignore

    Whether ignore punctuations

    • true (default value): Ignore punctuations.
    • false: Do not ignore punctuations.

    grapsymbol_ignore

    Whether ignore graphical characters.

    • true: Ignore graphical characters.
    • false (default value): Do not ignore graphical characters.

    zhparser

    punctuation_ignore

    The word segmentation result whether ignores special characters including punctuations (\r and \n will not be ignored).

    • true (default value): Ignore all the special characters including punctuations.
    • false: Do not ignore all the special characters including punctuations.

    seg_with_duality

    Whether aggregate segments with duality.

    • true: Aggregate segments with duality.
    • false (default value): Do not aggregate segments with duality.

    multi_short

    Whether execute long words composite divide.

    • true (default value): Execute long words composite divide.
    • false: Do not execute long words composite divide.

    multi_duality

    Whether aggregate segments in long words with duality.

    • true: Aggregate segments in long words with duality.
    • false (default value): Do not aggregate segments in long words with duality.

    multi_zmain

    Whether display key single word individually.

    • true: Display key single word individually.
    • false (default value): Do not display key single word individually.

    multi_zall

    Whether display all single words individually.

    • true: Display all single words individually.
    • false (default value): Do not display all single words individually.

Examples

-- Create a text search configuration:
CREATE TEXT SEARCH CONFIGURATION ngram2 (parser=ngram) WITH (gram_size = 2, grapsymbol_ignore = false);

-- Create a text search configuration:
CREATE TEXT SEARCH CONFIGURATION ngram3 (copy=ngram2) WITH (gram_size = 2, grapsymbol_ignore = false);

-- Add type mapping:
ALTER TEXT SEARCH CONFIGURATION ngram2 ADD MAPPING FOR multisymbol WITH simple;

-- Create user joe:
CREATE USER joe IDENTIFIED BY 'Bigdata123@';

-- Change the owner of text search configuration:
ALTER TEXT SEARCH CONFIGURATION ngram2 OWNER TO joe;

-- Modify the schema of text search configuration:
ALTER TEXT SEARCH CONFIGURATION ngram2 SET SCHEMA joe;

-- Rename a text search configuration:
ALTER TEXT SEARCH CONFIGURATION joe.ngram2 RENAME TO ngram_2;

-- Delete type mapping:
ALTER TEXT SEARCH CONFIGURATION joe.ngram_2 DROP MAPPING IF EXISTS FOR multisymbol;

-- Delete a text search configuration:
DROP TEXT SEARCH CONFIGURATION joe.ngram_2;
DROP TEXT SEARCH CONFIGURATION ngram3;

-- Delete the schema and user joe:
DROP SCHEMA IF EXISTS joe CASCADE;
DROP ROLE IF EXISTS joe;