ClickHouse

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:

1. In the ClickHouse Cloud service settings, copy the Service role ID (IAM) under Network security information.
2. Create an IAM role in your AWS account whose trust policy allows that service role to call sts:AssumeRole.
3. Give the role write access to the unload prefix.
4. 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:

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.