Execute the CREATE TABLE statement to create a mapping table - 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, specify the names and types of fields in the table, the primary key information about the table, and the execution engine for data queries based on your business requirements. This topic describes how to create a mapping table for an existing table.

Note

For information about how to create a mapping table for a 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 column, 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 that a success response is returned regardless of whether the table exists. If you do not specify IF NOT EXISTS, a success response is returned only if 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 information about data type mappings, see Data type mappings in SQL.
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, set this parameter to NOT NULL for the attribute column.
key_part	Yes	The name of the primary key column. You can specify multiple primary key columns. 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 you set the ENGINE parameter to searchindex, you must configure the index_name and table_name items in the ENGINE_ATTRIBUTE parameter.
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 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

Example 1
The following sample code provides an example on how to create a mapping table named exampletable1. The table contains the id primary key column and the colvalue and content attribute columns. 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(20) PRIMARY KEY, colvalue BIGINT(20), content MEDIUMTEXT);
```
Example 2
The following sample code provides an example on how to create a mapping table named exampletable2. The table contains the id and colvalue primary key columns 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(20), colvalue VARCHAR(1024), content MEDIUMTEXT, PRIMARY KEY(colvalue, id)) ENGINE_ATTRIBUTE='{"consistency": "strong"}';
```

FAQ

What do I do if the "Table 'instancename.tablename' doesn't exist ?" error message is returned when I create a mapping table?

Possible cause
The table for which you want to create a mapping table does not exist.
Solution
You can execute the CREATE TABLE statement in SQL to create a mapping table only for an existing table or search index. Make sure that the table or search index for which you want to create a mapping table exists.

What do I do if the "Table 'instancename.tablename' already exist?" error message is returned when I create a mapping table?

Possible cause
A mapping table is created for the table.
- The user manually created a mapping table for the table.
- Tablestore automatically created a mapping table for the table. If you directly use SQL statements (such as DESCRIBE, SELECT, etc.) to query data in a data table without creating a mapping table for the data table, Tablestore automatically creates a mapping table for the data table. The automatically created mapping table only contains the primary key columns and predefined columns of the data table. You cannot update the attribute columns of the mapping table.
Solution
You can execute the DESCRIBE statement to query the schema of the mapping table. If the schema does not meet your business requirements, you can update the attribute columns of the mapping table. If the mapping table is automatically created by Tablestore, you cannot update the attribute columns. In this case, you can delete the mapping table and create a new one if you are sure that your business is not affected after you delete the mapping table.

Note

For common errors and solutions of the SQL query feature, see How do I troubleshoot common errors of SQL queries?.

References

After you create a mapping table, you can update the attribute columns of the mapping table, query information of the mapping table, query the names of mapping tables, query the index information of the mapping table, and delete the mapping table. For more information, see Update the attribute columns of a mapping table, Query information about tables, List mapping table names, Query the index information about a table, and Delete mapping tables.
You can execute the SELECT statement to query and analyze data in a mapping table. For more information, see Query data.
If you want to use a search index to query and analyze data, you can execute the CREATE TABLE statement to create a mapping table for the search index. For more information, see Create mapping tables for search indexes.