Initial setup to write to ClickHouse

ClickHouse Cloud uses port 8443 for JDBC by default; this port is open by default and network settings are managed by ClickHouse Cloud. For a self-hosted deployment, open the following ports as needed:

If you use Replicated or Distributed table engines with a self-hosted, clustered ClickHouse deployment, your ClickHouse cluster must also have replication configured, including ClickHouse Keeper (or ZooKeeper) and the interserver HTTP port used for data-part transfer between replicas. See ClickHouse's own clustering documentation for cluster-level configuration; ClickHouse Writer does not configure cluster topology.

Striim recommends creating a dedicated ClickHouse user for ClickHouse Writer rather than using the default administrative user. Grant the user the following permissions on the target database:

GRANT CREATE TABLE ON <database>.* TO <user>;
GRANT ALTER TABLE ON <database>.* TO <user>;
GRANT DROP TABLE ON <database>.* TO <user>;
GRANT TRUNCATE ON <database>.* TO <user>;
GRANT CREATE DATABASE ON *.* TO <user>;
GRANT SHOW TABLES ON *.* TO <user>;
GRANT SHOW DATABASES ON *.* TO <user>;
GRANT SELECT ON information_schema.* TO <user>;
GRANT SELECT ON system.tables TO <user>;
GRANT SELECT ON system.columns TO <user>;
GRANT SELECT ON system.parts TO <user>;
GRANT SELECT, INSERT, UPDATE, DELETE ON <database>.* TO <user>;

Alternatively, if you want to use X.509 SSL authentication, see Using SSL X.509 authentication for ClickHouse.

ClickHouse Writer supports the Shared database engine (used by ClickHouse Cloud services) and the Atomic database engine (the default for self-hosted ClickHouse deployments).

Connections to ClickHouse Cloud are always encrypted using TLS. For a self-hosted deployment, you can configure TLS separately, and enable it in the Connection Profile using the EnableSSL property.

To use SSL X.509 (mutual TLS) authentication with a self-managed ClickHouse deployment, generate a client certificate and key, and create a matching ClickHouse user:

CREATE USER <user> IDENTIFIED WITH ssl_certificate CN '<host>:<user>';
-- or, to convert an existing user:
ALTER USER <user> IDENTIFIED WITH ssl_certificate CN '<host>:<user>';

The private key must be in PKCS#8 PEM format. In the Connection Profile, set Authentication Type to SslX509 and provide the Username, Client SSL Certificate, Client SSL Key, and CA Certificate. ClickHouse Writer performs mutual TLS: the client authenticates the server, and the server authenticates the client, using TLS v1.3 where the ClickHouse server supports it.

AWS PrivateLink lets Striim connect to a ClickHouse Cloud service over private networking instead of a public endpoint. This feature is available only on ClickHouse Cloud's Scale and Enterprise plans. Cross-region PrivateLink is also supported, across a broad set of AWS regions; AWS charges separately for cross-region data transfer. If PrivateLink is already configured for another service in your organization, ClickHouse Cloud can often reuse the same endpoint within a region (this is not guaranteed across different ClickHouse organizations).

ClickHouse Cloud issues a public JDBC URL such as jdbc:clickhouse://<cluster-id>.<region>.aws.clickhouse.cloud:8443. Rather than changing the Connection Profile to point at the PrivateLink-specific hostname, the recommended approach is to leave the Connection URL exactly as ClickHouse Cloud provided it, and let DNS resolve it privately from inside your VPC:

With this in place, anything inside the VPC that resolves the original public hostname is transparently redirected to the private endpoint, while the same hostname still resolves to ClickHouse's public IP addresses from outside the VPC. Your Connection Profile's Connection URL does not need to change.

From an EC2 instance inside the VPC where the private hosted zone is associated:

Configure the following properties in a ClickHouse connection profile: