After you use the Tablestore CLI to create a time series table, you can run the CLI commands to write time series data to the time series table, and retrieve time series and query time series data in the time series table. You can also use SQL statements to retrieve time series and query time series data in the time series table.

Prerequisites

Sample scenario

The following example shows how to use the TimeSeries model in the Internet of vehicles (IoV) scenario. In this example, a time series table named car_data is used. The table records the states of vehicles and contains the measurement, data source, tags, timestamp, and fields columns. The following figure shows the schema of the car_data time series table.

fig_20211124_timeseries

Operations on a time series table

  1. Run the create command to create a time series table named car_data. 
    create -m timeseries -t car_data
  2. Run the use --ts command to select the car_data time series table. 
    use --ts -t car_data
  3. Import time series data by using one of the following methods:
    • Write a single row of time series data

      Run the putts command to write a single row of time series data. In the following example, a row of time series data is written to the car_data time series table. 

      putts --k '["car_data","car_0000010", ["brand=brand0","id=car_0000010","model=em3"]]' --field '[{"c":"duration","v":121,"isint":true},{"c":"mileage","v":6480,"isint":true},{"c":"power","v":69,"isint":true},{"c":"speed","v":24,"isint":true},{"c":"temperature","v":13,"isint":true}]' --time 1636460000000000
    • Import multiple rows of time series data at the same time

      Download the sample data and run the import_timeseries command to batch import the time series data. The sample data contains a total of 5 million rows of time series data. You can include the -l parameter in the command to specify the number of rows of time series data that you want to import. You can import up to 10 million rows of time series data free of charge.

      In the following example, 50,000 rows of time series data are imported. In the command, yourFilePath specifies the path where the sample data package is decompressed. Example: D:\\timeseries_demo_data_5000000. 

      import_timeseries -i yourFilePath -l 50000
      Sample output:
      Current speed is: 11000 rows/s. Total succeed count 11000, failed count 0.
Current speed is: 13000 rows/s. Total succeed count 24000, failed count 0.
Current speed is: 16400 rows/s. Total succeed count 40400, failed count 0.
Import finished, total count is 50000, failed 0 rows.
  4. Run the qtm command to query time series. In the following example, all time series are queried and 10 time series are returned. 
    qtm -l 10
    Sample output:
    +-------------+-------------+-----------------------------------------------+------------+------------------+
| measurement | data_source | tags                                          | attributes | update_time      |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000005 | ["brand=brand0","id=car_0000005","model=m0"]  | null       | 1637722788684102 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000009 | ["brand=brand2","id=car_0000009","model=em3"] | null       | 1637722790158982 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000000 | ["brand=brand0","id=car_0000000","model=m3"]  | null       | 1637722787172818 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000008 | ["brand=brand0","id=car_0000008","model=m3"]  | null       | 1637722789832880 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000002 | ["brand=brand1","id=car_0000002","model=nm1"] | null       | 1637722787915852 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | null       | 1637722789006974 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000001 | ["brand=brand2","id=car_0000001","model=em2"] | null       | 1637722787260034 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000004 | ["brand=brand0","id=car_0000004","model=m2"]  | null       | 1637722788529313 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000003 | ["brand=brand1","id=car_0000003","model=nm0"] | null       | 1637722788288273 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
| car_data    | car_0000007 | ["brand=brand2","id=car_0000007","model=em2"] | null       | 1637722789315575 |
+-------------+-------------+-----------------------------------------------+------------+------------------+
  5. Run the getts command to query the first five time points in a time series. 
    getts --k '["car_data","car_0000006", ["brand=brand2","id=car_0000006","model=em2"]]' -l 5
    Sample output:
    +-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| measurement | data_source | tags                                          | timestamp        | duration | mileage | power | speed | temperature |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560000000000 | 190      | 1770    | 33    | 54    | 29          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560010000000 | 554      | 6670    | 42    | 24    | 12          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560020000000 | 564      | 9750    | 14    | 75    | 22          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560030000000 | 176      | 7950    | 90    | 24    | 22          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+
| car_data    | car_0000006 | ["brand=brand2","id=car_0000006","model=em2"] | 1636560040000000 | 441      | 6280    | 30    | 38    | 31          |
+-------------+-------------+-----------------------------------------------+------------------+----------+---------+-------+-------+-------------+

Use SQL to query time series data

