Execute the CREATE TABLE statement to create mapping tables for search indexes - Tablestore

You can execute the CREATE TABLE statement to create a mapping table for an existing table or search index. When you create a mapping table, you need to specify the field names, field types, and primary keys, and configure the engine that is used when you use the mapping table to query data. 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

When you execute the CREATE TABLE statement to create a mapping table for a data table, indexes of different types may be created for the data table. When you use the mapping table to query data, the SQL engine automatically selects a data table primary key, 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 attribute 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 you set the ENGINE parameter to searchindex, you must configure the index_name and table_name items in the ENGINE_ATTRIBUTE parameter.
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 you create a mapping table 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 you create a mapping table 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 the index information about tables
Query index 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.
Query data that matches the specified string
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.

FAQ

How do I troubleshoot common errors of SQL queries?

References

After you create a mapping table for a search index, you can execute the SELECT statement to query and analyze data in the table. For more information, see Query data, ARRAY supported in search indexes, NESTED supported in search indexes, Full-text search, and Virtual columns of search indexes.
If an attribute column of the data table changes, you must modify the attribute column in the search index before you modify the attribute column of the mapping table.
1. Select a method to modify the attribute column in the search index based on your business requirements.
  - Dynamically modify the schema to modify the fields in the search index. For more information, see Dynamically modify the schema of a search index.
  - Delete the search index and recreate a search index. For more information, see Create search indexes.
2. Select a method to modify the attribute column in the mapping table based on your business requirements.
  - Execute the ALTER TABLE statement to modify the attribute column in the mapping table. For more information, see Update attribute columns of mapping tables.
  - Delete the mapping table and recreate a mapping table.
If you want to query the description of a table, you can execute the DESCRIBE statement. For more information, see Query the information about a table.
If you no longer require a mapping table, you can execute the DROP MAPPING TABLE statement to delete the mapping table. For more information, see Delete mapping tables.
If you want to view the index information about a table, you can execute the SHOW INDEX statement. For more information, see Query the index information about a table.
If you want to list the names of mapping tables in the current database, you can execute the SHOW TABLES statement. For more information, see List table names.