Read and write data with the Tablestore CLI - Tablestore

Prerequisites

The Tablestore CLI installed and access credentials configured. For more information, see Tablestore CLI.
A data table created. For more information, see Data table operations.

Before you run data operations, run the following command to select the target data table:

use -t <tableName>

Insert data

Insert a row of data into a data table.

put --pk '<pk_values>' --attr '<attr_json>'

Parameter	Required	Description
-k, --pk	Yes	The primary key values of the data table, represented as a JSON array. Important The number and types of primary key columns must match those of the data table. If a primary key column is an auto-increment column, set its value to the placeholder null.
-a, --attr	Yes	The attribute columns, represented as a JSON array. Each attribute column contains the following fields: c (required): The attribute column name. v (required): The attribute column value. t (optional): The attribute column type. Valid values: integer, string (UTF-8 encoded string), binary, Boolean, and double. This field is required when the attribute column type is binary. ts (optional): The timestamp, which is the data version. If you do not set this parameter, Tablestore generates a timestamp.
--condition	No	The row existence condition. Conditional update is supported. Valid values: ignore (default): Inserts data regardless of whether the row exists. Existing rows are overwritten. exist: Inserts data only if the row exists. The existing data is overwritten. not_exist: Inserts data only if the row does not exist.
-i, --input	No	Inserts data from a JSON configuration file. When you use a configuration file, you do not need to specify --pk and --attr.

To insert data from a configuration file, run the following command:

Windows

put -i D:\\localpath\\filename.json

Linux and macOS

put -i /localpath/filename.json

Configuration file example:

{
    "PK":{
        "Values":["86", 6771]
    },
    "Attr":{
        "Values":[
            {"C":"age", "V":32, "TS":1626860801604, "IsInt":true}
        ]
    }
}

Examples

Insert a row. The first primary key column value is "86" and the second primary key column value is 6771. The row has two attribute columns: name and country.
```
put --pk '["86", 6771]' --attr '[{"c":"name", "v":"redchen"}, {"c":"country", "v":"china"}]'
```

Insert data only if the row does not exist.

put --pk '["86", 6771]' --attr '[{"c":"name", "v":"redchen"}, {"c":"country", "v":"china"}]' --condition not_exist

Read data

Read a row of data by primary key.

Note

If the specified row does not exist, an empty result is returned.

get --pk '<pk_values>'

Parameter	Required	Description
-k, --pk	Yes	The primary key values of the data table, represented as a JSON array. The number and types of primary key columns must match those of the data table.
-c, --columns	No	The columns to read. Specify primary key columns or attribute columns, separated by commas. If you do not set this parameter, all columns are returned.
--max_version	No	The maximum number of data versions to read.
--time_range_start	No	Reads data within the specified version number range. time_range_start and time_range_end specify the start and end timestamps respectively. The range is a half-open interval [start, end).
--time_range_end	No
--time_range_specific	No	Reads data of a specific version number.
-o, --output	No	Outputs the query result to a local file.

Example

Read the row where the first primary key column value is "86" and the second primary key column value is 6771.

get --pk '["86", 6771]'

Update data

Update the attribute columns of a row in a data table.

update --pk '<pk_values>' --attr '<attr_json>'

Parameter	Required	Description
-k, --pk	Yes	The primary key values of the data table, represented as a JSON array. The number and types of primary key columns must match those of the data table.
--attr	Yes	The attribute columns, represented as a JSON array. Each attribute column contains the following fields: c (required): The attribute column name. v (required): The attribute column value. t (optional): The attribute column type. Valid values: integer, string (UTF-8 encoded string), binary, Boolean, and double. This field is required when the attribute column type is binary. ts (optional): The timestamp, which is the data version. If you do not set this parameter, Tablestore generates a timestamp.
--delete	No	The name of the attribute column to delete. Use this parameter with `--attr '[]'` to delete the specified attribute column without updating other columns. Both --delete and --attr are required when you delete an attribute column.
--condition	No	The row existence condition. Conditional update is supported. Valid values: ignore (default): Updates data regardless of whether the row exists. exist: Updates data only if the row exists. not_exist: Inserts data only if the row does not exist.
-i, --input	No	Updates data from a JSON configuration file. When you use a configuration file, you do not need to specify --pk and --attr.

