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 (Chengdu), China (Hong Kong), Japan (Tokyo), Malaysia (Kuala Lumpur), Germany (Frankfurt), Indonesia (Jakarta), UK (London), US (Silicon Valley), US (Virginia), SAU (Riyadh - Partner Region), and Singapore.

• You can use the Tablestore CLI to create only high-performance instances in on-demand mode. To create capacity instances in on-demand mode, use the Tablestore console. For more information, see Step 2: Create an instance or Create an instance for the TimeSeries model.

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.

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

   2. For Linux and 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.

   If the "ts" cannot be opened because Apple cannot check it for malicious software message appears in macOS, you must specify that the ts command is allowed to run in the Privacy & Security setting of macOS.

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

  The following sample code provides an example on how to import time series data from the import_timeseries.txt file to a time series table:

  import_timeseries --input /temp/import_timeseries.txt

  Sample configurations in the import_timeseries.txt 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

  The following sample code provides an example on how to query the time series data that is 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

  The following sample code provides an example on how to retrieve a 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

  The following sample code provides an example on how to update the attributes of a 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 query a time series, select a mapping table for time series metadata and execute the SELECT statement.

     For example, if you want to query the time series whose metric name is cpu by using the mytable::meta mapping table for time series metadata, 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_model 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 Billing overview.

• 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.