Connect to and use the time series engine using Lindorm-cli

Create a table

1. Create a time series table.

   CREATE TABLE sensor (
       device_id VARCHAR NOT NULL,
       region VARCHAR NOT NULL,
       time TIMESTAMP NOT NULL,
       temperature DOUBLE,
       humidity BIGINT,
       PRIMARY KEY(device_id, region, time)
   );

   Note

   • When creating a time series table, specify a primary key (PRIMARY KEY). The Basic Edition does not support PRIMARY KEY. Typically, use a unique identifier from your data source as the PRIMARY KEY. Examples include device ID in Internet of Things scenarios, vehicle ID in Internet of vehicles scenarios, application ID in monitoring scenarios, or ip:port.

   • The column name for the timestamp must be time, which represents the data timestamp in milliseconds (ms).

2. Verify that the table sensor was created successfully.

   SHOW TABLES;

   The query result is as follows:

   +-------------------+
   | Tables_In_default |
   +-------------------+
   | sensor            |
   +-------------------+

3. View the columns of the time series table.

   DESCRIBE TABLE sensor;

   The query result is as follows:

   +-------------+-----------+------------+------------+--------------+
   | columnName  | typeName  | columnKind | primaryKey | partitionTag |
   +-------------+-----------+------------+------------+--------------+
   | device_id   | VARCHAR   | TAG        | true       | true         |
   | region      | VARCHAR   | TAG        | true       | true         |
   | time        | TIMESTAMP | TIMESTAMP  | true       | false        |
   | temperature | DOUBLE    | FIELD      | false      | false        |
   | humidity    | BIGINT    | FIELD      | false      | false        |
   +-------------+-----------+------------+------------+--------------+

Write data

• Write data one row at a time.

  INSERT INTO sensor (device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45);
  INSERT INTO sensor (device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47);
  INSERT INTO sensor (device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46);
  INSERT INTO sensor (device_id, region, time, temperature, humidity) VALUES('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44);
  INSERT INTO sensor (device_id, region, time, temperature, humidity) VALUES('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44);

• Write data in batches.

  INSERT INTO sensor (device_id, region, time, temperature, humidity) 
  VALUES ('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45),
         ('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47),
         ('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46),
         ('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44),
         ('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44);

Query data

SELECT device_id,region,time,temperature,humidity FROM sensor WHERE device_id = 'F07A1260' AND time >= '2021-04-22 15:33:00' AND time <= '2021-04-22 15:33:20';

+-----------+----------+---------------------------+-------------+----------+
| device_id |  region  |           time            | temperature | humidity |
+-----------+----------+---------------------------+-------------+----------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.1        | 45       |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.2        | 47       |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.6        | 46       |
+-----------+----------+---------------------------+-------------+----------+

SELECT device_id,region,time,max(temperature) AS max_temperature FROM sensor WHERE device_id = 'F07A1260' AND time >= '2021-04-22 15:33:00' AND time <= '2021-04-22 15:33:20' SAMPLE BY 20s;

+-----------+----------+---------------------------+-----------------+
| device_id |  region  |           time            | max_temperature |
+-----------+----------+---------------------------+-----------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 13.2            |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.6            |
+-----------+----------+---------------------------+-----------------+

SELECT region,max(temperature) AS max_temperature FROM sensor WHERE time >= '2021-04-22 15:33:00' AND time <= '2021-04-22 15:33:20' GROUP BY region;

+----------+-----------------+
|  region  | max_temperature |
+----------+-----------------+
| north-cn | 13.2            |
| south-cn | 19.7            |
+----------+-----------------+

Why is the query result empty after successful data insertion?

If a query returns no data after a successful write, check whether the database has a time-to-live (TTL) setting by running DESCRIBE DATABASE <database name>. Data older than the TTL is automatically deleted and cannot be queried.

Can Lindorm-cli import or export time series data?

• For small data volumes, you can use Lindorm-cli to quickly import or export CSV files. Contact Lindorm technical support (DingTalk ID: s0s3eg3) if needed.

• For large data volumes, import data using DataX. Exporting large-scale data is not supported.