SQL Federation & Acceleration

Spice is one runtime that does two jobs. Federation runs one SQL query across many data sources. Acceleration copies the tables that you query most into a local engine, so those queries do not wait on the source. You declare both in a spicepod.yaml file:

1. Connect: each dataset names a source in from, such as postgres:public.customers or s3://example-bucket/orders/. Spice adds the dataset to its SQL catalog.
2. Federate: Spice plans each query with Apache DataFusion. It sends filters and other supported operations to each source, and then joins the results.
3. Accelerate: set acceleration.enabled: true on a dataset. Spice loads the dataset into an accelerator engine and answers queries from that local copy.
4. Refresh: Spice reloads the local copy on each refresh_check_interval, or applies a stream of changes when refresh_mode is changes.

The following Spicepod federates a PostgreSQL table and accelerates a Parquet dataset from Amazon S3 in Spice Cayenne:

version: v1
kind: Spicepod
name: orders-analytics

datasets:
  - from: postgres:public.customers
    name: customers
    params:
      pg_host: ${secrets:PG_HOST}
      pg_db: app
      pg_user: ${secrets:PG_USER}
      pg_pass: ${secrets:PG_PASS}

  - from: s3://example-bucket/orders/
    name: orders
    params:
      file_format: parquet
    acceleration:
      enabled: true
      engine: cayenne
      mode: file
      refresh_check_interval: 10m

Start the runtime with spice run. Then send SQL to the HTTP API on port 8090:

curl http://localhost:8090/v1/sql \
  -H 'Content-Type: text/plain' \
  -d 'SELECT c.name, SUM(o.total) AS revenue
      FROM orders o JOIN customers c ON o.customer_id = c.id
      GROUP BY c.name ORDER BY revenue DESC LIMIT 10'

Spice reads orders from the local Cayenne files. It sends the customers part of the query to PostgreSQL. To get started, read the federation docs and the acceleration docs.

You can use both in one query, as in the example above. The SQL results cache is on by default for both. A repeated query can return a recent cached result, not a new read of the source or the local copy. Configure it with runtime.caching.sql_results.

Use Spice Cayenne. Since Spice v2.4, Cayenne is the default engine on Linux and macOS, and every other engine is deprecated. On Windows, the default is Arrow. Set acceleration.mode: file to keep the data on disk, because the default mode is memory:

acceleration:
  enabled: true
  engine: cayenne
  mode: file

In file mode, Cayenne keeps each dataset in columnar files on local disk. It handles datasets of 10 GB or larger. Add indexes and a primary_key to an accelerated dataset to speed up lookups. To use Cayenne on Windows, run Spice in Docker or WSL. Read the Spice Cayenne docs.

The refresh_mode setting controls how Spice updates the local copy:

• full: Spice reloads the full dataset on each refresh. This is the default for most connectors.
• append: Spice adds only new rows. For a streaming source such as Kafka, Spice appends each new message. For other sources, set a time_column so that Spice can find the new rows.
• changes: Spice applies a change data capture (CDC) stream, such as PostgreSQL logical replication, DynamoDB Streams, or Debezium on Kafka. This is the default for debezium and cdc datasets. Set it on PostgreSQL, MySQL, MongoDB, and DynamoDB datasets.
• caching: Spice stores the results of HTTP-based datasets and uses the request metadata as the cache key. On a cache miss, Spice sends the request to the source.
• snapshot: Spice loads a read-only copy from the snapshot store only, and does not read the source. Enable snapshots to use it.

Read data refresh for each mode's settings.

With the default settings, queries on an accelerated dataset read only the local copy. Three settings let some queries go to the source:

• ready_state: on_registration or on_schema_resolved makes the dataset ready before the first load completes. Until then, queries read an acceleration from a previous run if one exists. Otherwise they go to the source. The default, on_load, waits for the load.
• on_zero_results: use_source sends the query to the source when the local copy returns no rows. The default is return_empty.
• refresh_mode: caching sends the request to the source on a cache miss.

Keep the defaults when queries must read only the local copy.