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 table.

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

Syntax

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type [NOT NULL | NULL],...
| PRIMARY KEY(key_part[,key_part])
)
ENGINE='tablestore',
ENGINE_ATTRIBUTE='{"consistency": consistency [,"allow_inaccurate_aggregation": allow_inaccurate_aggregation]}';
If a table has only one primary key, you can use the following syntax to create a mapping table for the existing table: 
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type PRIMARY KEY,column_name data_type [NOT NULL | NULL],...
)
ENGINE='tablestore',
ENGINE_ATTRIBUTE='{"consistency": consistency [,"allow_inaccurate_aggregation": allow_inaccurate_aggregation]}';

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.
table_name Yes The name of the table, which is used to identify the table.

The table name in SQL must be the same as the table name in Tablestore.

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.

NOT NULL | NULL No Specifies whether the value of the column can be NULL. Valid values:
  • NOT NULL: The value of the column cannot be NULL. By default, the value of a primary key column cannot be NULL.
  • NULL: The value of the column can be NULL. By default, the value of an attribute column can be NULL.

    If the value of an attribute column cannot be NULL, you must set this parameter to NOT NULL for the attribute column.

key_part Yes The name of the primary key column. Separate multiple primary key columns with commas (,).

The name of the primary key column must be included in the column names.

ENGINE No 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 No 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 table named exampletable1. The table contains the id primary key column, the colvalue attribute column, and the content attribute column. The id primary key column and colvalue attribute column are of the BIGINT type, and the content attribute column is of the MEDIUMTEXT type. 
    CREATE TABLE exampletable1 (id BIGINT PRIMARY KEY, colvalue BIGINT, content MEDIUMTEXT);
  • Create a table named exampletable2. The table contains the id primary key column, the colvalue primary key column, and the content attribute column. The id primary key column is of the BIGINT type, the colvalue primary key column is of the VARCHAR type, and the content attribute column is of the MEDIUMTEXT type. The results of queries that are performed on the table must be in strong consistency mode. 
    CREATE TABLE exampletable2 (id BIGINT, colvalue VARCHAR(1024), content MEDIUMTEXT, PRIMARY KEY(colvalue, id)) ENGINE_ATTRIBUTE='{"consistency": "strong"}';