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 an attribute column in only 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 remove only one attribute column in a mapping table by executing the ALTER TABLE statement. If you want to add or remove multiple attribute columns in a mapping table, you can execute the ALTER TABLE statement multiple times.

• You can execute the ALTER TABLE statement to update only the schema of a mapping table. The schema of the Tablestore table for which the mapping table is created is not updated.

• You cannot execute the ALTER TABLE statement to add or remove the primary key columns in a mapping table.

• After you execute the ALTER TABLE statement, the SQL engine asynchronously refreshes the mapping table. Up to 30 seconds are required to complete the refresh. 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.

Parameters

Examples

• Add an attribute column

  The following example adds a 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

  The following example removes the colvalue column 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

• What do I do if the "Unsupported operation: Unsupported alter non-mapping table" error message is returned when I update the attribute columns of a mapping table?

• How do I troubleshoot common errors of SQL queries?

What's next

• To accelerate SQL data queries and computing, you can create a secondary index or a search index. For more information, see Index selection policy and Computation pushdown.

• Run the SELECT statement to query and analyze data using the mapping table. For more information, see Query data.

• Run the DESCRIBE statement to view table details. For more information, see Query information about tables.

• Run the DROP MAPPING TABLE statement 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 INDEX statement to view index information for a table. For more information, see Query index information about tables.

• Run the SHOW TABLES statement to list mapping tables in the current database. For more information, see List the names of tables.