Lindorm:Query data

Access methods

Three access methods are available for SQL queries.

How SAMPLE BY and GROUP BY work together

LindormTSDB uses the SAMPLE BY clause for time-series downsampling and the GROUP BY clause for aggregating across multiple time series. The two clauses cannot be combined at the same query level — use GROUP BY in an outer query wrapping a SAMPLE BY subquery.

Downsample a single time series

The following query downsamples humidity readings for a specific device at 8-hour intervals:

SELECT device_id, region, time, count(humidity) AS count_humidity
FROM sensor
WHERE device_id = 'F07A1260'
SAMPLE BY 8h;

Downsample multiple time series, then aggregate

The following query downsamples all devices at 8-hour intervals in a subquery, then aggregates the results by device ID in the outer query:

SELECT device_id, max(avg_humidity) AS max_humidity
FROM (
  SELECT device_id, region, time, avg(humidity) AS avg_humidity
  FROM sensor
  SAMPLE BY 8h
)
GROUP BY device_id;

For more information, see:

• Downsampling queries

• Query the latest values

• Common time series functions

Key mapping: metrics and tables

In TSDB, data is organized by metric name. In LindormTSDB, metrics map to tables. When querying data written via the HTTP SQL API, Java Native SDK, InfluxDB line protocol, or TSDB-compatible multi-value operations, set the metric in metric@field format:

• metric — the table name

• field — the column name storing the target data

Query multi-value data

Multi-value data written through any of the following methods can be queried using TSDB-compatible operations:

• TSDB-compatible API operations

• HTTP SQL API

• Java Native SDK

• InfluxDB line protocol

For step-by-step instructions, see:

• Query a multivariate data point — using API operations

• Use the SDK multi-value data model to read data — using SDKs

Query single-value data

Single-value data written using TSDB-compatible API operations can be queried via the same TSDB-compatible API operations. This approach is not recommended for new development — use SQL queries instead.

For reference, see:

• Query single-value data points — using API operations

• Query data — using SDKs

Alibaba Cloud Lindorm data source plug-in

The Alibaba Cloud Lindorm data source plug-in lets you query LindormTSDB using auto-generated or custom SQL statements directly in the Grafana query editor. See Use Grafana to access LindormTSDB.

OpenTSDB data source plug-in

LindormTSDB exposes OpenTSDB-compatible API operations, so you can connect the OpenTSDB data source plug-in in Grafana to query time-series data in Lindorm.

When querying data written via the HTTP SQL API, Java Native SDK, InfluxDB line protocol, or TSDB-compatible multi-value operations through this plug-in, set metrics in metric@field format as described in Key mapping: metrics and tables.