Configure user settings

Elena Dvoryadkina

In ADQM/ClickHouse, there are two main groups of settings:

  • Server-level settings — parameters that are specified in the config.xml configuration file and applied globally for the ClickHouse server. These settings cannot be changed at the user, session, or query level. You can find the list of these parameters and their values in the system.server_settings system table.

  • User-level settings — parameters that can be configured for a particular user, session, or individual query, depending on a use case and how long the specified values ​​should remain relevant. Available parameters with their values are listed in the system.settings table. The ways to configure these settings are described below.

Configure settings at the user account level

To specify values of parameters ​​that will be applied when a user logs in, use the SETTINGS clause in the CREATE USER query when creating a new user or ALTER USER when modifying an existing user:

CREATE USER <user_name> ... SETTINGS <parameter_name> = <value>, ...;

You can also specify parameter values in a settings profile and then assign it to a user by running the CREATE USER or ALTER USER query:

CREATE SETTINGS PROFILE <profile_name> SETTINGS <parameter_name> = <value>, ...;
CREATE USER <user_name> SETTINGS PROFILE <profile_name>;

The settings profile of the default ADQM user can be managed through the ADCM interface — use the default_profile_settings parameter in the Default user and policy settings section on the configuration page of the ADQMDB service to edit the list of parameters for the default user.

Manage settings for the default ADQM user via ADCM
Manage settings for the default ADQM user via ADCM

After you have set necessary parameters, do not forget to click Save and execute the Reconfig and restart action for the ADQMDB service to save configuration changes and restart the service.

Configure settings at the session level

Use the SET operator to set a parameter value for the current session (this value will temporarily replace the value set at the user account level):

SET <parameter_name> = <value>;

The SET query also allows applying all values ​​from the specified settings profile:

SET profile = '<profile_name>';

Session-level settings can be useful for ad-hoc testing or when you want certain parameter values ​​to be in effect for queries within a single session, but not longer.

Configure settings for a single query

To configure settings for an individual query, list required parameters with their values after the SETTINGS clause:

SELECT <column_list> FROM <table_name> SETTINGS <parameter_name> = <value>, ...;

The specified settings will be applied only during this query execution — after it is completed, the parameters will use the values ​​specified at the user/session level or the default values.

Default values of parameters

To view which parameters have been changed for a user/session and are different from the default settings, run the query:

SELECT name, value FROM system.settings WHERE changed;

To reset a parameter to its default value, you can use the command:

SET <parameter_name> = DEFAULT;

Examples

Log in to the clickhouse-client console client as the default user. See the max_columns_to_read parameter value:

SELECT name, value FROM system.settings WHERE name = 'max_columns_to_read';
   ┌─name────────────────┬─value─┐
1. │ max_columns_to_read │ 0     │
   └─────────────────────┴───────┘

Create a new user with a custom value for the max_columns_to_read parameter specified via the SETTINGS clause, for example:

CREATE USER test_user IDENTIFIED WITH sha256_password BY 'qwerty123' SETTINGS max_columns_to_read = 20;

Log in to clickhouse-client as the test_user user and run the following command:

SELECT name, value FROM system.settings WHERE changed;

The max_columns_to_read parameter appears in the list of modified settings with the value set for the current user:

    ┌─name───────────────────────────────┬─value───────┐
 1. │ max_columns_to_read                │ 20          │
      ...
    └────────────────────────────────────┴─────────────┘

Change the parameter value at the session level:

SET max_columns_to_read = 25;

Check the parameter value again:

SELECT name, value FROM system.settings WHERE changed;
    ┌─name───────────────────────────────┬─value───────┐
 1. │ max_columns_to_read                │ 25          │
      ...
    └────────────────────────────────────┴─────────────┘

If you log out of the console client (end the session) and log in back as test_user, you can make sure that the max_columns_to_read parameter is set to 20 again (the value assigned at the user account level).

Found a mistake? Seleсt text and press Ctrl+Enter to report it