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

How do I troubleshoot common errors of SQL queries?

References

If you want to accelerate data queries and computing by executing SQL statements, you can create a secondary index or a search index. For more information, see Index selection policy and Computing pushdown.
You can execute the SELECT statement to query and analyze data in the table for which you created 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.
If the attribute column of a data table changes, you can execute the ALTER TABLE statement to modify the mapping table that is created for the data table. For more information, see Update attribute columns of mapping tables.
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.