Lindorm:Use Lindorm-cli to connect to LindormTSDB

Prerequisites

The IP address of your client is added to the whitelist of the Lindorm instance. For more information, see Configure a whitelist.

Step 1: Install Lindorm-cli

1. Download a Lindorm-cli client package based on the operating system of your client. The following table provides the download links of Lindorm-cli client packages for different operating systems.

   Operating system	Download link
   Linux	lindorm-cli for linux
   macOS	lindorm-cli for macOS
   Windows	lindorm-cli for windows-x64
   Linux-arm64	lindorm-cli for linux-arm64

2. Run the following command to decompress the downloaded Lindorm-cli client package.

   In this topic, the client is decompressed in Linux as an example.

   tar zxvf lindorm-cli-linux-latest.tar.gz

   The executable file lindorm-cli.exe is located in the lindorm-cli-linux-latest directory.

Step 2: Connect to LindormTSDB

connect <LindormTSDB endpoint for SQL> <Username> <Password> -database <Database name>

Step 3: Use LindormTSDB to perform operations

Create a table

1. Create a time series table

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

   Note

   • We recommend that you specify a primary key when you create a time series table. Note that primary keys are not supported by single-node Lindorm instances. In most cases, you can specify columns that correspond to the unique identifiers (UIDs) of a data source as a primary key, such as device ID columns in IoT scenarios, vehicle ID columns in Internet of vehicles (IoV) scenarios, and application ID or ip:port columns in monitoring scenarios.

   • You must set the name of the timestamp column to time. This column provides the timestamps of your data and the unit of the timestamps is milliseconds.

2. Check whether the table named sensor is created.

   SHOW tables;

   The following result is returned:

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

3. Query the information about a specific column in a time series table.

   DESCRIBE table sensor;

   The following result is returned:

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

Write data to LindormTSDB

• Write single rows of data into a time series table in sequence.

  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 multiple rows of data at the same time to a time series table.

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

• Query data by condition.

  Query data that was generated by the F07A1260 device from 2021-04-22 15:33:00 to 2021-04-22 15:33:20.

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

  The following result is returned:

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

• Perform a downsampling query in which sampling ratio is decreased to reduce the number of time records in the result set.

  Query the highest temperature that was generated by the F07A1260 device within each 20-second interval from 2021-04-22 15:33:00 to 2021-04-22 15:33:20.

  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;

  The following result is returned:

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

• Query aggregated data.

  Query the highest temperature of each region within the specified time range.

  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;

  The following result is returned:

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

Import and export CSV files

Import CSV files

lindorm-cli -url <LindormTSDB endpoint for SQL> -database <Database name> -format=csv -input /path/of/data.csv -table sensor

Export CSV files

lindorm-cli -url <LindormTSDB endpoint for SQL> -database <Database name> -execute "select * from sensor" -format=csv -output /path/of/data.csv

Common commands used in Lindorm-cli

• help: You can run this command to view help information.

• connect: You can run this command to connect to a server.

• precision: You can run this command to specify the time format. Valid values: rfc3339, h, m, s, ms, u, and ns.

• exit or quit: You can run one of the two commands to exit the current connection to LindormTSDB.