Support SQL query
Introduction
The TSDB supports SQL query interface, which can be used to filter and query TSDB data through the SQL language. With the strong power of SQL, users can be familiar with operations on data in a convenient way, and can also make full use of the computing ability of the SQL function, to mine data values.
Basic Operation Instructions
- The table of SQL statements corresponds to the metric of TSDB.
- The field in SQL statements corresponds to the timestamp, field and tag of TSDB.
- If SQL query criteria contain Chinese characters, enclose them in single quotation marks. If it only contains the English characters, enclose them in either single quotation marks or double quotation marks.
For example: An IoT integration solution for smart meter monitoring, which collects data from all monitoring points of the smart meter. In TSDB (as shown in the image below), the metric is SmartMeter, which means that TSDB stores data for smart meter, each with two fields (field), power and MeterCurrent, with two tags, namely MeterID and City, to represent the meter ID and city where each data point comes from. The meter uploads power and current values every 5s.
The above table can be seen as a two-dimension table. The SQL statements are written for twodimensional tables
Application scenario 1: To filter data with a power value greater than 400, a current value less than 5. The meter ID is 2345HDYE
select timestamp, power, MeterCurrent, MeterID, City from SmartMeter where power > 400 and current<5 and MeterID = " 2345HDYE"
Application scenario 2: Return power average every 10 seconds in meter with meter ID 2345HDYE
select time_bucket(timestamp, '10 seconds') as TIME, avg(power) as AVG_POWER from SmartMeter group by time_bucket(timestamp, '10 seconds') order by time_bucket(timestamp, '10 seconds')
Application scenario 3: Join two metrics
Two metrics are shown in the following table
Use MeterID to join two tables. The SQL statement is as follows: select * from SmartMeter join SmartTemperature on SmartMeter.MeterID = SmartTemperature.MeterID ;
To get the following data:
TSDB_SQL_01-1
SQL Function
- Examples of query functions
| Type | SQL | Interpretation |
|---|---|---|
| Time Query | select timestamp from metric | timestamp: Fix query timestamp keyword metric: name of the metric that the user needs to query |
| Single-field Query | select value from metric | value: Means the name of the field that the user needs to query metric: name of the metric that the user needs to query |
| Tag Query | select tag_key from metric | tag_key: key of the tag that the user needs to query metric: name of the metric that the user needs to query |
| Multi-column Query | select timestamp,field1 from metric select * from metric |
tag_key: key of the tag that the user needs to query field1: name of the field that the user needs to query metric: name of the metric that the user needs to query |
| Order by Time | select timestamp, value from metric order by timestamp select timestamp, value from metric order by timestamp desc select timestamp, value from metric order by timestamp offset 1 limit 1 |
order by: Query order mode asc/desc: ascending/descending order offset and limit: After sorting, take the limit starting from the offset. |
| Filtering Query | select timestamp, value from metric where value > 30 and timestamp >150937263000 | value > 30: filter value's value, UTC timestamp accuracy in milliseconds |
| Group Query | select tag_key, count(1) from metric group by tag_key | group by tag_key: Group by key values of tag |
| Aggregation Query | select time_bucket(timestamp, '2 days') as DAY, sum(value) as SUM from metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days') | time_bucket: Time aggregator timestamp: Timestamp fields in actual data points (no modifications required) 2 days: time window is 2 group by time_bucket: group by 2 days sum(value): aggregate values in 2 days time_bucket supports calendar alignment and custom starting time: Taking time_bucket (timestamp, 1dc) as an example, the character 'c' indicates calendar alignment. For time units, see time units in the documentation. Default starting time of the window is 20000101 00:00:00, and if custom starting time is required, you can change '2 days' to '2 days, 20190101 00:00:00', which indicates one time window every 2 days from 20190101. Examples of calendar alignment rules are below: As for time_bucket (timestamp, '1 dc, 20190101 12:00:00'), the first window is January 01 12:00:00.000 January 01 23:59:59.99. The second window is January 02 00:00:00.000 - Jan 02 23:59:59.99 and so on |
| Custom Function Calculation and Query | select timestamp, ((field2 - field1) * 10) as RESULT, tag_key from metric | (field2 field1) * 10: Custom function calculation by the user |
- Common time functions for SQL query
| Classification | Supported Function | Example | Description |
|---|---|---|---|
| Date and Time Operator | +/- | mysql> select (date '2012-08-08' + interval '2' day) 2012-08-10 mysql> select (timestamp '2012-08-08 01:00' + interval '29' hour) 2012-08-09 06:00:00.000 mysql> select (date '2012-08-08' - interval '2' day) 2012-08-06 mysql> select (timestamp '2012-10-31 01:00' - interval '1' month) 2012-09-30 01:00:00.000 |
Operator |
| Time zone conversion | AT TIME ZONE | mysql> SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'America/Los_Angeles' 2012-10-30 18:00:00.000 America/Los_Angeles mysql> SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'Asia/Shanghai' 2012-10-31 09:00:00.000 Asia/Shanghai |
UTC time zone conversion |
| Date and time functions | current_date current_timestamp from_unixtime(unix_timestamp) now() to_unixtime(timestamp) cast(to_unixtime(timestamp) as bigint) |
mysql> select current_date 2021-04-21 mysql> select current_timestamp 2021-04-21 10:59:56.378 UTC mysql> select from_unixtime(1619031748); 2021-04-21 19:02:28.000 mysql> select now() 2021-04-21 11:01:38.221 UTC mysql> select to_unixtime(timestamp '2021-04-21 19:02:28') 1.619031748E9 mysql> select cast(to_unixtime(timestamp '2021-04-21 19:02:28') as bigint) 1619031748 |
Date and time related |
| Interception function | date_trunc(unit,timestamp) | mysql> select date_trunc('day',timestamp '2021-04-21 19:02:28') 2021-04-21 00:00:00.000 mysql> select date_trunc('hour',timestamp '2021-04-21 19:02:28') 2021-04-21 19:00:00.000 |
Intercept part of timestamp |
| Time period | date_add(unit,bigint,timestamp) | mysql> select date_add('hour', 8, timestamp '2021-04-21 19:02:28') 2021-04-22 03:02:28.000 |
Add and subtract based on a certain time |
| Duration | parse_duration(string) | mysql> SELECT parse_duration('5m') 0 00:05:00.000 |
Time conversion of string format |
| Date Format | date_format(timestamp,string format) | mysql> select date_format(timestamp '2021-04-21 19:02', '%Y-%m-%d %H:%i:%s') 2021-04-21 19:02:00 |
Format transformation |
| Extract function | day_of_week(timestamp) | mysql> select day_of_week(timestamp '2021-04-21 19:02') 3 |
Return bigint |
- Custom Function:
The TSDB supports standard ANSI SQL statements, and some common custom functions are selected as shown below.
Description of Custom Functions:
| Classification | Supported Function | Example | Description |
|---|---|---|---|
| Condition expression | CASE | SELECT field1, CASE field1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'many' END FROM metric |
case expression |
| Condition expression | IF | SELECT field1, IF (field1>100,1,0) as result FROM metric |
if expression, if (condition, true_value) or if (condition, true_value, false_value) |
| Condition expression | COALESCE | SELECT field1, field2, COALESCE (field1, field2) as result FROM metric |
Return the first nonempty value in the list |
| Computing function | abs(x) | SELECT field1, abs (field1) as result FROM metric |
Return the absolute value of x |
| Computing function | sqrt(x) | SELECT field1, sqrt (field1) as result FROM metric |
Return the square root of x |
| Computing function | cbrt(x) | SELECT field1, cbrt (field1) as result FROM metric |
Return the cube root of x |
| Computing function | ceil(x) | SELECT field1, ceil (field1) as result FROM metric |
Return the smallest integer not less than x. |
| Computing function | ceiling(x) | Same as ceil(x) | Return the smallest integer greater than or equal to x |
| Computing function | floor(x) | SELECT field1, floor (field1) as result FROM metric |
Return the largest integer less than or equal to x |
| String operation | || | SELECT field1 || field2 as result FROM metric |
String cascade |
| Aggregator | avg(x) | SELECT time_bucket(timestamp, '2 days') as DAY, avg(field1) as result FROM metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days') |
Return the average of field1 |
| Aggregator | count(*) | SELECT count(*) as result FROM metric where timestamp < 1525611901 |
Return the quantity |
| Aggregator | count(x) | SELECT time_bucket(timestamp, '2 days') as DAY, count(field1) as count FROM metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days') |
Return the quantity of nonempty values |
| Aggregator | max_by(x, y) | SELECT max_by(field1,field2) as result FROM metric where timestamp < 1525611901000 |
Return x when y reaches its maximum value, and UTC timestamp accuracy is in millisecond. |
| Aggregator | min_by(x, y) | SELECT min_by(field1,field2) as result FROM metric where timestamp < 1525611901000 |
Return x when y reaches its minimum value, and UTC timestamp accuracy is in millisecond. |
| Aggregator | max(x) | SELECT max(field1) as result FROM metric where timestamp < 1525611901000 |
Return the maximum value of x, and UTC timestamp accuracy is in millisecond. |
| Aggregator | min(x) | SELECT min(field1) as result FROM metric where timestamp < 1525611901000 |
Return the minimum value of x, and UTC timestamp accuracy is in millisecond. |
| Aggregator | sum(x) | SELECT time_bucket(timestamp, '2 days') as DAY, sum(field1) as sum FROM metric group by time_bucket(timestamp, '2 days') order by time_bucket(timestamp, '2 days') |
Return the sum value of x |
SQL Usage of TSDB SDK
All the TSDB Java-SDK, Node-SDK, Python-SDK and API support the SQL query. For more information, please see Java-SDK, Node-SDK, Python-SDK and API respectively.