S3 Data Connector

The S3 Data Connector enables federated SQL querying on files stored in S3 or S3-compatible systems (e.g., MinIO, Cloudflare R2).

If a folder path is specified as the dataset source, all files within the folder will be loaded.

File formats are specified using the file_format parameter, as described in File Formats.

Quickstart

Query a public S3 dataset with no authentication:

version: v1
kind: Spicepod
name: s3_demo

datasets:
  - from: s3://spiceai-demo-datasets/taxi_trips/2024/
    name: taxi_trips
    params:
      file_format: parquet

For private buckets, add authentication (see Authentication):

datasets:
  - from: s3://my-private-bucket/data/
    name: my_data
    params:
      file_format: parquet
      s3_auth: key
      s3_key: ${secrets:AWS_ACCESS_KEY_ID}
      s3_secret: ${secrets:AWS_SECRET_ACCESS_KEY}
      s3_region: us-west-2
    acceleration:
      enabled: true

Configuration

from

S3-compatible URI to a folder or file, in the format s3://<bucket>/<path>

Example: from: s3://my-bucket/path/to/file.parquet

name

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: s3://s3-bucket-name/taxi_sample.csv
    name: cool_dataset
    params:
      file_format: csv

SELECT COUNT(*) FROM cool_dataset;

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

The dataset name cannot be a reserved keyword.

params

For additional CSV, JSON, and Parquet specific parameters, see File Formats.

s3_url_style

The S3 connector supports two request URL styles:

• vhost: https://<bucket>.<endpoint>/<key> (virtual-hosted)
• path: https://<endpoint>/<bucket>/<key> (path-style)

When s3_url_style is not set, the connector auto-detects the correct style:

