Run the ALTER TABLE statement to add or remove an attribute column in an existing mapping table.
For more information, see DDL operations.
Prerequisites
-
An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
-
A mapping table is created. For more information, see Create mapping tables.
Usage notes
-
Execute a
CREATE TABLEstatement to create a mapping table for the data table first, and then use anALTER TABLEstatement to update attribute columns. -
Each
ALTER TABLEstatement can add or remove only one attribute column. To update multiple attribute columns, execute anALTER TABLEstatement for each column. -
An
ALTER TABLEstatement updates only the mapping table schema. It does not update the data storage schema of the Tablestore table. -
An
ALTER TABLEstatement cannot add or remove primary key columns from a mapping table. -
After you execute an
ALTER TABLEstatement, the SQL engine refreshes the mapping table asynchronously. The refresh can take up to 30 seconds. During the refresh, queries that return all attribute columns may not return the newly added column. -
To change the definition of an existing attribute column, use
DROP COLUMNto remove the column and then useADD COLUMNto add the column with the new definition.
Parameters
|
Parameter |
Description |
|
query |
The SQL statement. Configure this parameter based on the required feature. |
Examples
-
Add an attribute column
The following example adds a
colvaluecolumn of the BIGINT type to a mapping table namedexampletable:const params = { query: "alter table exampletable add column colvalue bigint", } client.sqlQuery(params, function (err, data) { if (err) { console.log('sqlQuery error:', err.toString()); } else { console.log('sqlQuery success:', data); } }); -
Remove an attribute column
The following example removes the
colvaluecolumn from a mapping table namedexampletable:const params = { query: "alter table exampletable drop column colvalue", } client.sqlQuery(params, function (err, data) { if (err) { console.log('sqlQuery error:', err.toString()); } else { console.log('sqlQuery success:', data); } });
You can remove attribute columns that you no longer need and then add attribute columns based on your business requirements.
FAQ
What's next
-
Index selection optimization and computation pushdown are supported (Query optimization).
-
Run the
SELECTstatement to query and analyze data using the mapping table. For more information, see Query data. -
Run the
DESCRIBEstatement to view table details. For more information, see Query information about tables. -
Run the
DROP MAPPING TABLEstatement to delete a mapping table that is created for a table or a search index. For more information, see Delete mapping tables. -
Run the
SHOW INDEXstatement to view index information for a table. For more information, see Query index information about tables. -
Run the
SHOW TABLESstatement to list mapping tables in the current database. For more information, see List the names of tables.