Tablestore:Use the Wide Column model in the Tablestore console

Step 1: Activate Tablestore

If Tablestore is activated, skip this step. You must activate Tablestore only once. You are not charged when you activate Tablestore.

1. Log on to the Tablestore product page.

2. Click Get it Free.

3. On the Table Store (Pay-As-You-Go) page, click Buy Now.

4. On the Confirm Order page, select I have read and agree to Table Store (Pay-As-You-Go) Agreement of Service and click Activate Now.

   After you activate Tablestore, click Console to go to the Tablestore console.

   You can also click Console in the upper-right corner of the homepage of the Alibaba Cloud official website. Click the icon. In the left-side navigation pane, choose Products and Services > Storage > Essential Storage Service > Tablestore to go to the Tablestore console.

Step 2: Create an instance

Instances are the basic units of resource management in Tablestore. Tablestore manages access to Tablestore from applications and measures resources at the instance level. After you create an instance, you can create and manage tables in the instance.

1. Log on to the Tablestore console.

2. In the top navigation bar, select a resource group and a region.

   The instance types that are available in the region are displayed in the console.

3. Click Create Instance.

4. On the On-demand tab in the Billing Method dialog box, specify Instance Name, select an instance type from the Instance Type drop-down list, and specify Instance Description.

   Note

   • For more information about the naming conventions for instances and how to select an instance type, see Instances.

   • If you have a large number of Tablestore instances, you can add tags to the instances to manage instances by tag. For information about tags, see Tag overview.

5. If you want to change the network types that are allowed to access the instance or specify whether the Tablestore resources can be accessed by using the Tablestore console, turn on Advanced Settings and configure the parameters that are described in the following table.

   Parameter	Description
   Allow Network	The type of the network that can be used to access Tablestore resources. You can select multiple network types at a time. Valid values: VPC: specifies whether the instance can be accessed over a virtual private cloud (VPC). By default, VPC is selected, which indicates that the instance can be accessed over a VPC. If you do not want to allow access to the instance over a VPC, clear VPC. Internet: specifies whether the instance can be accessed over the Internet. By default, Internet is selected, which indicates that the instance can be accessed over the Internet. If you do not need to access the instance over the Internet, clear Internet. Classic Network: specifies whether the instance can be accessed over the classic network. By default, Classic Network is selected, which indicates that the instance can be accessed over the classic network. If you do not need to access the instance over the classic network, clear Classic Network.
   Allowed Source	Specifies whether the Tablestore resources can be accessed by using the Tablestore console. By default, Trusted Gateway (Console) is selected, which indicates that the Tablestore resources can be accessed by using the Tablestore console. If you do not allow the Tablestore resources to be accessed by using the Tablestore console, clear Trusted Gateway (Console).

6. Click OK.

Step 3: Create a data table

You can read and write data in a data table. When you create a data table, you can create a global secondary index to read data.

1. On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.

2. On the Instance Details tab of the Instance Management page, click Create Table.

   Note

   You can create up to 64 data tables in an instance.

3. In the Create Table dialog box, configure the parameters that are described in the following table.

   Parameter	Description
   Table Name	The name of the data table, which is used to identify a data table in an instance. The name must be 1 to 255 bytes in length and can contain letters, digits, and underscores (_). The name must start with a letter or an underscore (_).
   Primary Key	The primary key column. All primary key columns are used to identify a record in a table. Enter a name for the primary key column and select a data type. Click Add Primary Key Column to add a primary key column. You can add one to four primary key columns. The first primary key column is the partition key. After you create a data table, you cannot modify the configurations and order of the primary key columns. Important In Tablestore, only one primary key column can be specified as an auto-increment primary key column for each data table. You cannot specify a partition key as an auto-increment primary key column. After you specify a primary key column as an auto-increment primary key column, Tablestore automatically generates a value for the auto-increment primary key column when you write a row of data. The values of auto-increment primary key columns are incremental and unique within the rows that share the same partition key. Naming conventions for primary key columns: The name must be 1 to 255 bytes in length and can contain letters, digits, and underscores (_). The name must start with a letter or an underscore (_). The STRING, INTEGER, and BINARY data types are supported for primary key columns.
   Allow Updates:	Specifies whether to allow the UpdateRow operation. The default value is Yes, which specifies that the UpdateRow operation is allowed. If you want to use the time to live (TTL) feature of search indexes, you must set this parameter to No. For more information, see Specify the TTL of a search index.
   Encryption	Specifies whether to enable data encryption. By default, this feature is disabled. You can enable this feature by turning on Encryption. Important After the feature is enabled, the feature cannot be disabled. Proceed with caution.
   Encryption Type	The encryption type of data. This parameter is required only if you turn on Encryption. Valid values: CMK of KMS: Tablestore automatically creates a customer master key (CMK) in Key Management Service (KMS) to encrypt and decrypt data. BYOK-based Key: Tablestore encrypts and decrypts data based on the Bring Your Own Key (BYOK)-based key. Important Before you use BYOK-based encryption, you must create a software-protected key in the KMS console, create a Resource Access Management (RAM) role, and then grant permissions to the RAM role. For more information, see Data encryption.
   Key ID	The ID of the key. This parameter is required only if you set the Encryption Type parameter to BYOK-based Key.
   ARN	The Alibaba Cloud Resource Name (ARN) of the RAM role. This parameter is required only if you set the Encryption Type parameter to BYOK-based Key.

