spiceai/docs

spiceai/docs/README.md

title: 'Data Connectors' sidebar_label: 'Data Connectors' description: 'Learn how to use Data Connector to query external data.' image: /img/og/data-connectors.png sidebar_position: 1 pagination_prev: null pagination_next: null tags:

data-connectors
overview
federation

Data Connectors provide connections to databases, data warehouses, and data lakes for federated SQL queries and data replication.

Each connector is configured using the from field in a dataset definition. For example:

datasets:
  - from: postgres:public.orders    # Database connector
    name: orders
    params:
      pg_host: localhost
      pg_db: mydb
      pg_user: reader
      pg_pass: ${secrets:PG_PASS}

  - from: s3://my-bucket/events/    # Object storage connector
    name: events
    params:
      file_format: parquet
      s3_auth: iam_role

datasets:
  - from: postgres:public.orders    # Database connector
    name: orders
    params:
      pg_host: localhost
      pg_db: mydb
      pg_user: reader
      pg_pass: ${secrets:PG_PASS}

  - from: s3://my-bucket/events/    # Object storage connector
    name: events
    params:
      file_format: parquet
      s3_auth: iam_role

Supported Data Connectors include:

Name	Description	Status	Protocol/Format
`adbc`	ADBC	Stable	Arrow
`databricks (mode: delta_lake)`	Databricks (opens in a new tab)	Stable	S3/Delta Lake
`databricks (mode: spark_connect)`	Databricks (opens in a new tab)	Stable	Spark Connect (opens in a new tab)
`databricks (mode: sql_warehouse)`	Databricks (opens in a new tab)	Stable	SQL Statement Execution API
`delta_lake`	Delta Lake	Stable	Delta Lake
`dremio`	Dremio (opens in a new tab)	Stable	Arrow Flight
`duckdb`	DuckDB	Stable	Embedded
`file`	File	Stable	Parquet, CSV, ORC
`github`	GitHub	Stable	GitHub API
`http`, `https`	HTTP(s) (dynamic headers, pagination)	Stable	Parquet, CSV, JSON
`localpod`	Local dataset replication (opens in a new tab)	Stable
`postgres`	PostgreSQL (with native WAL CDC)	Stable
`s3`	S3 (opens in a new tab)	Stable	Parquet, CSV, ORC
`mysql`	MySQL (with native binlog CDC)	Stable
`spice.ai`	Spice.ai (opens in a new tab)	Stable	Arrow Flight
`dynamodb`	Amazon DynamoDB (with Streams)	Stable
`iceberg`	Apache Iceberg (opens in a new tab) (read+write)	Stable	Parquet
`flightsql`	FlightSQL	Stable	Arrow Flight SQL
`glue`	AWS Glue (opens in a new tab)	Stable	Iceberg, Parquet, ORC, CSV
`mongodb`	MongoDB (with native Change Streams CDC)	Stable
`graphql`	GraphQL	Release Candidate	JSON
`cosmosdb`	Azure Cosmos DB (NoSQL)	Release Candidate
`git`	Git repositories (opens in a new tab)	Release Candidate
`snowflake`	Snowflake	Release Candidate	Arrow
`oracle`	Oracle	Release Candidate	Oracle ODPI-C (opens in a new tab)
`ducklake`	[DuckLake][ducklake]	Beta	Parquet
`mssql`	Microsoft SQL Server	Beta	Tabular Data Stream (TDS)
`odbc`	ODBC (Spice.ai Enterprise)	Beta	ODBC
`spark`	Spark	Beta	Spark Connect (opens in a new tab)
`sharepoint`	Microsoft SharePoint	Beta	Object-store listing
`kafka`	Kafka	Beta	Kafka + JSON
`abfs`	Azure BlobFS	Alpha	Parquet, CSV, ORC
`clickhouse`	ClickHouse	Alpha
`debezium`	Debezium CDC (opens in a new tab) (Kafka consumer)	Alpha	Kafka + JSON
`cdc`	Debezium push ingest (opens in a new tab) (no Kafka)	Alpha	JSON + Avro HTTP
`elasticsearch`	Elasticsearch (BM25 + kNN + RRF) (Spice.ai Enterprise)	Alpha
`gcs`, `gs`	[Google Cloud Storage][gcs]	Alpha	Parquet, CSV, JSON, ORC
`hf`	Hugging Face (opens in a new tab) datasets	Alpha	Parquet, CSV, TSV, JSON, JSONL, ORC
`ftp`, `sftp`	FTP/SFTP	Alpha	Parquet, CSV, ORC
`imap`	IMAP	Alpha	IMAP Emails
`scylladb`	ScyllaDB (Spice.ai Enterprise)	Alpha
`smb`	SMB 3.1.1	Alpha	SMB
`nfs`	NFS (Spice.ai Enterprise)	Alpha	Parquet, CSV, JSON, ORC

File Formats

Data connectors that read files from object stores (S3, Azure Blob, GCS) or network-attached storage (FTP, SFTP, SMB, NFS) support a variety of file formats. These connectors work with both structured data formats (Parquet, CSV) and document formats (Markdown, PDF).

Specifying File Format

When connecting to a directory, specify the file format using params.file_format:

datasets:
  - from: s3://bucket/data/sales/
    name: sales
    params:
      file_format: parquet

datasets:
  - from: s3://bucket/data/sales/
    name: sales
    params:
      file_format: parquet

When connecting to a specific file, the format is inferred from the file extension:

