Query Data Using SQL

Query Using SQL

SQL is used by TDengine as its query language. Application programs can send SQL statements to TDengine through REST API or client libraries. TDengine's CLI taos can also be used to execute ad hoc SQL queries. Here is the list of major query functionalities supported by TDengine：

Query on single column or multiple columns
Filter on tags or data columns: >, <, =, <>, like
Grouping of results: Group By
Sorting of results: Order By
Limit the number of results: Limit/Offset
Arithmetic on columns of numeric types or aggregate results
Join query with timestamp alignment
Aggregate functions: count, max, min, avg, sum, twa, stddev, leastsquares, top, bottom, first, last, percentile, apercentile, last_row, spread, diff

For example, the SQL statement below can be executed in TDengine CLI taos to select records with voltage greater than 215 and limit the output to only 2 rows.

SQL
select * from test.d101 where voltage > 100 order by ts desc limit 2;

output
           ts            |       current        |   voltage   |        phase         |
======================================================================================
 2018-10-03 14:38:16.800 |             12.30000 |         221 |              0.31000 |
 2018-10-03 14:38:15.000 |             12.60000 |         218 |              0.33000 |
Query OK, 2 row(s) in set (0.001100s)

To meet the requirements of varied use cases, some special functions have been added in TDengine. Some examples are twa (Time Weighted Average), spread (The difference between the maximum and the minimum), and last_row (the last row). Furthermore, continuous query is also supported in TDengine.

For detailed query syntax please refer to Select.

Aggregation among Tables

In most use cases, there are always multiple kinds of data collection points. A new concept, called STable (abbreviation for super table), is used in TDengine to represent one type of data collection point, and a subtable is used to represent a specific data collection point of that type. Tags are used by TDengine to represent the static properties of data collection points. A specific data collection point has its own values for static properties. By specifying filter conditions on tags, aggregation can be performed efficiently among all the subtables created via the same STable, i.e. same type of data collection points. Aggregate functions applicable for tables can be used directly on STables; the syntax is exactly the same.

In summary, records across subtables can be aggregated by a simple query on their STable. It is like a join operation. However, tables belonging to different STables can not be aggregated.

Example 1

In TDengine CLI taos, use the SQL below to get the average voltage of all the meters in California grouped by location.

SQL
SELECT location, AVG(voltage) FROM test.meters GROUP BY location;

output
         location         |       avg(voltage)        |
=======================================================
 California.PaloAlto      |             109.507000000 |
 California.Sunnyvale     |             109.507000000 |
 California.MountainView  |             109.507000000 |
 California.SanFrancisco  |             109.507000000 |
 California.SanJose       |             109.507000000 |
 California.SanDiego      |             109.507000000 |
 California.SantaClara    |             109.507000000 |
 California.Cupertino     |             109.507000000 |
 California.Campbell      |             109.507000000 |
 California.LosAngles     |             109.507000000 |
Query OK, 10 row(s) in set

Example 2

In TDengine CLI taos, use the SQL below to get the number of rows and the maximum current in the past 24 hours from meters whose groupId is 2.

SQL
SELECT count(*), max(current) FROM test.meters where groupId = 2 and ts > now - 24h;

output
     count(*)  |    max(current)  |
==================================
            5 |             13.4 |
Query OK, 1 row(s) in set (0.002136s)

Join queries are only allowed between subtables of the same STable. In Select, all query operations are marked as to whether they support STables or not.

Down Sampling and Interpolation

In IoT use cases, down sampling is widely used to aggregate data by time range. The INTERVAL keyword in TDengine can be used to simplify the query by time window. For example, the SQL statement below can be used to get the sum of current every 10 seconds from meters table d1001.

SQL
SELECT _wstart, sum(current) FROM test.d101 INTERVAL(10s) limit 3;

output
         _wstart         |       sum(current)        |
======================================================
 2017-07-14 10:40:00.000 |               9.920000076 |
 2017-07-14 10:55:00.000 |               9.840000153 |
 2017-07-14 11:10:00.000 |               9.840000153 |
Query OK, 3 row(s) in set

Down sampling can also be used for STable. For example, the below SQL statement can be used to get the sum of current from all meters in California.

SQL
SELECT _wstart, SUM(current) FROM test.meters where location like "California%" INTERVAL(1s) limit 5;

output
         _wstart         |       sum(current)        |
======================================================
 2017-07-14 10:40:00.000 |            9920.000076294 |
 2017-07-14 10:55:00.000 |            9840.000152588 |
 2017-07-14 11:10:00.000 |            9840.000152588 |
 2017-07-14 11:25:00.000 |           10119.999885559 |
 2017-07-14 11:40:00.000 |            9800.000190735 |
Query OK, 5 row(s) in set

Down sampling also supports time offset. For example, the below SQL statement can be used to get the sum of current from all meters but each time window must start at the boundary of 500 milliseconds.

SQL
SELECT _wstart, SUM(current) FROM test.meters INTERVAL(1s, 500a) limit 5;

output
         _wstart         |       sum(current)        |
======================================================
 2017-07-14 10:39:59.500 |            9920.000076294 |
 2017-07-14 10:54:59.500 |            9840.000152588 |
 2017-07-14 11:09:59.500 |            9840.000152588 |
 2017-07-14 11:24:59.500 |           10119.999885559 |
 2017-07-14 11:39:59.500 |            9800.000190735 |
Query OK, 5 row(s) in set

In many use cases, it's hard to align the timestamp of the data collected by each collection point. However, a lot of algorithms like FFT require the data to be aligned with same time interval and application programs have to handle this by themselves. In TDengine, it's easy to achieve the alignment using down sampling.

Interpolation can be performed in TDengine if there is no data in a time range.

For more details please refer to Aggregate by Window.

