spice-connect-data

name

spice-connect-data

description

Connect Spice to data sources and query across them with federated SQL — including datasets, catalogs, views, and writes. Use this skill whenever the user wants to set up federated queries across multiple sources, create views, configure catalogs (Unity Catalog, Databricks, Iceberg), write data with INSERT INTO, or understand how Spice's query federation works. This skill focuses on the federation layer — cross-source joins, views, catalogs, and data writes. For configuring individual data source connectors (PostgreSQL params, S3 file formats, etc.), see spice-data-connector.

Connect to Data Sources

Spice federates SQL queries across 30+ data sources without ETL. Connect databases, data lakes, warehouses, and APIs, then query across them with standard SQL.

How Federation Works

Configure datasets pointing to different sources. Spice's query planner (built on Apache DataFusion) optimizes and routes queries with filter pushdown and column projection:

datasets:
  - from: postgres:customers
    name: customers
    params:
      pg_host: db.example.com
      pg_user: ${secrets:PG_USER}
  - from: s3://bucket/orders/
    name: orders
    params:
      file_format: parquet
  - from: snowflake:analytics.sales
    name: sales

-- Query across all three sources in one statement
SELECT c.name, o.order_total, s.region
FROM customers c
  JOIN orders o ON c.id = o.customer_id
  JOIN sales s ON o.id = s.order_id
WHERE s.region = 'EMEA';

Dataset Configuration

datasets:
  - from: <connector>:<identifier>
    name: <dataset_name>
    params:
      # connector-specific parameters
    acceleration:
      enabled: true # optional: materialize locally (see spice-acceleration)

Supported Connectors

Databases

Connector	From Format	Status
PostgreSQL	postgres:schema.table	Stable (also Amazon Redshift)
MySQL	mysql:schema.table	Stable
DuckDB	duckdb:database.table	Stable
MS SQL Server	mssql:db.table	Beta
DynamoDB	dynamodb:table	Release Candidate
MongoDB	mongodb:collection	Alpha
ClickHouse	clickhouse:db.table	Alpha

Data Warehouses

Connector	From Format	Status
Snowflake	snowflake:db.schema.table	Beta
Databricks (Delta Lake)	databricks:catalog.schema.table	Stable
Spark	spark:db.table	Beta

Data Lakes & Object Storage

Connector	From Format	Status
S3	s3://bucket/path/	Stable
Delta Lake	delta_lake:/path/to/delta/	Stable
Iceberg	iceberg:table	Beta
Azure BlobFS	abfs://container/path/	Alpha
File (local)	file:./path/to/data	Stable

Other Sources

Connector	From Format	Status
Spice.ai	spice.ai:path/to/dataset	Stable
Dremio	dremio:source.table	Stable
GitHub	github:github.com/owner/repo/issues	Stable
GraphQL	graphql:endpoint	Release Candidate
FlightSQL	flightsql:query	Beta
ODBC	odbc:connection	Beta
FTP/SFTP	sftp://host/path/	Alpha
HTTP/HTTPS	https://url/path/data.csv	Alpha
Kafka	kafka:topic	Alpha
Debezium CDC	debezium:topic	Alpha
SharePoint	sharepoint:site/path	Alpha
IMAP	imap:mailbox	Alpha

Common Examples

PostgreSQL

datasets:
  - from: postgres:public.users
    name: users
    params:
      pg_host: localhost
      pg_port: 5432
      pg_user: ${ env:PG_USER }
      pg_pass: ${ env:PG_PASS }
    acceleration:
      enabled: true

S3 with Parquet

datasets:
  - from: s3://my-bucket/data/sales/
    name: sales
    params:
      file_format: parquet
      s3_region: us-east-1
    acceleration:
      enabled: true
      engine: duckdb

GitHub Issues

datasets:
  - from: github:github.com/spiceai/spiceai/issues
    name: spiceai.issues
    params:
      github_token: ${ secrets:GITHUB_TOKEN }
    acceleration:
      enabled: true
      refresh_mode: append
      refresh_check_interval: 24h
      refresh_data_window: 14d

Local File

datasets:
  - from: file:./data/sales.parquet
    name: sales

File Formats

Connectors reading from object stores (S3, ABFS) or network storage (FTP, SFTP) support:

Format	file_format	Type
Apache Parquet	parquet	Structured
CSV	csv	Structured
Markdown	md	Document
Text	txt	Document
PDF	pdf	Document
Microsoft Word	docx	Document

Document files produce a table with location and content columns:

datasets:
  - from: file:docs/decisions/
    name: my_documents
    params:
      file_format: md

Hive Partitioning

datasets:
  - from: s3://bucket/data/
    name: partitioned_data
    params:
      file_format: parquet
      hive_partitioning_enabled: true

SELECT * FROM partitioned_data WHERE year = '2024' AND month = '01';

Dataset Naming

• name: foo → spice.public.foo
• name: myschema.foo → spice.myschema.foo
• Use . to organize datasets into schemas

Catalogs

Catalog connectors expose external data catalogs, preserving the source schema hierarchy. Tables are accessed as <catalog>.<schema>.<table>.

Note: Acceleration is not supported for catalog tables. Use datasets for accelerated access.

catalogs:
  - from: <connector>
    name: <catalog_name>
    params:
      # connector-specific parameters
    include:
      - 'schema.*' # optional: filter with glob patterns

Supported Catalogs

Connector	From Value	Status
Unity Catalog	unity_catalog	Stable
Databricks	databricks	Beta
Iceberg	iceberg	Beta
Spice.ai	spice.ai	Beta
AWS Glue	glue	Alpha

Catalog Example

catalogs:
  - from: unity_catalog
    name: unity
    params:
      unity_catalog_endpoint: https://my-workspace.cloud.databricks.com
      databricks_token: ${ secrets:DATABRICKS_TOKEN }
    include:
      - 'my_schema.*'

SELECT * FROM unity.my_schema.customers LIMIT 10;

Views

Views are virtual tables defined by SQL queries — useful for pre-aggregations, transformations, and simplified access:

views:
  - name: daily_sales
    sql: |
      SELECT DATE(created_at) as date, SUM(amount) as total, COUNT(*) as orders
      FROM orders
      GROUP BY DATE(created_at)

  - name: order_details
    sql: |
      SELECT o.id, c.name as customer, p.name as product, o.quantity
      FROM orders o
      JOIN customers c ON o.customer_id = c.id
      JOIN products p ON o.product_id = p.id

Views can be accelerated:

views:
  - name: rankings
    sql: |
      SELECT product_id, SUM(quantity) as total_sold
      FROM orders GROUP BY product_id ORDER BY total_sold DESC LIMIT 100
    acceleration:
      enabled: true
      refresh_check_interval: 1h

Views are read-only and queried like regular tables: SELECT * FROM daily_sales.

Writing Data

Spice supports writing to Apache Iceberg tables and Amazon S3 Tables via INSERT INTO:

datasets:
  - from: iceberg:https://catalog.example.com/v1/namespaces/sales/tables/transactions
    name: transactions
    access: read_write # required for writes

INSERT INTO transactions SELECT * FROM staging_transactions;

Referencing Secrets

Use ${ store_name:KEY } syntax in params. See spice-secrets for full configuration:

params:
  pg_user: ${ env:PG_USER }
  pg_pass: ${ secrets:PG_PASSWORD }

Documentation

• Data Connectors
• Datasets Reference
• Catalogs
• Views
• Query Federation
• Data Ingestion / Writes