We’re excited to announce the official release of TimechoDB v2.0.1 and v2.0.2, the enterprise-grade time series database based on Apache IoTDB， designed to meet the rigorous demands of modern data management.

This release introduces several powerful features including table mode access control, user management, and fine-grained operation authorization. It also brings new capabilities such as user-defined functions (UDFs) for the table mode, system tables, and nested queries. We’ve also significantly enhanced the data subscription mechanism, and unified the import-data/export-data tools to support TsFile, CSV, and SQL formats. Performance, stability, and monitoring features have all been comprehensively improved.

Key Updates

• Time Series Table Mode: Full SQL support including SELECT, WHERE, JOIN, GROUP BY, ORDER BY, LIMIT, and nested queries for the table mode.

• Query Engine Enhancements:

  • Extended function/operator support (logical operators, math functions, and time series-specific functions like DIFF)

  • Table mode UDF management: support for UDSF (user-defined scalar functions) and UDAF (user-defined aggregate functions)

  • Configurable loading of UDFs, PipePlugins, Triggers, and AINodes via URI

  • Access control: table mode now supports user management and authorization

  • New system tables and improved management operations

• System Enhancements:

  • C# client support for the table mode

  • C++ session interface for table mode writes

  • Multi-tiered storage now supports S3-compatible object storage beyond AWS

  • UDF library expanded with a new pattern_match function

• Data Synchronization: Table mode now supports metadata sync and sync deletion

• Stream Processing: Plugins like WriteBackSink and OPC UA Sink are adapted to support the table mode

• Ecosystem: Kubernetes Operator integration supported

• Tools:

  • Enhanced import-data/export-data scripts with support for new data types (e.g., strings, large binary objects, dates, timestamps)

  • Unified scripts that support import/export of TsFile, CSV, and SQL formats

Feature Spotlight: Nested Queries

Nested queries (also known as subqueries) are now supported in table mode. These allow for embedding one or more queries within another. Based on the shape of the result set, subqueries are classified into:

• Scalar subquery

• Column subquery

• Table subquery (Row subqueries are not yet supported)

Example: Non-Correlated Scalar Subquery

Sample Data:

IoTDB> select * from table1 WHERE device_id = 'd01' ;
+-----------------------------+-----------+-----------+---------+----------+------+-------+-------------+-------------+-------------+-------------+
|                         time|    country|       city|   region| device_id| color|   type| temperature1| temperature2| temperature3| temperature4|
+-----------------------------+-----------+-----------+---------+----------+------+-------+-------------+-------------+-------------+-------------+
|2024-09-24T14:13:30.000+08:00|    germany|     berlin|    mitte|      d01|   red|       A|           30|           30|         30.0|         30.0|      
|2024-09-24T14:14:30.000+08:00|    germany|     berlin|    mitte|      d01|   red|       A|           40|           40|         40.0|         40.0| 
|2024-09-24T14:15:30.000+08:00|    germany|     berlin|    mitte|      d01|   red|       A|           50|           50|         50.0|         50.0| 
|2024-09-24T14:16:30.000+08:00|    germany|     berlin|    mitte|      d01|   red|       A|           60|           60|         60.0|         60.0|  
|2024-09-24T14:17:30.000+08:00|    germany|     berlin|    mitte|      d01|   red|       A|           70|           70|         70.0|         70.0| 
+-----------------------------+-----------+-----------+---------+----------+------+-------+-------------+-------------+-------------+-------------+
Total line number = 64

Goal: From table1, find all records where device_id = 'd01' and temperature1 is greater than or equal to the average temperature1 for that device.

SELECT temperature1 FROM table1 
     WHERE device_id = 'd01' 
     and temperature1 >= (SELECT avg(temperature1) from table1 WHERE device_id = 'd01');

Sample Output:

+-----+
|   s1|
+-----+
|   50|   
|   60|
|   70|
+-----+
Total line number = 3

More Details

For more information about the release, feel free to reach out to the Timecho team. We’re here to help you get the most out of TimechoDB!