# ClickHouse
source: https://docs.chalk.ai/docs/clickhouse

## Integrate with ClickHouse and configure optional S3 data unloading.

Chalk supports ClickHouse as a SQL source.

### Add a ClickHouse source

In the Chalk dashboard, go to Integrations > Data Sources > Add a data source, select
ClickHouse, and enter the connection details. Then declare the source in your Chalk project
using the integration's name:

```
from chalk.sql import ClickhouseSource

warehouse = ClickhouseSource(name="WAREHOUSE")
```

Reference that name from a SQL file resolver:

```
-- type: offline
-- resolves: User
-- source: WAREHOUSE
SELECT id, lifetime_value
FROM users
```

### Data unloading

ClickHouse data unloading is optional. When enabled, ClickHouse writes query results as Parquet
files to S3 and Chalk reads those files in parallel. This avoids returning a large result through
the ClickHouse query connection. The unload produces multiple files for large results, with
approximately one million rows per file.

The unload path is the opt-in switch. If it is blank, Chalk keeps using the direct-query path.
ClickHouse unload currently supports S3 destinations only; GCS destinations are not supported in
this version.

Two independent identities need access to the same prefix:

- ClickHouse writer: writes the Parquet files.
- Chalk workload: lists and reads the files.

Each identity can use its ambient AWS credentials or assume a separately configured IAM role. The
role ARNs are non-secret selectors; Chalk does not store long-lived S3 access keys in the data source.
Separate roles are recommended so each principal receives only its required permissions.

### ClickHouse writer

For ClickHouse Cloud, use its standard role-based S3 access:

- In the ClickHouse Cloud service settings, copy the Service role ID (IAM) under
Network security information.
- Create an IAM role in your AWS account whose trust policy allows that service role to call
sts:AssumeRole.
- Give the role write access to the unload prefix.
- In the Chalk data source's Data unload section, set Unload destination to an
s3://bucket/prefix/ URI and ClickHouse writer role ARN to the role created above.

ClickHouse documents the complete trust-policy procedure in
Accessing S3 data securely.
Chalk passes the role to ClickHouse's
s3() table function using
extra_credentials(role_arn = ...). ClickHouse assumes the role and refreshes its temporary
credentials; no short-lived access key is frozen into Chalk's connection pool.

For self-managed ClickHouse, leave ClickHouse writer role ARN blank and configure the server so
its s3() table function can
write to the destination without credentials in the query. A typical deployment gives the
ClickHouse process or pod an AWS workload identity, such as an EC2 instance profile or EKS pod
identity. Existing endpoint-specific S3 server configuration can also supply the writer identity.

The ClickHouse s3() documentation describes its environment credential support. Verify the
identity from the ClickHouse server environment, not from the machine running your Chalk project.

### Chalk reader

Set Chalk reader role ARN when the Chalk workload must assume a customer IAM role to list and
read the unload prefix. The role's trust policy must allow the Chalk workload identity—not the
ClickHouse Cloud service role—to call sts:AssumeRole. Chalk refreshes the temporary credentials
while listing and scanning the output files.

Leave Chalk reader role ARN blank when the Chalk workload's ambient AWS identity already has
read access to the prefix.

### S3 permissions

Scope both identities to the configured bucket and unload prefix. A typical policy grants:

| Identity          | Bucket permissions                      | Unload-prefix permissions                                                |
| ----------------- | --------------------------------------- | ------------------------------------------------------------------------ |
| ClickHouse writer | `s3:GetBucketLocation`, `s3:ListBucket` | `s3:PutObject`, `s3:AbortMultipartUpload`, `s3:ListMultipartUploadParts` |
| Chalk workload    | `s3:GetBucketLocation`, `s3:ListBucket` | `s3:GetObject`                                                           |

If the bucket uses a customer-managed KMS key, also grant the ClickHouse writer the KMS permissions
needed to encrypt objects and the Chalk workload permission to decrypt them.

Chalk does not currently delete ClickHouse unload files after reading them. Configure an S3
lifecycle rule on the dedicated unload prefix so old query outputs expire automatically.

The dashboard's Test connection action checks ClickHouse connectivity, but does not write or
read an S3 object. Run an offline query after configuring unloading to validate both storage
identities.

### Configure in Python

Dashboard configuration is recommended. You can also provide the three non-secret values directly:

```
from chalk.sql import ClickhouseSource

warehouse = ClickhouseSource(
    name="WAREHOUSE",
    unload_path="s3://customer-bucket/chalk/clickhouse/",
    unload_aws_role_arn="arn:aws:iam::123456789012:role/ClickHouseUnloadWriter",
    unload_chalk_aws_role_arn="arn:aws:iam::123456789012:role/ChalkUnloadReader",
)
```

Omit either role when that principal uses its ambient workload identity. For named dashboard
integrations, ClickhouseSource(name="WAREHOUSE") loads all three settings automatically.