4. Optional. In the Create Table dialog box, turn on Advanced Settings or Global Secondary Index.

   To configure advanced settings such as TTL, turn on Advanced Settings. To create a global secondary index, turn on Global Secondary Index.

   Configure advanced settings

   To manage the retention period of data in a data table, you can specify the Time to Live parameter. To manage multiple versions of data that has the same primary key value, you can specify the Max Versions parameter. For more information, see Data versions and TTL.

   To reduce the costs of a high-performance instance in CU mode, you can specify the reserved read and write throughput for data tables. For more information, see Reserved read and write throughput.

   1. Turn on Advanced Settings.

   2. Configure the advanced parameters. The following table describes the parameters.

      Parameter	Description
      Time To Live	The retention period of data in the data table. If the retention period exceeds the TTL value, Tablestore automatically deletes expired data. Unit: seconds. The minimum value is 86,400 seconds, which is one day. A value of -1 specifies that the data never expires.
      Max Versions	The maximum number of versions that can be retained for data in each attribute column of the data table. If the number of versions of data in an attribute column exceeds the value of this parameter, the system deletes the data of earlier versions. The value of this parameter must be a positive integer.
      Valid Version Offset	The maximum difference between the current system time and the specified data version. Unit: seconds. The value of the Max Version Offset parameter is a positive integer that can be greater than the number of seconds that have elapsed since January 1, 1970, 00:00:00 UTC. The difference between the version number and the time at which the data is written must be no more than the value of the Max Version Offset parameter. Otherwise, an error occurs when the data is written. The valid version range of data in an attribute column is calculated by using the following formula: Valid version range = [max{Data written time - Max version offset, Data written time - TTL value}, Data written time + Max version offset).
      Reserved Read Throughput	The two parameters are not supported for capacity instances. The reserved read or write throughput for the data table. The value is of the INTEGER type. Valid values: 0 to 100000. Unit: capacity unit (CU). If you set the Reserved Read Throughput or Reserved Write Throughput parameter to a value greater than 0, Tablestore allocates and reserves related resources for the data table. After you create the data table, you are charged for the reserved throughput resources. If you set the Reserved Read Throughput or Reserved Write Throughput parameter to 0, Tablestore does not allocate or reserve related resources for the data table.
      Reserved Write Throughput

   Configure a secondary index

   If an application needs to use different attribute columns as query conditions to query data, you can specify these attribute columns as the primary key columns of a secondary index to speed up the query. For more information, see Overview.

   1. Turn on Global Secondary Index.

   2. Click Add next to Pre-defined Column. Specify a name for the predefined column and select a data type from the drop-down list.

      Naming conventions for predefined columns: The name must be 1 to 255 bytes in length and can contain letters, digits, and underscores (_). The name must start with a letter or an underscore (_).

      Predefined columns support the STRING, INTEGER, BINARY, FLOAT, and BOOLEAN data types.

   3. Click Add Global Secondary Index. You must configure the Index Name and Primary Key parameters. You can configure the Pre-defined Column and Index Type parameters based on your business requirements.

      Important

      The index name must be different from the data table name. The index name and the data table name must be unique in the instance.

      The name of a secondary index must be 1 to 255 bytes in length and can contain letters, digits, and underscores (_). The name must start with a letter or an underscore (_).

      • You can specify one or more primary key columns or predefined columns of the data table for the Primary Key parameter. You can specify one or more predefined columns of the data table for the Pre-defined Column parameter. However, you cannot specify the same predefined column as a primary key column and a predefined column of the secondary index.

      • You can set the Index Type parameter to Global or Local.

        Important

        The first primary key column of a global secondary index can be a primary key column or predefined column of the data table. The first primary key column of a local secondary index must be the first primary key column of the data table.

        When you use the global secondary index feature, Tablestore automatically synchronizes data from the indexed columns and primary key columns of a data table to the columns of an index table in asynchronous mode. The synchronization latency can be as low as milliseconds. When you use the local secondary index feature, Tablestore automatically synchronizes data from the indexed columns and primary key columns of a data table to the columns of an index table in synchronous mode. You can query data from the index table immediately after the data is written to the data table.

