To use SQLite as Data Accelerator, specify sqlite as the engine for acceleration.
datasets : - from : spice.ai:path.to.my_dataset name : my_dataset acceleration : engine : sqlite Warning
Spice v2.4 deprecates the sqlite engine, and Spice v2.5 removes it. Move each acceleration to Spice Cayenne : set engine: cayenne. On native Windows, Cayenne is not available: run Spice in Docker or WSL first. Restart Spice and confirm that Cayenne finished its first load before you delete anything. The old acceleration can hold data that the source no longer has. Then delete the SQLite database file and its -wal and -shm files, unless another dataset still uses that file on sqlite. A mode: memory acceleration leaves no file. See the Cayenne migration guide .
Configuration
The connection to SQLite can be configured by providing the following params:
busy_timeout: Optional. Specifies the duration for the SQLite busy timeout when connecting to the database file. Default: 5000 ms.
Configuration params are provided in the acceleration section of a dataset. Other common acceleration fields can be configured for sqlite, see see datasets .
Warning
LIMITATIONS
The SQLite accelerator doesn't support advanced grouping features such as ROLLUP and GROUPING.
In SQLite, CAST(value AS DECIMAL) doesn't convert an integer to a floating-point value if the casted value is an integer. Operations like CAST(1 AS DECIMAL) / CAST(2 AS DECIMAL) will be treated as integer division, resulting in 0 instead of the expected 0.5. Use FLOAT to ensure conversion to a floating-point value: CAST(1 AS FLOAT) / CAST(2 AS FLOAT).
Updating a dataset with SQLite acceleration while the Spice Runtime is running (hot-reload) will cause SQLite accelerator query federation to disable until the Runtime is restarted.
The SQLite accelerator doesn't support arrow Interval types, as SQLite doesn't have a native interval type.
The SQLite accelerator only supports arrow List types of primitive data types; lists with structs are not supported.
Warning
MEMORY CONSIDERATIONS
When accelerating a dataset using mode: memory (the default), some or all of the dataset is loaded into memory. Ensure sufficient memory is available, including overhead for queries and the runtime, especially with concurrent queries.