S3Queue table engine

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/s3queue.md)

This engine provides integration with [Amazon S3](https://aws.amazon.com/s3/) ecosystem and allows streaming import. This engine is similar to the [Kafka](/docs/engines/table-engines/integrations/kafka), [RabbitMQ](/docs/engines/table-engines/integrations/rabbitmq) engines, but provides S3-specific features.

It is important to understand this note from the [original PR for S3Queue implementation](https://github.com/ClickHouse/ClickHouse/pull/49086/files#diff-e1106769c9c8fbe48dd84f18310ef1a250f2c248800fde97586b3104e9cd6af8R183): when the `MATERIALIZED VIEW` joins the engine, the S3Queue Table Engine starts collecting data in the background.

## Create table[​](#creating-a-table "Direct link to Create table")

```
CREATE TABLE s3_queue_engine_table (name String, value UInt32)
    ENGINE = S3Queue(path, [NOSIGN, | aws_access_key_id, aws_secret_access_key,] format, [compression], [headers], [extra_credentials])
    [SETTINGS]
    [mode = '',]
    [after_processing = 'keep',]
    [keeper_path = '',]
    [loading_retries = 0,]
    [processing_threads_num = 16,]
    [parallel_inserts = false,]
    [enable_logging_to_queue_log = true,]
    [last_processed_path = "",]
    [tracked_files_limit = 1000,]
    [tracked_file_ttl_sec = 0,]
    [polling_min_timeout_ms = 1000,]
    [polling_max_timeout_ms = 10000,]
    [polling_backoff_ms = 0,]
    [cleanup_interval_min_ms = 10000,]
    [cleanup_interval_max_ms = 30000,]
    [buckets = 0,]
    [list_objects_batch_size = 1000,]
    [enable_hash_ring_filtering = 0,]
    [max_processed_files_before_commit = 100,]
    [max_processed_rows_before_commit = 0,]
    [max_processed_bytes_before_commit = 0,]
    [max_processing_time_sec_before_commit = 0,]
```

Note

Before `24.7`, it is required to use `s3queue_` prefix for all settings apart from `mode`, `after_processing` and `keeper_path`.

**Engine parameters**

`S3Queue` parameters are the same as `S3` table engine supports. See parameters section [here](/docs/engines/table-engines/integrations/s3#parameters).

**Example**

```
CREATE TABLE s3queue_engine_table (name String, value UInt32)
ENGINE=S3Queue('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/*', 'CSV', 'gzip')
SETTINGS
    mode = 'unordered';
```

Using named collections:

```
<clickhouse>
    <named_collections>
        <s3queue_conf>
            <url>'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/*</url>
            <access_key_id>test<access_key_id>
            <secret_access_key>test</secret_access_key>
        </s3queue_conf>
    </named_collections>
</clickhouse>
```

```
CREATE TABLE s3queue_engine_table (name String, value UInt32)
ENGINE=S3Queue(s3queue_conf, format = 'CSV', compression_method = 'gzip')
SETTINGS
    mode = 'ordered';
```

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

To get a list of settings, configured for the table, use `system.s3_queue_settings` table. Available from `24.10`.

Setting Names (24.7+)

Starting from version 24.7, S3Queue settings can be specified with or without the `s3queue_` prefix:

- **Modern syntax** (24.7+): `processing_threads_num`, `tracked_file_ttl_sec`, etc.
- **Legacy syntax** (all versions): `s3queue_processing_threads_num`, `s3queue_tracked_file_ttl_sec`, etc.

Both forms are supported in 24.7+. The examples on this page use the modern syntax with no prefix.

### Mode[​](#mode "Direct link to Mode")

Possible values:

- unordered — With unordered mode, the set of all already processed files is tracked with persistent nodes in ZooKeeper.
- ordered — With ordered mode, the files are processed in lexicographic order. It means that if file named 'BBB' was processed at some point and later on a file named 'AA' is added to the bucket, it will be ignored. Only the max name (in lexicographic sense) of the successfully consumed file, and the names of files that will be retried after unsuccessful loading attempt are being stored in ZooKeeper.

Default value: `ordered` in versions before 24.6. Starting with 24.6 there is no default value, the setting becomes required to be specified manually. For tables created on earlier versions the default value will remain `Ordered` for compatibility.

### `after_processing`[​](#after_processing "Direct link to after_processing")

How to handle file after successful processing.

Possible values:

- keep.
- delete.
- move.
- tag.

Default value: `keep`.

Move requires additional settings. In case of a move within the same bucket, a new path prefix must be provided as `after_processing_move_prefix`.

Move to another S3 bucket requires the target bucket URI as `after_processing_move_uri`, S3 credentials as `after_processing_move_access_key_id` and `after_processing_move_secret_access_key`.

Example:

```
CREATE TABLE s3queue_engine_table (name String, value UInt32)
ENGINE=S3Queue('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/*', 'CSV', 'gzip')
SETTINGS
    mode = 'unordered',
    after_processing = 'move',
    after_processing_retries = 20,
    after_processing_move_prefix = 'dst_prefix',
    after_processing_move_uri = 'https://clickhouse-public-datasets.s3.amazonaws.com/dst-bucket',
    after_processing_move_access_key_id = 'test',
    after_processing_move_secret_access_key = 'test';
```

Move from an Azure container to another Azure container requires the Blob Storage connection string as `after_processing_move_connection_string` and the container name as `after_processing_move_container`. See [the AzureQueue settings](/docs/engines/table-engines/integrations/azure-queue#settings).

Tagging requires tag key and value provided as `after_processing_tag_key` and `after_processing_tag_value`.

### `after_processing_retries`[​](#after_processing_retries "Direct link to after_processing_retries")

Number of retries for the requested after-processing action, before giving up.

Possible values:

- Non-negative integer.

Default value: `10`.

### `after_processing_move_access_key_id`[​](#after_processing_move_access_key_id "Direct link to after_processing_move_access_key_id")

Access Key ID for S3 bucket to move successfully processed files to, if the destination is another S3 bucket.

Possible values:

- String.

Default value: empty string.

### `after_processing_move_prefix`[​](#after_processing_move_prefix "Direct link to after_processing_move_prefix")

Path prefix to move successfully processed files to. It is applicable in both cases, move within the same and to another bucket.

Possible values:

- String.

Default value: empty string.

### `after_processing_move_secret_access_key`[​](#after_processing_move_secret_access_key "Direct link to after_processing_move_secret_access_key")

Secret Access Key for S3 bucket to move successfully processed files to, if the destination is another S3 bucket.

Possible values:

- String.

Default value: empty string.

### `after_processing_move_uri`[​](#after_processing_move_uri "Direct link to after_processing_move_uri")

URI of S3 bucket to move successfully processed files to, if the destination is another S3 bucket.

Possible values:

- String.

Default value: empty string.

### `after_processing_tag_key`[​](#after_processing_tag_key "Direct link to after_processing_tag_key")

Tag key to put tagging on successfully processed files, if `after_processing='tag'`.

Possible values:

- String.

Default value: empty string.

### `after_processing_tag_value`[​](#after_processing_tag_value "Direct link to after_processing_tag_value")

Tag value to put tagging on successfully processed files, if `after_processing='tag'`.

Possible values:

- String.

Default value: empty string.

### `keeper_path`[​](#keeper_path "Direct link to keeper_path")