Hi everyone,

I would like to share some recent experiments integrating SQLite with TsFile, 
and invite anyone interested to join the discussion, development, and 
maintenance.


As a time-series data file format, TsFile can serve a variety of data 
collection and storage scenarios. In conversations with users, I have 
encountered requests to store TsFile data directly on embedded devices. These 
devices often have tight constraints on storage space, memory, and runtime 
overhead, which presents an opportunity to benefit from TsFile's compression 
and storage efficiency.


However, using TsFile Writer directly on a device also raises questions about 
the durability of recent data. Some records remain temporarily in memory while 
data is organized and written in batches. If the device suddenly loses power or 
the process terminates unexpectedly, records that have not yet been persisted 
may be lost. This is particularly relevant to fault diagnosis: the last records 
before a failure may be exactly what is needed to understand its cause. This 
concern has made some users hesitant to use TsFile directly on edge devices.


To explore this, I have implemented a hybrid storage mechanism combining SQLite 
and TsFile. SQLite handles recent writes, using its transaction and journaling 
mechanisms to manage persistence while allowing recent rows to be updated or 
deleted. Once data no longer needs to be modified, it can be sealed into TsFile 
to take advantage of its compression and historical data storage capabilities.


Sealing is also performed through SQL within SQLite. The extension handles the 
operation internally, and it participates in SQLite's transaction management. 
Applications work with the same logical table throughout, querying recent data 
in SQLite and historical data in TsFile together without having to combine the 
two sets of results themselves. Sealed rows remain queryable but can no longer 
be updated or deleted.


The current implementation supports three ways to use it:


- **Start writing to an empty table:** Declare the columns and a data 
directory. New rows initially enter SQLite's hot data area and can be sealed as 
needed.
- **Continue writing from an existing TsFile:** Specify the source file, its 
internal table name, and a new data directory. The schema is inferred from the 
file. The original file remains unchanged, and new rows with timestamps later 
than the source table's maximum timestamp enter the hot data area.
- **Query an existing TsFile directly:** Specify the source file and its 
internal table name without providing a data directory. This creates a 
read-only logical table that can be queried using SQL.


The extension also supports exporting the complete contents of a table through 
SQL. Export automatically seals the current hot data and then produces an 
independent TsFile copy. Users do not need to seal the data manually beforehand.


The goal of this experiment is to combine the strengths of both systems: SQLite 
for transactional reads and writes in embedded environments, and TsFile for 
time-series compression and historical data storage. We hope this combination 
can make TsFile easier to adopt on edge devices and extend the range of 
scenarios it can serve.


The code for this integration experiment is now available on the 
[experiment-sqlite 
branch](https://github.com/apache/tsfile/tree/experiment-sqlite) of the Apache 
TsFile repository. It includes the implementation, tests, and user manuals in 
Chinese and English. Please refer to the [Chinese user 
manual](https://github.com/apache/tsfile/blob/experiment-sqlite/cpp/src/sqlite/USER_GUIDE.md)
 or the [English user 
manual](https://github.com/apache/tsfile/blob/experiment-sqlite/cpp/src/sqlite/USER_GUIDE_EN.md)
 for instructions on building and trying it out.


Next, I plan to prepare this functionality for inclusion in `tsfile-extras`, 
continuing to explore how SQLite and TsFile can work together in embedded and 
edge environments.


Feedback and use cases are welcome, as are contributions to development and 
maintenance. If you have relevant devices, applications, or real-world data, I 
would be glad to work together to evaluate the approach and improve its 
reliability, performance, and usability.


Thank you!
Colin

Reply via email to