Perform operations on data in a time series table by using the Tablestore CLI - Tablestore

The Tablestore CLI allows you to write and import time series data to time series tables and query time series data in time series tables. You can also use the Tablestore CLI to retrieve, scan, and update time series in time series tables.

Note

Before you perform data operations on a time series table, use the table. For more information, see Use a time series table.

Write time series data

Write time series data to a time series table.

Command syntax

putts --k '["measurement_name","data_source",["tagKey1=tagValue1","tagKey2=tagValue2"]]' --field '[{"c":"fieldname","v":"fieldvalue"},{"c":"bool_field","v":true},{"c":"double_field","v":1.1},{"c":"int_value","v":10,"isint":true}]' --time 1635162859000000

The following table describes the parameters in the command.

Parameter	Required	Example	Description
--k	Yes	'["cpu","localhost",["region=hangzhou","os=ubuntu"]]'	The identifiers of the time series. The value of this parameter is an array that contains the following content: Measurement name: the metric name. In this example, the metric name is cpu. Data source: the data source from which data is generated. In this example, the data source is localhost. Tags: the tags. The value is an array in which each tag consists of tagKey and tagValue in the format of tagKey=tagValue.
--field	Yes	'[{"c":"fieldname","v":"fieldvalue"},{"c":"bool_field","v":true},{"c":"double_field","v":1.1},{"c":"int_value","v":10,"isint":true}]'	The data column of time series data, which is in the JSON format and contains the following content: c: This parameter is required and specifies the name of the data column. v: This parameter is required and specifies the value of the data column. The value of this parameter can be of the string, numeric, or Bool type. isint: This parameter is optional and specifies whether the value of the numeric data column is converted to a value of the integer type. The value of this parameter is of the Bool type. Valid values: true and false. The default value is false, which specifies that the value of the numeric data column is converted to a value of the floating-point type. If you set this parameter to true, the value of the numeric data column is converted to a value of the integer type.
--time	No	1635162859000000	The time when the row of time series data is generated. The value of this parameter is a timestamp. Unit: microseconds (μs).

Example

Insert a row of time series data.

putts --k '["cpu","localhost",["region=hangzhou","os=ubuntu"]]' --field '[{"c":"fieldname","v":"fieldvalue"},{"c":"bool_field","v":true},{"c":"double_field","v":1.1},{"c":"int_value","v":10,"isint":true}]' --time 1635162859000000

Import time series data

Import time series data from a local file to a time series table.

Command syntax

Windows

import_timeseries --input D:\\localpath\\filename.txt

Linux and macOS

import_timeseries --input /localpath/filename.txt

The following table describes the parameters in the command.
Parameter
Required
Example
Description
-i, --input
Yes
/temp/import_timeseries.txt
The configuration file from which you want to import time series data.

The following sample code provides an example of the content of a configuration file:

cpu,hostname=host_0,region=cn-hangzhou usage_user=58i,usage_system=2i,usage_idle=24i 1609459200000000000
cpu,hostname=host_1,region=cn-hangzhou usage_user=58i,usage_system=2i,usage_idle=24i 1609459200000000000

Each row in the configuration file is a row of time series data in the format of measurement_name,tags fields timestamp. The following table describes the parameters.

Parameter	Required	Example	Description
measurement_name	Yes	cpu	The metric type.
tags	Yes	hostname=host_0,region=cn-hangzhou	The tags. Each tag consists of tagKey and tagValue in the format of tagKey=tagValue. The tagValue of the first tag specifies the data source from which the data is generated. In this example, host_0 specifies the data source of the time series data in the row.
fields	Yes	usage_user=58i,usage_system=2i,usage_idle=24i	The data column of the time series data. Each data column consists of columnKey and columnValue in the format of columnKey=columnValue. The value of columnValue can be of the integer or floating-point type. If you add i to the end of the value, the data type of the value is integer. Otherwise, the data type of the value is floating-point.
timestamp	Yes	1609459200000000000	The time when the row of time series data is generated. The value of this parameter is a timestamp. Unit: nanoseconds (ns). Important After data is imported to the time series table, the timestamp in nanoseconds in the configuration file is automatically converted into a timestamp in microseconds.

Example

Import the time series data from the import_timeseries.txt file to a time series table.

import_timeseries --input /temp/import_timeseries.txt

Query time series data

Query the time series data in a specific time range.

