S3 table engine

### Querying partitioned data[​](#querying-partitioned-data "Direct link to Querying partitioned data")

This example uses the [docker compose recipe](https://github.com/ClickHouse/examples/tree/5fdc6ff72f4e5137e23ea075c88d3f44b0202490/docker-compose-recipes/recipes/ch-and-minio-S3), which integrates ClickHouse and MinIO. You should be able to reproduce the same queries using S3 by replacing the endpoint and authentication values.

Notice that the S3 endpoint in the `ENGINE` configuration uses the parameter token `{_partition_id}` as part of the S3 object (filename), and that the SELECT queries select against those resulting object names (e.g., `test_3.csv`).

Note

As shown in the example, querying from S3 tables that are partitioned is
not directly supported at this time, but can be accomplished by querying the individual partitions
using the S3 table function.

The primary use-case for writing
partitioned data in S3 is to enable transferring that data into another
ClickHouse system (for example, moving from on-prem systems to ClickHouse
Cloud). Because ClickHouse datasets are often very large, and network
reliability is sometimes imperfect it makes sense to transfer datasets
in subsets, hence partitioned writes.

#### Create the table[​](#create-the-table "Direct link to Create the table")

```
CREATE TABLE p
(
    `column1` UInt32,
    `column2` UInt32,
    `column3` UInt32
)
ENGINE = S3(
-- highlight-next-line
           'http://minio:10000/clickhouse//test_{_partition_id}.csv',
           'minioadmin',
           'minioadminpassword',
           'CSV')
PARTITION BY column3
```

#### Insert data[​](#insert-data "Direct link to Insert data")

```
INSERT INTO p VALUES (1, 2, 3), (3, 2, 1), (78, 43, 45)
```

#### Select from partition 3[​](#select-from-partition-3 "Direct link to Select from partition 3")

Tip

This query uses the s3 table function

```
SELECT *
FROM s3('http://minio:10000/clickhouse//test_3.csv', 'minioadmin', 'minioadminpassword', 'CSV')
```

```
┌─c1─┬─c2─┬─c3─┐
│  1 │  2 │  3 │
└────┴────┴────┘
```

#### Select from partition 1[​](#select-from-partition-1 "Direct link to Select from partition 1")

```
SELECT *
FROM s3('http://minio:10000/clickhouse//test_1.csv', 'minioadmin', 'minioadminpassword', 'CSV')
```

```
┌─c1─┬─c2─┬─c3─┐
│  3 │  2 │  1 │
└────┴────┴────┘
```

#### Select from partition 45[​](#select-from-partition-45 "Direct link to Select from partition 45")

```
SELECT *
FROM s3('http://minio:10000/clickhouse//test_45.csv', 'minioadmin', 'minioadminpassword', 'CSV')
```

```
┌─c1─┬─c2─┬─c3─┐
│ 78 │ 43 │ 45 │
└────┴────┴────┘
```

#### Limitation[​](#limitation "Direct link to Limitation")

You may naturally try to `Select * from p`, but as noted above, this query will fail; use the preceding query.

```
SELECT * FROM p
```

```
Received exception from server (version 23.4.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Reading from a partitioned S3 storage is not implemented yet. (NOT_IMPLEMENTED)
```

## Insert data[​](#inserting-data "Direct link to Insert 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. To avoid this you can use `s3_truncate_on_insert` and `s3_create_new_file_on_insert` settings. 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)`.
- `_file` — Name of the file. Type: `LowCardinality(String)`.
- `_size` — Size of the file in bytes. Type: `Nullable(UInt64)`. If the size is unknown, the value is `NULL`.
- `_time` — Last modified time of the file. Type: `Nullable(DateTime)`. If the time is unknown, the value is `NULL`.
- `_etag` — ETag of the file. Type: `LowCardinality(String)`. If the etag is unknown, the value is `NULL`.
- `_tags` — Tags of the file. Type: `Map(String, String)`. If no tag exist, the value is an empty map `'.

For more information about virtual columns see [here](/docs/engines/table-engines#table_engines-virtual_columns).

## Implementation details[​](#implementation-details "Direct link to Implementation details")

- Reads and writes can be parallel
- Not supported:

  - `ALTER` and `SELECT...SAMPLE` operations.
  - Indexes.
  - [Zero-copy](/docs/operations/storing-data#zero-copy) replication is possible, but not supported.

  Zero-copy replication is not ready for production

  Zero-copy replication is disabled by default in ClickHouse version 22.8 and higher. This feature is not recommended for production use.

## Wildcards in path[​](#wildcards-in-path "Direct link to Wildcards in path")

`path` argument can specify multiple files using bash-like wildcards. For being processed file should exist and match to the whole path pattern. Listing of files is determined during `SELECT` (not at `CREATE` moment).

- `*` — Substitutes any number of any characters except `/` including empty string.
- `**` — Substitutes any number of any character include `/` including empty string.
- `?` — Substitutes any single character.
- `{some_string,another_string,yet_another_one}` — Substitutes any of strings `'some_string', 'another_string', 'yet_another_one'`.
- `{N..M}` — Substitutes any number in range from N to M including both borders. N and M can have leading zeroes e.g. `000..078`.

Constructions with `{}` are similar to the [remote](/docs/sql-reference/table-functions/remote) table function.

Note

If the listing of files contains number ranges with leading zeros, use the construction with braces for each digit separately or use `?`.

**Example with wildcards 1**

Create table with files named `file-000.csv`, `file-001.csv`, ... , `file-999.csv`:

```
CREATE TABLE big_table (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/my_folder/file-{000..999}.csv', 'CSV');
```

**Example with wildcards 2**

Suppose we have several files in CSV format with the following URIs on S3:

- '<https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/some_folder/some_file_1.csv>'
- '<https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/some_folder/some_file_2.csv>'
- '<https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/some_folder/some_file_3.csv>'
- '<https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/another_folder/some_file_1.csv>'
- '<https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/another_folder/some_file_2.csv>'
- '<https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/another_folder/some_file_3.csv>'

There are several ways to make a table consisting of all six files:

1. Specify the range of file postfixes:

```
CREATE TABLE table_with_range (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/some_file_{1..3}', 'CSV');
```

2. Take all files with `some_file_` prefix (there should be no extra files with such prefix in both folders):

```
CREATE TABLE table_with_question_mark (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/some_file_?', 'CSV');
```

3. Take all the files in both folders (all files should satisfy format and schema described in query):

```
CREATE TABLE table_with_asterisk (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/*', 'CSV');
```

## 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.