s3 Table Function

```
SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;
```

## Using S3 credentials (ClickHouse Cloud)[​](#using-s3-credentials-clickhouse-cloud "Direct link to Using S3 credentials (ClickHouse Cloud)")

For non-public buckets, users can pass an `aws_access_key_id` and `aws_secret_access_key` to the function. For example:

```
SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv', '<KEY>', '<SECRET>','TSVWithNames')
```

This is appropriate for one-off accesses or in cases where credentials can easily be rotated. However, this is not recommended as a long-term solution for repeated access or where credentials are sensitive. In this case, we recommend users rely on role-based access.

Role-based access for S3 in ClickHouse Cloud is documented [here](/docs/cloud/data-sources/secure-s3).

Once configured, a `roleARN` can be passed to the s3 function via an `extra_credentials` parameter. For example:

```
SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001'))
```

Further examples can be found [here](/docs/cloud/data-sources/secure-s3#access-your-s3-bucket-with-the-clickhouseaccess-role)

## Working with archives[​](#working-with-archives "Direct link to Working with archives")

Suppose that we have several archive files with following URIs on S3:

- '<https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-10.csv.zip>'
- '<https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-11.csv.zip>'
- '<https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-12.csv.zip>'

Extracting data from these archives is possible using ::. Globs can be used both in the url part as well as in the part after :: (responsible for the name of a file inside the archive).

```
SELECT *
FROM s3(
   'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv'
);
```

Note

ClickHouse supports three archive formats:
ZIP
TAR
7Z
While ZIP and TAR archives can be accessed from any supported storage location, 7Z archives can only be read from the local filesystem where ClickHouse is installed.

## Inserting Data[​](#inserting-data "Direct link to Inserting Data")

Note that rows can only be inserted into new files. There are no merge cycles or file split operations. Once a file is written, subsequent inserts will fail. See more details [here](/docs/integrations/s3#inserting-data).

## Virtual Columns[​](#virtual-columns "Direct link to Virtual Columns")

- `_path` — Path to the file. Type: `LowCardinality(String)`. In case of archive, shows path in a format: `"{path_to_archive}::{path_to_file_inside_archive}"`
- `_file` — Name of the file. Type: `LowCardinality(String)`. In case of archive shows name of the file inside the archive.
- `_size` — Size of the file in bytes. Type: `Nullable(UInt64)`. If the file size is unknown, the value is `NULL`. In case of archive shows uncompressed file size of the file inside the archive.
- `_time` — Last modified time of the file. Type: `Nullable(DateTime)`. If the time is unknown, the value is `NULL`.

## use\_hive\_partitioning setting[​](#hive-style-partitioning "Direct link to use_hive_partitioning setting")

This is a hint for ClickHouse to parse hive style partitioned files upon reading time. It has no effect on writing. For symmetrical reads and writes, use the `partition_strategy` argument.

When setting `use_hive_partitioning` is set to 1, ClickHouse will detect Hive-style partitioning in the path (`/name=value/`) and will allow to use partition columns as virtual columns in the query. These virtual columns will have the same names as in the partitioned path.

**Example**

```
SELECT * FROM s3('s3://data/path/date=*/country=*/code=*/*.parquet') WHERE date > '2020-01-01' AND country = 'Netherlands' AND code = 42;
```

## Accessing requester-pays buckets[​](#accessing-requester-pays-buckets "Direct link to Accessing requester-pays buckets")

To access a requester-pays bucket, a header `x-amz-request-payer = requester` must be passed in any requests. This is achieved by passing the parameter `headers('x-amz-request-payer' = 'requester')` to the s3 function. For example:

```
SELECT
    count() AS num_rows,
    uniqExact(_file) AS num_files
FROM s3('https://coiled-datasets-rp.s3.us-east-1.amazonaws.com/1trc/measurements-100*.parquet', 'AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY', headers('x-amz-request-payer' = 'requester'))

┌───num_rows─┬─num_files─┐
│ 1110000000 │       111 │
└────────────┴───────────┘

1 row in set. Elapsed: 3.089 sec. Processed 1.09 billion rows, 0.00 B (353.55 million rows/s., 0.00 B/s.)
Peak memory usage: 192.27 KiB.
```

## Storage Settings[​](#storage-settings "Direct link to Storage Settings")

- [s3\_truncate\_on\_insert](/docs/operations/settings/settings#s3_truncate_on_insert) - allows to truncate file before insert into it. Disabled by default.
- [s3\_create\_new\_file\_on\_insert](/docs/operations/settings/settings#s3_create_new_file_on_insert) - allows to create a new file on each insert if format has suffix. Disabled by default.
- [s3\_skip\_empty\_files](/docs/operations/settings/settings#s3_skip_empty_files) - allows to skip empty files while reading. Enabled by default.

## Nested Avro Schemas[​](#nested-avro-schemas "Direct link to Nested Avro Schemas")

When reading Avro files that contain **nested records** which diverge across files (for example, some files have an extra field inside a nested object), ClickHouse may return an error such as:

> The number of leaves in record doesn't match the number of elements in tuple...

This happens because ClickHouse expects all nested record structures to match the same schema.  
To handle this scenario, you can:

- Use `schema_inference_mode='union'` to merge different nested record schemas, or
- Manually align your nested structures and enable  
  `use_structure_from_insertion_table_in_table_functions=1`.

Performance note

`schema_inference_mode='union'` may take longer on very large S3 datasets because it must scan each file to infer the schema.

**Example**

```
INSERT INTO data_stage
SELECT
    id,
    data
FROM s3('https://bucket-name/*.avro', 'Avro')
SETTINGS schema_inference_mode='union';

## Related \{#related}

- [S3 engine](../../engines/table-engines/integrations/s3.md)
- [Integrating S3 with ClickHouse](/integrations/s3)
```

- [Syntax](#syntax)- [Returned value](#returned_value)- [Examples](#examples)- [Usage](#usage)- [Partitioned Write](#partitioned-write)
          - [Partition Strategy](#partition-strategy)- [Accessing public buckets](#accessing-public-buckets)- [Using S3 credentials (ClickHouse Cloud)](#using-s3-credentials-clickhouse-cloud)- [Working with archives](#working-with-archives)- [Inserting Data](#inserting-data)- [Virtual Columns](#virtual-columns)- [use\_hive\_partitioning setting](#hive-style-partitioning)- [Accessing requester-pays buckets](#accessing-requester-pays-buckets)- [Storage Settings](#storage-settings)- [Nested Avro Schemas](#nested-avro-schemas)

Was this page helpful?