Basic Operating Description

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.

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 which meter ID and city each data point comes from. The meter uploads power and current values every 5s.

The above table can be seen as a two-dimensional table, and SQL statements are written for two-dimensional tables

Application scenario 1: To filter data with a power value greater than 400, a current value less than 5, and 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, and 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
  

Types	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: 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: in an ascending/descending order offset and limit: After sorting, start with the offsetth entry and take limit entries.
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
Aggregating 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 documentation of Time Units Default starting time of the window is 2000-01-01 00:00:00, and if custom starting time is required, you can change '2 days' to '2 days, 2019-01-01 00:00:00', which indicates one time window every 2 days from 2019-01-01. Examples of calendar alignment rules are below: As for time_bucket (timestamp, '1 dc, 2019-01-01 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

- Custom Function:

TSDB supports standard ANSI SQL statements, and some common custom functions are selected as shown below.

Description of Custom Functions:

Classification	Support functions	Example	Description
Conditional expression	CASE	SELECT field1, CASE field1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'many' END FROM metric	case expression
Conditional 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)
Conditional expression	COALESCE	SELECT field1, field2, COALESCE (field1, field2) as result FROM metric	Return the first non-null value in the list
Calculation function	abs(x)	SELECT field1, abs (field1) as result FROM metric	Return the absolute value of x
Calculation function	sqrt(x)	SELECT field1, sqrt (field1) as result FROM metric	Return the square root of x
Calculation function	cbrt(x)	SELECT field1, cbrt (field1) as result FROM metric	Return the cube root of x
Calculation function	ceil(x)	SELECT field1, ceil (field1) as result FROM metric	Return the smallest integer value not less than x
Calculation function	ceiling(x)	Same as ceil (x)	Return the smallest integer value greater than or equal to x
Calculation function	floor(x)	SELECT field1, floor (field1) as result FROM metric	Return the largest integer value less than or equal to x
String manipulation	||	SELECT field1 || field2 as result FROM metric	String concatenation
Aggregate function	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
Aggregate function	count(*)	SELECT count(*) as result FROM metric where timestamp < 1525611901	Return quantity
Aggregate function	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 number of non-null values
Aggregate function	max_by(x, y)	SELECT max_by(field1,field2) as result FROM metric where timestamp < 1525611901000	Return x when y is maximum, UTC timestamp precision in milliseconds
Aggregate function	min_by(x, y)	SELECT min_by(field1,field2) as result FROM metric where timestamp < 1525611901000	Return x when y is minimum, UTC timestamp precision in milliseconds
Aggregate function	max(x)	SELECT max(field1) as result FROM metric where timestamp < 1525611901000	Return the maximum value of x, UTC timestamp precision in milliseconds
Aggregate function	min(x)	SELECT min(field1) as result FROM metric where timestamp < 1525611901000	Return the minimum value of x, UTC timestamp precision in milliseconds
Aggregate function	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 of x