Tablestore:Use the TimeSeries model in the Tablestore CLI

Usage notes

• The TimeSeries model is supported in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Hong Kong), Germany (Frankfurt), US (Virginia), India (Mumbai), and Singapore.

• 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 or the Create an instance for the TimeSeries model topic.

If you have questions about using the Tablestore CLI, contact technical support in the DingTalk group (ID 44327024), which is the group for IoTstore developers.

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 and later versions
   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 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.

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

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.

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 time series table

After you create a time series table, you can select the time series table as the table to use. Then, you can manage the table or data in the table.

1. Run the following command to create a time series table named mytable:

   create -m timeseries -t mytable --ttl -1

2. Run the use --ts -t mytable command to use the time series table named mytable.

Step 6: Perform operations on data

Perform operations on data based on your business requirements. You can write time series data, import time series data, query time series data, retrieve a time series, scan a time series, and update a time series.

• Import time series data

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

  import_timeseries --input /temp/import_timeseries.txt

  The following example shows 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

• Query time series data

  Query all time series data that was generated before 1667638230000000 in the time series whose metric name is cpu, data source is host_0, and tags are "hostname=host_0" and "region=cn-hangzhou."

  getts --k '["cpu","host_0",["hostname=host_0","region=cn-hangzhou"]]' --time_start 0 --time_end 1667638230000000 --limit 100

• Retrieve a time series

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

  query_ts_meta --measurement cpu --datasource host_0 --limit 10

• Scan a time series

  query_ts_meta --limit 10

• Update a time series

  Modify the properties of the specified time series to "hostname=host_1" and "region=cn-hangzhou."

  update_ts_meta --k '["cpu","host_1",["hostname=host_1","region=cn-hangzhou"]]' --attrs '["hostname=host_1","region=cn-hangzhou"]' 

Step 7: Use SQL to query data

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. Query data in the mytable table based on your business requirements.

   • To retrieve a time series, select a mapping table for metadata of time series and execute the SELECT statement to query the time series.

     For example, if you want to query the time series whose metric name is cpu by using the mytable::meta mapping table for metadata of time series, execute the following SQL statement:

     SELECT * FROM `mytable::meta` WHERE _m_name = "cpu" LIMIT 100;

   • To query time series data by using the mapping table in the single-value model, select the mapping table in the single-value model and execute the SELECT statement to query time series data that meets the query conditions.

     For example, if you want to query the data whose metric name is cpu by using the mytable mapping table in the single-value model, execute the following SQL statement:

     SELECT * FROM mytable WHERE _m_name = "cpu" LIMIT 10;

   • To query time series data by using a mapping table in the multi-value model, create a mapping table in the multi-value model and execute the SELECT statement to query time series data that meets the query conditions.

     For example, if you want to query the data whose usage_idle metric is greater than 20 by using the mytable::muti_model1 mapping table in the multi-value model, execute the following SQL statement:

     SELECT usage_idle,usage_user,usage_system FROM `mytable::muti_model1` WHERE usage_idle > 20 LIMIT 10;

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

References

• You can also get started with the TimeSeries model by using the Tablestore console. For more information, see Use the TimeSeries model in the Tablestore console.

• For more information about the limits of and procedure for using the TimeSeries model in actual business scenarios, see Overview and Use the TimeSeries model by using Tablestore SDKs. For more information about how to select a cost-effective billing method based on your business characteristics, see the "Recommended solutions" section of the Billing overview topic.

• For more information about the operations that you can perform on the TimeSeries model in the Tablestore CLI, see the following topics: Operations on a time series table, Operations on time series data, SQL query, and View options.