To update data from a configuration file, run the following command:

Windows

update -i D:\\localpath\\filename.json

Linux and macOS

update -i /localpath/filename.json

The configuration file format is the same as for inserting data.

Examples

Update the row where the first primary key column value is "86" and the second primary key column value is 6771.

update --pk '["86", 6771]' --attr '[{"c":"name", "v":"redchen"}, {"c":"country", "v":"china"}]' --condition ignore

Delete the aaa attribute column from the row where the primary key value is "abc".
```
update --pk '["abc"]' --delete aaa --attr '[]'
```

Delete data

Delete a row of data by primary key.

delete --pk '<pk_values>'

Parameter	Required	Description
-k, --pk	Yes	The primary key values of the data table, represented as a JSON array. The number and types of primary key columns must match those of the data table.

Example

Delete the row where the first primary key column value is "86" and the second primary key column value is 6771.

delete --pk '["86", 6771]'

Scan data

Scan and retrieve all data or a specified number of rows from a data table.

scan --limit <n>

Parameter	Required	Description
--limit	No	The maximum number of rows to return. If you do not set this parameter, all rows in the table are scanned.

Example

Scan and retrieve up to 10 rows from a data table.

scan --limit 10

Export data

Export data from a data table to a local JSON file.

scan -o /localpath/filename.json

Parameter	Required	Description
-o, --output	Yes	The local file path to export data to.
-c, --columns	No	The columns to export. Specify primary key columns or attribute columns, separated by commas. If you do not set this parameter, all columns are exported.
--max_version	No	The maximum number of data versions to export.
--time_range_start	No	Exports data within the specified version number range. time_range_start and time_range_end specify the start and end timestamps respectively. The range is a half-open interval [start, end).
--time_range_end	No
--time_range_specific	No	Exports data of a specific version number.
--backward	No	Exports data in descending order by primary key.
-l, --limit	No	The maximum number of rows to export.
-b, --begin	No	The start and end points of the export range. The primary key range is a half-open interval [start, end). Note If --begin is set to [null,null], the start point is [INF_MIN,INF_MIN], which means starting from the minimum value of each primary key column. If --end is set to [null,null], the end point is [INF_MAX,INF_MAX], which means ending at the maximum value of each primary key column.
-e, --end	No

Examples

Export all data from the current table to a local file named mydata.json.
```
scan -o /tmp/mydata.json
```
Export the uid and name columns only.
```
scan -o /tmp/mydata.json -c uid,name
```

Export data within a specified primary key range.

scan -o /tmp/mydata.json -b '["86", 6771]' -e '["86", 6775]'

Import data

Import data from a local JSON file into a data table.

Important

If the file path contains Chinese characters, the import fails.

import -i /localpath/filename.json

Parameter	Required	Description
-a, --action	No	The import mode. Valid values: put (default): If the row exists, the original row data (all columns and all versions) is deleted first, and then the new row data is written. update: If the row exists, you can add or delete attribute columns, delete data of specific versions, or update attribute column values. If the row does not exist, a new row is inserted.
-i, --input	Yes	The path to a local JSON data file. Important The number and types of primary key columns must match those of the data table. If a primary key column is an auto-increment column, set its value to the placeholder null.
--ignore_version	No	Ignores the timestamp check and uses the current time as the timestamp.

Data file format (one JSON record per line):

{"PK":{"Values":["redchen",0]},"Attr":{"Values":[{"C":"country","V":"china0"},{"C":"name","V":"redchen0"}]}}
{"PK":{"Values":["redchen",1]},"Attr":{"Values":[{"C":"country","V":"china1"},{"C":"name","V":"redchen1"}]}}

Examples

Import data from the mydata.json file into the current table.
```
import -i /tmp/mydata.json
```
Import data and use the current time as the timestamp.
```
import -i /tmp/mydata.json --ignore_version
```