All Products
Search

This Product

    ApsaraDB for ClickHouse:Configure parameters in the user.xml file

    Document Center

    ApsaraDB for ClickHouse:Configure parameters in the user.xml file

    Last Updated:Jul 04, 2024

    The user.xml file in ApsaraDB for ClickHouse is used to manage the settings of user parameters, user information, and permissions. You can configure user parameters for an ApsaraDB for ClickHouse cluster by modifying the user.xml file. This topic describes how to change the values of the parameters in the user.xml file of an ApsaraDB for ClickHouse cluster.

    Background information

    Multiple parameters can be configured for an ApsaraDB for ClickHouse cluster. The default parameter values are set in the config.xml file and the user.xml file. You can improve the performance of an ApsaraDB for ClickHouse cluster by changing the parameter values in the configuration files.

    • You can change the parameter values of the config.xml file in the ApsaraDB for ClickHouse console and restart the ApsaraDB for ClickHouse cluster to make the modifications take effect. For more information, see Configure parameters in the config.xml file.

    • You can change the parameter values of the user.xml file by using a client to connect to the ApsaraDB for ClickHouse cluster and running the relevant commands. You cannot change the parameter values of the user.xml file by using clickhouse-client.

    Prerequisites

    Your cluster is in the Running state.

    Procedure

    The procedure for modifying the user.xml file varies based on the cluster edition.

    Community-compatible Edition

    The following section describes the syntax and examples of modifying the user.xml file of a Community-compatible Edition cluster.

    Syntax

    SET GLOBAL ON cluster default <setting_name> = <setting_value>;

    Parameter description

    • For more information about the parameters that are supported by ApsaraDB for ClickHouse, see the Core Settings topic of the open source ClickHouse documentation.

    • If the value of the setting_value parameter is a Boolean value or a string, the value must be enclosed by using single quotation marks (').

    Examples

    Example 1: Set the maximum number of rows included in a single block when data is loaded from a table. 

    SET GLOBAL ON cluster default max_block_size = 10000;

    Example 2: Enable order-preserving parallel parsing of data formats. 

    SET GLOBAL ON cluster default input_format_parallel_parsing = 'True';

    View parameters

    You can execute the following statement to view the parameters configured in the user.xml file of the current cluster: 

    SELECT * FROM system.settings;

    Enterprise Edition

    The parameter settings of the users.xml file for Enterprise Edition clusters are different from those for Community-compatible Edition clusters. You can execute the corresponding statements based on your requirements.

    Important

    For more information about the values of the setting_name, setting_value, and profile_name parameters in the following syntax, see the Core Settings and Restrictions on Query Complexity topics of the open source ClickHouse documentation.

    Configure parameters that temporarily take effect

    You can modify the parameter settings of the current session.

    Note

    In this case, the parameter settings for a cluster are temporarily effective. If the cluster is disconnected and then reconnected, the parameter settings become invalid.

    Syntax

    SET <setting_name> = <setting_value>;

    Example

    Enable the live view feature for the current session. 

    SET allow_experimental_live_view = 1;

    Configure parameters that permanently take effect for an account

    You can configure parameters, user information, and permissions for an account that is created in a cluster.

    Note

    • You must use a privileged account or an account that has the ACCESS MANAGEMENT permission to execute the following SQL statement.

    • In this case, the parameter settings for a cluster are permanently effective. However, after you modify the parameter settings, you must reconnect the cluster to make the modifications take effect in sessions.

    Syntax

    ALTER USER user_name SETTINGS <setting_name> = <setting_value>;

    Example

    Set the maximum memory usage of the account whose username is Nancy to 4 GB. 

    ALTER USER Nancy SETTINGS max_memory_usage_for_user = 4294967296;

    Configure parameters that permanently take effect for all accounts

    You can configure parameters, user information, and permissions for all accounts that are created in a cluster.

    Note

    • You must use a privileged account or an account that has the ACCESS MANAGEMENT permission to execute the following SQL statement.

    • In this case, the parameter settings for a cluster are permanently effective. However, after you modify the parameter settings, you must reconnect the cluster to make the modifications take effect in sessions.

    Syntax

    CREATE SETTINGS PROFILE profile_name SETTINGS <setting_name> = <setting_value> TO ALL;

    Example

    Set the maximum query execution time of all accounts to 10 seconds. 

    CREATE SETTINGS PROFILE max_execution_time_profile SETTINGS max_execution_time = 10 TO ALL;

    Check whether the specified parameters are permanently effective

    Syntax

    SHOW SETTINGS LIKE '%<settings_name>%';

    Example

    Check whether the specified parameter allow_experimental_live_view is permanently effective. 

    SHOW SETTINGS LIKE '%allow_experimental_live_view%';

    References