Client Library Examples

IMPORTANT

Before executing the sample code in this section, you need to firstly establish connection to TDegnine cloud service, please refer to Connect to TDengine Cloud Service.

In this example, we use query method to execute SQL and get a result object.

result = conn.query("SELECT ts, current FROM power.meters LIMIT 2")

Get column metadata(column name, column type and column length) from result:

print(result.fields)
# output: [{'name': 'ts', 'type': 9, 'bytes': 8}, {'name': 'current', 'type': 6, 'bytes': 4}]

Get total rows from result:

print(result.rows)
# output: 3

Iterate over each rows:

for row in result:
    print(row)
# output:
# [datetime.datetime(2018, 10, 3, 14, 38, 5, tzinfo=datetime.timezone.utc), 10.3]
# [datetime.datetime(2018, 10, 3, 14, 38, 15, tzinfo=datetime.timezone.utc), 12.6]

view source code

In this example we use executeQuery method of Statement object and get a ResultSet object.

ResultSet result = stmt.executeQuery("SELECT ts, current FROM power.meters LIMIT 2");

Get column meta from the result:

// print column names
ResultSetMetaData meta = result.getMetaData();
System.out.println(meta.getColumnLabel(1) + "\t" + meta.getColumnLabel(2));
// output: ts	current

Iterate over the result and print each row:

while(result.next()) {
    System.out.println(result.getTimestamp(1) + "\t" + result.getFloat(2));
}
// output:
//2018-10-03 14:38:05.0	10.3
//2018-10-03 14:38:15.0	12.6

view source code

In this example we use Query method to execute SQL and get a sql.Rows object.

	rows, err := taos.Query("SELECT ts, current FROM power.meters LIMIT 2")
	if err != nil {
		fmt.Println("failed to select from table, err:", err)
		return
	}
	defer rows.Close()

Get column names from rows:

	// print column names
	colNames, _ := rows.Columns()
	fmt.Println(colNames)

Iterate over rows and print each row:

	for rows.Next() {
		var r struct {
			ts      time.Time
			current float32
		}
		err := rows.Scan(&r.ts, &r.current)
		if err != nil {
			fmt.Println("scan error:\n", err)
			return
		}
		fmt.Println(r.ts, r.current)
	}
	// 2018-10-03 14:38:05 +0000 UTC 10.3
	// 2018-10-03 14:38:15 +0000 UTC 12.6

view source code

In this example, we use query method to execute SQL and get a result object.

let result = conn.query("SELECT ts, current FROM power.meters LIMIT 2").await?;

Get column meta from the result:

let fields = result.fields();
for column in fields {
    println!("name:{} bytes: {}", column.name(), column.bytes());
}
// name:ts bytes: 8
// name:current bytes: 4

Get all rows and print each row:

    let mut rows = result.rows();
    while let Some(row) = rows.try_next().await? {
        for (name, value) in row {
            println!("got value of {}: {}", name, value);
        }
    }
// 2018-10-03 14:38:05.000 10.3
// 2018-10-03 14:38:15.000 12.6

view source code

const { options, connect } = require("@tdengine/rest");

function checkError(result) {
  if (result.getErrCode() !== undefined) {
    console.log(result.getErrCode(), result.getErrStr());
    process.exit(1);
  }
}

async function test() {
  options.url = process.env.TDENGINE_CLOUD_URL;
  options.query = { token: process.env.TDENGINE_CLOUD_TOKEN };
  let conn = connect(options);
  let cursor = conn.cursor();
  try {
    let result = await cursor.query("SELECT ts, current FROM power.meters LIMIT 2");
    console.log(result.getMeta());
    // [
    //   { columnName: 'ts', code: 9, size: 8, typeName: 'timestamp' },
    //   { columnName: 'current', code: 6, size: 4, typeName: 'float' }
    // ]
    console.log(result.getData());
    // [ [ '2018-10-03T14:38:05Z', 10.3 ], [ '2018-10-03T14:38:15Z', 12.6 ] ]
  } catch (err) {
    console.log(err);
  }
}

test();

view source code

In this example, we use query method to execute SQL and get a result object.

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="TDengine.Connector" Version="3.0.*" GeneratePathProperty="true" />
  </ItemGroup>
  <Target Name="copyDLLDependency" BeforeTargets="BeforeBuild">
    <ItemGroup>
      <DepDLLFiles Include="$(PkgTDengine_Connector)\runtimes\**\*.*" />
    </ItemGroup>
    <Copy SourceFiles="@(DepDLLFiles)" DestinationFolder="$(OutDir)" />
  </Target>

</Project>

view source code

                    string selectTable = "select * from test.meters";
                    res = LibTaosWS.WSQueryTimeout(conn, selectTable, 5000);
                    ValidQueryExecution(res);

                    // get meta info of the retrieved data as List
                    List<TDengineMeta> metas = LibTaosWS.WSGetFields(res);
                    Console.WriteLine(metas.Count);
                    // get data of the retrieved data as List.
                    List<object> dataSet = LibTaosWS.WSGetData(res);
                    Console.WriteLine(dataSet.Count);
                    // Free the query result every time when used up it.
                    LibTaosWS.WSFreeResult(res);
                    void ValidQueryExecution(IntPtr res)
                    {
                        int code = LibTaosWS.WSErrorNo(res);
                        if (code != 0)
                        {
                            throw new Exception($"execute SQL failed: reason: {LibTaosWS.WSErrorStr(res)}, code:{code}");
                        }
                    }

view source code

Query Using SQL​

Aggregation among Tables​

Example 1​

Example 2​

Down Sampling and Interpolation​

Client Library Examples​

Support and Feedback

Query Using SQL

Aggregation among Tables

Example 1

Example 2

Down Sampling and Interpolation

Client Library Examples