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 and settings profiles. For every MES user, the following object names are derived from the user name and must not be used for any other purpose in the ClickHouse instance.

Where <user> is the ClickHouse user name (for example, any of the MES users described below).

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 of the same name plus a _role suffix; 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:

CREATE ROLE `<user>_role`;
GRANT `<user>_role` TO `<user>`;

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
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: <instance>, <instance>ODS, <instance>CDM, <instance>DWH

• Required Permissions:

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

MES (Read/Write)#

User responsible for reading and writing core operational MES data.

• Databases: <instance>

• Required Permissions:

  GRANT
      SELECT, INSERT, ALTER, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY
  ON `<instance>`.* TO `<mes_rw_user>_role`;
  

Analytics (Read)#

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

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

• Required Permissions:

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

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

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

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

• Required Permissions:

  GRANT SELECT ON `<instance>CDM`.* TO `<analytics_read_dwh_rw_user>_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
  ON `<instance>DWH`.* TO `<analytics_read_dwh_rw_user>_role`;
  

DWH (Read)#

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

• Databases: <instance>DWH

• Required Permissions:

  GRANT SELECT ON `<instance>DWH`.* TO `<dwh_read_user>_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: <instance>DWH

• Required Permissions:

  GRANT SELECT ON `<instance>DWH`.* TO `<dwh_playground_read_user>_role`;
  

• Recommended Quotas:

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

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, replacing <user> with one of:
--   <analytics_rw_admin_user>
--   <mes_rw_user>
--   <analytics_read_user>
--   <analytics_read_dwh_rw_user>
--   <dwh_read_user>
--   <dwh_playground_read_user>
CREATE SETTINGS PROFILE OR REPLACE `<user>_profile`
  SETTINGS
    compatibility = '25.3',
    do_not_merge_across_partitions_select_final = 0,
    max_query_size = 10000000,
    throw_on_max_partitions_per_insert_block = 0,
    transform_null_in = 1
  TO `<user>_role`;