Tablestore:Use the Wide Column model in the Tablestore CLI

Prerequisites

Obtain the logon information of the Alibaba Cloud account or (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.

If you want to use the Tablestore CLI to access Tablestore, you must create an AccessKey pair for your Alibaba Cloud account or the RAM user. For more information, see Obtain an AccessKey pair.

Usage notes

You can use the Tablestore CLI to create only pay-as-you-go high-performance instances. To create pay-as-you-go capacity instances, use the Tablestore console. For more information, see the Step 2: Create an instance section of the Use the Wide Column model in the Tablestore console topic.

Step 1: Download and start the Tablestore CLI

1. Download the Tablestore CLI package based on your operating system.

   Operating system	Download link
   Windows	Windows 10 or later
   Linux	Linux (AMD64) Linux (ARM64)
   macOS	macOS (AMD64) macOS (ARM64)

2. Decompress the Tablestore CLI package that you downloaded. Go to the root directory of the package and select a method that is used to start the Tablestore CLI based on your operating system.

   • For Windows, double-click the ts.exe file.

   • For Linux or macOS, run the ./ts command.

     Note

     If you do not have the required permissions to run the command in Linux or macOS, run the chmod 755 ts command to obtain the permissions and then run the command to start the Tablestore CLI.

   The following information is displayed upon startup:

   # Welcome to use Command Line Tool for Aliyun Tablestore. Current Version is '2021-11-11'.
   #    _______      _      _              _
   #   |__   __|    | |    | |            | |
   #      | |  __ _ | |__  | |  ___   ___ | |_   ___  _ __    ___
   #      | | / _' || '_ \ | | / _ \ / __|| __| / _ \ | '__| / _ \
   #      | || (_| || |_) || ||  __/ \__ \| |_ | (_) || |   |  __/
   #      |_| \__,_||_.__/ |_| \___| |___/ \__| \___/ |_|    \___|
   #
   # Please visit our product website: https://www.aliyun.com/product/ots
   # You can also join our DingTalk Chat Group (ID: 11789671 or 23307953) to discuss and ask Tablestore related questions.
   #
   tablestore>

Step 2: Activate Tablestore

Before using Tablestore features, you must activate Tablestore. If you have activated Tablestore, you do not need to activate it again. You need to activate Tablestore only once. You are not charged when you activate Tablestore.

1. Run the config command to configure the AccessKey pair information. For more information, see Start the Tablestore CLI and configure access information.

   Important

   An AccessKey pair, which consists of the AccessKey ID and the AccessKey secret of an Alibaba Cloud account, is required. For more information about how to obtain an AccessKey pair, see Obtain an AccessKey pair.

   config --id NTSVLeBHzgX2i************ --key 7NR2DiotscDbauohSq9kSHX8BDp9****************

2. Run the enable_service command to activate Tablestore.

Step 3: Create an instance

Run the create_instance command to create a high-performance instance. For more information, see Instance operations.

create_instance -d "First instance created by CLI." -n myinstance -r cn-hangzhou

Step 4: Configure access information

Run the config command to configure access information. For more information, see Start the Tablestore CLI and configure access information.

The following sample code shows how to configure access information for the instance named myinstance:

config --endpoint https://myinstance.cn-hangzhou.ots.aliyuncs.com --instance myinstance --id NTSVLeBHzgX2iZfcaXXPJ**** --key 7NR2DiotscDbauohSq9kSHX8BDp99bjs7eNpCR7o****

Step 5: Create and use a data table

After a data table is created, you can perform operations on the table or data in the table. For more information, see Operations on data tables.

1. Run the following command to create a data table named order:

   create -t order --pk '[{"c":"id","t":"string"}]'

2. Run the following command to use the data table named order:

   use --wc -t order

Step 6: Perform operations on data

You can insert, update, read, and delete a row of data, and import or export data based on your business requirements. For more information, see Operations on data.

• Insert a row of data.

  The following sample code shows how to insert a row of data into a data table:

  put --pk '["000000114d884ca1dbd6b9a58e8d0d94"]' --attr '[{"c":"pBrand","v":"brand1"},{"c":"pPrice","v":1599.0},{"c":"payTime","v":1509615334404,"isint":true},{"c":"totalPrice","v":2498.99},{"c":"sName","v":"Tom"},{"c":"pId","v":"p0003004"},{"c":"oId","v":"o0039248410"},{"c":"hasPaid","v":true},{"c":"sId","v":"s0015"},{"c":"orderTime","v":1509614885965,"isint":true},{"c":"pName","v":"brand1 type"},{"c":"cName","v":"Alice"},{"c":"pType","v":"Mobile phone"},{"c":"pCount","v":1,"isint":true},{"c":"cId","v":"c0018"}]'

• Read a row of data.

  The following sample code shows how to read the row whose primary key column value is 000000114d884ca1dbd6b9a58e8d0d94:

  get --pk '["000000114d884ca1dbd6b9a58e8d0d94"]'

  The following result is returned:

  +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-------------+--------+-------+---------------+-------+--------+------------+
  | id                               | cId   | cName  | hasPaid | oId         | orderTime     | pBrand | pCount | pId      | pName       | pPrice | pType | payTime       | sId   | sName  | totalPrice |
  +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-------------+--------+-------+---------------+-------+--------+------------+
  | 000000114d884ca1dbd6b9a58e8d0d94 | c0018 | Alice | true    | o0039248410 | 1509614885965 | brand1 | 1      | p0003004 | brand1 type | 1599   | Mobile phone  | 1509615334404 | s0015 | Tom | 2498.99    |
  +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-------------+--------+-------+---------------+-------+--------+------------+

• Update a row of data.

  The following sample code shows how to update the row whose primary key column value is 000000114d884ca1dbd6b9a58e8d0d94. Data is inserted regardless of whether the row exists. If the row exists, the inserted data overwrites the existing data.

  update --pk '["000000114d884ca1dbd6b9a58e8d0d94"]' --attr '[{"c":"pBrand","v":"brand2"},{"c":"pPrice","v":1599.0},{"c":"payTime","v":1509615334404,"isint":true},{"c":"totalPrice","v":2498.99},{"c":"sName","v":"Tom"},{"c":"pId","v":"p0003004"},{"c":"oId","v":"o0039248410"},{"c":"hasPaid","v":true},{"c":"sId","v":"s0015"},{"c":"orderTime","v":1509614885965,"isint":true},{"c":"pName","v":"brand2 type"},{"c":"cName","v":"Alice"},{"c":"pType","v":"Mobile phone"},{"c":"pCount","v":1,"isint":true},{"c":"cId","v":"c0018"}]'  --condition ignore

• Import data.

  Download the sample data package to your local device, decompress the package, and then run the import command to import the data in a batch.

  Note

  The sample data file contains a total of 1 million rows of order data. You can specify the number of rows that you want to import by using the import -l parameter.

  The following sample code shows how to import 50,000 rows of order data in the sample data file to the current table and use the current time as the timestamp. In the code, yourFilePath specifies the path where the sample data package is decompressed. Example: D:\\order_demo_data_1000000\\order_demo_data_1000000.

  import -i yourFilePath --ignore_version -l 50000

  The following result is returned:

  Current speed is: 15800 rows/s. Total succeed count 15800, failed count 0.
  Current speed is: 27400 rows/s. Total succeed count 43200, failed count 0.
  Import finished, total count is 50000, failed 0 rows.

• Scan data.

  The following sample code shows how to scan up to 10 rows of data in a data table:

  scan --limit 10

• Delete a row of data.

  The following sample code shows how to delete the row whose primary key column value is 000000114d884ca1dbd6b9a58e8d0d94:

  delete --pk '["000000114d884ca1dbd6b9a58e8d0d94"]'

• Export data.

  You can export data from the data table to a local JSON file.

  The following sample code shows how to export data from the pId, oId, and cName columns of the current table to the local file mydata.json:

  scan -o /tmp/mydata.json -c pId,oId,cName

Step 7: Execute SQL statements 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.

To use SQL statements to quickly query data in a table, perform the following steps:

1. Run the sql command to enter the SQL mode.

2. Run the following command to create a mapping table for the data table named order.

   Note

   For more information about the field type mappings between data tables and mapping tables, see Data type mappings in SQL.

   CREATE TABLE `order` (
       `id` VARCHAR(1024),
       `cId` MEDIUMTEXT,
       `cName` MEDIUMTEXT,
       `hasPaid` BOOL,
       `oId` MEDIUMTEXT,
       `orderTime` BIGINT(20),
       `pBrand` MEDIUMTEXT,
       `pCount` BIGINT(20),
       `pId` MEDIUMTEXT,
       `pName` MEDIUMTEXT,
       `pPrice` DOUBLE,
       `pType` MEDIUMTEXT,
       `payTime` BIGINT(20),
       `sId` MEDIUMTEXT,
       `sName` MEDIUMTEXT,
       `totalPrice` DOUBLE,
       PRIMARY KEY(`id`)
   );

3. Query data in the order data table based on your business requirements.

   • Example 1: Query 10 orders in which the salesperson name is Tom and sort the orders in ascending order by order amount.

     SELECT * FROM `order` WHERE sName = "Tom" ORDER BY totalPrice ASC LIMIT 10;

   • Example 2: Count the number of orders in which the product type is Mobile phone.

     SELECT count(*) FROM `order` WHERE pType = "Mobile phone";

   • Example 3: Count the number of orders in which the number of products is greater than 1.

     SELECT count(*) FROM `order` WHERE pCount > 1;

If you want to exit the SQL mode, run the exit; command.

References

• You can quickly get started with the Wide Column model in the Tablestore console. For more information, see Use the Wide Column model in the Tablestore console.

• 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 CLI, see Instance operations, Operations on data tables, Operations on data, Secondary index, Search index, Tunnel Service, SQL query, and View options.