:::warning[Deprecated]
The sqlite acceleration engine is deprecated in v2.4 and will be removed in v2.5. A Spicepod with a dataset or view that uses it logs one warning at startup, and one on each hot reload that adds or changes such a component, naming those datasets and views. A cluster executor does not log it. Runtimes up to and including v2.4.0-rc.3 do not log this warning. Move these accelerations to Spice Cayenne (opens in a new tab) before upgrading to v2.5; see Migrating to Spice Cayenne (opens in a new tab).
:::
To use SQLite as Data Accelerator, specify sqlite as the engine for acceleration.
The connection to SQLite can be configured by providing the following params:
sqlite_file: The filename for the file to back the SQLite database. Only applies if mode is file.busy_timeout: Optional. Specifies the duration for the SQLite busy timeout (opens in a new tab) when connecting to the database file. Default: 5000 ms, or 15000 ms when the acceleration storage_profile resolves to EBS-class network storage (where fsync latency spikes are more frequent).Configuration params are provided in the acceleration section of a dataset. Other common acceleration fields can be configured for sqlite, see see datasets (opens in a new tab).
:::warning[Limitations]
Interval types, as SQLite (opens in a new tab) doesn't have a native interval type.List types of primitive data types; lists with structs are not supported.Dictionary or Map types.ROLLUP, CUBE, and GROUPING SETS are evaluated in Spice, not in SQLite, because SQLite has no grouping sets.TRY_CAST is never sent to SQLite, and a CAST is sent only when SQLite evaluates it the same way Spice does. See Casts and Federation (opens in a new tab).AVG and SUM over a decimal column are never sent to SQLite. SQLite stores a decimal value as a double, so a value with more than about 15 significant digits reads back changed. See Decimal Aggregates and Federation (opens in a new tab).upper, lower, concat, LIKE, and ILIKE are evaluated in Spice, not in SQLite, because SQLite answers them differently. Several functions SQLite does not have, such as btrim and md5, and every aggregate except count, sum, avg, min, and max, are also evaluated in Spice. See Functions and Federation (opens in a new tab).:::
:::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.
In-memory limitations can be mitigated by storing acceleration data on disk, which is supported by duckdb (opens in a new tab) and sqlite (opens in a new tab) accelerators by specifying mode: file.
:::
SQLite's CAST never fails. A cast into a numeric type converts the longest numeric prefix of its operand and returns 0 when there is none, so CAST('abc' AS BIGINT) returns 0 and CAST('12abc' AS BIGINT) returns 12 (see CAST expressions (opens in a new tab) in the SQLite documentation). SQLite also formats floats and booleans as text differently, stores dates and timestamps in its own representation, and has no TRY_CAST. To return the same results as an unaccelerated query, Spice sends a cast to the SQLite accelerator only when SQLite evaluates it the same way:
Float64, or into text.Float32 cast into Float64.YYYY-MM-DD, such as DATE '1994-01-01'.Every other CAST, and every TRY_CAST, is evaluated in Spice above the scan of the accelerated table. This includes a cast whose operand type Spice cannot determine. For example, CAST(s AS BIGINT) over a string column that holds 'abc' returns the error a DataFusion query returns, not 0, and CAST(1 AS DECIMAL) / CAST(2 AS DECIMAL) returns 0.5, not the result of SQLite integer division. A query whose plan contains such a cast still runs, but that cast does not push down to SQLite.
SQLite has no decimal type. It stores a decimal value as a REAL or an INTEGER, and computes avg and sum over it in floating point or in 64-bit integers. Its avg returns a rounded floating-point value where Spice computes the average in decimal arithmetic and truncates it to the scale of the result type. Its sum over values stored as integers fails with an integer overflow once the total exceeds the 64-bit integer range, and a sum that includes a value stored as a REAL is computed in floating point instead.
So that these aggregates follow Spice's decimal semantics, Spice does not send AVG or SUM over a decimal column to the SQLite accelerator, whether called as an aggregate or as a window function. The aggregate is evaluated in Spice above the scan of the accelerated table, and the scan is still sent to SQLite with its filters and projection, except for any expression Spice keeps local, such as the casts in Casts and Federation (opens in a new tab). An aggregate whose argument type Spice cannot determine is also evaluated in Spice. Aggregates over integer and floating-point columns, and other aggregates over decimal columns such as MIN, MAX, and COUNT, are still sent to SQLite.
This changes where the aggregate runs, not how SQLite stores the values. SQLite stores a decimal value as a double, so a value with more than about 15 significant digits is rounded when it is written and reads back changed, with no error (spiceai/spiceai#14662 (opens in a new tab)). An aggregate evaluated in Spice is exact over the values SQLite returns, so over such values it can still differ from an unaccelerated query. In the reproduction on that issue, the arrow and duckdb accelerators return such a value unchanged.
Spice keeps the following functions and expressions out of the SQL sent to the SQLite accelerator, because SQLite does not have them or answers them differently. Each is evaluated in Spice above the scan of the accelerated table, over the values SQLite returns. This changes where the expression runs, not how SQLite stores the data, so a decimal value that SQLite rounded on write still reads back changed, as described in Decimal Aggregates and Federation (opens in a new tab):
upper and lower. SQLite changes the case of ASCII letters only, so upper('Ångström') returns 'ÅNGSTRöM' in SQLite and 'ÅNGSTRÖM' in Spice.concat. SQLite's concat skips a NULL argument, while Spice's concat returns NULL when any argument is NULL.LIKE and ILIKE. SQLite's LIKE ignores the case of ASCII letters, so 'alice' LIKE '%ALICE%' is true in SQLite and false in Spice. SQLite has no ILIKE.btrim (including trim), to_hex, md5, sha256, encode, date_part (including EXTRACT), date_trunc, regexp_like, regexp_replace, regexp_match, regexp_instr, and regexp_count, which SQLite does not have or answers differently.count, sum, avg, min, and max, and any aggregate call with more than one argument, such as count(a, b). This includes string_agg, array_agg, stddev, median, and approx_distinct.row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, and nth_value) and the five aggregates above, a window function with DISTINCT, and an aggregate or window function with IGNORE NULLS.These rules add to the checks Spice applies to every federated source, which already keep Spice-defined and user-registered functions, DataFusion's cast functions such as arrow_cast, and type functions such as arrow_typeof in Spice. A DataFusion built-in scalar function that passes those checks and is not listed above is sent to SQLite by name. The list covers the functions Spice is known to evaluate differently from SQLite; it is not a check of every function against SQLite.