datasets:
  - from: sftp://files.example.com/reports/quarterly.parquet
    name: quarterly_report

datasets:
  - from: sftp://files.example.com/reports/quarterly.parquet
    name: quarterly_report

Supported Formats

Name	Parameter	Status	Description
Apache Parquet (opens in a new tab)	`file_format: parquet`	Stable	Columnar format optimized for analytics
Apache ORC (opens in a new tab)	`file_format: orc`	Stable	Columnar format. Read-only; a directory of ORC objects infers its schema by merging every object's footer.
CSV (opens in a new tab)	`file_format: csv`	Stable	Comma-separated values
JSON	`file_format: json`	Stable	JavaScript Object Notation
Delta Lake (opens in a new tab)	`file_format: delta`	Stable	Open table format with ACID transactions. Object stores only.
Apache Iceberg (opens in a new tab)	`file_format: iceberg`	Stable	Open table format for large analytic datasets. Object stores only. Requires a catalog (opens in a new tab).
Microsoft Excel	`file_format: xlsx`	Alpha	Excel spreadsheet format (document format)
Markdown	`file_format: md`	Stable	Plain text with formatting (document format)
Text	`file_format: txt`	Stable	Plain text files (document format)
PDF	`file_format: pdf`	Beta	Portable Document Format (document format)
Microsoft Word	`file_format: docx`	Alpha	Word document format (document format)
Microsoft PowerPoint	`file_format: pptx`	Alpha	PowerPoint presentation format (document format)

Format-Specific Parameters

File formats support additional parameters for fine-grained control. Common examples include:

Parameter	Applies To	Description
`csv_has_header`	CSV	Whether the first row contains column headers
`csv_delimiter`	CSV	Field delimiter character (default: `,`)
`csv_quote`	CSV	Quote character for fields containing delimiters

For complete format options, see File Formats Reference (opens in a new tab).

