Enabling the Read-Only Mode for ClickHouse Tables

Scenarios

During operations such as data migration, one-click balancing, decommissioning, and scaling-in, it is essential to set the table to read-only mode to prevent unintended write operations. ClickHouse offers a table-level parameter only_allow_select_statement, which can be configured for MergeTree-series table engines. This parameter restricts operations such as ALTER, RENAME, DROP, and INSERT, allowing only SELECT operations to be executed.

Notes and Constraints

This section applies only to MRS 3.2.0-LTS or later.

Procedure for Enabling the Read-Only Mode of the ClickHouse Table

1. Install the client. For details, see Installing a Client.
2. Run the following commands to log in to the node where the client is installed as user root:

   Navigate to the client installation directory.

   cd Client installation directory

   Configure environment variables.

   source bigdata_env

3. Run the following command to authenticate the user if the cluster is in security mode (with Kerberos authentication enabled). Otherwise, skip this step.

   kinit Component service user

   

   The user must have the ClickHouse administrator permissions.

4. Run the proper client command to connect to the ClickHouse server.
   • Normal mode

     clickhouse client --host IP address of the ClickHouse instance --user Username --password --port 9440 --secure

     Enter the user password.

   • Security mode

     clickhouse client --host IP address of the ClickHouse instance --port 9440 --secure

   

   • MRS 3.2.0-LTS and earlier: For a cluster in normal mode, the default user is used. Alternatively, you can create an administrator using the open source capability provided by the ClickHouse community. Do not use the users created on FusionInsight Manager.
   • MRS 3.3.0-LTS and later: For a cluster in normal mode, you can use the users created on FusionInsight Manager.
   • To obtain the IP address of the ClickHouseServer instance, log in to FusionInsight Manager, choose Cluster > Services > ClickHouse, and click the Instances tab.

5. Run the following statement to set the table to read-only:

   ALTER TABLE {table_name} MODIFY SETTING only_allow_select_statement = true;

Disabling the Read-Only Mode of the Table

1. Log in to the ClickHouse client by referring to 2 to 4.
2. Run the following statement to disable the read-only mode of the table:

   ALTER TABLE {table_name} MODIFY SETTING only_allow_select_statement = false settings hw_internal_operation = true;