Skip to content
View as markdown Ask ChatGPT Ask Claude

ClickHouse Connection Setup#

In this section you can see information on specific settings that must be set in your system to allow connection and operation with ClickHouse:

Item Description
ClickHouse Username The ClickHouse User name
ClickHouse Password The ClickHouse User password

Table: ClickHouse connection setup

See more information on Accounts and Security for specific information on accounts and security settings for ClickHouse.

Deployment#

Critical Manufacturing MES uses ClickHouse for data persistence. For the MES system to operate correctly, deploy ClickHouse-Server with the appropriate version matching the definitions listed in ClickHouse Requirements. You can use the provided ClickHouse templates available here: ClickHouse Templates ↗.

Warning

These templates are available for guidance purposes only. Critical Manufacturing recommends consulting an expert for proper ClickHouse setup.

Furthermore, at least one user must exist with the permissions listed in Accounts and Security.

If you are using mTLS as the authentication method, a secret is required in the namespace where ClickHouse will be installed. This secret should contain the following key-value pairs:

Key Value
root.crt (Cluster Certificate Authority)
client.crt (User Public Key)
client.key (User Private Key)

The name of this secret should be passed to values.yaml.

Warning

If you are using SASL Plain as the authentication method, it is necessary to pass the username and password to values.yaml instead of user certificates.

Fully managed ClickHouse service#

It is also possible to use a fully managed ClickHouse service such as ClickHouse Cloud. If this is the setup you wish to deploy in your system, please contact ClickHouse on ClickHouse Support ↗ for detailed instructions on setting up ClickHouse and integrating with Confluent Cloud.

Setting Up a Replicated Cluster#

If you want Data Platform to stay available after a ClickHouse server fails, you can use a replicated cluster. A replicated cluster keeps a copy of the data on more than one server.

This section only applies to external self-managed ClickHouse since ClickHouse Cloud already handles replication. For the requirements of each deployment option, see ClickHouse Requirements.

Preconditions#

  • Two or more ClickHouse servers, with the version listed in ClickHouse Requirements.
  • Three ClickHouse Keeper instances.
  • Network access between all ClickHouse servers and all ClickHouse Keeper instances.
  • A load balancer in front of the ClickHouse servers. This is not required, but it is strongly recommended.

Setup Steps#

  1. Install ClickHouse on each server.
  2. Install and start the three ClickHouse Keeper instances. For the configuration details, see the ClickHouse Keeper documentation ↗.
  3. Define a cluster in the ClickHouse server configuration, under remote_servers. List every server as a replica of the same shard, and give the cluster a name.
  4. Configure the macros on each ClickHouse server. A Replicated*MergeTree table uses the macros to identify the shard and the replica it belongs to.
  5. Restart the ClickHouse servers and the ClickHouse Keeper instances, so they read the new configuration.
  6. Confirm that the cluster appears in the system.clusters table.
  7. Configure the load balancer, as described in Load Balancer.
  8. Set the Cluster Name to the name of the cluster from step 3 when you create the Customer Environment, then install the MES.

The installation creates the Data Platform databases and tables across the cluster. When you install over an existing single-instance database, the installation also converts that database and its tables to the cluster.

Cluster Name maps to the CLICKHOUSE_CLUSTER_NAME environment variable. The Environment Manager, the HouseKeeper, and the Aggregation Engine read it. When the value is empty, the MES creates the tables on a single server and the data is not replicated.

Warning

The value of Cluster Name must match the cluster name in the ClickHouse server configuration. If the name does not match, the installation will fail.

Load Balancer#

Configure the load balancer for failover. A failover configuration defines a chain of priorities. All connections reach the primary replica, and another replica takes over only when the primary fails. This keeps the Data Platform available when a server fails, and keeps the data consistent, since every session reads from the replica that received the write.

If your load balancer cannot do failover, configure sticky sessions instead, so that one session stays on one replica. Failover remains the recommended configuration.

Consistency matters for the Aggregation Engine, which uses dbt to read back the data it has just written. A read served by a replica that has not caught up yet makes the aggregation fail. A failover configuration avoids this, because all connections reach the same replica.

Information

On ClickHouse Cloud, the MES handles the read-after-write consistency. The installation sets select_sequential_consistency to 1 for every user it creates.

Connection Settings#

Point the ClickHouse connection settings at the load balancer, not at a single replica. A connection that points to one replica fails when that replica goes down. Enter these settings in the General Data step of the Customer Environment.

If you do not use a load balancer, point the connection settings at the primary replica. If that replica goes down, you have to change the settings and restart before the MES connects again.

Cluster Name and the connection settings serve different purposes. The connection settings tell the MES where to send the queries. Cluster Name tells the MES which cluster to create the objects on.

Note

Adding a replica to a cluster that already runs is a separate procedure. ClickHouse Keeper does not enable replication on a new replica on its own, so the databases, tables, and views must be created on it before replication starts.