All Products
Search

This Product

    Tablestore:Use the Wide Column model in the Tablestore console

    Document Center

    Tablestore:Use the Wide Column model in the Tablestore console

    Last Updated:Oct 31, 2023

    You can access Tablestore by using the Tablestore console, Tablestore CLI, or Tablestore SDKs. You can get started with Tablestore in the Tablestore console. This topic describes how to use the Wide Column model in the Tablestore console.

    Prerequisites

    Before you use Tablestore, make sure that you have an Alibaba Cloud account and have completed the real-name verification. If you do not have an Alibaba Cloud account, create an Alibaba Cloud account as prompted when you activate Tablestore.

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

      1. Turn on Advanced Settings.

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

        Parameter

        Description

        Time To Live

        The duration during which the data in the data table can be retained. 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 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 for an attribute column is 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 less than or equal to 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

        This parameter is unavailable to capacity instances.

        This parameter specifies 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

      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. For more information, see Overview.

    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 to delete an 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. Set the Modes parameter to GetRow and select a 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. Set the Modes parameter to Range Search and select a 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. Note

        • If you set the Modes parameter 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 and 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.

    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 is created, the Create Mapping Table dialog box is displayed when you click the Query by Executing SQL Statement tab.
        fig_createtablemapping
      2. In the Create Mapping Table dialog box, configure the parameters. The following table describes the parameters.
        ParameterDescription
        Table nameThe name of the data table for which you want to create the mapping table if you select Table mapping for Mapping mode or the name of the data table whose search index for which you want to create the mapping table if you select Multivariate Index mapping for Mapping mode.
        Mapping modeThe mode in which the mapping table is created. Default value: Table mapping. Valid values:
        • Table mapping: creates a mapping table for an existing data table.
        • Multivariate Index mapping: creates a mapping table for an existing search index.
        Advanced optionsSpecifies options for the consistency mode and whether to enable inaccurate aggregation for the mapping table. You can turn on Advanced options to configure the Consistency mode and Inaccurate aggregation parameters. The Advanced options parameter is available only if you select Table mapping for Mapping mode.
        Consistency modeThe consistency mode that is supported by the execution engine. Default value: Final agreement. Valid values:
        • Final agreement: 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.

        You can configure this parameter only if you turn on Advanced options.

        Inaccurate aggregationSpecifies whether query performance can be improved by compromising the accuracy of aggregate operations. Default value: Yes. Valid values:
        • Yes
        • No

        You can configure this parameter only if you turn on Advanced options.

        Search index tableThe name of the search index for which you want to create the mapping table. You can configure this parameter only if you select Multivariate Index mapping for Mapping mode.
        Map table nameThe name of the mapping table that you want to create.
        • If you select Table mapping for Mapping mode, the value of this parameter is the same as the value of the Table name parameter and cannot be modified.
        • If you select Multivariate Index mapping for Mapping mode, specify a value for this parameter.
      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 field data types in the mapping table match the field data types in the data table. For information about data type mappings, see Data type mappings.
      4. After you modify the schema of the mapping table based on your business requirements, hold down the left mouse button to select an 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 the mapping table, the schema that you specify for the mapping table must include the columns that are required for subsequent data query operations.
        • Before you execute an SQL statement, select the SQL statement that you want to execute. If you do not select an SQL statement, the system executes the first SQL statement.
        • You cannot execute multiple SQL statements 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.