Open-source APMGolang ORMGolang HTTP routerBlog
Golang ClickHouse

ClickHouse Configuration Guide

How to configure ClickHouse?

ClickHouse stores configuration in 2 different config files:

  • /etc/clickhouse-server/config.xml contains server settings.
  • /etc/clickhouse-server/users.xml contains users configuration and server settings that can be overriden for each user.

You are not supposed to edit those files directly and instead you should store your configuration in separate files in /etc/clickhouse-server/config.d/ and /etc/clickhouse-server/users.d/ directories, for example:

<!-- /etc/clickhouse-server/config.d/my.xml -->
<?xml version="1.0"?>
<clickhouse>
  <listen_host>::</listen_host>
  <timezone>UTC</timezone>
</clickhouse>

Or for user settings:

<!-- /etc/clickhouse-server/users.d/my.xml -->
<?xml version="1.0"?>
<clickhouse>
  <profiles>
    <default>
      <prefer_column_name_to_alias>1</prefer_column_name_to_alias>
    </default>
  </profiles
</clickhouse>

Which config file to choose?

Choosing the right file for the setting can be confusing, but there is a trick. ClickHouse comes with the system.settings table that contains session settings for the current user, for example:

SELECT * FROM system.settings WHERE name = 'queue_max_wait_ms'

If the setting exists in the system.settings table, it should be put in the users.d directory. Otherwise, try the config.d directory.

User settings

users.xml and users.d contain users configuration and settings that can be overrided for each user. To override settings for detault user:

<!-- /etc/clickhouse-server/users.d/my.xml -->
<?xml version="1.0"?>
<clickhouse>
  <profiles>
    <default>
      <background_pool_size>16</background_pool_size>
      <prefer_column_name_to_alias>1</prefer_column_name_to_alias>
    </default>
  </profiles
</clickhouse>

To select settings with non-default values:

SELECT * FROM system.settings WHERE changed

Query settings

You can also change user settings on the query level by providing SETTINGS clause, for example:

SELECT value
FROM system.settings
WHERE name = 'prefer_column_name_to_alias'
SETTINGS prefer_column_name_to_alias = 0

┌─value─┐
│ 0     │
└───────┘

SELECT value
FROM system.settings
WHERE name = 'prefer_column_name_to_alias'
SETTINGS prefer_column_name_to_alias = 1

┌─value─┐
│ 1     │
└───────┘

See also

Last Updated: