Lindorm:Connect to and use LindormTSDB using Lindorm-cli

Create a table

LindormTSDB uses SQL syntax for DDL. Two constraints apply when creating a time series table:

• The timestamp column must be named time. It stores timestamps in milliseconds (ms).

• Specify a PRIMARY KEY to uniquely identify a data source — for example, a device ID in Internet of Things (IoT) scenarios, a vehicle identifier in Internet of Vehicles (IoV) scenarios, or an ip:port pair in monitoring scenarios.

The Basic Edition does not support primary keys.

1. Create the sensor 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)
   );

2. Verify the table was created.

   SHOW TABLES;

   Expected output:

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

3. Inspect the table schema.

   DESCRIBE TABLE sensor;

   Expected output:

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

Data points with the same tags and timestamp are treated as the same data point. Later writes overwrite earlier ones.

Write individual rows:

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

Or write multiple rows in a single statement:

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 my query returning no results after writing data?

Run DESCRIBE DATABASE <database name> and check whether a time to live (TTL) is configured. If the data's age exceeds the TTL, LindormTSDB automatically deletes it and the data no longer appears in query results.

Can Lindorm-cli import and export CSV files?

For small datasets, Lindorm-cli supports CSV import and export. Contact Lindorm technical support (DingTalk group: s0s3eg3) for this feature.

For large datasets, use DataX to import data. Exporting large-scale data is not supported.