SQLite TsFile Extension

This guide is intended for data engineers and platform developers who need to read and write Apache TsFile in SQLite. tsfile_sqlite is a SQLite loadable extension that registers the tsfile_hybrid virtual table module. It combines SQLite hot data and TsFile cold data behind a single logical table: newly written rows stay in SQLite and can be updated or deleted, while sealed rows are written to TsFile and remain queryable but immutable.

Remember three things: load the extension with .load, create tables with CREATE VIRTUAL TABLE ... USING tsfile_hybrid(...), and seal hot data with tsfile_seal. The current version supports local, Table-model TsFiles.

Quick Start

Load the Extension

Load the extension in the SQLite CLI or your application. It must be reloaded on every new connection:

.load /path/to/tsfile_sqlite

Create a Writable Table and Write Rows

CREATE VIRTUAL TABLE sensor USING tsfile_hybrid(
  time TIMESTAMP TIME,
  device STRING TAG,
  temperature DOUBLE FIELD,
  directory='/data/sensor-segments',
  timestamp_precision='ms'
);

INSERT INTO sensor VALUES
  (1000, 'd1', 21.5),
  (2000, 'd1', 22.0),
  (3000, 'd2', 19.0);

time stores millisecond timestamps, device identifies devices, and temperature stores measurements. directory is the exclusive directory used to seal this table's data; the extension creates it. At this point all three rows are still in SQLite and not yet sealed.

Seal into TsFile

SELECT tsfile_seal('main.sensor', 200);

Returns the number of sealed rows. The argument is the size limit for a single output file, in MB. After sealing, the rows remain queryable but can no longer be modified.

Query

SELECT time, device, temperature FROM sensor WHERE device = 'd1';

Reading and Querying

Open an Existing TsFile (Read-Only)

CREATE VIRTUAL TABLE history USING tsfile_hybrid(
  file='/data/export/sensor-0001.tsfile',
  source_table='sensor'
);

SELECT * FROM history;

file is a file path (or a directory), and source_table is the table name stored inside the TsFile. A table without directory is read-only and needs no column declarations.

Continue Writing on Top of an Existing TsFile

CREATE VIRTUAL TABLE continued USING tsfile_hybrid(
  file='/data/export/sensor-0001.tsfile',
  source_table='sensor',
  directory='/data/continued-segments'
);

INSERT INTO continued VALUES (4000, 'd1', 23.0);

Historical rows are read from the TsFile, and new rows are written to the SQLite hot region. The write boundary is per device: a new time must be no earlier than that device's maximum time in the source file plus one. New devices are not constrained by old device boundaries.

Filters and Pushdown

Time ranges, TAG equality conditions, and FIELD comparison conditions are pushed down to the TsFile read layer; expressions that cannot be pushed down are evaluated by SQLite over the result set.

Filtering by device or another device condition is the most effective pruning: data is organized by device, so a device filter can skip other devices' data, whereas a global time-range query may scan a large amount of data even when it returns few rows. Business queries should include a device condition whenever possible.

Writing and Sealing

Table Modes

Start from scratch: declare columns plus an exclusive directory and a time precision; writable.
Read-only query: file= without directory.
Continue writing: file= plus directory=, appending on top of an existing TsFile.

Hot Writes, Updates, and Deletes

BEGIN;
INSERT INTO sensor VALUES (4000, 'd2', 20.0);
UPDATE sensor SET temperature = 22.5 WHERE time = 2000 AND device = 'd1';
DELETE FROM sensor WHERE time = 3000;
COMMIT;

Rows that are not yet sealed can be modified. If an UPDATE or DELETE actually hits a sealed row, the whole statement fails.

Seal

SELECT tsfile_seal('main.sensor', 200);

Seals all current hot rows and returns the number of sealed rows. Seal runs synchronously and can participate in an explicit transaction (BEGIN IMMEDIATE; ... ROLLBACK/COMMIT;). After sealing, each device‘s new data must be no earlier than that device’s new maximum time plus one.

Compact Cold Segments

SELECT tsfile_compact('main.sensor', 50, 200);

50 is the small-file threshold and 200 is the target output size limit, both in MB. Only files smaller than the threshold are compacted; the target size cannot be smaller than the threshold.

Export

SELECT tsfile_export('main.sensor', '/data/export');

Export copies sealed cold segments directly and does not copy hot-region data. Run seal first when you need to deliver current hot data. The output directory must be an absolute path that does not yet exist.

Type Mapping and NULL

Declared type	Written value
BOOLEAN	integer; 0 is false, non-zero is true
INT32, DATE	integer within int32 range
INT64, TIMESTAMP	integer within int64 range
FLOAT, DOUBLE	integer or decimal
STRING, TEXT	text
BLOB	binary value, preserving length and zero bytes

TIME cannot be NULL. TAG and FIELD may be NULL; TAG must be STRING. New rows with the same time and TAG combination conflict. NULL, the empty string, and the text 'null' are all distinct; use IS NULL to find NULLs.

End-to-End Example

Collect from scratch, seal, export, and read back:

-- Create a table and write rows
CREATE VIRTUAL TABLE sensor USING tsfile_hybrid(
  time TIMESTAMP TIME,
  device STRING TAG,
  temperature DOUBLE FIELD,
  directory='/tmp/sqlite-demo/segments',
  timestamp_precision='ms'
);
INSERT INTO sensor VALUES (1000,'d1',21.5), (2000,'d1',22.0), (3000,'d2',19.0);

-- Seal and export
SELECT tsfile_seal('main.sensor', 200);
SELECT tsfile_export('main.sensor', '/tmp/sqlite-demo/export');

-- Read back as read-only
CREATE VIRTUAL TABLE history USING tsfile_hybrid(
  file='/tmp/sqlite-demo/export', source_table='sensor'
);
SELECT time, device, temperature FROM history ORDER BY time;

Current Limitations and Troubleshooting

Before Writing

Confirm the table is writable (provides directory) or intended as read-only.
Confirm the TIME column is non-NULL and new times are no earlier than the device's watermark.
Confirm TAG columns are STRING and column names are not duplicated.

Known Limitations

The current version supports one Table-model table in one local TsFile file or directory.
ASCII uppercase TAG/FIELD names may be lowercased in a read-only view; the TIME column name is preserved. SQLite identifiers are case-insensitive, but be aware when downstream systems map by exact measurement name.
A FIELD column name whose UTF-8 byte length reaches 1018 may fail cold reads; keep it at or below 1017 bytes.
Global time-range queries without a device condition can be slow; include a device condition whenever possible.
A long read transaction that continues querying an old snapshot after a compact may report a missing or changed file; end and restart the transaction.

Capability Quick Reference

Task	Entry point
Load the extension	`.load /path/to/tsfile_sqlite`
Create a writable table	`CREATE VIRTUAL TABLE ... USING tsfile_hybrid(...)`
Query a TsFile read-only	`file=...` + `source_table=...`
Continue writing	`file=...` + `directory=...`
Seal	`tsfile_seal('main.t', max_mb)`
Compact cold segments	`tsfile_compact('main.t', small_mb, target_mb)`
Export	`tsfile_export('main.t', '/path')`
Inspect status	`tsfile_table_info` / `tsfile_device_info` / `tsfile_schema`
Verify files	`tsfile_verify('main.t')`