Integrating Amazon S3 with ClickHouse Cloud

It is also possible to emit S3 event notifications to SQS via an SNS topic. This can be used in case some of the limitations of direct S3 → SQS integration have been met. In this case, you’ll need to enable the [raw message delivery](https://docs.aws.amazon.com/sns/latest/dg/sns-large-payload-raw-message-delivery.html) option.

### File pattern matching[​](#file-pattern-matching "Direct link to File pattern matching")

Object Storage ClickPipes follow the POSIX standard for file pattern matching. All patterns are **case-sensitive** and match the **full path** after the bucket name. For better performance, use the most specific pattern possible (e.g., `data-2024-*.csv` instead of `*.csv`).

#### Supported patterns[​](#supported-patterns "Direct link to Supported patterns")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Pattern Description Example Matches|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `?` Matches exactly **one** character (excluding `/`) `data-?.csv` `data-1.csv`, `data-a.csv`, `data-x.csv`| `*` Matches **zero or more** characters (excluding `/`) `data-*.csv` `data-1.csv`, `data-001.csv`, `data-report.csv`, `data-.csv`| `**`   Recursive Matches **zero or more** characters (including `/`). Enables **recursive directory traversal**. `logs/**/error.log` `logs/error.log`, `logs/2024/error.log`, `logs/2024/01/error.log` | | | | | | | | | | | | | | | |

**Examples:**

- `https://bucket.s3.amazonaws.com/folder/*.csv`
- `https://bucket.s3.amazonaws.com/logs/**/data.json`
- `https://bucket.s3.amazonaws.com/file-?.parquet`
- `https://bucket.s3.amazonaws.com/data-2024-*.csv.gz`

#### Unsupported patterns[​](#unsupported-patterns "Direct link to Unsupported patterns")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Pattern Description Example Alternatives|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `{abc,def}` Brace expansion. `{logs,data}/file.csv` Create separate ClickPipes for each path.|  |  |  |  | | --- | --- | --- | --- | | `{N..M}` Numeric range expansion `file-{1..100}.csv` Use `file-*.csv` or `file-?.csv`. | | | | | | | | | | | |

**Examples:**

- `https://bucket.s3.amazonaws.com/{documents-01,documents-02}.json`
- `https://bucket.s3.amazonaws.com/file-{1..100}.csv`
- `https://bucket.s3.amazonaws.com/{logs,metrics}/data.parquet`

### Exactly-once semantics[​](#exactly-once-semantics "Direct link to Exactly-once semantics")

Various types of failures can occur when ingesting large dataset, which can result in a partial inserts or duplicate data. Object Storage ClickPipes are resilient to insert failures and provides exactly-once semantics. This is accomplished by using temporary "staging" tables. Data is first inserted into the staging tables. If something goes wrong with this insert, the staging table can be truncated and the insert can be retried from a clean state. Only when an insert is completed and successful, the partitions in the staging table are moved to target table. To read more about this strategy, check-out [this blog post](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part3).

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

To track which files have been ingested, include the `_file` virtual column to the column mapping list. The `_file` virtual column contains the filename of the source object, which can be used to query which files have been processed.

## Access control[​](#access-control "Direct link to Access control")

### Permissions[​](#permissions "Direct link to Permissions")

The S3 ClickPipe supports public and private buckets. [Requester Pays](https://docs.aws.amazon.com/AmazonS3/latest/userguide/RequesterPaysBuckets.html) buckets are **not** supported.

#### S3 bucket[​](#s3-bucket "Direct link to S3 bucket")

Buckets must allow the following actions in the bucket policy:

- [`s3:GetObject`](https://docs.aws.amazon.com/AmazonS3/latest/API/API_GetObject.html)
- [`s3:ListBucket`](https://docs.aws.amazon.com/AmazonS3/latest/API/API_ListObjectsV2.html)

#### SQS queue[​](#sqs-queue "Direct link to SQS queue")

When using [unordered mode](#continuous-ingestion-any-order), the SQS must allow the following actions in the queue policy:

- [`sqs:ReceiveMessage`](https://docs.aws.amazon.com/AWSSimpleQueueService/latest/APIReference/API_ReceiveMessage.html)
- [`sqs:DeleteMessage`](https://docs.aws.amazon.com/AWSSimpleQueueService/latest/APIReference/API_DeleteMessage.html)
- [`sqs:GetQueueAttributes`](https://docs.aws.amazon.com/AWSSimpleQueueService/latest/APIReference/API_GetQueueAttributes.html)
- [`sqs:ListQueues`](https://docs.aws.amazon.com/AWSSimpleQueueService/latest/APIReference/API_ListQueues.html)

### Authentication[​](#authentication "Direct link to Authentication")

#### IAM credentials[​](#iam-credentials "Direct link to IAM credentials")

To use [access keys](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html) to authenticate, choose `Credentials` under **Authentication method** when setting up your ClickPipe connection. Then, provide the access key ID (e.g., `AKIAIOSFODNN7EXAMPLE`) and secret access key (e.g., `wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY`) under `Access key` and `Secret key`, respectively.

#### IAM role[​](#iam-role "Direct link to IAM role")

To use [role-based access](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles.html) to authenticate, choose `IAM role` under **Authentication method** when setting up your ClickPipe connection.

Follow [this guide](/docs/cloud/data-sources/secure-s3) to [create a role](/docs/cloud/data-sources/secure-s3#option-2-manually-create-iam-role) with the required trust policy for S3 access. Then, provide the IAM role ARN under `IAM role ARN`.

### Network access[​](#network-access "Direct link to Network access")

S3 ClickPipes use two distinct network paths for metadata discovery and data ingestion: the ClickPipes service and the ClickHouse Cloud service, respectively. If you want to configure an additional layer of network security (e.g., for compliance reasons), network access **must be configured for both paths**.

- For **IP-based access control**, the S3 bucket policy must allow the static IPs for the ClickPipes service region listed [here](/docs/integrations/clickpipes#list-of-static-ips), as well as the [static IPs](/docs/manage/data-sources/cloud-endpoints-api) for the ClickHouse Cloud service. To obtain the static IPs for your ClickHouse Cloud region, open a terminal and run:

  ```
  # Replace <your-region> with your ClickHouse Cloud region
  curl -s https://api.clickhouse.cloud/static-ips.json | jq -r '.aws[] | select(.region == "<your-region>") | .egress_ips[]'
  ```
- For **VPC endpoint-based access control**, the S3 bucket must be in the same region as the ClickHouse Cloud service and restrict `GetObject` operations to the VPC endpoint IDs of the ClickHouse Cloud service. To obtain the VPC endpoints for your ClickHouse Cloud region, open a terminal and run:

  ```
  # Replace <your-region> with your ClickHouse Cloud region
  curl -s https://api.clickhouse.cloud/static-ips.json | jq -r '.aws[] | select(.region == "<your-region>") | .s3_endpoints[]'
  ```

## Advanced settings[​](#advanced-settings "Direct link to Advanced settings")

ClickPipes provides sensible defaults that cover the requirements of most use cases. If your use case requires additional fine-tuning, you can adjust the following settings: