You can execute the CREATE TABLE statement to create a mapping table for an existing table or search index. This topic describes how to create a mapping table for an existing search index.

Note For more information about how to create a mapping table for an existing table, see Create mapping tables for tables.

Background information

Indexes of different types may be created for a data table. When you execute the CREATE TABLE statement to create a mapping table for the data table, and the mapping table is used to query data, the SQL engine automatically selects a data table index, secondary index, or search index to meet your business requirements. You can execute the CREATE TABLE statement in SQL to create a mapping table for a specified search index so that you can select the specified search index to perform the query.

Syntax

CREATE TABLE [IF NOT EXISTS] user_defined_name(column_name data_type [,column_name data_type])
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name": index_name, "table_name": table_name}';

Parameters

Parameter Required Description
IF NOT EXISTS No Specifies whether a success response is returned. If you specify IF NOT EXISTS, a success response is returned regardless of whether the table exists. Otherwise, a success response is returned only when the table does not exist.
user_defined_name Yes The name of the mapping table for the search index. The name is used to identify the mapping table in SQL.

The name is used for SQL operations.

column_name Yes The name of the column.

The column name in SQL must be equivalent to the column name in the Tablestore table. For example, if the column name in the Tablestore table is Aa, the column name in SQL must be Aa, AA, aA, or aa.

data_type Yes The data type of the column, such as BIGINT, DOUBLE, or BOOL.

The data type of the column in SQL must match the data type of the column in the Tablestore table. For more information about data type mappings, see Data type mappings.

ENGINE Yes The execution engine that is used when you use the mapping table to query data. Default value: tablestore. Valid values:
  • tablestore: The SQL engine automatically selects a suitable index to perform the query.
  • searchindex: The SQL engine uses the specified search index to perform the query. If ENGINE is set to searchindex, you must configure the index_name and table_name parameters in ENGINE_ATTRIBUTE.
ENGINE_ATTRIBUTE Yes The attribute of the execution engine. The value of this parameter is in the JSON format and includes the following items:
  • index_name: the name of the search index for which a mapping table is created. You need to specify this item only when a mapping table is created for the search index.
  • table_name: the name of the data table for which the search index is created. You need to specify this item only when a mapping table is created for the search index.
  • consistency: the consistency mode that is supported by the execution engine.

    Valid values when you create a mapping table for a table:

    • eventual: The query results are in eventual consistency mode. This is the default value. You can query data a few seconds after the data is written to the table.
    • strong: The query results are in strong consistency mode. You can query data immediately after the data is written to the table.

    When you create a mapping table for a search index, the value of consistency is eventual and cannot be changed.

  • allow_inaccurate_aggregation: specifies whether query performance can be improved by compromising the accuracy of aggregate operations. Type: Boolean.

    When you create a mapping table for a table, the default value of allow_inaccurate_aggregation is true, which indicates that query performance can be improved by compromising the accuracy of aggregate operations. You can set allow_inaccurate_aggregation to false based on your business requirements.

    When you create a mapping table for a search index, the value of allow_inaccurate_aggregation is true and cannot be changed.

Examples

Create a mapping table named search_exampletable1 for the exampletable1_index search index that is created for the exampletable1 data table. The mapping table contains the id, colvalue, and content columns. The id column is of the BIGINT type, and the colvalue and content columns are of the MEDIUMTEXT type. 

CREATE TABLE search_exampletable1(id BIGINT, colvalue MEDIUMTEXT, content MEDIUMTEXT) ENGINE='searchindex' ENGINE_ATTRIBUTE='{"index_name": "exampletable1_index", "table_name": "exampletable1"}';
After the search_exampletable1 mapping table is created, you can perform the following operations:
  • Query information about the search_exampletable1 mapping table. 
    SHOW INDEX IN search_exampletable1;

    For more information about how to query index information about a table, see Query the index information about a table.

  • Use the search_exampletable1 mapping table to query the rows in which the value of the content column matches at least one of the tokens that are obtained by tokenizing the "tablestore cool" string. Specify that up to 10 rows are returned and the id and content columns are returned in each row that meets the query conditions. 
    SELECT id,content FROM search_exampletable1 WHERE TEXT_MATCH(content, "tablestore cool") LIMIT 10;

    For more information about how to query data that matches the specified string, see Query data and Full-text search.