# S3 Data Connector Query Parquet, CSV, JSON, and other files in Amazon S3 or S3-compatible stores such as MinIO and Cloudflare R2 with SQL. 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](https://spice.ai/docs/building-blocks/data-connectors#file-formats). ## Quickstart Query a public S3 dataset with no authentication: ```yaml 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](https://spice.ai/docs/building-blocks/data-connectors/s3#authentication)): ```yaml 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:///` Example: `from: s3://my-bucket/path/to/file.parquet` ### `name` The dataset name. This will be used as the table name within Spice. Example: ```yaml datasets: - from: s3://s3-bucket-name/taxi_sample.csv name: cool_dataset params: file_format: csv ``` ```sql SELECT COUNT(*) FROM cool_dataset; ``` ```shell +----------+ | count(*) | +----------+ | 6001215 | +----------+ ``` The dataset name cannot be a [reserved keyword](https://spiceai.org/docs/reference/spicepod/keywords). ### `params` | Parameter Name | Description | | - | - | | `file_format` | Specifies the data format. Required if it cannot be inferred from the object URI. Options: `parquet`, `orc`, `csv`, `json`, `snapshot`. Refer to [File Formats](https://spice.ai/docs/building-blocks/data-connectors#file-formats) for details. `snapshot` reads acceleration snapshots that another Spice instance publishes; see [Serve a dataset from published snapshots](https://spiceai.org/docs/features/data-acceleration/snapshots#serve-a-dataset-from-published-snapshots). | | `s3_endpoint` | S3 endpoint URL (e.g., for MinIO). Default is the region endpoint. E.g. `s3_endpoint: https://my.minio.server` | | `s3_url_style` | URL style used for S3 requests. Options: `vhost` and `path`. When not set, auto-detected: IP endpoints use `path`; other custom endpoints are detected via DNS lookup on the virtual-hosted hostname; standard AWS endpoints use `vhost`, except buckets whose name contains a dot, which use `path`. | | `s3_region` | S3 bucket region. Default: `us-east-1`. | | `client_timeout` | Optional. Timeout for S3 operations. Defaults to `30s` (the underlying object store HTTP client default) when unset. | | `hive_partitioning_enabled` | Enable partitioning using hive-style partitioning from the folder structure. Defaults to `false` | | `s3_auth` | Authentication type. Options: `public`, `key` and `iam_role`. When unset, uses the default AWS credential chain (IAM-based authentication). Set to `public` for unauthenticated access to public buckets. If set to `key` the `s3_key` and `s3_secret` parameters must also be set. If set to `iam_role` the credentials will be loaded from environment variables or IAM roles (see [Authentication](https://spice.ai/docs/building-blocks/data-connectors/s3#authentication) for details). | | `s3_iam_role_source` | Optional. IAM role credential source (used when `s3_auth` is `iam_role` or unset). `auto` (default) uses the default AWS credential chain, `metadata` uses only instance/container metadata (IMDS, ECS, EKS/IRSA), `env` uses only environment variables. | | `s3_key` | Access key (e.g. `AWS_ACCESS_KEY_ID` for AWS). Requires `s3_auth` is set to `key`. | | `s3_secret` | Secret key (e.g. `AWS_SECRET_ACCESS_KEY` for AWS). Requires `s3_auth` is set to `key`. | | `s3_session_token` | Session token (e.g. `AWS_SESSION_TOKEN` for AWS) for temporary credentials. Requires `s3_auth` is set to `key`. | | `s3_versioning` | Enables support for S3 buckets with [S3 Versioning](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Versioning.html). Options: `enabled` and `disabled`. Defaults to `enabled`. | | `allow_http` | Enables insecure HTTP connections to `s3_endpoint`. Defaults to `false`. | | `schema_source_path` | Specifies the URL used to infer the dataset schema. Default to the most recently modified file | | `refresh_skip` | Controls skipping refreshes for accelerated **single-file** S3 datasets when the cached `ETag` / version ID still matches the object in S3. Options: `enabled` and `disabled`. Defaults to `enabled`. Has no effect on directory (prefix) datasets or on datasets without acceleration. | | `s3_changes_queue_url` | SQS queue URL that receives this dataset's S3 event notifications. Required with `refresh_mode: changes`, and rejected without it. Must be a queue URL (`https://sqs..amazonaws.com//`), not an ARN. Stored as a secret. See [Event-driven refresh with SQS](https://spice.ai/docs/building-blocks/data-connectors/s3#event-driven-refresh-with-sqs). | | `s3_changes_region` | Optional. AWS region of the SQS queue. Defaults to the region in `s3_changes_queue_url`, then `s3_region`. | | `s3_changes_key_prefix` | Optional. Object-key prefix to apply from the queue and the listing backfill. Defaults to the key prefix of `from`. Must be equal to or nested under the dataset path. | | `s3_changes_backfill_interval` | Optional. How often Spice lists the dataset prefix and applies objects it has not yet applied, so missed or expired notifications still load. A duration greater than 0. Defaults to `1h`. | | `s3_on_object_removed` | Optional. What to do with `s3:ObjectRemoved:*` notifications. `ignore` (default) keeps the removed object's rows. `rebuild` replaces the whole acceleration from a listing of the prefix; it does not delete individual rows. | For additional CSV, JSON, and Parquet specific parameters, see [File Formats](https://spiceai.org/docs/reference/file_format). ### `s3_url_style` The S3 connector supports two request URL styles: - `vhost`: `https://./` (virtual-hosted) - `path`: `https:////` (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 `.`. 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..amazonaws.com`) that the AWS wildcard certificate (`*.s3..amazonaws.com`) cannot match, so the TLS handshake fails. The connector defaults these buckets to path style, matching [AWS's own recommendation](https://docs.aws.amazon.com/AmazonS3/latest/userguide/VirtualHosting.html). 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/EventNotifications.html) 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](https://spiceai.org/docs/features/data-acceleration/refresh-modes/append) with `time_column: last_modified`. ```yaml 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/grant-destinations-permissions-to-s3.html). 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..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: ```ini # 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 ``` > **Tip** > > To set up SSO authentication: > > 1. Run `aws configure sso` to configure a new SSO profile > 2. Use the profile by setting `AWS_PROFILE=sso-profile` > 3. Run `aws sso login --profile sso-profile` to start a new SSO session 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](https://docs.aws.amazon.com/eks/latest/userguide/iam-roles-for-service-accounts.html)) - 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](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/configuring-instance-metadata-service.html). 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. > **Note** > > **IAM Permissions** > > Regardless of the credential source, the IAM role or user must have appropriate S3 permissions (e.g., `s3:ListBucket`, `s3:GetObject`) to access the files. If the Spicepod connects to multiple different AWS services, the permissions should cover all of them. > **Note** > > **kube2iam** > > [`kube2iam`](https://github.com/jtblin/kube2iam) is a project that provides IAM roles to Kubernetes pods based on annotations. It has been superceded by [IAM Roles for service accounts (IRSA)](https://docs.aws.amazon.com/eks/latest/userguide/iam-roles-for-service-accounts.html), which should be preferred for new deployments. > > Spice requires `kube2iam >= 0.12` - versions prior to [`0.12`](https://github.com/jtblin/kube2iam/releases/tag/0.12.0) only supported IMDSv1. ### Required IAM Permissions Minimum IAM policy for S3 access: ```json { "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 | Permission | Purpose | | - | - | | `s3:ListBucket` | Required. Allows scanning all objects from the bucket | | `s3:GetObject` | Required. Allows fetching objects | ## Types Refer to [Object Store Data Types](https://spiceai.org/docs/reference/datatypes/object_store) 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. ```yaml - 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. ```yaml - 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: ```plaintext 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`. ```yaml 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. ```yaml - 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](https://spice.ai/docs/building-blocks/data-connectors#metadata-columns) for full details. ```yaml - 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: ```sql SELECT id, value, _location, _size, _last_modified FROM hive_data ORDER BY id LIMIT 5; ``` ```shell +----+---------+-------------------------------------------------------------------------------------------+-------+----------------------+ | 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: ```sql 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; ``` ```shell +----+---------+ | 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: ```sql SELECT _location, COUNT(*) AS row_count, _size FROM hive_data GROUP BY _location, _size ORDER BY _location; ``` ```shell +-------------------------------------------------------------------------------------------+-----------+-------+ | _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](https://spiceai.org/docs/components/secret-stores). Additionally, learn how to use referenced secrets in component parameters by visiting the [using referenced secrets guide](https://spiceai.org/docs/components/secret-stores#using-secrets). ## Limitations > **Warning** > > **Performance Considerations** > > When using the S3 Data connector without acceleration, data is loaded into memory during query execution. Ensure sufficient memory is available, including overhead for queries and the runtime, especially with concurrent queries. > > Memory limitations can be mitigated by storing acceleration data on disk, which is supported by [`duckdb`](https://spiceai.org/docs/components/data-accelerators/duckdb) and [`sqlite`](https://spiceai.org/docs/components/data-accelerators/sqlite) accelerators by specifying `mode: file`. > > Each query retrieves data from the S3 source, which might result in significant network requests and bandwidth consumption. This can affect network performance and incur costs related to data transfer from S3. ## Cookbook - A cookbook recipe to configure S3 as a data connector in Spice. [S3 Data Connector](https://github.com/spiceai/cookbook/tree/trunk/s3#readme)