The user.xml file in ClickHouse manages user-level parameter settings, user information, and permissions. This topic describes how to modify the user.xml configuration file for an ApsaraDB for ClickHouse cluster.
Background information
An ApsaraDB for ClickHouse cluster has multiple parameters whose default values are configured in the config.xml and user.xml files. You can modify the parameters in these files to optimize cluster performance.
You can modify parameters in the config.xml file in the ApsaraDB for ClickHouse console and restart the cluster to apply the changes. For more information, see Configure config.xml parameters.
To modify parameters in the user.xml file, you must connect to the ApsaraDB for ClickHouse cluster using a client and run the required commands. These parameters cannot be modified using the clickhouse-client.
Prerequisites
The cluster is in the Running state.
Procedure
The method for modifying the user.xml file varies based on the cluster edition.
Community-compatible Edition
This section describes the syntax and provides examples of how to modify the user.xml file of a Community-compatible Edition cluster.
Syntax
SET GLOBAL ON cluster default <setting_name> = <setting_value>;Parameter description
For a list of supported parameters, see the ClickHouse parameter list.
If the value of setting_value is a Boolean value or a string, enclose it in single quotation marks (').
Examples
Example 1: Set the maximum number of rows in a single block when you load data from a table.
SET GLOBAL ON cluster default max_block_size = 10000;Example 2: Enable order-preserving parallel parsing for the data format.
SET GLOBAL ON cluster default input_format_parallel_parsing = 'True';View parameters
You can view the parameters that are configured in the user.xml file of the current cluster.
SELECT * FROM system.settings;Enterprise Edition
The parameter settings in the user.xml file for Enterprise Edition clusters are different from those for Community-compatible Edition clusters. Run the statements that meet your business requirements.
For more information about the values of setting_name, setting_value, and profile_name in the following syntax, see Core Settings | ClickHouse Docs and Restrictions on Query Complexity | ClickHouse Docs.
Set parameters that take effect temporarily
You can modify the parameter settings for the current logon session.
These parameter settings are not persistent in the cluster. The settings become invalid if you disconnect from the cluster and then reconnect.
Syntax
SET <setting_name> = <setting_value>;Example
Enable the live view feature for the current session.
SET allow_experimental_live_view = 1;Make parameters for a single account persistent
You can set parameters, account information, and permissions for a single account in the cluster.
You must use a privileged account or an account that has the ACCESS MANAGEMENT permission to run the following SQL statement.
The ALTER USER statement resets the user configuration.
These parameter settings are persistent in the cluster. However, the settings do not take effect in the current session. You must reconnect to the cluster for the settings to take effect.
Syntax
ALTER USER <user_name> SETTINGS <setting_name> = <setting_value>;Example
Limit the memory usage for the user Nancy to 4 GB.
ALTER USER Nancy SETTINGS max_memory_usage_for_user = 4294967296;Make parameters for all accounts persistent
You can set parameters, user information, and permissions for all accounts in the cluster.
You must use a privileged account or an account that has the ACCESS MANAGEMENT permission to run the following SQL statement.
These parameter settings are persistent in the cluster. However, the settings do not take effect in the current session. You must reconnect to the cluster for the settings to take effect.
Syntax
CREATE SETTINGS PROFILE profile_name SETTINGS <setting_name> = <setting_value> TO ALL;Example
Limit the query execution time for all users to 10 seconds.
CREATE SETTINGS PROFILE max_execution_time_profile SETTINGS max_execution_time = 10 TO ALL; Check whether the parameter settings are persistent
Syntax
SHOW SETTINGS LIKE '%<settings_name>%';Example
Check whether the allow_experimental_live_view parameter setting is persistent.
SHOW SETTINGS LIKE '%allow_experimental_live_view%';