1. IP address endpoints (e.g. http://192.168.1.100:9000) → path
2. Custom hostname endpoints → DNS lookup on <bucket>.<host>. If the name resolves, vhost is used; otherwise path.
3. No custom endpoint (standard AWS S3) → vhost, except a bucket whose name contains a dot (e.g. my.bucket.name), which uses path.

A dotted bucket name cannot use virtual-hosted style over HTTPS on standard AWS: the bucket becomes part of a multi-label hostname (my.bucket.name.s3.<region>.amazonaws.com) that the AWS wildcard certificate (*.s3.<region>.amazonaws.com) cannot match, so the TLS handshake fails. The connector defaults these buckets to path style, matching AWS's own recommendation. An explicit s3_url_style always takes precedence.

Set s3_url_style explicitly to skip auto-detection.

Event-driven refresh with SQS

An accelerated S3 prefix can refresh from S3 Event Notifications delivered to an Amazon SQS queue instead of on a polling interval. Set refresh_mode: changes and s3_changes_queue_url. Spice long-polls the queue and, for each s3:ObjectCreated:* notification, reads the new object and appends its rows to the acceleration.

This is object-level, not row-level, change data capture. A new object adds rows; a removed object never deletes individual rows. For the list-and-poll alternative, see append refresh with time_column: last_modified.

datasets:
  - from: s3://my-bucket/events/
    name: events
    params:
      file_format: parquet
      s3_region: us-east-1
      s3_auth: iam_role
      s3_changes_queue_url: ${secrets:s3_events_queue_url}
    acceleration:
      enabled: true
      engine: cayenne
      mode: file
      refresh_mode: changes

How objects are applied

When the acceleration is empty, Spice lists the from prefix once and loads every object it finds before it marks the dataset ready. On a restart with a non-empty acceleration, Spice lists the prefix once and replaces the acceleration with that listing, rather than appending every listed object again. If any listed object cannot be read, Spice neither loads nor replaces from the partial listing, and retries.

After that, Spice applies notifications from the queue:

• s3:ObjectCreated:* reads the object and appends its rows as one write. A key that Spice has already applied is acknowledged without appending it again, so SQS redelivery and an overlap between the listing and the queue do not duplicate rows. The SQS message is deleted after the rows are written (at-least-once delivery).
• s3:ObjectRemoved:* is acknowledged and ignored by default, so queries still return the removed object's rows. With s3_on_object_removed: rebuild, Spice lists the prefix and replaces the whole acceleration from it.
• Objects the dataset does not read, such as a _SUCCESS marker or a file that does not match file_format or file_extension, are acknowledged without loading.

Every s3_changes_backfill_interval (default 1h), Spice lists the prefix again and applies objects it has not yet applied. SQS keeps unconsumed messages for 4 days by default and up to 14 days, so notifications sent while Spice is stopped are still delivered on restart; the backfill covers notifications that expired or were never delivered.

Spice accepts direct S3-to-SQS notifications, S3 notifications wrapped by Amazon SNS (S3 → SNS → SQS), and Amazon EventBridge S3 events. s3:TestEvent messages are ignored.

AWS setup

1. Create one SQS queue per dataset. A notification for a key outside the dataset's bucket and prefix is left on the queue, where it reappears after each visibility timeout until the queue's retention period expires. To feed several datasets from one bucket, fan out with SNS to one queue per dataset, or filter the bucket notification by the dataset prefix.
2. Add a queue access policy that allows the s3.amazonaws.com service principal to call sqs:SendMessage for the bucket. Without it, S3 cannot deliver notifications to the queue. See Granting permissions to publish event notification messages to a destination.
3. Configure the bucket's event notifications to send s3:ObjectCreated:* to the queue. Add s3:ObjectRemoved:* only when s3_on_object_removed: rebuild is set.
4. Grant the credentials Spice uses s3:GetObject and s3:ListBucket on the prefix, and sqs:ReceiveMessage and sqs:DeleteMessage on the queue.

The SQS client uses the same credentials as the S3 connector (s3_auth, s3_key, s3_secret, s3_session_token, or the IAM role chain).

Configuration errors

Spice refuses to register the dataset when:

• refresh_mode: changes is set without s3_changes_queue_url, or s3_changes_queue_url is set without refresh_mode: changes.
• s3_changes_queue_url is empty, is an ARN, or is not an HTTPS SQS queue URL.
• s3_auth is public. An unauthenticated client cannot receive from SQS.
• No SQS region can be resolved from s3_changes_region, the queue URL, or s3_region.
• s3_on_object_removed is not ignore or rebuild, s3_changes_key_prefix is not under the from prefix, or s3_changes_backfill_interval is not a duration greater than 0.
• The dataset has no structured file format (Parquet, CSV, JSON, JSONL, NDJSON, LDJSON, TSV, ORC, or, except on Windows, Vortex) from file_format, file_extension, or the from path.
• from contains a wildcard or names a single object instead of a prefix. End the prefix with /; keep a single object on refresh_mode: full.

For example, an ARN in s3_changes_queue_url fails with:

Failed to register dataset events (s3): `s3_changes_queue_url` must be an SQS queue URL (https://sqs.<region>.amazonaws.com/...), not an ARN. See: https://spiceai.org/docs/components/data-connectors/s3

Limitations

• A removed object's rows are never deleted individually. s3_on_object_removed: rebuild replaces the whole acceleration from the prefix.
• Overwriting an object in place under a key Spice has already applied does not reload it. S3 sends s3:ObjectCreated:* for an overwrite, and s3_on_object_removed: rebuild acts only on s3:ObjectRemoved:*. Write changed data to a new key, or restart Spice to replace the acceleration from a listing of the prefix.
• The set of applied keys is held in memory. A restart replaces the acceleration from one listing of the prefix.
• An object key with a . or .. path segment is skipped by both the queue and the listing.
• One queue cannot be shared across datasets.
• A custom SQS endpoint, such as LocalStack, is not configurable.

Authentication

No authentication is required for public endpoints. For private buckets, set s3_auth to key or iam_role.

If s3_auth is set to iam_role, the connector will automatically load credentials from the following sources in order.

1. Environment Variables:

   • AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY
   • AWS_SESSION_TOKEN (if using temporary credentials)

2. Shared AWS Config/Credentials Files:

   • Config file: ~/.aws/config (Linux/Mac) or %UserProfile%\.aws\config (Windows)

   • Credentials file: ~/.aws/credentials (Linux/Mac) or %UserProfile%\.aws\credentials (Windows)

   • The AWS_PROFILE environment variable can be used to specify a named profile, otherwise the [default] profile is used.

   • Supports both static credentials and SSO sessions

   • Example credentials file:

     # Static credentials (in .aws/credentials)
     [default]
     aws_access_key_id = YOUR_ACCESS_KEY
     aws_secret_access_key = YOUR_SECRET_KEY
     
     # SSO profile (in .aws/config)
     [profile sso-profile]
     sso_start_url = https://my-sso-portal.awsapps.com/start
     sso_region = us-west-2
     sso_account_id = 123456789012
     sso_role_name = MyRole
     region = us-west-2

3. AWS STS Web Identity Token Credentials:

   • Used primarily with OpenID Connect (OIDC) and OAuth
   • Common in Kubernetes environments using IAM roles for service accounts (IRSA)
   • Relies on the environment variables AWS_WEB_IDENTITY_TOKEN_FILE and AWS_ROLE_ARN to be present.
     • These environment variables are automatically injected by EKS when the IAM Role is annotated on the pod Service Account.

4. ECS Container Credentials:

   • Used when running in Amazon ECS containers
   • Automatically uses the task's IAM role
   • Retrieved from the ECS credential provider endpoint.
   • Relies on the environment variable AWS_CONTAINER_CREDENTIALS_RELATIVE_URI or AWS_CONTAINER_CREDENTIALS_FULL_URI which are automatically injected by ECS.

5. AWS EC2 Instance Metadata Service (IMDSv2):

   • Used when running on EC2 instances.
   • Automatically uses the instance's IAM role.
   • Retrieved securely using IMDSv2.

The connector will try each source in order until valid credentials are found. If no valid credentials are found, an authentication error will be returned.

Required IAM Permissions

Minimum IAM policy for S3 access:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": "arn:aws:s3:::company-bucketname-datasets"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject"],
      "Resource": "arn:aws:s3:::company-bucketname-datasets/*"
    }
  ]
}

