DDL operations

DDL statement overview

Statement	Engine	Description
CREATE TABLE	tablestore	Creates a mapping for a data table. The SQL engine automatically selects the optimal index for queries.
CREATE TABLE	searchindex	Creates a mapping for a search index. The SQL engine runs queries through the specified search index.
ALTER TABLE	-	Adds or removes attribute columns in an existing mapping table.
DROP MAPPING TABLE	-	Removes a mapping without affecting the underlying data table or index.
DESCRIBE	-	Returns the field names, data types, primary key flags, and nullability of a mapping table.
SHOW INDEX	-	Returns the index information associated with a mapping table, including primary key, secondary, and search indexes.
SHOW TABLES	-	Lists all mapping tables in the current instance.

Note

Unlike traditional relational databases, CREATE TABLE in Tablestore does not create a physical table. Instead, it creates a SQL access mapping for an existing data table or search index. DROP MAPPING TABLE removes only the mapping and leaves the underlying data intact.

Create a data table mapping

A CREATE TABLE statement with ENGINE='tablestore' creates a mapping for an existing data table. After the mapping is created, the SQL engine automatically selects the optimal index (primary key, secondary index, or search index) to run queries.

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]}';

For tables with a single primary key column, specify PRIMARY KEY directly in the column definition:

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}';

Parameters

Parameter	Required	Description
IF NOT EXISTS	No	If specified, no error is returned when the mapping table already exists. If not specified, a `Table already exists` error is returned.
table_name	Yes	The mapping table name. This name must match the data table name.
column_name	Yes	The column name. Column names in SQL must match the column names in the data table (case-insensitive). For example, if the data table column is `Aa`, you can use `Aa`, `AA`, `aA`, or `aa`.
data_type	Yes	The SQL data type of the column. The data type must match the column data type in the data table. Primary key columns support only BIGINT, VARCHAR(1024), and VARBINARY(1024). Attribute columns additionally support DOUBLE, BOOL, MEDIUMTEXT, and MEDIUMBLOB. For the full type mapping, see Data type mappings in SQL.
NOT NULL \| NULL	No	Whether the column allows NULL values. Primary key columns default to NOT NULL. Attribute columns default to NULL. Specify NOT NULL for attribute columns that must have a value.
PRIMARY KEY	Yes	The primary key definition. The primary key must match the primary key columns of the data table in order and type.
ENGINE	No	The execution engine type. Defaults to `tablestore`, which means the SQL engine automatically selects an index.
consistency	No	The consistency level of query results. Valid values: `strong` (strong consistency) and `eventual` (eventual consistency, default). When set to `strong`, the SQL engine does not select search indexes because search indexes provide only eventual consistency.
allow_inaccurate_aggregation	No	Whether to trade aggregation accuracy for query performance. Defaults to `true`. When set to `false`, the SQL engine does not select search indexes for aggregation. For search index mapping tables, this parameter is fixed at `true`.

Examples

Create a mapping for exampletable with primary key columns colvalue (VARCHAR) and id (BIGINT), and an attribute column content (MEDIUMTEXT):

CREATE TABLE exampletable(colvalue VARCHAR(1024), id BIGINT, content MEDIUMTEXT, PRIMARY KEY(colvalue, id)) ENGINE='tablestore';

Create a mapping with strong consistency:

CREATE TABLE exampletable2(id BIGINT(20), colvalue VARCHAR(1024), content MEDIUMTEXT, PRIMARY KEY(colvalue, id)) ENGINE_ATTRIBUTE='{"consistency": "strong"}';

Create a search index mapping

To create a mapping for an existing search index, use a CREATE TABLE statement with ENGINE='searchindex'. After the mapping is created, the SQL engine runs all queries through the specified search index.

Use cases

Search index capabilities such as full-text search, vector search, or nested type queries are required.
Query routing must be pinned to a specific search index instead of relying on automatic index selection by the SQL engine.

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	If specified, no error is returned when the mapping table already exists.
user_defined_name	Yes	A custom mapping table name. The name does not need to match the data table or index name.
column_name	Yes	The column name. Primary key definitions are not required. Search index mapping tables do not support the VARCHAR type. Use MEDIUMTEXT for string columns.
index_name	Yes	The search index name. Specified in ENGINE_ATTRIBUTE. The index must already exist.
table_name	Yes	The data table name to which the search index belongs. Specified in ENGINE_ATTRIBUTE.

Examples

Create a mapping table search_exampletable1 for the search index exampletable1_index on data table exampletable1:

CREATE TABLE search_exampletable1(id BIGINT, colvalue MEDIUMTEXT, content MEDIUMTEXT) ENGINE='searchindex' ENGINE_ATTRIBUTE='{"index_name": "exampletable1_index", "table_name": "exampletable1"}';

Use IF NOT EXISTS to avoid errors when the mapping already exists:

