LindormTSDB exposes an HTTP-based SQL API that accepts standard SQL statements over HTTP POST. Use this API to create databases and time series tables, write data, and run queries from any non-Java application without installing a client library.
For Java applications, use the JDBC driver instead. Single-node Lindorm instances do not support the HTTP-based SQL API.
How it works
All requests go to a single endpoint using HTTP POST. The SQL statement goes in the request body. The API returns JSON.
POST /api/v2/sql (port 8242)POST is required for all statement types, including SELECT.
If authentication is enabled, add a Basic Authentication header. Optionally, pass query parameters to set the target database or stream large result sets in chunks.
Prerequisites
Before you begin, make sure you have:
A LindormTSDB instance endpoint (format:
ld-<instance-id>-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242)Network access to port
8242on the endpoint(If authentication is enabled) A valid username and password
Send a request
Endpoint
| Path | Method | Description |
|---|---|---|
/api/v2/sql | POST | Executes an SQL statement |
Request format
Include the SQL statement in the request body. Set Content-Type: text/plain in the request header.
Do not end SQL statements with a semicolon (;). Although LindormTSDB supports semicolons as SQL-92 statement terminators internally, appending one to a request body causes an error.
Query parameters
| Parameter | Description | Default |
|---|---|---|
database | The database to run the SQL statement against. If the statement does not specify a database, LindormTSDB searches in this database. | default |
chunked | Set to true to stream the result set in multiple JSON blocks. Use this for queries that return large numbers of rows to avoid buffering the entire result set in memory. | false |
chunk_size | Maximum rows per JSON block. Takes effect only when chunked=true. | 1000 |
Authentication
If user authentication is enabled, add an Authorization header using Basic Authentication:
Authorization: Basic <Base64-encoded credentials>The Base64-encoded credentials are the username and password joined by a colon: username:password.
For example, the default credentials (root:root) encode to:
Authorization: Basic cm9vdDpyb290For language-specific encoding, refer to the Base64 library documentation for your programming language.
Examples
The following examples use curl and cover the full workflow: creating a database, creating a time series table, inserting data, and querying data.
Do not end SQL statements with a semicolon (;) in the request body. This causes a request error.
Create a database
curl -i -X POST \
http://ld-xxxxxxxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242/api/v2/sql \
-d 'CREATE DATABASE DB1'A successful response returns HTTP 200.
Create a time series table
Pass the target database as a query parameter, or qualify the table name with the database name. Both statements below create the same table:
# Using the database query parameter
curl -i -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)'# Using a fully qualified table name
curl -i -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)'Query data with authentication
Use -u username:password to pass credentials. Make sure the user has the required permissions on the table.
curl -i -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'A successful response returns HTTP 200 with the result set:
HTTP/1.1 200 OK
Content-Type: application/json
{
"columns": ["device_id", "region", "time", "max_t"],
"metadata": ["VARCHAR", "VARCHAR", "TIMESTAMP", "DOUBLE"],
"rows": [
["<device_id_value>", "<region_value>", "<timestamp_value>", <numeric_value>],
...
]
}An invalid SQL statement returns HTTP 400 with an error body:
HTTP/1.1 400 Bad Request
Content-Type: application/json
{
"code": <error_code>,
"sqlstate": "<sql_state_code>",
"message": "<error_message>"
}For a full list of error codes, see Common error codes.
Response parameters
Success response (HTTP 200)
| Parameter | Type | Description |
|---|---|---|
columns | Array of strings | Column names in the result set |
metadata | Array of strings | Data types of each column. For supported types, see Data types. |
rows | Array of arrays | Each inner array is one row, with values corresponding to columns |
Error response (HTTP 400)
| Parameter | Type | Description |
|---|---|---|
code | int | Error code |
sqlstate | String | SQL status code |
message | String | Error message |
Python example
The following example creates a table, inserts rows, and runs a query using the requests library.
Create a time series table
import requests
endpoint = "http://ld-bp1s0vbu8955w****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242/api/v2/sql"
sql = """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(endpoint, sql)
print(r.status_code, r.content)Insert data
sql = """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(endpoint, sql)
print(r.status_code, r.content)Query data
r = requests.post(endpoint, "SELECT * FROM sensor")
print(r.status_code, r.content)