Tdengine_tsdb_tutorial

TDengine is an open source, high-performance, cloud native and AI powered time-series database designed for Internet of Things (IoT), Connected Cars, and Industrial IoT. It enables efficient, real-time data ingestion, processing, and analysis of TB and even PB scale data per day, generated by billions of sensors and data collectors.

concept

Reference: https://www.taosdata.com/

Super Table

TDengine introduces the concept of Super Tables (STables). A Super Table is a specialized data structure designed to aggregate data collection points of the same type into a unified logical table.

subtable

A subtable is a logical abstraction of a data collection point, representing a concrete table derived from a Super Table. Users can treat the Super Table as a template and create subtables by assigning specific tag values. These derived tables are collectively referred to as subtables.

ts field

In TDengine, the ts field is a mandatory timestamp column that represents the time of each data record. It is essential for time-series data indexing, sorting, and querying. Every table must include a ts column of the TIMESTAMP type to leverage TDengine’s core time-series functionalities.

connection

TDengine supports multiple connection protocols, including native, REST, and WebSocket.Native connections require the installation of the TDengine client, which is needed for JDBC drivers.However, compatibility issues may arise between different versions of the client and server, and some legacy server versions lack full cross-platform client support.Therefore, finding a compatible version for your system might be difficult.So use rest or WebSocket protocol was a good chooice.

Now, Let’s intergrate tdengine with springboot.

dependency

<dependency>
    <groupId>com.taosdata.jdbc</groupId>
    <artifactId>taos-jdbcdriver</artifactId>
    <version>2.0.42</version>
</dependency>

intergrate with springboot

config yaml

spring:
  datasource:
    dynamic:
      druid:
        initial-size: 5
        min-idle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,slf4j
        wall:
          selectWhereAlwayTrueCheck: false
        stat:
          merge-sql: true
          slow-sql-millis: 5000
      datasource:
        master:
          url: jdbc:postgresql://127.0.0.1:5432/postgres?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
          username: postgres
          password: postgres
          driver-class-name: org.postgresql.Driver
        slave:
          url: jdbc:TAOS-RS://127.0.0.1:6041/simple
          username: root
          password: root
          driver-class-name: com.taosdata.jdbc.rs.RestfulDriver

Config JdbcTemplate for db connection.

TDengine’s SQL syntax is not fully standard, which may cause issues when using it with MyBatis. Some TDengine SQL statements may not be compatible with MyBatis and need to be executed directly through the JDBC driver.

package org.jeecg.config;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

@Component
public class DynamicJdbcTemplateProvider {

    @Autowired
    private DataSource dataSource; 

    public JdbcTemplate getJdbcTemplate(String dataSourceName) {
        DynamicRoutingDataSource routingDataSource = (DynamicRoutingDataSource) dataSource;
        DataSource target = routingDataSource.getDataSource(dataSourceName);
        return new JdbcTemplate(target);
    }
}

/**
 * create subtable
 */

public static String SUB_TABLE_NAME = "device_";
public static String CREATE_DEVICE_SUB_TABLE = " CREATE TABLE IF NOT EXISTS %s%s " +
                                               " USING iot_device_status         " +
                                               " TAGS ('%s', '%s', '%s')         ";
@Override
public int createDeviceSubTable(IotDeviceCtDto iotDeviceCtDto) {
    String createTableSql = String.format(CREATE_DEVICE_SUB_TABLE,
            SUB_TABLE_NAME,
            iotDeviceCtDto.getDeviceId(),
            iotDeviceCtDto.getDeviceId(),
            iotDeviceCtDto.getRvId(), iotDeviceCtDto.getDeviceType());
    return jdbcTemplateProvider.getJdbcTemplate(DB_NAME).update(createTableSql);
}

package org.jeecg.modules.iot.entity.po;

import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

/**
 * @author van
 * @time 2025-05-09 13:49:16
 **/
@Data
@Slf4j
@NoArgsConstructor
@TableName("iot_device_status")
public class IotDeviceStatus {

    private Date ts;

    private Double value;

    private Integer source;

    @JsonIgnore
    private String deviceId;
}


package org.jeecg.modules.iot.mapper;

import java.util.List;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.jeecg.modules.iot.entity.po.IotDeviceStatus;

@Mapper
@DS("slave")
public interface IotDeviceStatusMapper extends BaseMapper<IotDeviceStatus>{

    void insertStates(
            @Param("tableName") String tableName,
            @Param("status") IotDeviceStatus iotSwitctStatus);

    List<IotDeviceStatus> page(
            @Param("page") Page<IotDeviceStatus> page,
            @Param("deviceId") String deviceId);

    IPage<IotDeviceStatus> avgByInterval(
            @Param("page") Page<IotDeviceStatus> page,
            @Param("deviceIds") List<String> deviceIds,
            @Param("begin") String begin,
            @Param("end") String end,
            @Param("interval") String interval);

}

Here we have super table which named iot_device_status and subtable named device_01. You can adjust the value of INTERVAL according to your business needs to perform statistical analysis over arbitrary time intervals.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.iot.mapper.IotDeviceStatusMapper">

    <insert id="insertStates">
        INSERT INTO ${tableName} (ts, value, source)
        VALUES (#{status.ts}, #{status.value}, #{status.source})
    </insert>

    <select id="page" resultType="org.jeecg.modules.iot.entity.po.IotDeviceStatus">
        SELECT ts, value, source
        FROM iot_device_status
        WHERE device_id = #{deviceId}
        ORDER BY ts DESC
    </select>

    <select id="avgByInterval" resultType="org.jeecg.modules.iot.entity.po.IotDeviceStatus">
        SELECT (AVG(value) * 100 + 0.5 - (AVG(value) * 100 + 0.5) % 1) / 100 AS value
        FROM iot_device_status
        WHERE ts between #{begin} AND #{end}
        AND device_id IN
        <foreach item="item" index="index" collection="deviceIds" open="(" separator="," close=")">
            #{item}
        </foreach>
        INTERVAL(#{interval})
    </select>
</mapper>