SQLite Data Accelerator
Works with v1.0+
Follow this recipe to configure dataset acceleration using SQLite.
Tip: Open and refer to the SQLite Data Accelerator (opens in a new tab) documentation while completing this recipe.
Tip: Follow the Advanced Data Refresh Recipe (opens in a new tab) to learn more about advanced data refresh scenarios, such as programmatically updating refresh_sql and triggering data refreshes.
Step 1. Initialize the Spice app
Ensure the Spice CLI is installed. If not, follow the Spice Getting Started (opens in a new tab) guide to install.
Clone the Spice samples repository and navigate to the sqlite directory:
git clone https://github.com/spiceai/cookbook.git
cd cookbook/sqlite/accelerator
git clone https://github.com/spiceai/cookbook.git
cd cookbook/sqlite/accelerator
Start the Spice runtime
Output:
2024-09-10T06:54:36.185086Z INFO runtime::flight: Spice Runtime Flight listening on 127.0.0.1:50051
2024-09-10T06:54:36.187305Z INFO runtime::http: Spice Runtime HTTP listening on 127.0.0.1:8090
2024-09-10T06:54:36.385124Z INFO runtime::init::caching: Initialized sql results cache; max size: 128.00 MiB, item ttl: 1s, hashing algorithm: XXH3, encoding: none
2024-09-10T06:54:37.020990Z INFO runtime::init::dataset: Dataset taxi_trips registered (s3://spiceai-demo-datasets/taxi_trips/2024/), results cache enabled.
2024-09-10T06:54:36.185086Z INFO runtime::flight: Spice Runtime Flight listening on 127.0.0.1:50051
2024-09-10T06:54:36.187305Z INFO runtime::http: Spice Runtime HTTP listening on 127.0.0.1:8090
2024-09-10T06:54:36.385124Z INFO runtime::init::caching: Initialized sql results cache; max size: 128.00 MiB, item ttl: 1s, hashing algorithm: XXH3, encoding: none
2024-09-10T06:54:37.020990Z INFO runtime::init::dataset: Dataset taxi_trips registered (s3://spiceai-demo-datasets/taxi_trips/2024/), results cache enabled.
Step 2. Run query against the dataset using the Spice SQL REPL
In a new terminal, start the Spice SQL REPL.
Enter a query to display the longest taxi trips:
SELECT trip_distance, total_amount FROM taxi_trips ORDER BY trip_distance DESC LIMIT 10;
SELECT trip_distance, total_amount FROM taxi_trips ORDER BY trip_distance DESC LIMIT 10;
Output:
+---------------+--------------+
| trip_distance | total_amount |
| float64 | float64 |
+---------------+--------------+
| 312722.3 | 22.15 |
| 97793.92 | 36.31 |
| 82015.45 | 21.56 |
| 72975.97 | 20.04 |
| 71752.26 | 49.57 |
| 59282.45 | 33.52 |
| 59076.43 | 23.17 |
| 58298.51 | 18.63 |
| 51619.36 | 24.2 |
| 44018.64 | 52.43 |
+---------------+--------------+
Time: 2.1508365 seconds. 10 rows.
+---------------+--------------+
| trip_distance | total_amount |
| float64 | float64 |
+---------------+--------------+
| 312722.3 | 22.15 |
| 97793.92 | 36.31 |
| 82015.45 | 21.56 |
| 72975.97 | 20.04 |
| 71752.26 | 49.57 |
| 59282.45 | 33.52 |
| 59076.43 | 23.17 |
| 58298.51 | 18.63 |
| 51619.36 | 24.2 |
| 44018.64 | 52.43 |
+---------------+--------------+
Time: 2.1508365 seconds. 10 rows.
Step3. Enable SQLite Accelerator
Use a text editor to open spicepod.yaml and add the acceleration block shown below. Save.
Before:
version: v1
kind: Spicepod
name: sqlite
datasets:
- from: s3://spiceai-demo-datasets/taxi_trips/2024/
name: taxi_trips
description: taxi trips in s3
params:
file_format: parquet
version: v1
kind: Spicepod
name: sqlite
datasets:
- from: s3://spiceai-demo-datasets/taxi_trips/2024/
name: taxi_trips
description: taxi trips in s3
params:
file_format: parquet
After:
version: v1
kind: Spicepod
name: sqlite
datasets:
- from: s3://spiceai-demo-datasets/taxi_trips/2024/
name: taxi_trips
description: taxi trips in s3
params:
file_format: parquet
acceleration:
enabled: true
engine: sqlite
mode: file
version: v1
kind: Spicepod
name: sqlite
datasets:
- from: s3://spiceai-demo-datasets/taxi_trips/2024/
name: taxi_trips
description: taxi trips in s3
params:
file_format: parquet
acceleration:
enabled: true
engine: sqlite
mode: file
The following output is shown in the Spice runtime terminal confirming new configuration is applied.
2024-09-10T06:59:21.908667Z INFO runtime: Unloaded dataset taxi_trips
2024-09-10T06:59:22.524295Z INFO runtime::init::dataset: Dataset taxi_trips registered (s3://spiceai-demo-datasets/taxi_trips/2024/), acceleration (sqlite:file), results cache enabled.
2024-09-10T06:59:22.525789Z INFO runtime_table::accelerated::refresh_task: Loading data for dataset taxi_trips
2024-09-10T06:59:39.244473Z INFO runtime_table::accelerated::refresh_task: Loaded 2,964,624 rows (399.38 MiB) for dataset taxi_trips in 16s 718ms.
2024-09-10T06:59:21.908667Z INFO runtime: Unloaded dataset taxi_trips
2024-09-10T06:59:22.524295Z INFO runtime::init::dataset: Dataset taxi_trips registered (s3://spiceai-demo-datasets/taxi_trips/2024/), acceleration (sqlite:file), results cache enabled.
2024-09-10T06:59:22.525789Z INFO runtime_table::accelerated::refresh_task: Loading data for dataset taxi_trips
2024-09-10T06:59:39.244473Z INFO runtime_table::accelerated::refresh_task: Loaded 2,964,624 rows (399.38 MiB) for dataset taxi_trips in 16s 718ms.
Run query to display the longest taxi trips again:
SELECT trip_distance, total_amount FROM taxi_trips ORDER BY trip_distance DESC LIMIT 10;
SELECT trip_distance, total_amount FROM taxi_trips ORDER BY trip_distance DESC LIMIT 10;
Output:
+---------------+--------------+
| trip_distance | total_amount |
| float64 | float64 |
+---------------+--------------+
| 312722.3 | 22.15 |
| 97793.92 | 36.31 |
| 82015.45 | 21.56 |
| 72975.97 | 20.04 |
| 71752.26 | 49.57 |
| 59282.45 | 33.52 |
| 59076.43 | 23.17 |
| 58298.51 | 18.63 |
| 51619.36 | 24.2 |
| 44018.64 | 52.43 |
+---------------+--------------+
Time: 0.193560667 seconds. 10 rows.
+---------------+--------------+
| trip_distance | total_amount |
| float64 | float64 |
+---------------+--------------+
| 312722.3 | 22.15 |
| 97793.92 | 36.31 |
| 82015.45 | 21.56 |
| 72975.97 | 20.04 |
| 71752.26 | 49.57 |
| 59282.45 | 33.52 |
| 59076.43 | 23.17 |
| 58298.51 | 18.63 |
| 51619.36 | 24.2 |
| 44018.64 | 52.43 |
+---------------+--------------+
Time: 0.193560667 seconds. 10 rows.
Observe query execution time decreased from 2.1508365 to 0.193560667 seconds.
![dependabot[bot]](https://avatars.githubusercontent.com/in/29110?v=4)
