ClickHouse

Prerequisites

If your ClickHouse security posture requires IP whitelisting, have our static IP available during the following steps. It will be required in Step 1.

Allow access

SSH Tunneling Not SupportedSSH Tunneling is currently unsupported for Clickhouse destinations. Please ensure your Clickhouse destination is accessible over the public internet.

Create a rule in a security group or firewall settings to whitelist:

incoming connections to your host and port (usually 9440) from the static IP.
outgoing connections from ports 1024 to 65535 to the static IP.

Network allowlistingCloud Hosted (US): 35.192.85.117/32Cloud Hosted (EU): 104.199.49.149/32If private-cloud or self-hosted, contact support for the static egress IP.

Create writer user

Create a database user to perform the writing of the data.

Open a connection to your ClickHouse database.
Create a user for the data transfer by executing the following SQL command.

Create user

CREATE USER <username>@'%' IDENTIFIED BY '<some-password>';

Password RulesPasswords may only include alphanumeric characters (A-Z, a-z, 0-9), dashes (-), and underscores (_).

Grant user required privileges on the database.

Grant privileges

GRANT SELECT ON information_schema.columns TO <username>;
GRANT CREATE, INSERT, DROP, ALTER, OPTIMIZE, SHOW, TRUNCATE ON <database>.* TO <username>@'%';
grant CREATE TEMPORARY TABLE, S3 on *.* to <username>@'%';

Understanding the CREATE TEMPORARY TABLE, S3 permissionsThe CREATE TEMPORARY TABLE and S3 permissions are required to efficiently transfer data to ClickHouse. Under the hood, these permissions are used to stage data in object storage as compressed files, COPY INTO temporary tables, and finally merge into the target tables. By definition, the temporary table will not exist outside of the session.The S3 permission is required for both S3 and GCS staging buckets, because ClickHouse accesses GCS buckets through its S3 integration.

Set up staging bucket

ClickHouse destinations require a staging bucket to efficiently transfer data. Configure your staging bucket using one of the following types of ClickHouse supported object storage:

S3 staging bucket
GCS staging bucket

By default, S3 authentication uses role-based access. You will need the trust policy prepopulated with our identifier to grant access. It should look similar to the following JSON object with a proper service account identifier:

Trust policy

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "sts:AssumeRoleWithWebIdentity"
      ],
      "Principal": {
        "Federated": "accounts.google.com"
      },
      "Condition": {
        "StringEquals": {
          "accounts.google.com:oaud": "<some_organization_identifier>",
          "accounts.google.com:sub": "<some_service_account_identifier>"
        }
      }
    }
  ]
}

Create staging bucket

Navigate to the S3 service page.
Click Create bucket.
Enter a Bucket name and modify any of the default settings as desired. Note: Object Ownership can be set to “ACLs disabled” and Block Public Access settings for this bucket can be set to “Block all public access” as recommended by AWS. Make note of the Bucket name and AWS Region.
Click Create bucket.

Create policy

