ClickHouse Requirements#

System Requirements#

Table: Minimum and recommended system requirements for ClickHouse

Server-Level Settings#

The following settings must be configured at the ClickHouse server level before proceeding with user provisioning.

Table: Mandatory server-level ClickHouse settings

Databases#

As of MES version 11.0.0, the platform uses four ClickHouse databases. Their names are derived from the MES System Name — the example below assumes a system named MES:

Reserved Names#

MES deployment and runtime operations rely on a fixed naming convention for ClickHouse roles, settings profiles, and quotas. The following object names must not be used for any other purpose in the ClickHouse instance.

Table: ClickHouse reserved names

Where <SystemName> is the name of the MES, the same value that gives the databases their names, and <KEY> is one of the keys listed in Users, Roles, and ACLs.

The role attached to the Default User is not covered by this convention. The customer creates and names that role.

Users, Roles, and ACLs#

MES requires two categories of ClickHouse users:

• Installation User — Must already exist in the ClickHouse installation before setup. The Environment Manager uses it to connect and provision the remaining users. See:

  • Default User

• MES Users — Created automatically or manually, depending on the provisioning mode selected when creating the Customer Environment. Each MES user is paired with a role, and permissions are granted to the role rather than directly to the user. See:

  • Analytics (Read/Write) Admin
  • MES (Read/Write) User
  • Analytics (Read) User
  • Analytics (Read) / DWH (Read/Write) User
  • DWH (Read) User
  • DWH Playground (Read) User

For each MES user, the role must first be created and assigned to the user before the permissions in the sections below can be applied. From version 12.0.0, the name of the role is built from the name of the system and a fixed key, in the form <SystemName>_<KEY>_ROLE:

CREATE ROLE IF NOT EXISTS `<SystemName>_<KEY>_ROLE`;
GRANT `<SystemName>_<KEY>_ROLE` TO <user>;

Each MES user has its own key:

Table: ClickHouse role keys

On a system named MES, the role of the Analytics (Read) user is MES_CLICKHOUSE_ANALYTICS_READ_ROLE.

The permissions assigned to MES users follow the standard ClickHouse security model. For a detailed breakdown of each privilege, refer to ClickHouse SQL Reference: GRANT Permissions ↗.

Default User#

The Default User is the account that must already exist in the customer ClickHouse installation. This user is either created manually by the customer or corresponds to the default user provided by their ClickHouse setup.

The Environment Manager uses this account to connect to ClickHouse during environment creation and configuration. Therefore, the following administrative grants must be assigned to a role attached to this user to allow for the automatic provisioning of users required by Critical Manufacturing MES.

Table: Default User required permissions — all scoped to *.* (Global)

You can apply all the above permissions using the following SQL statement:

GRANT
    CREATE USER, ALTER USER, DROP USER,
    CREATE ROLE, ALTER ROLE, DROP ROLE, ROLE ADMIN,
    CREATE SETTINGS PROFILE, ALTER SETTINGS PROFILE, DROP SETTINGS PROFILE,
    CREATE QUOTA, ALTER QUOTA, DROP QUOTA,
    BACKUP, SHOW ACCESS
ON *.* TO <default_user>_role;

GRANT 
    CREATE DATABASE, DROP DATABASE, SHOW, SELECT, INSERT, ALTER,
    CREATE TABLE, DROP TABLE, UNDROP TABLE, CREATE TEMPORARY TABLE, 
    CREATE VIEW, DROP VIEW, CREATE DICTIONARY, DROP DICTIONARY, TRUNCATE, OPTIMIZE, 
    CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY, SHOW ROW POLICIES, DICTGET, CLUSTER
ON *.* TO <default_user>_role WITH GRANT OPTION;

Analytics (Read/Write) Admin#

Administrative user with extended privileges for managing all analytics databases and deployments.

• Databases: <SystemName>, <SystemName>ODS, <SystemName>CDM, <SystemName>DWH

