Use the HTTP-based SQL API of LindormTSDB - Lindorm - Alibaba Cloud Documentation Center

This topic describes the definition and usage of the HTTP-based SQL API provided by LindormTSDB.

Usage notes

If you develop non-Java applications, you can directly use the API described in this topic to send SQL statements to LindormTSDB.
If you develop Java applications, we recommend that you use the Java Database Connectivity (JDBC) driver to connect your applications with LindormTSDB. For more information, see Use the JDBC driver to connect to and use LindormTSDB.
Note
Single-node Lindorm instances do not support the HTTP-based SQL API.

Request path and method

Request path	Method	Description
/api/v2/sql	POST	Sends and executes an SQL statement.

Request content

When you call the preceding API, include an SQL statement in the request body of the HTTP request. We recommend that you set Content-Type to text/plain in the request header. The following Python code provides an example on how to call the /api/v2/sql API to send an SQL statement:

import requests

url = "http://ld-bp1s0vbu8955w****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242/api/v2/sql"

payload = """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)
)"""

r = requests.post(url, payload)
if r.status_code == 200:
    print(r.content)
else:
    print(r.content)

payload = """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)"""

r = requests.post(url, payload)
if r.status_code == 200:
    print(r.content)
else:
    print(r.content)

payload = "select * from sensor"

r = requests.post(url, payload)
if r.status_code == 200:
    print(r.content)
else:
    print(r.content)

Note

LindormTSDB supports the use of semicolons (;) as the terminator of SQL statements based on the SQL-92 standard. However, when you call the /api/v2/sql API to pass an SQL statement, semicolons (;) cannot be used at the end of the SQL statement. Otherwise, an error is reported during the request.

Supported query parameters

The following table describes the URL query parameters that are supported by the /api/v2/sql API.

Parameter	Description
database	The default database on which the SQL statement is executed. You can pass an SQL statement into an SQL API request to write data to a time series table or query data from a time series table. If the database to which the time series table belongs is not specified in the SQL statement, LindormTSDB searches for the specified table in the database that is specified by the database parameter. If you do not specify the database parameter, LindormTSDB searches for the specified table in the database named `default`.
chunked	Specifies whether to return data results in chunks. Default value: false. If this parameter is set to true, the query result data is divided into multiple JSON blocks and then returned. Each JSON block contains a maximum of N rows of data. N is specified by the chunk_size parameter. When your application receives the query results, it needs to only parse the JSON blocks one by one. For more information about the structure of each JSON block, see the "Response parameters" section of this topic.
chunk_size	The maximum number of rows to return at a time. This parameter takes effect when the chunked parameter is set to true. Default value: 1000.

Specify user credentials for authentication information

If user authentication is enabled for LindormTSDB, you must include the authentication information of the user in the HTTP request headers when you use the /api/v2/sql API to send SQL queries. The /api/v2/sql API uses the BASIC AUTH method. The encoded authentication information must be specified in the Authorization field of the HTTP request header.

You can specify user credentials in the following format for basic authentication:

BASIC {Base64-encoded authentication information}

The Base64-encoded authentication information is in the following format: ${username}:${password}. The username and password are separated by a colon (:).

Note

For more information about how to encode and specify user credentials for basic authentication by using different programming languages, see the documentation for relevant class libraries of the programming languages.

For example, if you use the default username and password (which are both root) as the credential for basic authentication, the Base64-encoded value of the Authorization field in the HTTP header is Basic cm9vdDpyb290, as shown in the following example:

Authorization: Basic cm9vdDpyb290

Response parameters

If a request is successful, the HTTP status code in the response message is 200 and the response content is returned in the JSON format. The following table describes the response parameters.

Parameter	Type	Description
columns	Array	An array of strings. It indicates the name of each column in the returned result set.
metadata	Array	An array of strings. It indicates the data type of each column in the returned result set. For more information about the types of returned data, see Data types.
rows	Array	An array of arrays. It indicates a collection of rows in the returned result set. Each array indicates a row of data, and the specific values in each row correspond to the columns that are indicated by the columns parameter.

When an SQL execution error occurs, the HTTP status code in the response message is 400 and the response content is returned in the JSON format. The following table describes the parameters.

Parameter	Type	Description
code	int	The returned error code.
sqlstate	String	The returned SQL status code.
message	String	The returned error message.

Note

For more information about the error codes, see Common error codes.

Examples

The following examples show how to call the /api/v2/sql API to execute SQL statements in LindormTSDB. In the examples, the common tool curl is used.

Create a database named DB1.

curl -X POST http://ld-xxxxxxxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242/api/v2/sql -d 'CREATE DATABASE DB1'

Create a time series table named SENSOR in DB1. You can execute one of the following two statements to create the table:

curl -X POST http://ld-xxxxxxxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242/api/v2/sql?database=DB1 -d 'CREATE TABLE SENSOR (device_id VARCHAR TAG,region VARCHAR TAG,time TIMESTAMP,temperature DOUBLE,humidity DOUBLE)'

curl -X POST http://ld-xxxxxxxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242/api/v2/sql -d 'CREATE TABLE DB1.SENSOR (device_id VARCHAR TAG,region VARCHAR TAG,time TIMESTAMP,temperature DOUBLE,humidity DOUBLE)'

If user authentication is enabled, use an account whose username is tsdbuser to query data in the SENSOR time series table.

curl -X POST -u tsdbuser:password http://ld-xxxxxxxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242/api/v2/sql?database=DB1 -d 'SELECT device_id, region, time, MAX(temperature) as max_t FROM SENSOR WHERE time >= 1619076780000 AND time <= 1619076800000 SAMPLE BY 20s'

Note

Make sure that tsdbuser is granted the required permissions on the SENSOR time series table.