DEV Community

Timecho
Timecho

Posted on

Query Apache TsFile Directly with DuckDB

#sql #opensource #apachetsfile #duckdb

Industrial IoT systems continuously generate massive amounts of time-series data from sensors, devices, and machines. Efficiently storing and analyzing this data is critical for many IoT applications.

Apache TsFile is an open-source time-series file format developed as part of the Apache IoTDB ecosystem. It serves as the underlying storage format for IoTDB and is designed to efficiently store large volumes of industrial IoT time-series data with optimized encoding, compression, and time-series access capabilities.

However, after data is stored in TsFile, users may want to explore and analyze it with different tools beyond the original database environment.
This is where the DuckDB-TsFile Extension comes in.

Bringing TsFile into DuckDB Analytics

DuckDB is an in-process analytical database that provides powerful SQL capabilities for data exploration and analysis.

Instead of converting TsFile data into another format first, the DuckDB-TsFile Extension connects Apache TsFile with DuckDB, allowing users to query TsFile table-model files directly through SQL.

The workflow becomes simple:
IoT Data → Apache TsFile → DuckDB → SQL Analytics

Getting Started

Install and load the extension:

INSTALL tsfile FROM community;
LOAD tsfile;
Enter fullscreen mode Exit fullscreen mode

Then query a TsFile using read_tsfile():

SELECT time, device_id, temperature
FROM read_tsfile('/data/measurements.tsfile', 'sensors')
WHERE time BETWEEN 1700000000000 AND 1700003600000;
Enter fullscreen mode Exit fullscreen mode

With this extension, TsFile data can be explored using familiar SQL operations such as filtering, aggregation, and analysis.

Why DuckDB + TsFile?

TsFile and DuckDB focus on different parts of the data workflow:

  • Apache TsFile focuses on efficient storage of industrial time-series data.
  • DuckDB focuses on lightweight analytical SQL processing.

By connecting them together, developers can use TsFile as a time-series data source while taking advantage of DuckDB's analytical capabilities.
This provides a simpler workflow for scenarios such as:

  • Exploring historical sensor data
  • Performing time-range analysis
  • Preparing datasets for further analytics or machine learning workflows

Current Capabilities

The DuckDB-TsFile Extension currently supports querying Apache TsFile table-model files through DuckDB.

Key capabilities include:

  • Reading TsFile data directly with read_tsfile()
  • Querying TsFile data using DuckDB SQL
  • Projection pushdown
  • Time-predicate pushdown
  • TAG predicate pushdown
  • Exporting query results to TsFile format using COPY ... TO ... (FORMAT TSFILE)

The initial release focuses on one local file and one table per scan. Features such as tree-model file support, multi-file scans, automatic table discovery, parallel scans, and FIELD predicate pushdown are not yet implemented.

Conclusion

The combination of Apache TsFile and DuckDB creates a bridge between time-series optimized storage and modern SQL analytics.

With the DuckDB-TsFile Extension, developers can access TsFile data directly through DuckDB SQL, making industrial IoT time-series analysis more flexible and accessible.

Explore the extension: https://duckdb.org/community_extensions/extensions/tsfile

Top comments (0)