Manage the Wide Column model in the Tablestore CLI - Tablestore

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

Step 1: Download and start the Tablestore CLI

Download the Tablestore CLI package based on your operating system.
Operating system Download link
Windows Windows10
Linux
Linux(AMD64)
Linux(ARM64)
macOS macOS

Operating system	Download link
Windows	Windows10
Linux	Linux(AMD64) Linux(ARM64)
macOS	macOS

Decompress the Tablestore CLI package that you downloaded. Go to the root directory of the Tablestore CLI 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 execute permissions to run the command in Linux or macOS, run the chmod 755 ts command to obtain permissions and start the Tablestore CLI.

The following code shows a sample Tablestore startup interface:

# 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

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

Run the config command to configure the AccessKey pair information.
Important An AccessKey pair, which consists of the AccessKey ID and the AccessKey secret of an Alibaba Cloud account, is required. For information about how to obtain an AccessKey pair, see Obtain an AccessKey pair.
```
config --id NTSVLeBHzgX2i************ --key 7NR2DiotscDbauohSq9kSHX8BDp9****************
```
Run the enable_service command to activate Tablestore.

Step 3: Create an instance

Run the create_instance command to create a high-performance instance.

The following sample code shows how to create a high-performance instance named myinstance in the China (Hangzhou) region:

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.

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.

Run the following command to create a data table named order:
```
create -t order --pk '[{"c":"id","t":"string"}]'
```
Run the following command to use the order data table:
```
use --wc -t order
```

Step 6: Perform data operations

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

Insert a row of data

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"}]'

Update a row of data

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

Read a row of data

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

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.
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 command, 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 data in a data table to return up to 10 rows of data:
```
scan --limit 10
```
Delete a row of data
Delete the row whose primary key column value is 000000114d884ca1dbd6b9a58e8d0d94.
```
delete --pk '["000000114d884ca1dbd6b9a58e8d0d94"]'
```
Export data
The following sample code shows how to export all data from the current table to the mydata.json local file:
```
scan -o /tmp/mydata.json
```
Export data from the pId, oId, and cName columns of the current table to the mydata.json local file:
```
scan -o /tmp/mydata.json -c pId,oId,cName
```

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

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

Run the sql command to enter the SQL mode.

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

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

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`)
);

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.