Tablestore:Update attribute columns of a mapping table

Prerequisites

Before you begin, ensure that you have:

• An initialized OTSClient instance. For more information, see Initialize an OTSClient instance.

• A mapping table. For more information, see Create a mapping table.

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

Both examples use SQLQueryRequest to send an ALTER TABLE statement via client.SQLQuery. The request ID in the response confirms the operation succeeded.

• Add an attribute column

  The following sample code adds a colvalue column of the BIGINT type to a mapping table named exampletable:

  func alterTableAddColumn(client *tablestore.TableStoreClient) {
      // Create an SQL request.
      request := &tablestore.SQLQueryRequest{Query: "alter table exampletable add column colvalue bigint"}
  
      // Obtain the response to the SQL request.
      res, err := client.SQLQuery(request)
      if err != nil {
          fmt.Println(err.Error())
      } else {
          fmt.Println(res.ResponseInfo.RequestId)
      }
  }

• Remove an attribute column

  The following sample code removes the colvalue column from a mapping table named exampletable:

  func alterTableDropColumn(client *tablestore.TableStoreClient) {
      // Create an SQL request.
      request := &tablestore.SQLQueryRequest{Query: "alter table exampletable drop column colvalue"}
  
      // Obtain the response to the SQL request.
      res, err := client.SQLQuery(request)
      if err != nil {
          fmt.Println(err.Error())
      } else {
          fmt.Println(res.ResponseInfo.RequestId)
      }
  }

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?

References

• 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.

• After updating attribute columns, run the SELECT statement to query and analyze data. For more information, see Query data.

• To view table details, run the DESCRIBE statement. For more information, see Query information about a table.

• To delete a mapping table you no longer need, run the DROP MAPPING TABLE statement. For more information, see Delete mapping tables.

• To view index information for a table, run the SHOW INDEX statement. For more information, see Query index information about tables.

• To list mapping tables in the current database, run the SHOW TABLES statement. For more information, see Query the names of mapping tables.