• Required Permissions:

  GRANT
      CREATE DATABASE, DROP DATABASE, SHOW, SELECT,
      INSERT, ALTER, CREATE TABLE, DROP TABLE, UNDROP TABLE,
      CREATE TEMPORARY TABLE, CREATE VIEW, DROP VIEW,
      CREATE DICTIONARY, DROP DICTIONARY, TRUNCATE, OPTIMIZE,
      CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY, 
      SHOW ROW POLICIES, DICTGET, CLUSTER
  ON <SystemName>*.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READWRITE_ADMIN_ROLE`;
  

MES (Read/Write)#

User responsible for reading and writing core operational MES data.

• Databases: <SystemName>

• Required Permissions:

  GRANT
      SELECT, INSERT, ALTER, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, CLUSTER
  ON <SystemName>.* TO `<SystemName>_CLICKHOUSE_MES_READWRITE_ROLE`;
  

Analytics (Read)#

User with read-only access for general reporting and data consumption across all Analytics databases.

• Databases: <SystemName>, <SystemName>ODS, <SystemName>CDM, <SystemName>DWH, system

• Required Permissions:

  GRANT 
      SELECT ON <SystemName>*.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_ROLE`,
      SELECT(volume_name, policy_name) ON system.storage_policies,
      SELECT(value, name) ON system.build_options,
      CREATE TEMPORARY TABLE ON *.*
  TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_ROLE`;
  

Analytics (Read) / DWH (Read/Write)#

User responsible for processes that populate the DWH, with read-only access to source databases.

• Databases: <SystemName>CDM, <SystemName>DWH

• Required Permissions:

  GRANT SELECT ON <SystemName>CDM.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_DWH_WRITE_ROLE`;
  
  GRANT
      SHOW,
      SELECT, INSERT, ALTER, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY,
      DROP TABLE, DROP VIEW, DROP DICTIONARY, UNDROP TABLE, TRUNCATE, OPTIMIZE,
      CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY, SHOW ROW POLICIES, 
      DICTGET, CLUSTER
  ON <SystemName>DWH.* TO `<SystemName>_CLICKHOUSE_ANALYTICS_READ_DWH_WRITE_ROLE`;
  

DWH (Read)#

User with read-only access exclusively to the Data Warehouse.

• Databases: <SystemName>DWH

• Required Permissions:

  GRANT SELECT ON <SystemName>DWH.* TO `<SystemName>_CLICKHOUSE_DWH_READ_ROLE`;
  

DWH Playground (Read)#

User with read-only access to the Data Warehouse for Cube Explorer ad-hoc queries. Unlike other MES users that run controlled application-driven workloads, this user can be unpredictable in cost. Quotas are applied to protect the ClickHouse cluster from runaway queries.

• Databases: <SystemName>DWH

• Required Permissions:

  GRANT SELECT ON <SystemName>DWH.* TO `<SystemName>_CLICKHOUSE_DWH_READ_PLAYGROUND_ROLE`;
  

• Recommended Quotas:

  CREATE QUOTA OR REPLACE `<SystemName>_CLICKHOUSE_DWH_READ_PLAYGROUND_QUOTA`
      FOR INTERVAL 1 hour MAX execution_time = 900,
      FOR INTERVAL 1 hour MAX result_rows = 1000000
      TO `<SystemName>_CLICKHOUSE_DWH_READ_PLAYGROUND_ROLE`;
  

User-Level Settings Profiles#

Once users and roles are created, a settings profile must be applied to ensure correct MES operation. The following settings are mandatory for all MES ClickHouse users:

Table: Mandatory user-level ClickHouse settings

When using automatic provisioning, MES creates these profiles automatically. For manual provisioning, run the following statement once for each MES user role:

-- Run this once for each MES user role, replacing <KEY> with one of:
--   CLICKHOUSE_MES_READWRITE
--   CLICKHOUSE_ANALYTICS_READ
--   CLICKHOUSE_ANALYTICS_READWRITE_ADMIN
--   CLICKHOUSE_ANALYTICS_READ_DWH_WRITE
--   CLICKHOUSE_DWH_READ
--   CLICKHOUSE_DWH_READ_PLAYGROUND
CREATE SETTINGS PROFILE OR REPLACE `<SystemName>_<KEY>_PROFILE`
  SETTINGS
    compatibility = '26.3',
    do_not_merge_across_partitions_select_final = 0,
    http_max_field_value_size = 262144,
    max_query_size = 10000000,
    throw_on_max_partitions_per_insert_block = 0,
    transform_null_in = 1
  TO `<SystemName>_<KEY>_ROLE`;

Row Policies#

From version 12.0.0, the MES creates ClickHouse row policies to restrict the rows that a Data Platform query returns. Each policy is created with its table and applies to the <SystemName>_CLICKHOUSE_ANALYTICS_READ_ROLE role.

The privileges that support this behavior are already listed in the sections above:

• CREATE TEMPORARY TABLE ON *.* on the Analytics (Read) role. Data Platform creates a temporary table for each request to hold the Data Groups of the caller, and the row policy reads that table.
• CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY, and SHOW ROW POLICIES on the Analytics (Read/Write) Admin role and on the role attached to the Default User.

Do not remove these privileges. Without them, tables are created without a policy, and every Data Platform query returns every row.

High Availability#

Version 12.0 supports ClickHouse running as a replicated cluster. A replicated cluster keeps a copy of the data on more than one server, so that Data Platform stays available when a server fails.

The requirements depend on how you deploy ClickHouse.

Table: ClickHouse replication support per deployment option

ClickHouse Keeper#

A self-managed replicated cluster needs ClickHouse Keeper. Deploy three Keeper instances.

The Keeper needs a majority of instances to agree on the state of the data. Always use an odd number of instances. That is because with three instances, the cluster keeps working when one instance fails, because the other two still form a majority. With two instances, a single failure leaves no majority and the cluster stops.

Load Balancer#

A load balancer in front of the replicas is not required, but it is strongly recommended. The MES connects to the load balancer rather than to a single replica, so the connection stays up when a replica fails.

Without a load balancer, the MES connects to one replica directly. If that replica goes down, the MES loses connectivity, and it needs manual reconfiguration and a restart.

Configure the load balancer for failover. A failover configuration defines a chain of priorities, in which all connections reach the primary replica and another replica takes over only when the primary fails.

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

For the configuration steps, see ClickHouse Connection Setup.

Cluster Name#

On a self-managed cluster, the MES needs the name of the ClickHouse cluster. Set the Cluster Name when you create the Customer Environment.

The name must match the cluster defined in the ClickHouse server configuration. The Environment Manager, the HouseKeeper, and the Aggregation Engine use this name to create and change tables on the cluster. Without it, the MES creates the tables on a single server and the data is not replicated.

Table Engines#

The MES chooses the table engine based on the deployment:

• Self-managed cluster - tables use the Replicated*MergeTree engine family.
• Single instance - tables use the standard *MergeTree engine family.
• ClickHouse Cloud - tables use the standard *MergeTree engine family, and the service converts them to Shared*MergeTree tables.

Read-After-Write Consistency#

The Data Platform builds the analytics models with dbt, which needs to read the data it has just written. On a replicated cluster, a query that reaches a different replica may not see that data yet.

• Self-managed cluster - configure the load balancer for failover, as described in ClickHouse Connection Setup. All connections then reach the same replica, so a query always sees the data written before it.
• ClickHouse Cloud - MES sets select_sequential_consistency to 1 in the settings profile of each MES user. For more information, see User-Level Settings Profiles.

Migration of Existing Installations#

When you upgrade an existing environment to version 12.0 with a cluster name configured, the MES converts the databases and the tables that already exist to the cluster and to the Replicated*MergeTree engine family. You do not need to convert them yourself.

The same conversion runs when you restore a backup package, even when the restore does not include a version upgrade.