Navigate to the IAM service page, click on the Policies navigation tab, and click Create policy.
Click the JSON tab, and paste the following policy, being sure to replace BUCKET_NAME with the name of the bucket chosen above.
1. Note: the first policy applies to BUCKET_NAME whereas the second policy applies only to the bucket’s contents (BUCKET_NAME/*), an important distinction.

IAM policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::BUCKET_NAME"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::BUCKET_NAME/*"
        }
    ]
}

Click through to the Review step, choose a name for the policy, for example, transfer-service-policy (this will be referenced in the next step), add a description, and click Create policy.

Create role

Navigate to the IAM service page.
Navigate to the Roles navigation tab, and click Create role.
Select Custom trust policy and paste the provided trust policy to allow AssumeRole access to the new role. Click Next.
Add the permissions policy created above, and click Next.
Enter a Role name, for example, transfer-role, and click Create role.
Once successfully created, search for the created role in the Roles list, click the role name, and make a note of the ARN value.

Granting ClickHouse Cloud role-based access to S3

If your ClickHouse instance runs on ClickHouse Cloud, you can have it authenticate to your S3 staging bucket using the same IAM role instead of access keys to avoid relying on long-lived static credentials.Add an additional statement to the trust policy of the role created above to allow ClickHouse to assume the role too.

Trust policy statement

{
    "Version": "2012-10-17",
    "Statement": [
        {
            // Existing statements
        },
        {
            "Effect": "Allow",
            "Principal": {"AWS": "<CLICKHOUSE_IAM_ARN>"},
            "Action": "sts:AssumeRole"
        }
    ]
}

Replace <CLICKHOUSE_IAM_ARN> with your ClickHouse instance’s IAM ARN. To obtain the ARN, go to your ClickHouse Cloud account, navigate to Settings → Network security information → View service details and copy the Service role ID (IAM).See the ClickHouse Secure S3 documentation for full details, including an automated CloudFormation setup option.

Optional: Add a short retention lifecycle policyYou may configure a lifecycle rule on the bucket to automatically delete objects older than 2 days as the bucket is not used to persist data. Note that transfer logic automatically cleans up files after transfer completion, so this is an optional step.

Add your destination

Connection ProtocolUse the ClickHouse TCP native protocol, not HTTPS. This is commonly exposed on port 9000.

Use the following details to complete the connection setup: host name, port, cluster, database name, schema name, username, password, and staging bucket details.

Understanding the database vs. schema fields (connection database vs. write database)Depending on the version of your integration, you may be asked for both a database and schema, or a connection database and write database.

database (also referred to as connection_database): is the database used to establish the connection with ClickHouse.
schema (also referred to as write_database): is the database/schema within which data will be written

These can be (and often are) the same values, but do not need to be.

Using the ClickHouse data

Querying ClickHouse data without duplicatesThe resulting ClickHouse tables use the ReplacingMergeTree table engine in order to efficiently upsert changes. To properly query this data, the FINAL keyword must be used when selecting from these tables guarantee duplicates are removed. For example:

Query without duplicates

SELECT
  *
FROM
  schema.table FINAL
WHERE
  foo = bar
ORDER BY foo
LIMIT 10;

Permissions checklist

User has SELECT on information_schema.columns.
User has CREATE, INSERT, DROP, ALTER, OPTIMIZE, SHOW, TRUNCATE on <database>.*.
User has CREATE TEMPORARY TABLE, S3 on *.*.
Staging bucket configured (S3 or GCS).
Firewall or security group allows the service’s egress IP on the ClickHouse native protocol port (default: 9440 for TLS, 9000 for TCP).
Password uses only alphanumeric characters, dashes (-), or underscores (_).

FAQ

How is the ClickHouse connection secured?

We connect using the username and password you configure over the ClickHouse native TCP protocol. Network access can be restricted by allowlisting the service’s static egress IP in your firewall or security group. Note: SSH tunneling is not supported for ClickHouse destinations.

Getting started

Core concepts

Features

Deploying Prequel

Logging, Monitoring, and Observability

Integrations

Developer SDKs

Sources

Destinations

Security & compliance

Prerequisites

Create staging bucket

Create policy

Create role

Granting ClickHouse Cloud role-based access to S3

Create a service account

Create staging bucket

Generate HMAC key

Using the ClickHouse data

Permissions checklist

FAQ

​Prerequisites

​Create staging bucket

​Create policy

​Create role

​Granting ClickHouse Cloud role-based access to S3

​Create a service account

​Create staging bucket

​Generate HMAC key

​Using the ClickHouse data

​Permissions checklist

​FAQ

Prerequisites

Create staging bucket

Create policy

Create role

Granting ClickHouse Cloud role-based access to S3

Create a service account

Create staging bucket

Generate HMAC key

Using the ClickHouse data

Permissions checklist

FAQ