Applicable Connectors {#object-store-file-formats}

The following data connectors support file format configuration:

Connector Type	Connectors
Object Stores	S3, Azure Blob (ABFS), GCS, HTTP/HTTPS
Network-Attached Storage	FTP, SFTP, SMB, NFS
Local Storage	File

Hive Partitioning

File-based connectors support Hive-style partitioning, which extracts partition columns from folder names. Enable with hive_partitioning_enabled: true.

Given a folder structure:

/data/
  year=2024/
    month=01/
      data.parquet
    month=02/
      data.parquet

/data/
  year=2024/
    month=01/
      data.parquet
    month=02/
      data.parquet

Configure the dataset:

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

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

Query with partition filters:

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

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

Partition pruning improves query performance by reading only the relevant files.

Metadata Columns

File-based connectors can expose per-file object store metadata as virtual columns in the dataset schema. These columns are not stored in the data files — they are derived from object store file metadata at query time.

Available Columns

Column	Type	Description
`_location`	`Utf8`	Full URI of the source file
`_last_modified`	`Timestamp(µs, "UTC")`	When the file was last modified
`_size`	`UInt64`	File size in bytes

Enabling Metadata Columns

Metadata columns are enabled by adding a metadata section to the dataset definition with each desired column set to enabled:

datasets:
  - from: s3://bucket/data/
    name: my_data
    params:
      file_format: parquet
    metadata:
      _location: enabled
      _last_modified: enabled
      _size: enabled

datasets:
  - from: s3://bucket/data/
    name: my_data
    params:
      file_format: parquet
    metadata:
      _location: enabled
      _last_modified: enabled
      _size: enabled

Each column can be individually enabled or omitted:

metadata:
  _location: enabled     # Only add the _location column

metadata:
  _location: enabled     # Only add the _location column

:::note If the data files already contain a column with the same name as a metadata column (e.g., a Parquet file with a _size column), the metadata column is not added to avoid conflicts. :::

Querying Metadata Columns

Once enabled, metadata columns appear alongside the regular data columns:

SELECT * FROM my_data LIMIT 3;

SELECT * FROM my_data LIMIT 3;

+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| id | value   | year | month | day | _last_modified       | _location                                                    | _size |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| 0  | value_0 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 1  | value_1 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 2  | value_2 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+

+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| id | value   | year | month | day | _last_modified       | _location                                                    | _size |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| 0  | value_0 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 1  | value_1 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 2  | value_2 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+

Metadata columns can be used in filters, projections, aggregations, and joins like any other column:

-- Filter by file location
SELECT id, value FROM my_data
WHERE _location = 's3://bucket/data/year=2022/month=1/day=1/data_0.parquet';

-- Find recently modified files
SELECT DISTINCT _location, _last_modified FROM my_data
WHERE _last_modified > '2024-01-01T00:00:00Z';

-- Aggregate by file
SELECT _location, COUNT(*) AS row_count, _size
FROM my_data
GROUP BY _location, _size
ORDER BY _location;

-- Filter by file location
SELECT id, value FROM my_data
WHERE _location = 's3://bucket/data/year=2022/month=1/day=1/data_0.parquet';

-- Find recently modified files
SELECT DISTINCT _location, _last_modified FROM my_data
WHERE _last_modified > '2024-01-01T00:00:00Z';

-- Aggregate by file
SELECT _location, COUNT(*) AS row_count, _size
FROM my_data
GROUP BY _location, _size
ORDER BY _location;

File Listing Pruning with Metadata Columns

When a query filters on enabled metadata columns, Spice evaluates the filter against each object's listing metadata and skips the files it excludes before opening them. A skipped file is never opened, so its footer is not read and a compressed file such as jsonl.gz is not decompressed. Query results are unchanged.

This helps an append refresh that uses _last_modified as its time_column, which enables the column automatically. Each refresh filters on _last_modified > <last refresh watermark>, so a refresh with no new files reads no data files.

A condition prunes the listing when it references only metadata columns (_location, _last_modified, _size). It is evaluated the same way as a row-level WHERE, so it can use comparisons, BETWEEN, IN, LIKE, casts, OR, NOT, and expressions that evaluate to a constant, such as now() - INTERVAL '1 day'. Both of these queries read only the files that match:

SELECT * FROM my_data WHERE _last_modified > now() - INTERVAL '1 day';

SELECT * FROM my_data WHERE _size > 1048576 OR _location LIKE '%/year=2024/%';

SELECT * FROM my_data WHERE _last_modified > now() - INTERVAL '1 day';

SELECT * FROM my_data WHERE _size > 1048576 OR _location LIKE '%/year=2024/%';

Conditions joined by AND are considered separately, so _last_modified > '2024-01-01T00:00:00Z' AND id = 0 still prunes by _last_modified. A single condition that mixes a metadata column with a data column, such as _last_modified > '2024-01-01T00:00:00Z' OR id = 0, does not prune the listing: Spice reads every file and applies the condition to each row.

A _location = '<uri>' or _location IN (...) condition reads only the named objects. Every other condition in the query, including a filter on a Hive partition column, still applies to the rows of those objects.

Queries That Read Only Partition or Metadata Columns

Hive partition columns and metadata columns have the same value in every row of a file. When a query reads only these columns and answers them with GROUP BY, DISTINCT, MAX, or MIN, Spice needs one row per file instead of every row. Two common queries have this shape:

-- Find the newest partition
SELECT year FROM partitioned_data GROUP BY year ORDER BY year DESC LIMIT 1;

-- Find the most recent file change
SELECT MAX(_last_modified) FROM my_data;

-- Find the newest partition
SELECT year FROM partitioned_data GROUP BY year ORDER BY year DESC LIMIT 1;

-- Find the most recent file change
SELECT MAX(_last_modified) FROM my_data;

Spice answers these queries in one of two ways:

When every file reports an exact row count, such as Parquet, and the query reads only partition columns, Spice builds the result from the file listing and opens no data files.
Otherwise, including JSON, CSV, and compressed files, and any query that reads a metadata column, Spice reads at most the first record of each file. EXPLAIN shows first_record_probe=true on the scan.

Files with no rows are skipped in both cases, so an empty partition does not appear in the result. A filter on partition or metadata columns keeps the optimization. A filter on a data column, or an aggregate that depends on row counts, such as COUNT, SUM, or AVG, reads the files in full.

Applicable Connectors

Metadata columns are supported by all file-based connectors:

Connector Type	Connectors
Object Stores	S3, Azure Blob (ABFS), HTTP/HTTPS
Network-Attached Storage	FTP, SFTP, SMB, NFS
Local Storage	File

Schema Inference

Spice infers the schema for each dataset from its data source at startup. The inferred schema defines the column names, data types, and nullability used by the dataset for the lifetime of that runtime process.

Schema inference happens once, when the dataset is first registered. Some connectors support tuning the inference behavior with connector-specific parameters:

Connector	Parameter	Default	Description
Kafka (opens in a new tab)	`schema_infer_max_records`	1	Number of messages sampled to infer the JSON schema
DynamoDB (opens in a new tab)	`schema_infer_max_records`	10	Number of items sampled to infer the schema
MongoDB (opens in a new tab)	`mongodb_schema_infer_max_records`	400	Number of documents sampled to infer the schema
CSV files (opens in a new tab)	`csv_schema_infer_max_records`	1000	Number of rows sampled to infer the CSV schema

For connectors that read self-describing formats (Parquet, Arrow, Avro), the schema is read directly from file metadata and does not require sampling.

Runtime Schema Changes

Accelerated datasets keep the schema registered at startup. The default on_schema_change: block (opens in a new tab) does not adopt a later source change. The dataset stays healthy and continues to serve queries on the registered schema. A refresh that cannot write the new source rows into that schema fails, for example:

Failed to load data for dataset <name>: Cannot cast struct field ...

That failure is intentional. Incompatible source changes stay blocked until an explicit policy accepts them. Plan the rollout, then set one of:

Policy	What it accepts
`block` (default)	Nothing. The registered schema stays in force.
`fail`	Nothing. The dataset reports an error status with an actionable message while the source schema diverges, and recovers if the source reverts.
`append_new_columns`	New nullable columns. Type and nullability changes stay on `block`.
`sync_all_columns`	Lossless widening changes: new nullable columns, widened types, relaxed nullability. Removals and narrowing stay blocked.
`drop_and_recreate`	Widening changes in place, and a destructive rebuild for incompatible changes. The rebuild runs only with `refresh_mode: full`.

Restarting the runtime re-infers the schema from the source. acceleration.mode: file_update (opens in a new tab) drops and recreates the accelerated table when an incompatible change is found, leaving the acceleration file in place. Federated queries against a dataset that is not accelerated always see the live source schema; on_schema_change does not apply to them.

:::tip[Recommendation] Pin a known-good schema version in the data source or use the columns (opens in a new tab) configuration to explicitly define the expected columns. This makes schema expectations explicit and produces clear errors if the source drifts. :::

Name	Parameter	Supported	Is Document Format
Apache Parquet (opens in a new tab)	`file_format: parquet`	✅	❌
Apache ORC (opens in a new tab)	`file_format: orc`	✅	❌
CSV (opens in a new tab)	`file_format: csv`	✅	❌
Delta Lake (opens in a new tab)	`file_format: delta`	✅	❌
Apache Iceberg (opens in a new tab)	`file_format: iceberg`	✅	❌
JSON	`file_format: json`	✅	❌
Microsoft Excel	`file_format: xlsx`	Alpha	✅
Markdown	`file_format: md`	✅	✅
Text	`file_format: txt`	✅	✅
PDF	`file_format: pdf`	Beta	✅
Microsoft Word	`file_format: docx`	Alpha	✅
Microsoft PowerPoint	`file_format: pptx`	Alpha	✅

Document Formats {#document-formats}

Document formats (Markdown, Text, PDF, Word, Excel, PowerPoint) are handled differently from structured data formats. Each file becomes a row in the resulting table, with the file contents stored in a content column.

:::warning[Note] Document formats in Alpha (DOCX, XLSX, PPTX) may not parse all structure or text from the underlying documents correctly. :::

Archive Consistency Checks

Spice checks that each DOCX, XLSX, and PPTX file is a consistent ZIP archive before it reads any entry. A file is rejected when a local file header disagrees with its central directory entry, when two entries share a name, or when entries overlap, because different ZIP readers can see different content in such a file (GHSA-m665-v7pm-7grj (opens in a new tab)). Reading a rejected file fails with an error that starts with Internal parsing DOCX document. Error: (XLSX or PPTX for those formats).

XLSX files are read with calamine (opens in a new tab) 0.37. An XLSX file must contain the _rels/.rels part, which files saved by Microsoft Excel include; a file without it fails to parse. Leading and trailing spaces, tabs, and line breaks are removed from each text element of a cell unless that element sets xml:space="preserve". A rich-text cell stores each formatted run in its own text element, so a space at the edge of a run is lost: runs Hello and world read as Helloworld.

Document Table Schema

Column	Type	Description
`location`	String	Path to the source file
`content`	String	Full text content of the document

Example

Consider a local filesystem:

>>> ls -la
total 232
drwxr-sr-x@ 22 jeadie  staff    704 30 Jul 13:12 .
drwxr-sr-x@ 18 jeadie  staff    576 30 Jul 13:12 ..
-rw-r--r--@  1 jeadie  staff   1329 15 Jan  2024 DR-000-Template.md
-rw-r--r--@  1 jeadie  staff   4966 11 Aug  2023 DR-001-Dremio-Architecture.md
-rw-r--r--@  1 jeadie  staff   2307 28 Jul  2023 DR-002-Data-Completeness.md

>>> ls -la
total 232
drwxr-sr-x@ 22 jeadie  staff    704 30 Jul 13:12 .
drwxr-sr-x@ 18 jeadie  staff    576 30 Jul 13:12 ..
-rw-r--r--@  1 jeadie  staff   1329 15 Jan  2024 DR-000-Template.md
-rw-r--r--@  1 jeadie  staff   4966 11 Aug  2023 DR-001-Dremio-Architecture.md
-rw-r--r--@  1 jeadie  staff   2307 28 Jul  2023 DR-002-Data-Completeness.md

And the spicepod

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

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

A Document table will be created.

>>> SELECT * FROM my_documents LIMIT 3
+----------------------------------------------------+--------------------------------------------------+
| location                                           | content                                          |
+----------------------------------------------------+--------------------------------------------------+
| Users/docs/decisions/DR-000-Template.md            | # DR-000: DR Template                            |
|                                                    | **Date:** <>                                     |
|                                                    | **Decision Makers:**                             |
|                                                    | - @<>                                            |
|                                                    | - @<>                                            |
|                                                    | ...                                              |
| Users/docs/decisions/DR-001-Dremio-Architecture.md | # DR-001: Add "Cached" Dremio Dataset            |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | We use [Dremio](https://www.dremio.com/) to p... |
| Users/docs/decisions/DR-002-Data-Completeness.md   | # DR-002: Append-Only Data Completeness          |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | Our Ethereum append-only dataset is incomple...  |
+----------------------------------------------------+--------------------------------------------------+

>>> SELECT * FROM my_documents LIMIT 3
+----------------------------------------------------+--------------------------------------------------+
| location                                           | content                                          |
+----------------------------------------------------+--------------------------------------------------+
| Users/docs/decisions/DR-000-Template.md            | # DR-000: DR Template                            |
|                                                    | **Date:** <>                                     |
|                                                    | **Decision Makers:**                             |
|                                                    | - @<>                                            |
|                                                    | - @<>                                            |
|                                                    | ...                                              |
| Users/docs/decisions/DR-001-Dremio-Architecture.md | # DR-001: Add "Cached" Dremio Dataset            |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | We use [Dremio](https://www.dremio.com/) to p... |
| Users/docs/decisions/DR-002-Data-Completeness.md   | # DR-002: Append-Only Data Completeness          |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | Our Ethereum append-only dataset is incomple...  |
+----------------------------------------------------+--------------------------------------------------+

Identifier Case Sensitivity and Quoting

Spice follows PostgreSQL conventions (opens in a new tab) for identifier handling: unquoted identifiers are normalized to lowercase. This applies to both the from field in dataset definitions and the name field used for SQL queries.

Quoting in the `from` field

To reference a table or schema with mixed-case or uppercase characters in the from field, wrap each case-sensitive part in double quotes:

datasets:
  # Without quoting — "ActionExecutions" is lowercased to "actionexecutions"
  - from: postgres:my_schema.ActionExecutions
    name: action_executions

  # With quoting — case is preserved for the table name
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

  # Quote each part individually as needed
  - from: postgres:"MySchema"."ActionExecutions"
    name: action_executions

datasets:
  # Without quoting — "ActionExecutions" is lowercased to "actionexecutions"
  - from: postgres:my_schema.ActionExecutions
    name: action_executions

  # With quoting — case is preserved for the table name
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

  # Quote each part individually as needed
  - from: postgres:"MySchema"."ActionExecutions"
    name: action_executions

Each dotted part of the identifier is treated independently — quote only the parts that require case preservation. For example, postgres:my_schema."ActionExecutions" preserves the case of ActionExecutions while my_schema is normalized to lowercase.

This applies to all federated database connectors where the from field references a table identifier (e.g. postgres, mysql, snowflake, databricks, clickhouse, mssql, duckdb, dremio, flightsql, spark, mongodb, oracle, adbc). Connectors that interpret from as a file path (e.g. s3, delta_lake, ftp, abfs) do not apply identifier normalization.

Quoting in the `name` field

The name field controls the table name used in Spice SQL queries and follows the same lowercase normalization. To preserve case in the dataset name, wrap the value in double quotes. In YAML, use single quotes around the double-quoted value:

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: '"ActionExecutions"'

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: '"ActionExecutions"'

-- Query using the preserved-case name
SELECT * FROM "ActionExecutions";

-- Query using the preserved-case name
SELECT * FROM "ActionExecutions";

If you don't need to preserve case in queries, a lowercase name works without quoting:

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

SELECT * FROM action_executions;

SELECT * FROM action_executions;

Dataset name quoting works regardless of connector type. See the datasets name reference (opens in a new tab) for more details.

Data Connector Docs

import DocCardList from '@theme/DocCardList';

spiceai/docs/README.md

data-connectors
overview
federation

Data Connectors provide connections to databases, data warehouses, and data lakes for federated SQL queries and data replication.

Each connector is configured using the from field in a dataset definition. For example:

datasets:
  - from: postgres:public.orders    # Database connector
    name: orders
    params:
      pg_host: localhost
      pg_db: mydb
      pg_user: reader
      pg_pass: ${secrets:PG_PASS}

  - from: s3://my-bucket/events/    # Object storage connector
    name: events
    params:
      file_format: parquet
      s3_auth: iam_role

datasets:
  - from: postgres:public.orders    # Database connector
    name: orders
    params:
      pg_host: localhost
      pg_db: mydb
      pg_user: reader
      pg_pass: ${secrets:PG_PASS}

  - from: s3://my-bucket/events/    # Object storage connector
    name: events
    params:
      file_format: parquet
      s3_auth: iam_role

Supported Data Connectors include:

Name	Description	Status	Protocol/Format
`adbc`	ADBC	Stable	Arrow
`databricks (mode: delta_lake)`	Databricks (opens in a new tab)	Stable	S3/Delta Lake
`databricks (mode: spark_connect)`	Databricks (opens in a new tab)	Stable	Spark Connect (opens in a new tab)
`databricks (mode: sql_warehouse)`	Databricks (opens in a new tab)	Stable	SQL Statement Execution API
`delta_lake`	Delta Lake	Stable	Delta Lake
`dremio`	Dremio (opens in a new tab)	Stable	Arrow Flight
`duckdb`	DuckDB	Stable	Embedded
`file`	File	Stable	Parquet, CSV, ORC
`github`	GitHub	Stable	GitHub API
`http`, `https`	HTTP(s) (dynamic headers, pagination)	Stable	Parquet, CSV, JSON
`localpod`	Local dataset replication (opens in a new tab)	Stable
`postgres`	PostgreSQL (with native WAL CDC)	Stable
`s3`	S3 (opens in a new tab)	Stable	Parquet, CSV, ORC
`mysql`	MySQL (with native binlog CDC)	Stable
`spice.ai`	Spice.ai (opens in a new tab)	Stable	Arrow Flight
`dynamodb`	Amazon DynamoDB (with Streams)	Stable
`iceberg`	Apache Iceberg (opens in a new tab) (read+write)	Stable	Parquet
`flightsql`	FlightSQL	Stable	Arrow Flight SQL
`glue`	AWS Glue (opens in a new tab)	Stable	Iceberg, Parquet, ORC, CSV
`mongodb`	MongoDB (with native Change Streams CDC)	Stable
`graphql`	GraphQL	Release Candidate	JSON
`cosmosdb`	Azure Cosmos DB (NoSQL)	Release Candidate
`git`	Git repositories (opens in a new tab)	Release Candidate
`snowflake`	Snowflake	Release Candidate	Arrow
`oracle`	Oracle	Release Candidate	Oracle ODPI-C (opens in a new tab)
`ducklake`	[DuckLake][ducklake]	Beta	Parquet
`mssql`	Microsoft SQL Server	Beta	Tabular Data Stream (TDS)
`odbc`	ODBC (Spice.ai Enterprise)	Beta	ODBC
`spark`	Spark	Beta	Spark Connect (opens in a new tab)
`sharepoint`	Microsoft SharePoint	Beta	Object-store listing
`kafka`	Kafka	Beta	Kafka + JSON
`abfs`	Azure BlobFS	Alpha	Parquet, CSV, ORC
`clickhouse`	ClickHouse	Alpha
`debezium`	Debezium CDC (opens in a new tab) (Kafka consumer)	Alpha	Kafka + JSON
`cdc`	Debezium push ingest (opens in a new tab) (no Kafka)	Alpha	JSON + Avro HTTP
`elasticsearch`	Elasticsearch (BM25 + kNN + RRF) (Spice.ai Enterprise)	Alpha
`gcs`, `gs`	[Google Cloud Storage][gcs]	Alpha	Parquet, CSV, JSON, ORC
`hf`	Hugging Face (opens in a new tab) datasets	Alpha	Parquet, CSV, TSV, JSON, JSONL, ORC
`ftp`, `sftp`	FTP/SFTP	Alpha	Parquet, CSV, ORC
`imap`	IMAP	Alpha	IMAP Emails
`scylladb`	ScyllaDB (Spice.ai Enterprise)	Alpha
`smb`	SMB 3.1.1	Alpha	SMB
`nfs`	NFS (Spice.ai Enterprise)	Alpha	Parquet, CSV, JSON, ORC

File Formats

Specifying File Format

When connecting to a directory, specify the file format using params.file_format:

datasets:
  - from: s3://bucket/data/sales/
    name: sales
    params:
      file_format: parquet

datasets:
  - from: s3://bucket/data/sales/
    name: sales
    params:
      file_format: parquet

When connecting to a specific file, the format is inferred from the file extension:

datasets:
  - from: sftp://files.example.com/reports/quarterly.parquet
    name: quarterly_report

datasets:
  - from: sftp://files.example.com/reports/quarterly.parquet
    name: quarterly_report

Supported Formats

Name	Parameter	Status	Description
Apache Parquet (opens in a new tab)	`file_format: parquet`	Stable	Columnar format optimized for analytics
Apache ORC (opens in a new tab)	`file_format: orc`	Stable	Columnar format. Read-only; a directory of ORC objects infers its schema by merging every object's footer.
CSV (opens in a new tab)	`file_format: csv`	Stable	Comma-separated values
JSON	`file_format: json`	Stable	JavaScript Object Notation
Delta Lake (opens in a new tab)	`file_format: delta`	Stable	Open table format with ACID transactions. Object stores only.
Apache Iceberg (opens in a new tab)	`file_format: iceberg`	Stable	Open table format for large analytic datasets. Object stores only. Requires a catalog (opens in a new tab).
Microsoft Excel	`file_format: xlsx`	Alpha	Excel spreadsheet format (document format)
Markdown	`file_format: md`	Stable	Plain text with formatting (document format)
Text	`file_format: txt`	Stable	Plain text files (document format)
PDF	`file_format: pdf`	Beta	Portable Document Format (document format)
Microsoft Word	`file_format: docx`	Alpha	Word document format (document format)
Microsoft PowerPoint	`file_format: pptx`	Alpha	PowerPoint presentation format (document format)

Format-Specific Parameters

File formats support additional parameters for fine-grained control. Common examples include:

Parameter	Applies To	Description
`csv_has_header`	CSV	Whether the first row contains column headers
`csv_delimiter`	CSV	Field delimiter character (default: `,`)
`csv_quote`	CSV	Quote character for fields containing delimiters

For complete format options, see File Formats Reference (opens in a new tab).

Applicable Connectors {#object-store-file-formats}

The following data connectors support file format configuration:

Connector Type	Connectors
Object Stores	S3, Azure Blob (ABFS), GCS, HTTP/HTTPS
Network-Attached Storage	FTP, SFTP, SMB, NFS
Local Storage	File

Hive Partitioning

File-based connectors support Hive-style partitioning, which extracts partition columns from folder names. Enable with hive_partitioning_enabled: true.

Given a folder structure:

/data/
  year=2024/
    month=01/
      data.parquet
    month=02/
      data.parquet

/data/
  year=2024/
    month=01/
      data.parquet
    month=02/
      data.parquet

Configure the dataset:

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

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

Query with partition filters:

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

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

Partition pruning improves query performance by reading only the relevant files.

Metadata Columns

Available Columns

Column	Type	Description
`_location`	`Utf8`	Full URI of the source file
`_last_modified`	`Timestamp(µs, "UTC")`	When the file was last modified
`_size`	`UInt64`	File size in bytes

Enabling Metadata Columns

Metadata columns are enabled by adding a metadata section to the dataset definition with each desired column set to enabled:

datasets:
  - from: s3://bucket/data/
    name: my_data
    params:
      file_format: parquet
    metadata:
      _location: enabled
      _last_modified: enabled
      _size: enabled

datasets:
  - from: s3://bucket/data/
    name: my_data
    params:
      file_format: parquet
    metadata:
      _location: enabled
      _last_modified: enabled
      _size: enabled

Each column can be individually enabled or omitted:

metadata:
  _location: enabled     # Only add the _location column

metadata:
  _location: enabled     # Only add the _location column

:::note If the data files already contain a column with the same name as a metadata column (e.g., a Parquet file with a _size column), the metadata column is not added to avoid conflicts. :::

Querying Metadata Columns

Once enabled, metadata columns appear alongside the regular data columns:

SELECT * FROM my_data LIMIT 3;

SELECT * FROM my_data LIMIT 3;

+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| id | value   | year | month | day | _last_modified       | _location                                                    | _size |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| 0  | value_0 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 1  | value_1 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 2  | value_2 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+

+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| id | value   | year | month | day | _last_modified       | _location                                                    | _size |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| 0  | value_0 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 1  | value_1 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 2  | value_2 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+

Metadata columns can be used in filters, projections, aggregations, and joins like any other column:

-- Filter by file location
SELECT id, value FROM my_data
WHERE _location = 's3://bucket/data/year=2022/month=1/day=1/data_0.parquet';

-- Find recently modified files
SELECT DISTINCT _location, _last_modified FROM my_data
WHERE _last_modified > '2024-01-01T00:00:00Z';

-- Aggregate by file
SELECT _location, COUNT(*) AS row_count, _size
FROM my_data
GROUP BY _location, _size
ORDER BY _location;

-- Filter by file location
SELECT id, value FROM my_data
WHERE _location = 's3://bucket/data/year=2022/month=1/day=1/data_0.parquet';

-- Find recently modified files
SELECT DISTINCT _location, _last_modified FROM my_data
WHERE _last_modified > '2024-01-01T00:00:00Z';

-- Aggregate by file
SELECT _location, COUNT(*) AS row_count, _size
FROM my_data
GROUP BY _location, _size
ORDER BY _location;

File Listing Pruning with Metadata Columns

SELECT * FROM my_data WHERE _last_modified > now() - INTERVAL '1 day';

SELECT * FROM my_data WHERE _size > 1048576 OR _location LIKE '%/year=2024/%';

SELECT * FROM my_data WHERE _last_modified > now() - INTERVAL '1 day';

SELECT * FROM my_data WHERE _size > 1048576 OR _location LIKE '%/year=2024/%';

Queries That Read Only Partition or Metadata Columns

-- Find the newest partition
SELECT year FROM partitioned_data GROUP BY year ORDER BY year DESC LIMIT 1;

-- Find the most recent file change
SELECT MAX(_last_modified) FROM my_data;

-- Find the newest partition
SELECT year FROM partitioned_data GROUP BY year ORDER BY year DESC LIMIT 1;

-- Find the most recent file change
SELECT MAX(_last_modified) FROM my_data;

Spice answers these queries in one of two ways:

When every file reports an exact row count, such as Parquet, and the query reads only partition columns, Spice builds the result from the file listing and opens no data files.
Otherwise, including JSON, CSV, and compressed files, and any query that reads a metadata column, Spice reads at most the first record of each file. EXPLAIN shows first_record_probe=true on the scan.

Applicable Connectors

Metadata columns are supported by all file-based connectors:

Connector Type	Connectors
Object Stores	S3, Azure Blob (ABFS), HTTP/HTTPS
Network-Attached Storage	FTP, SFTP, SMB, NFS
Local Storage	File

Schema Inference

Schema inference happens once, when the dataset is first registered. Some connectors support tuning the inference behavior with connector-specific parameters:

Connector	Parameter	Default	Description
Kafka (opens in a new tab)	`schema_infer_max_records`	1	Number of messages sampled to infer the JSON schema
DynamoDB (opens in a new tab)	`schema_infer_max_records`	10	Number of items sampled to infer the schema
MongoDB (opens in a new tab)	`mongodb_schema_infer_max_records`	400	Number of documents sampled to infer the schema
CSV files (opens in a new tab)	`csv_schema_infer_max_records`	1000	Number of rows sampled to infer the CSV schema

For connectors that read self-describing formats (Parquet, Arrow, Avro), the schema is read directly from file metadata and does not require sampling.

Runtime Schema Changes

Failed to load data for dataset <name>: Cannot cast struct field ...

That failure is intentional. Incompatible source changes stay blocked until an explicit policy accepts them. Plan the rollout, then set one of:

Policy	What it accepts
`block` (default)	Nothing. The registered schema stays in force.
`fail`	Nothing. The dataset reports an error status with an actionable message while the source schema diverges, and recovers if the source reverts.
`append_new_columns`	New nullable columns. Type and nullability changes stay on `block`.
`sync_all_columns`	Lossless widening changes: new nullable columns, widened types, relaxed nullability. Removals and narrowing stay blocked.
`drop_and_recreate`	Widening changes in place, and a destructive rebuild for incompatible changes. The rebuild runs only with `refresh_mode: full`.

Name	Parameter	Supported	Is Document Format
Apache Parquet (opens in a new tab)	`file_format: parquet`	✅	❌
Apache ORC (opens in a new tab)	`file_format: orc`	✅	❌
CSV (opens in a new tab)	`file_format: csv`	✅	❌
Delta Lake (opens in a new tab)	`file_format: delta`	✅	❌
Apache Iceberg (opens in a new tab)	`file_format: iceberg`	✅	❌
JSON	`file_format: json`	✅	❌
Microsoft Excel	`file_format: xlsx`	Alpha	✅
Markdown	`file_format: md`	✅	✅
Text	`file_format: txt`	✅	✅
PDF	`file_format: pdf`	Beta	✅
Microsoft Word	`file_format: docx`	Alpha	✅
Microsoft PowerPoint	`file_format: pptx`	Alpha	✅

Document Formats {#document-formats}

:::warning[Note] Document formats in Alpha (DOCX, XLSX, PPTX) may not parse all structure or text from the underlying documents correctly. :::

Archive Consistency Checks

Document Table Schema

Column	Type	Description
`location`	String	Path to the source file
`content`	String	Full text content of the document

Example

Consider a local filesystem:

>>> ls -la
total 232
drwxr-sr-x@ 22 jeadie  staff    704 30 Jul 13:12 .
drwxr-sr-x@ 18 jeadie  staff    576 30 Jul 13:12 ..
-rw-r--r--@  1 jeadie  staff   1329 15 Jan  2024 DR-000-Template.md
-rw-r--r--@  1 jeadie  staff   4966 11 Aug  2023 DR-001-Dremio-Architecture.md
-rw-r--r--@  1 jeadie  staff   2307 28 Jul  2023 DR-002-Data-Completeness.md

>>> ls -la
total 232
drwxr-sr-x@ 22 jeadie  staff    704 30 Jul 13:12 .
drwxr-sr-x@ 18 jeadie  staff    576 30 Jul 13:12 ..
-rw-r--r--@  1 jeadie  staff   1329 15 Jan  2024 DR-000-Template.md
-rw-r--r--@  1 jeadie  staff   4966 11 Aug  2023 DR-001-Dremio-Architecture.md
-rw-r--r--@  1 jeadie  staff   2307 28 Jul  2023 DR-002-Data-Completeness.md

And the spicepod

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

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

A Document table will be created.

>>> SELECT * FROM my_documents LIMIT 3
+----------------------------------------------------+--------------------------------------------------+
| location                                           | content                                          |
+----------------------------------------------------+--------------------------------------------------+
| Users/docs/decisions/DR-000-Template.md            | # DR-000: DR Template                            |
|                                                    | **Date:** <>                                     |
|                                                    | **Decision Makers:**                             |
|                                                    | - @<>                                            |
|                                                    | - @<>                                            |
|                                                    | ...                                              |
| Users/docs/decisions/DR-001-Dremio-Architecture.md | # DR-001: Add "Cached" Dremio Dataset            |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | We use [Dremio](https://www.dremio.com/) to p... |
| Users/docs/decisions/DR-002-Data-Completeness.md   | # DR-002: Append-Only Data Completeness          |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | Our Ethereum append-only dataset is incomple...  |
+----------------------------------------------------+--------------------------------------------------+

>>> SELECT * FROM my_documents LIMIT 3
+----------------------------------------------------+--------------------------------------------------+
| location                                           | content                                          |
+----------------------------------------------------+--------------------------------------------------+
| Users/docs/decisions/DR-000-Template.md            | # DR-000: DR Template                            |
|                                                    | **Date:** <>                                     |
|                                                    | **Decision Makers:**                             |
|                                                    | - @<>                                            |
|                                                    | - @<>                                            |
|                                                    | ...                                              |
| Users/docs/decisions/DR-001-Dremio-Architecture.md | # DR-001: Add "Cached" Dremio Dataset            |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | We use [Dremio](https://www.dremio.com/) to p... |
| Users/docs/decisions/DR-002-Data-Completeness.md   | # DR-002: Append-Only Data Completeness          |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | Our Ethereum append-only dataset is incomple...  |
+----------------------------------------------------+--------------------------------------------------+

Identifier Case Sensitivity and Quoting

Quoting in the `from` field

To reference a table or schema with mixed-case or uppercase characters in the from field, wrap each case-sensitive part in double quotes:

datasets:
  # Without quoting — "ActionExecutions" is lowercased to "actionexecutions"
  - from: postgres:my_schema.ActionExecutions
    name: action_executions

  # With quoting — case is preserved for the table name
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

  # Quote each part individually as needed
  - from: postgres:"MySchema"."ActionExecutions"
    name: action_executions

datasets:
  # Without quoting — "ActionExecutions" is lowercased to "actionexecutions"
  - from: postgres:my_schema.ActionExecutions
    name: action_executions

  # With quoting — case is preserved for the table name
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

  # Quote each part individually as needed
  - from: postgres:"MySchema"."ActionExecutions"
    name: action_executions

Quoting in the `name` field

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: '"ActionExecutions"'

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: '"ActionExecutions"'

-- Query using the preserved-case name
SELECT * FROM "ActionExecutions";

-- Query using the preserved-case name
SELECT * FROM "ActionExecutions";

If you don't need to preserve case in queries, a lowercase name works without quoting:

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

SELECT * FROM action_executions;

SELECT * FROM action_executions;

Dataset name quoting works regardless of connector type. See the datasets name reference (opens in a new tab) for more details.

Data Connector Docs

import DocCardList from '@theme/DocCardList';

File Formats

Specifying File Format

Supported Formats

Format-Specific Parameters

Applicable Connectors {#object-store-file-formats}

Hive Partitioning

Metadata Columns

Available Columns

Enabling Metadata Columns

Querying Metadata Columns

File Listing Pruning with Metadata Columns

Queries That Read Only Partition or Metadata Columns

Applicable Connectors

Schema Inference

Runtime Schema Changes

Document Formats {#document-formats}

Archive Consistency Checks

Document Table Schema

Example

Identifier Case Sensitivity and Quoting

Quoting in the from field

Quoting in the name field

Data Connector Docs

File Formats

Specifying File Format

Supported Formats

Format-Specific Parameters

Applicable Connectors {#object-store-file-formats}

Hive Partitioning

Metadata Columns

Available Columns

Enabling Metadata Columns

Querying Metadata Columns

File Listing Pruning with Metadata Columns

Queries That Read Only Partition or Metadata Columns

Applicable Connectors

Schema Inference

Runtime Schema Changes

Document Formats {#document-formats}

Archive Consistency Checks

Document Table Schema

Example

Identifier Case Sensitivity and Quoting

Quoting in the from field

Quoting in the name field

Data Connector Docs

Quoting in the `from` field

Quoting in the `name` field

Quoting in the `from` field

Quoting in the `name` field