Query data - Lindorm - Alibaba Cloud Documentation Center

LindormTSDB allows you to use standard SQL syntaxes, PromQL, and APIs and SDKs that are compatible with Time Series Database (TSDB) to query data. This topic describes how to query spatio-temporal data in LindormTSDB.

Use standard SQL syntaxes to query data

We recommend that you use standard SQL syntaxes to query data in LindormTSDB. LindormTSDB optimizes the SQL syntaxes for time series data to improve query performance in most scenarios. This way, the stability of LindormTSDB server can be improved with less resource usage.

Features

Features that are specific to time series data, such as downsampling queries, aggregate queries, and latest value queries are supported. For more information, see Downsampling queries and Query the latest values.
Various function operators for time series data are provided. For more information, see Common time series functions.
Various SQL computing and analysis capabilities are provided based on Apache Calcite. For more information, see Calcite official documentation.

For more information about the features provided by standard SQL syntaxes in LindormTSDB, see SQL syntaxes.

Access methods supported by SQL queries

You can use Java Native SDK, Java Database Connectivity (JDBC) drivers, and HTTP SQL API operations to perform SQL queries. The following table describes the three methods.

Access method	Scenario	Description	Reference
Java Native SDK	Java application development	Java Native SDK supports streaming data transmission and provides secure threads for API calling. Therefore, you do not need to manage underlying connections.	Java Native SDK
JDBC Driver	Java application development that require frameworks	You must develop code in your applications or use Druid to manage the connection pool. JDBC must be used together with the Object-Relational Mapping (ORM) framework.	Java JDBC Driver
HTTP SQL API	Applications development by using non-Java programming languages	LindormTSDB provides HTTP-based API operations to perform SQL queries. The results can be returned in streaming mode.	Use the HTTP-based SQL API of LindormTSDB

Examples

LindormTSDB use the SAMPLE BY clause to implement time-series features in queries. The SAMPLE BY clause is used to perform downsampling on a single time series. The GROUP BY clause is used to perform aggregation on multiple time series. SAMPLE BY and GROUP BY cannot be used together in a clause. However, you can use separately them in different subqueries. The following examples show how to use the SAMPLE BY and GROUP BY clauses:

Use SAMPLE BY to perform a downsampling query on a single time series and specify the downsampling interval as 8 hours.
```
SELECT device_id,region,time,count(humidity) AS count_humidity FROM sensor WHERE device_id='F07A1260' sample by 8h;
```

Use SAMPLE BY to perform downsampling queries on multiple time series and then use GROUP BY to aggregate the downsampled data based on device IDs.

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;

Use APIs and SDKs that are compatible with TSDB to query data

LindormTSDB is compatible with 90% of the capabilities provided by TSDB. Therefore, if your application uses TSDB to store data, you can directly use the SQL syntax supported by LindormTSDB to query data without the need to modify your application. We recommend that you modify your application to allow SQL queries.

Use TSDB-compatible operations to query multi-value data

You can query multi-value data that is written by using TSDB-compatible operations, HTTP SQL API, Java Native SDK, and the InfluxDB line protocol.

Note

Metrics in TSDB correspond to tables in LindormTSDB.

For more information about how to query multi-value data, see the following topics:

Use API operations to query multi-value data: Query a multivariate data point.
Use SDKs to query multi-value data: Use the SDK multi-value data model to read data.

Use TSDB-compatible operations to query single-value data

You can query single-value data that is written by API operations that are compatible with TSDB. We recommend that you do not use this method to query data.

Note

If you want to query data that is HTTP SQL API, Java Native SDK, and the InfluxDB line protocol, or multi-value data that is written by using TSDB-compatible operations, you must set metrics in the metric@field format to adapt the query to multi-value data. In this format, set metric to the name of the table and set field to the name of the column that stores the data that you want to query.

For more information about how to query single-value data, see the following topics:

Use API operations to query single-value data: Query single-value data points.
Use SDKs to query single-value data: Query data.

Use Grafana plug-ins to query data

Grafana is an open source tool that can be used to display large amounts of measurement data in a visualized manner. You can use the Alibaba Cloud Lindorm data source plug-in provided by Grafana to access time series data in LindormTSDB.

Use the Grafana plug-in for Lindorm to query data

By using the Alibaba Cloud Lindorm data source plug-in provided by Grafana, you can use the SQL statements that are automatically generated in the editor or create custom SQL statements to query data. For more information, see Use Grafana to access LindormTSDB.

Use the Grafana plug-in for OpenTSDB to query data

LindormTSDB provides API operations that are compatible with OpenTSDB to query data. You can use the OpenTSDB data source plug-in provided by Grafana to query time series data in Lindorm.

Note

Improve query performance

Method	Scenario	Description	Difference
Pre-aggregation	Aggregate and query a large number of time series in real time.	Pre-aggregation uses the continuous query feature provided by LindormTSDB to calculate the data to be queried in advance and save the results to a result table. This way, the query is accelerated because only data in the result table need to be queried. For more information about continuous queries, see Continuous queries.	In pre-aggregation, data is calculated in advance before it is actually queried. The result table that stores the calculation results is different from the original table. Therefore, you must specify the original table and result table in your queries when you use pre-aggregation.
Pre-downsampling	Query time series within a wide time range.	Pre-downsampling uses LindormDFS to downsample and separately store the written data. When you query the written data, data that is downsampled at a pre-downsampling ratio that is closest to the downsampling ratio specified in the query conditions is queried. Then, the queried data is aggregated and calculated. This way, the amount of data that needs to be queried and calculated in real time is reduced and so that the query latency is decreased. For more information about pre-downsampling, see Pre-downsampling.	In pre-downsampling, the time precision of the original data is decreased. The downsampled data and original data is stored in the same table. Therefore, you do not need to specify different tables in your queries when you use pre-downsampling.