5. Click OK.

   After a data table is created, you can view the data table on the Tables tab. If the data table that you created is not displayed on the tab, click the icon to refresh the table list.

Step 4: Read and write data

You can write, update, delete, and read data in a data table.

1. On the Tables tab of the Instance Details tab, click the name of the data table that you want to manage, and click the Query Data tab. You can also click Query in the Actions column of the data table.

2. Read and write data based on your business requirements.

   Insert a row of data

   1. On the Query Data tab, click Insert.

   2. In the Insert dialog box, configure the Primary Key Value parameter. Click the + icon and configure the Name, Type, Value, and Version parameters.

      By default, System Time is selected. This value specifies that the current system time is used as the version number of the data. You can clear System Time and enter the version number of the data.

   3. Click OK.

   Update a row of data

   1. On the Query Data tab, select the row of data that you want to update. Click Update.

   2. In the Update dialog box, add or remove attribute columns or update data in or delete data from attribute columns.

      • You can click the + icon to add an attribute column. You can also click the icon to remove an attribute column.

      • In the Actions column, if you select Update, you can modify the data in an attribute column. If you select Delete and select a version, you can delete the data of the selected version. If you select Delete All, you can delete all versions of the data.

   3. Click OK.

   Read data

   You can read a single row of data or data whose primary key value is within a specific range in the Tablestore console based on your business requirements.

   Read a single row of data

   1. On the Query Data tab, click Search.

   2. In the Search dialog box, set the Mode parameter to Get Row and select the table or secondary index that you want to query from the Table/Secondary Index drop-down list.

   3. By default, all columns are returned. To return specific attribute columns, turn off All Columns and enter the names of the attribute columns that you want to return. Separate multiple attribute column names with commas (,).

   4. Configure the Primary Key Value parameter of the row that you want to query.

      The integrity and accuracy of the primary key value affect the query results.

   5. Configure the Max Versions parameter to specify the maximum number of versions to return.

   6. Click OK.

   Read data whose primary key values are within a specific range

   1. On the Query Data tab, click Search.

   2. In the Search dialog box, set the Mode parameter to Range Search and select the table or secondary index that you want to query from the Table/Secondary Index drop-down list.

   3. By default, all columns are returned. To return specific attribute columns, turn off All Columns and enter the names of the attribute columns that you want to return. Separate multiple attribute column names with commas (,).

   4. Specify the Start Primary Key Column and End Primary Key Column parameters.

   Important

   • If you set Mode to Range Search, the range that is specified by the start value and the end value in the right primary key column takes effect only when the start value and the end value are the same within each leftmost primary key column. If the start value and the end value in a leftmost primary key column are different, the range that is specified by the start value and the end value in the right primary key column does not take effect.

   • The range is a left-open, right-closed interval.

   5. Configure the Max Versions parameter to specify the maximum number of versions to return.

   6. Set the Sequence parameter to Forward Search or Backward Search.

   7. Click OK.

   Delete data

   Important

   The data that you delete cannot be restored. Proceed with caution.

   1. On the Query Data tab, select the row of data that you want to delete. Click Delete.

   2. In the Delete message, click OK.

Step 5: Use SQL to query data

The SQL query feature is compatible with MySQL query syntax and supports table creation by using Data Definition Language (DDL) statements. For existing data tables, you can execute the CREATE TABLE statement to create mapping tables for the existing data tables. Then, you can use SQL statements to access the data in the existing data tables. For more information, see SQL query.

