Tablestore:Update attribute columns of a mapping table

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

• You can execute the ALTER TABLE statement to update the schema of a mapping table. The schema of the Tablestore table for which the mapping table is created is not updated.
• You cannot add or delete a primary key column in a mapping table.
• After you execute the ALTER TABLE statement, the SQL engine asynchronously refreshes the mapping table. The refresh takes up to 30 seconds. During the refresh period, the column that you added may not be returned when you perform the operations that are supposed to return all columns.
• You can execute the ALTER TABLE statement to update an attribute column only in a mapping table that is created by executing the CREATE TABLE statement. You cannot execute the ALTER TABLE statement to update an attribute column in a mapping table that is automatically created for a table by executing the DESCRIBE statement.
• You can add or delete only one attribute column in a mapping table by executing the ALTER TABLE statement. If you want to add or delete multiple attribute columns in a mapping table, you can execute the ALTER TABLE statement multiple times.

Parameters

Examples

• Add an attribute column to a mapping table

  The following sample code provides an example on how to add the colvalue column of the BIGINT type to a mapping table named exampletable:

  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 from a mapping table

  The following sample code provides an example on how to remove the colvalue column of the BIGINT type from a mapping table named exampletable:

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

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.

• After you update attribute columns of a mapping table, you can execute the SELECT statement to query and analyze data by using the mapping table. For more information, see Query data.

• If you want to query the description of a table, you can execute the DESCRIBE statement. For more information, see Query information about tables.

• If you no longer require a mapping table that is created for a table or a search index, 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 index information about tables.

• If you want to query the names of mapping tables in the current database, you can execute the SHOW TABLES statement. For more information, see List the names of tables.