After you create a time series table in Tablestore, the system automatically creates two SQL mapping tables for the time series table: time series data table and time series metadata table.
  • Time series data table: uses the same name as the time series table. You can query time series data in the time series data table. The name of the time series data table for the car_data time series table is car_data.
  • Time series metadata table: uses a name that concatenates the ::meta string after the name of the time series table. You can query time series metadata in the time series metadata table. The name of the time series metadata table for the car_data time series table is car_data::meta.
  1. Run the sql command to enter the SQL mode. 
    sql
  2. Retrieve time series.
    • Example 1: Query the vehicles whose brand is brand0 and model is m3, and specify that only the first 10 query results are returned. 
      SELECT * FROM `car_data::meta` WHERE _m_name = "car_data" AND tag_value_at(_tags,"brand") = "brand0" AND tag_value_at(_tags,"model") = "m3" LIMIT 10;
      Sample output:
      +----------+--------------+----------------------------------------------+-------------+-------------------+
| _m_name  | _data_source | _tags                                        | _attributes | _meta_update_time |
+----------+--------------+----------------------------------------------+-------------+-------------------+
| car_data | car_0000000  | ["brand=brand0","id=car_0000000","model=m3"] | null        | 1637722787172818  |
+----------+--------------+----------------------------------------------+-------------+-------------------+
| car_data | car_0000008  | ["brand=brand0","id=car_0000008","model=m3"] | null        | 1637722789832880  |
+----------+--------------+----------------------------------------------+-------------+-------------------+
    • Example 2: Query the total number of vehicles whose brand is brand2. 
      SELECT count(*) FROM `car_data::meta` WHERE tag_value_at(_tags,"brand") = "brand2";
      Sample output:
      +----------+
| count(*) |
+----------+
| 4        |
+----------+
  3. Query time series data.
    • Example 1: Query the vehicles whose metric name is car_data and data source is car_0000175, and specify that only the first 10 data points of the power data column are returned. 
      SELECT _time, _field_name, _long_value as value FROM `car_data` WHERE _m_name = "car_data" AND _data_source = "car_0000001" AND _field_name = "power" LIMIT 10;
      Sample output:
      +------------------+-------------+-------+
| _time            | _field_name | value |
+------------------+-------------+-------+
| 1636560000000000 | power       | 68    |
+------------------+-------------+-------+
| 1636560010000000 | power       | 41    |
+------------------+-------------+-------+
| 1636560020000000 | power       | 69    |
+------------------+-------------+-------+
| 1636560030000000 | power       | 95    |
+------------------+-------------+-------+
| 1636560040000000 | power       | 27    |
+------------------+-------------+-------+
| 1636560050000000 | power       | 26    |
+------------------+-------------+-------+
| 1636560060000000 | power       | 98    |
+------------------+-------------+-------+
| 1636560070000000 | power       | 82    |
+------------------+-------------+-------+
| 1636560080000000 | power       | 24    |
+------------------+-------------+-------+
| 1636560090000000 | power       | 2     |
+------------------+-------------+-------+
    • Example 2: Query the maximum speed of vehicles whose metric name is car_data and data source is car_000002. 
      SELECT max(_long_value) as speed FROM `car_data` WHERE _m_name = "car_data" AND _data_source = "car_0000002" AND _field_name = "speed";
      Sample output:
      +-------+
| speed |
+-------+
| 100   |
+-------+
    • Example 3: Aggregate the temperature data of vehicles whose metric name is car_data and data source is car_0000001 based on a time window of 60s to calculate the lowest temperature per minute. 
      SELECT _time DIV 60000000 * 60 as time_sec, min(_long_value) as temperature FROM `car_data` WHERE _data_source = "car_0000001" AND _field_name = "temperature" GROUP BY time_sec ORDER BY time_sec ASC LIMIT 10;
      Sample output:
      +------------+-------------+
| time_sec   | temperature |
+------------+-------------+
| 1636560000 | 11          |
+------------+-------------+
| 1636560060 | 10          |
+------------+-------------+
| 1636560120 | 11          |
+------------+-------------+
| 1636560180 | 10          |
+------------+-------------+
| 1636560240 | 11          |
+------------+-------------+
| 1636560300 | 12          |
+------------+-------------+
| 1636560360 | 14          |
+------------+-------------+
| 1636560420 | 10          |
+------------+-------------+
| 1636560480 | 15          |
+------------+-------------+
| 1636560540 | 11          |
+------------+-------------+
  4. Exit the SQL mode 
    exit;
  5. Exit the Tablestore CLI. 
    exit