1. On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.

2. On the Query by Executing SQL Statement tab, create a mapping table.

   1. Click the icon.

      Note

      If no mapping table has been created, the Create Mapping Table dialog box appears when you click the Query by Executing SQL Statement tab.

      

   2. In the Create Mapping Table dialog box, configure the parameters that are described in the following table.

      Parameter	Description
      Table Type	The type of the table for which you want to create the mapping table. Valid values: Common Table (default): creates the mapping table for a data table. Time Series Table: creates the mapping table for a time series table.
      Table Name	The name of the table for which you want to create the mapping table.
      Mapping Mode	The mode in which the mapping table is created. This parameter is available only if you set the Table Type parameter to Common Table. Valid values: Mapping Table for Table (default): creates the mapping table for an existing data table. Mapping Table for Search Index: creates the mapping table for an existing search index.
      Advanced Settings	Specifies the consistency mode and whether to enable inaccurate aggregation for the mapping table. You can turn on Advanced Settings to configure the Consistency Mode and Inaccurate Aggregation parameters. The Advanced Settings parameter is available only if you set the Mapping Mode parameter to Mapping Table for Table.
      Consistency Mode	The consistency mode that is supported by the execution engine. This parameter is available only if you turn on Advanced Settings. Valid values: Eventual Consistency (default): The query results are in eventual consistency mode. You can query data a few seconds after the data is written to the table. Strong Consistency: The query results are in strong consistency mode. You can query data immediately after the data is written to the table.
      Inaccurate Aggregation	Specifies whether to improve the query performance by compromising the accuracy of aggregate operations. This parameter is available only if you turn on Advanced Settings. Valid values: Yes (default) No
      Search Index Name	The name of the search index for which you want to create the mapping table. This parameter is available only if you set the Mapping Mode parameter to Mapping Table for Search Index.
      Mapping Table Name	The name of the mapping table that you want to create. If you set the Table Type parameter to Common Table and the Mapping Mode parameter to Mapping Table for Table, the name of the mapping table is the same as the name of the data table and cannot be modified. If you set the Table Type parameter to Common Table and the Mapping Mode parameter to Mapping Table for Search Index, enter a name for the mapping table. If you set the Table Type parameter to Time Series Table, enter a name for the mapping table based on your business requirements. After a mapping table is created for a time series table, the system automatically adds the Time series table name:: prefix to the mapping table name.

   3. Click Generate SQL Statement.

      The system automatically generates the SQL statement to create the mapping table. Sample SQL statement:

      CREATE TABLE `exampletable` (
          `id` BIGINT(20),
          `colvalue` MEDIUMTEXT,
          `content` MEDIUMTEXT,
          PRIMARY KEY(`id`)
      );

      Important

      Make sure that the data types of fields in the mapping table match the data types of fields in the data table. For more information about data type mappings, see Data type mappings.

   4. After you modify the schema of the mapping table based on your business requirements, drag-select the SQL statement and click Execute SQL Statement(F8).

      After the execution is successful, the execution result is displayed in the Execution Result section.

      Important

      • When you create a mapping table, the schema that you specify for the mapping table must include the columns that are required for subsequent data query operations.

      • You must drag-select the SQL statement that you want to execute. If you do not drag-select an SQL statement, the system executes the first SQL statement.

      • You can drag-select only one SQL statement to execute at a time. If you execute multiple SQL statements at a time, the system reports an error.

      

3. Execute the SELECT statement to query data. For more information, see Query data.

FAQ

• What are the differences between Tablestore and traditional database services such as MySQL and SQL Server?

• What are the primary key, partitions, and partition key of a data table?

• What are the differences between tables, rows, columns, values, and spreadsheets in the Tablestore Wide Column model?

References

• You can use the Tablestore CLI to get started with the Wide Column model. For more information, see Use the Wide Column model in the Tablestore CLI.

• For more information about the limits of Tablestore and how to use Tablestore in the actual business, see Overview. For more information about how to select a cost-effective billing method based on your business requirements, see Billing overview.

• For more information about how to use the Wide Column model in the Tablestore console, see the following topics: Operations on a data table, Basic operations on data, Use the Tablestore console, Use secondary indexes in the Tablestore console, Use the SQL query feature in the Tablestore console, Quick start, Use the Tablestore console to deliver data to OSS, and View monitoring data in the Tablestore console.