CREATE TABLE IF NOT EXISTS search_exampletable1(id BIGINT, colvalue MEDIUMTEXT, content MEDIUMTEXT) ENGINE='searchindex' ENGINE_ATTRIBUTE='{"index_name": "exampletable1_index", "table_name": "exampletable1"}';

Update mapping table columns

The ALTER TABLE statement adds or removes attribute columns in an existing mapping table.

Note

Only mapping tables created with a CREATE TABLE statement support ALTER TABLE. Mapping tables auto-bound by DESCRIBE or other statements do not support this operation.
Each statement adds or removes one column. To modify multiple columns, run the statement multiple times.
ALTER TABLE updates only the mapping table schema and does not affect the storage structure of the data table.
Primary key columns cannot be added or removed.
After you run this statement, the SQL engine refreshes asynchronously and takes up to 30 seconds to complete. Queries during the refresh may not return the new column.

Syntax

ALTER TABLE table_name option column_name [data_type];

Parameters

Parameter	Required	Description
table_name	Yes	The mapping table name.
option	Yes	The operation type: `ADD COLUMN` to add an attribute column, or `DROP COLUMN` to remove an attribute column.
column_name	Yes	The column name. When you add a column, the name must not duplicate an existing column name. Column name case rules are the same as those for `CREATE TABLE`.
data_type	`ADD COLUMN` only	The SQL data type of the column. The data type must match the corresponding column in the data table. Not required for `DROP COLUMN`.

Examples

Add attribute columns to a mapping table:

ALTER TABLE exampletable ADD COLUMN colvalue BIGINT;
ALTER TABLE exampletable ADD COLUMN content MEDIUMTEXT;

Remove an attribute column from a mapping table:

ALTER TABLE exampletable DROP COLUMN colvalue;

Drop a mapping

The DROP MAPPING TABLE statement removes one or more mapping tables.

Note

This statement removes only the SQL mapping. The underlying data table and its data are not affected.

Syntax

DROP MAPPING TABLE [IF EXISTS] table_name, ...;

Parameters

Parameter	Required	Description
table_name	Yes	The mapping table name. Separate multiple names with commas (,).
IF EXISTS	No	If specified, no error is returned when the mapping does not exist. If not specified, an `Unknown mapping table` error is returned.

Examples

DROP MAPPING TABLE IF EXISTS exampletable;

View table details

The DESCRIBE statement returns the field names, data types, nullability, and primary key flags of a mapping table.

Syntax

DESCRIBE table_name;

Parameters

Parameter	Required	Description
table_name	Yes	The mapping table name.

Examples

View the details of the exampletable mapping table:

DESCRIBE exampletable;

The output contains the following columns:

Column	Example	Description
Field	pk1	The field name.
Type	varchar(1024)	The SQL data type of the field.
Null	NO	Whether the field allows NULL values. NO for primary key columns. YES for attribute columns.
Key	PRI	The primary key flag. PRI indicates a primary key column. An empty value indicates an attribute column.

View index details

The SHOW INDEX statement returns the indexes associated with a mapping table, including primary key, secondary, and search indexes.

Syntax

SHOW INDEX {FROM | IN} table_name;

Parameters

Parameter	Required	Description
table_name	Yes	The mapping table name. `FROM` and `IN` are interchangeable.

Examples

View the indexes of a data table mapping (returns primary key, secondary, and search indexes):

SHOW INDEX IN exampletable;

The Index_type column indicates the index type: an empty value for a primary key index, GlobalIndex for a secondary index, and SearchIndex for a search index.

View the indexes of a search index mapping:

SHOW INDEX IN search_exampletable1;

For search index mapping tables, SHOW INDEX returns _system_hidden_pk_field as the primary key index (a system-generated hidden primary key). Each column also shows a Search_type value such as LONG, KEYWORD, or TEXT.

View mapping tables

SHOW TABLES lists all mapping tables in the current instance.

Syntax

SHOW TABLES;

Sample output:

+------------------------+
| Tables_in_myinstance   |
+------------------------+
| exampletable           |
+------------------------+
| search_exampletable1   |
+------------------------+

Common errors

The following errors may occur when a DDL statement fails.

Error message	Statement	Cause and solution
Table 'instance.table' doesn't exist	`CREATE TABLE`	The data table does not exist. `CREATE TABLE` creates mappings only for existing data tables or search indexes. Make sure the data table is created first.
Table 'instance.table' already exists	`CREATE TABLE`	The mapping already exists. To suppress this error, use `IF NOT EXISTS`. To recreate the mapping, run `DROP MAPPING TABLE` first.
Unknown mapping table 'instance.table'	`DROP MAPPING TABLE`	The mapping does not exist. To suppress this error, use `IF EXISTS`.
Unsupported alter non-mapping table	`ALTER TABLE`	The target table is not a mapping table created with `CREATE TABLE`. It may be an auto-bound mapping table, which does not support `ALTER TABLE`.