All Products
Search
Document Center

Tablestore:Use the Wide Column model in the Tablestore console

Last Updated:Jan 22, 2024

You can access Tablestore by using the Tablestore console, Tablestore CLI, or Tablestore SDKs. Before you use the Wide Column model in the Tablestore console, you must activate Tablestore. Then, you can use the Tablestore console to create instances and data tables, read data from and write data to the tables, and use the SQL query feature to query data.

Prerequisites

Obtain the logon information of the Alibaba Cloud account or Resource Access Management (RAM) user that is used to log on to Tablestore.

If you have not created an Alibaba Cloud account, create one on the Alibaba Cloud official website.

To use Tablestore as a RAM user, you must create a RAM user by using your Alibaba Cloud account and grant the RAM user the permissions to access Tablestore. For more information, see Use a RAM policy to grant permissions to a RAM user.

Step 1: Activate Tablestore

If Tablestore is activated, skip this operation. 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 Tablestore (Pay-as-you-go) Agreement of Service and click Activate Now.

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

    You can also click Console in the upper-right corner of the homepage. Click the fig_tablestore_control_001 icon. In the left-side navigation pane, choose Products and Services > Storage > Basic Storage Services > Table Store to go to the Tablestore console.

Step 2: Create an instance

Instances are the basic unit of resource management in Tablestore. Tablestore controls whether applications can access a Tablestore instance and collects statistics about the resources that are used by applications at the instance level. After an instance is created, 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 Instance.

  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

    Allowed 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 need to access 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 specify 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 the order of 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 by primary key columns.

    Allow Updates

    Specifies whether to allow data writes by using UpdateRow. The default value is Yes, which indicates that UpdateRow is allowed to write data.

    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 TTL of search indexes.

    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. Valid value: CMK of KMS. After you specify this parameter, Tablestore automatically creates a customer master key (CMK) in Key Management Service (KMS) to encrypt and decrypt data. This parameter is displayed only when you turn on Encryption.

  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 pay-as-you-go high-performance instance, you can specify the reserved read and write throughput for data tables. For more information, see the "Reserved read and write throughput" section of the Read and write throughput topic.

    1. Turn on Advanced Settings.

    2. Configure the advanced parameters that are described in the following table.

      Parameter

      Description

      Time To Live

      The retention period of data in the data table. If the retention period of data exceeds the TTL value, the data expires and Tablestore automatically deletes the 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 attribute columns of the data table. If the number of versions of data in attribute columns exceeds the value of this parameter, the system deletes the data of earlier versions.

      The value of this parameter must be a positive integer.

      Max 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 displayed 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 attributes as query conditions to query data, you can set these attributes 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. Both 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 only 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 fig_tablestore_002 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 Add Column 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, modify the types and values of primary key columns, add or remove attribute columns, or update data in or delete data from attribute columns.

      • You can click Add Column to add an attribute column. You can also click the fig_tablestore_005 icon in the Actions column of an attribute column to delete the attribute column.

      • In the first Actions column, if you select Update, you can modify the data in attribute columns. If you select Delete, 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 data in a single row or within the specified range in the Tablestore console based on your business requirements.

    Read data in a single row

    1. On the Query Data tab, click Search.

    2. In the Search dialog box, set the Modes 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 specified attribute columns, turn off All Columns and enter the attribute columns to return. Separate multiple attribute columns 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 values affect the query results.

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

    6. Click OK.

    Read data within the specified range

    1. On the Query Data tab, click Search.

    2. In the Search dialog box, set the Modes 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 specified attribute columns, turn off All Columns and enter the attribute columns to return. Separate multiple attribute columns with commas (,).

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

    5. Important
      • If you use the range query mode, the values in the first primary key column take priority. If the values of the start primary key column and the end primary key column are the same in the first primary key column, the system uses the values in the second primary key column to perform queries. The query rules for subsequent primary key columns are the same as the query rules for the first two primary key columns.

      • The range that is supported for range queries is a left-open, right-closed interval.

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

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

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

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

      image.png

    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.

      fig_sqlquery

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

FAQ

References