Lindorm-cli is a simple command-line tool that is used to connect to and manage Lindorm databases. You can use Lindorm-cli to perform basic SQL operations, such as table creation, data query, and data writing. This topic describes how to use Lindorm-cli to connect to and use the Lindorm time series engine (LindormTSDB).
Prerequisites
The IP address of your client is added to the whitelist of the Lindorm instance. For more information, see Configure whitelists.
Step 1: Install Lindorm-cli
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 |
Run the following command to decompress the downloaded Lindorm-cli client package.
The following command provides an example on how to decompress the Lindorm-cli client package in Linux.
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
Clients deployed in Linux or macOS
Clients deployed in Windows
Run the following command to go to the path in which lindorm-cli.exe
is located:
cd <Path of lindorm-cli.exe>
Run the following command to connect to LindormTSDB:
./lindorm-cli -url <LindormTSDB endpoint for SQL> -username <Username> -password <Password> -database <Database name>
Parameters
Parameter | Required | Description |
LindormTSDB endpoint for SQL | Yes | The endpoint and port that are required to connect to LindormTSDB by using SQL. For more information about how to obtain the endpoint of LindormTSDB, see View endpoints. Example: jdbc:lindorm:tsdb:url=http://ld-bp12pc23yfb3*****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242 . Note You can also use the LindormTSDB endpoint for HTTP to connect to LindormTSDB. |
Username | No | The username used to connect to LindormTSDB. If the user authentication and permission verification feature is not enabled for LindormTSDB, you do not need to configure the username and password. For more information, see User and permission management. |
Password | No | The password used to connect to LindormTSDB. If you forget your password, you can change the password in the cluster management system console of LindormTable. For more information about how to change your password, see Change the password of a user. |
Database name | No | The name of the database to which you want to connect by using Lindorm-cli. If you do not specify a database, the client connects to the default database named default. During this process, you can run the use <Database name> command to connect to a specified database. |
If the client is connected to LindormTSDB, the following result is returned:
lindorm-cli version: 1.0.xx
In the result, 1.0.xx
indicates the version of Lindorm-cli.
Method 1
Run the following command in Command Prompt to go to the path in which lindorm-cli.exe is located:
cd <Path of lindorm-cli.exe>
Run the following command in Command Prompt to connect to LindormTSDB:
lindorm-cli -url <LindormTSDB endpoint for SQL> -username <Username> -password <Password> -database <Database name>
After the client is connected to LindormTSDB, the following result is returned:
Connected to jdbc:lindorm:tsdb:url=http://****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242
lindorm-cli version: 1.0.xx
In the result, 1.0.xx
indicates the version of Lindorm-cli.
Method 2
Double click lindorm-cli.exe to open the client and run the following command:
connect <LindormTSDB endpoint for SQL> <Username> <Password> -database <Database name>
No result is returned if the client is connected to LindormTSDB.
Step 3: Use LindormTSDB to perform operations
Create a table
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
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.
Check whether the table named sensor is created.
The following result is returned:
+-------------------+
| Tables_In_default |
+-------------------+
| sensor |
+-------------------+
Query the information about a specific column in a time series table.
The following result is returned:
+-------------+-----------+------------+------------+--------------+
| 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 to LindormTSDB
Note
If two rows of data have the same tags and the same value in the time column, the two rows of data are considered the same data record. In this case, the row of data that is written later overwrites the other row of data.
Query data in LindormTSDB
Important
If no results is returned when you query data that has been written to a table, execute the DESCRIBE DATABASE <Database name>
statement to query whether a Time to Live (TTL) is configured for the database. Data that is retained for a period longer than the TTL is automatically cleared and cannot be queried.
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 |
+----------+-----------------+
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.