# Data Connectors Learn how to use Data Connector to query external data. 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: ```yaml 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](https://github.com/spiceai/cookbook/tree/trunk/databricks#readme) | Stable | S3/Delta Lake | | `databricks (mode: spark_connect)` | [Databricks](https://github.com/spiceai/cookbook/tree/trunk/databricks#readme) | Stable | [Spark Connect](https://spark.apache.org/docs/latest/spark-connect-overview.html) | | `databricks (mode: sql_warehouse)` | [Databricks](https://github.com/spiceai/cookbook/tree/trunk/databricks#readme) | Stable | SQL Statement Execution API | | `delta_lake` | Delta Lake | Stable | Delta Lake | | `dremio` | [Dremio](https://github.com/spiceai/cookbook/tree/trunk/dremio#readme) | 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](https://github.com/spiceai/cookbook/blob/trunk/localpod/README.md) | Stable | | | `postgres` | PostgreSQL (with native WAL CDC) | Stable | | | `s3` | [S3](https://github.com/spiceai/cookbook/tree/trunk/s3#readme) | Stable | Parquet, CSV, ORC | | `mysql` | MySQL (with native binlog CDC) | Stable | | | `spice.ai` | [Spice.ai](https://github.com/spiceai/cookbook/tree/trunk/spiceai#readme) | Stable | Arrow Flight | | `dynamodb` | Amazon DynamoDB (with Streams) | Stable | | | `iceberg` | [Apache Iceberg](https://github.com/spiceai/cookbook/tree/trunk/catalogs/iceberg#readme) (read+write) | Stable | Parquet | | `flightsql` | FlightSQL | Stable | Arrow Flight SQL | | `glue` | [AWS Glue](https://github.com/spiceai/cookbook/tree/trunk/glue#readme) | 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](https://spice.ai/docs/building-blocks/data-connectors/git) | Release Candidate | | | `snowflake` | Snowflake | Release Candidate | Arrow | | `oracle` | Oracle | Release Candidate | [Oracle ODPI-C](https://oracle.github.io/odpi/) | | `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](https://spark.apache.org/docs/latest/spark-connect-overview.html) | | `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](https://spice.ai/docs/building-blocks/data-connectors/debezium) (Kafka consumer) | Alpha | Kafka + JSON | | `cdc` | [Debezium push ingest](https://spiceai.org/docs/features/cdc/debezium-ingest) (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](https://spice.ai/docs/building-blocks/data-connectors/huggingface) 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`: ```yaml 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: ```yaml datasets: - from: sftp://files.example.com/reports/quarterly.parquet name: quarterly_report ``` ### Supported Formats | Name | Parameter | Status | Description | | - | - | - | - | | [Apache Parquet](https://parquet.apache.org/) | `file_format: parquet` | Stable | Columnar format optimized for analytics | | [Apache ORC](https://spiceai.org/docs/reference/file_format#orc) | `file_format: orc` | Stable | Columnar format. Read-only; a directory of ORC objects infers its schema by merging every object's footer. | | [CSV](https://spiceai.org/docs/reference/file_format#csv) | `file_format: csv` | Stable | Comma-separated values | | JSON | `file_format: json` | Stable | JavaScript Object Notation | | [Delta Lake](https://delta.io/) | `file_format: delta` | Stable | Open table format with ACID transactions. Object stores only. | | [Apache Iceberg](https://iceberg.apache.org/) | `file_format: iceberg` | Stable | Open table format for large analytic datasets. Object stores only. Requires a [catalog](https://spiceai.org/docs/components/catalogs). | | 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](https://spiceai.org/docs/reference/file_format). ### Applicable Connectors 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: ```text /data/ year=2024/ month=01/ data.parquet month=02/ data.parquet ``` Configure the dataset: ```yaml datasets: - from: s3://bucket/data/ name: partitioned_data params: file_format: parquet hive_partitioning_enabled: true ``` Query with partition filters: ```sql 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`: ```yaml 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: ```yaml 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: ```sql SELECT * FROM my_data LIMIT 3; ``` ```shell +----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+ | 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: ```sql -- 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 > `, 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: ```sql 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 = ''` 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: ```sql -- 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](https://spice.ai/docs/building-blocks/data-connectors/kafka) | `schema_infer_max_records` | 1 | Number of messages sampled to infer the JSON schema | | [DynamoDB](https://spice.ai/docs/building-blocks/data-connectors/dynamodb) | `schema_infer_max_records` | 10 | Number of items sampled to infer the schema | | [MongoDB](https://spice.ai/docs/building-blocks/data-connectors/mongodb) | `mongodb_schema_infer_max_records` | 400 | Number of documents sampled to infer the schema | | [CSV files](https://spiceai.org/docs/reference/file_format) | `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`](https://spiceai.org/docs/reference/spicepod/datasets#on_schema_change) 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 : 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`](https://spiceai.org/docs/reference/spicepod/datasets#accelerationmode) 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`](https://spiceai.org/docs/reference/spicepod/datasets#columns) 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](https://parquet.apache.org/) | `file_format: parquet` | Yes | No | | [Apache ORC](https://spiceai.org/docs/reference/file_format#orc) | `file_format: orc` | Yes | No | | [CSV](https://spiceai.org/docs/reference/file_format#csv) | `file_format: csv` | Yes | No | | [Delta Lake](https://delta.io/) | `file_format: delta` | Yes | No | | [Apache Iceberg](https://iceberg.apache.org/) | `file_format: iceberg` | Yes | No | | JSON | `file_format: json` | Yes | No | | Microsoft Excel | `file_format: xlsx` | Alpha | Yes | | Markdown | `file_format: md` | Yes | Yes | | Text | `file_format: txt` | Yes | Yes | | PDF | `file_format: pdf` | Beta | Yes | | Microsoft Word | `file_format: docx` | Alpha | Yes | | Microsoft PowerPoint | `file_format: pptx` | Alpha | Yes | ### 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](https://github.com/zip-rs/zip2/security/advisories/GHSA-m665-v7pm-7grj)). 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](https://github.com/tafia/calamine) 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: ```shell >>> 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 ```yaml datasets: - name: my_documents from: file:docs/decisions/ params: file_format: md ``` A Document table will be created. ```shell >>> 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](https://spiceai.org/docs/reference/sql/select) 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: ```yaml 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: ```yaml datasets: - from: postgres:my_schema."ActionExecutions" name: '"ActionExecutions"' ``` ```sql -- 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: ```yaml datasets: - from: postgres:my_schema."ActionExecutions" name: action_executions ``` ```sql SELECT * FROM action_executions; ``` Dataset `name` quoting works regardless of connector type. See the [datasets `name` reference](https://spiceai.org/docs/reference/spicepod/datasets#name) for more details. ## Data Connector Docs