Command syntax

getts --k '["measurement_name","data_source",["tagKey1=tagValue1","tagKey2=tagValue2"]]' --time_start 0 --time_end 1635162900000000 --limit 100

The following table describes the parameters in the command.

Parameter	Required	Example	Description
--k	Yes	'["cpu","localhost",["region=hangzhou","os=ubuntu"]]'	The identifiers of the time series. The value of this parameter is an array that contains the following content: Measurement name: the metric name. In this example, the metric name is cpu. Data source: the data source from which data is generated. In this example, the data source is localhost. Tags: the tags. The value is an array in which each tag consists of tagKey and tagValue in the format of tagKey=tagValue.
--time_start	Yes	0	The beginning of the time range to query.
--time_end	Yes	1667638230000000	The end of the time range to query.
--limit	No	100	The maximum number of rows to return. Valid values: 1 to 5000. The actual number of returned rows is determined by the server.

Example

Query all time series data that is generated before 1667638230000000 in the time series whose metric name is cpu, data source is localhost, and tags are "region=hangzhou" and "os=ubuntu".

getts --k '["cpu","localhost",["region=hangzhou","os=ubuntu"]]' --time_start 0 --time_end 1667638230000000 --limit 100

Retrieve time series

Retrieve the time series that meet the specified conditions.

Command syntax

Note

You can abbreviate query_ts_meta in the command as qtm.

query_ts_meta --measurement measurement_name --datasource data_source --limit 10

The following table describes the parameters in the command.

Parameter	Required	Example	Description
--measurement	No	cpu	The name of the metric.
--datasource	No	localhost	The data source.
--limit	No	100	The maximum number of rows to return. Valid values: 1 to 1000. The actual number of returned rows is determined by the server.
-e, --edit	No	{"measurement":"cpu","data_source":"localhost"}	The query condition in the JSON format.

You can also run the qtm -e command and specify the query conditions to perform a complex query. The following example shows the query conditions:

{
    "Type":"COMPOSITE",
    "QueryCondition":{
        "Operator":"AND",
        "SubConditions":[
            {
                "Type":"MEASUREMENT",
                "QueryCondition":{
                    "Operator":"EQUAL",
                    "Value":"CPU"
                }
            },
            {
                "Type":"SOURCE",
                "QueryCondition":{
                    "Operator":"EQUAL",
                    "Value":"127.0.0.1"
                }
            },
            {
                "Type":"TAG",
                "QueryCondition":{
                    "Operator":"GREATER_EQUAL",
                    "TagName":"Region",
                    "Value":"Jiangning"
                }
            }
        ]
    }
}

Example

Retrieve the time series whose metric name is cpu and data source is localhost.

query_ts_meta --measurement cpu --datasource localhost --limit 10

Scan time series

Obtain all time series or a specified number of time series in a time series table.

Command syntax

Note

You can abbreviate query_ts_meta in the command as qtm.

query_ts_meta --limit limit

The following table describes the parameters in the command.

Parameter	Required	Example	Description
--limit	No	10	The maximum number of rows to return for this scan. If you do not configure this parameter, all data in the table is scanned.

Example

query_ts_meta --limit 10

Update time series

Modify the properties of a time series. If the time series metadata that you want to update does not exist, the time series metadata is added.

Command syntax

Note

You can abbreviate update_ts_meta in the command as utm.

update_ts_meta --k '["measurement_name","data_source",["tag1=value1","tag2=value2"]]' --attrs '["key1=value1","key2=value2"]'

The following table describes the parameters in the command.

Parameter

Required

Example

Description

--k

Yes

'["cpu","localhost",["region=hangzhou","os=ubuntu"]]'

The identifiers of the time series. The value of this parameter is an array that contains the following content:

Measurement name: the metric name. In this example, the metric name is cpu.
Data source: the data source from which data is generated. In this example, the data source is localhost.
Tags: the tags. The value is an array in which each tag consists of tagKey and tagValue in the format of tagKey=tagValue.

--attrs

'["city=nanjing","region=jiangning"]'

The properties of the time series. The value is an array in which each tag consists of a key and a value in the format of key=value.

Example

Modify the properties of the specified time series to "city=nanjing" and "region=jiangning".

update_ts_meta --k '["cpu","localhost",["city=hangzhou","region=xihu"]]' --attrs '["city=nanjing","region=jiangning"]'