Permission Details

Types

Refer to Object Store Data Types for data type mapping from object store files to arrow data type.

Examples

Public bucket Example

Create a dataset named taxi_trips from a public S3 folder.

- from: s3://spiceai-demo-datasets/taxi_trips/2024/
  name: taxi_trips
  params:
    file_format: parquet

MinIO Example

Create a dataset named cool_dataset from a Parquet file stored in MinIO.

- from: s3://s3-bucket-name/path/to/parquet/cool_dataset.parquet
  name: cool_dataset
  params:
    s3_endpoint: http://my.minio.server
    s3_region: 'us-east-1' # Best practice for MinIO
    s3_url_style: path
    allow_http: true

Hive Partitioning Example

Hive partitioning is a data organization technique that improves query performance by storing data in a hierarchical directory structure based on partition column values. This enables efficient data retrieval by skipping unnecessary data scans.

For example, a dataset partitioned by year, month, and day might have a directory structure like:

s3://bucket/dataset/year=2024/month=03/day=15/data_file.parquet
s3://bucket/dataset/year=2024/month=03/day=16/data_file.parquet

Spice can automatically infer these partition columns from the directory structure when hive_partitioning_enabled is set to true.

version: v1
kind: Spicepod
name: hive_data

datasets:
  - from: s3://spiceai-public-datasets/hive_partitioned_data/
    name: hive_data_infer
    params:
      file_format: parquet
      hive_partitioning_enabled: true

Schema Source Path example

Use schema_source_path to speed up dataset registration by specifying a URL to use to infer the schema.

- from: s3://spiceai-demo-datasets/taxi_trips/
  name: taxi_trips
  params:
    file_format: parquet
    schema_source_path: s3://spiceai-demo-datasets/taxi_trips/2014/1/trips_01.parquet # or s3://spiceai-demo-datasets/taxi_trips/2014/1/

Metadata Columns Example

Metadata columns expose per-file S3 object metadata (_location, _last_modified, _size) as virtual columns in query results. See Metadata Columns for full details.

- from: s3://spiceai-public-datasets/hive_partitioned_data/
  name: hive_data
  params:
    file_format: parquet
    hive_partitioning_enabled: true
  metadata:
    _location: enabled
    _last_modified: enabled
    _size: enabled

Query metadata alongside regular data:

SELECT id, value, _location, _size, _last_modified
FROM hive_data
ORDER BY id
LIMIT 5;

+----+---------+-------------------------------------------------------------------------------------------+-------+----------------------+
| id | value   | _location                                                                                 | _size | _last_modified       |
+----+---------+-------------------------------------------------------------------------------------------+-------+----------------------+
| 0  | value_0 | s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=1/day=1/data_0.parquet | 2317  | 2024-10-10T05:36:59Z |
| 1  | value_1 | s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=1/day=1/data_0.parquet | 2317  | 2024-10-10T05:36:59Z |
| 2  | value_2 | s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=1/day=1/data_0.parquet | 2317  | 2024-10-10T05:36:59Z |
| 3  | value_3 | s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=1/day=1/data_0.parquet | 2317  | 2024-10-10T05:36:59Z |
| 4  | value_4 | s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=1/day=1/data_0.parquet | 2317  | 2024-10-10T05:36:59Z |
+----+---------+-------------------------------------------------------------------------------------------+-------+----------------------+

Filter by specific file:

SELECT id, value FROM hive_data
WHERE _location = 's3://spiceai-public-datasets/hive_partitioned_data/year=2023/month=2/day=2/data_1.parquet'
ORDER BY id;

+----+---------+
| id | value   |
+----+---------+
| 10 | value_0 |
| 11 | value_1 |
| 12 | value_2 |
| 13 | value_3 |
| 14 | value_4 |
| 15 | value_5 |
| 16 | value_6 |
| 17 | value_7 |
| 18 | value_8 |
| 19 | value_9 |
+----+---------+

Aggregate per file:

SELECT _location, COUNT(*) AS row_count, _size
FROM hive_data
GROUP BY _location, _size
ORDER BY _location;

+-------------------------------------------------------------------------------------------+-----------+-------+
| _location                                                                                 | row_count | _size |
+-------------------------------------------------------------------------------------------+-----------+-------+
| s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=1/day=1/data_0.parquet | 10        | 2317  |
| s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=1/day=2/data_4.parquet | 10        | 2319  |
| s3://spiceai-public-datasets/hive_partitioned_data/year=2022/month=3/day=3/data_2.parquet | 10        | 2319  |
| s3://spiceai-public-datasets/hive_partitioned_data/year=2023/month=2/day=2/data_1.parquet | 10        | 2319  |
| s3://spiceai-public-datasets/hive_partitioned_data/year=2023/month=4/day=1/data_3.parquet | 10        | 2319  |
+-------------------------------------------------------------------------------------------+-----------+-------+

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Limitations

Cookbook

• A cookbook recipe to configure S3 as a data connector in Spice. S3 Data Connector