query_* server settings

These settings configure the ClickHouse server and are autogenerated from ClickHouse source.

query_cache

Query cache configuration. The following settings are available:

Setting	Description	Default Value
`max_entries`	The maximum number of `SELECT` query results stored in the cache.	`1024`
`max_entry_size_in_bytes`	The maximum size in bytes `SELECT` query results may have to be saved in the cache.	`1048576`
`max_entry_size_in_rows`	The maximum number of rows `SELECT` query results may have to be saved in the cache.	`30000000`
`max_size_in_bytes`	The maximum cache size in bytes. `0` means the query cache is disabled.	`1073741824`

Changed settings take effect immediately.
Data for the query cache is allocated in DRAM. If memory is scarce, make sure to set a small value for max_size_in_bytes or disable the query cache altogether.

Example

<query_cache>
    <max_size_in_bytes>1073741824</max_size_in_bytes>
    <max_entries>1024</max_entries>
    <max_entry_size_in_bytes>1048576</max_entry_size_in_bytes>
    <max_entry_size_in_rows>30000000</max_entry_size_in_rows>
</query_cache>

query_log

Setting for logging queries received with the log_queries=1 setting. Queries are logged in the system.query_log table, not in a separate file. You can change the name of the table in the table parameter (see below). The following settings can be configured by sub-tags:

Setting	Description	Default	Note
`database`	Name of the database.
`table`	Name of the system table.
`engine`	MergeTree Engine Definition for a system table.		Cannot be used if `partition_by` or `order_by` defined. If not specified `MergeTree` is selected by default
`partition_by`	Custom partitioning key for a system table.		If `engine` is specified for system table, `partition_by` parameter should be specified directly inside ‘engine’
`ttl`	Specifies the table TTL.		If `engine` is specified for system table, `ttl` parameter should be specified directly inside ‘engine’
`order_by`	Custom sorting key for a system table. Can’t be used if `engine` defined.		If `engine` is specified for system table, `order_by` parameter should be specified directly inside ‘engine’
`storage_policy`	Name of the storage policy to use for the table (optional).		If `engine` is specified for system table, `storage_policy` parameter should be specified directly inside ‘engine’
`settings`	Additional parameters that control the behavior of the MergeTree (optional).		If `engine` is specified for system table, `settings` parameter should be specified directly inside ‘engine’
`flush_interval_milliseconds`	Interval for flushing data from the buffer in memory to the table.	`7500`
`max_size_rows`	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	`1048576`
`reserved_size_rows`	Pre-allocated memory size in lines for the logs.	`8192`
`buffer_size_rows_flush_threshold`	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	`max_size_rows / 2`
`flush_on_crash`	Sets whether logs should be dumped to the disk in case of a crash.	`false`

Additionally, the following server-level setting controls the default flush policy for all system log tables:

<default_system_log_flush_policy>
    <skip_alias_columns>true</skip_alias_columns>
</default_system_log_flush_policy>

Setting	Description	Default
`skip_alias_columns`	When `true`, ALIAS columns are omitted from system log table schemas. Required for S3-backed system logs.	`false`

The enable_user_query_log parameter (enabled by default) creates system.user_query_log, which shows every user their own records of the configured query log table without requiring access to the table itself. This only works when the query log is stored locally: if engine delegates to another server (for example, Distributed), disable enable_user_query_log, since system.user_query_log cannot enforce the per-user access check across a ClickHouse-protocol server boundary and refuses to read from such a backend. If the table does not exist, ClickHouse will create it. If the structure of the query log changed when the ClickHouse server was updated, the table with the old structure is renamed, and a new table is created automatically. Example

<query_log>
    <database>system</database>
    <table>query_log</table>
    <engine>Engine = MergeTree PARTITION BY event_date ORDER BY event_time TTL event_date + INTERVAL 30 day</engine>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1048576</max_size_rows>
    <reserved_size_rows>8192</reserved_size_rows>
    <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
    <flush_on_crash>false</flush_on_crash>
    <enable_user_query_log>true</enable_user_query_log>
</query_log>

query_masking_rules

Regexp-based rules, which will be applied to queries as well as all log messages before storing them in server logs, system.query_log, system.text_log, system.processes tables, and in logs sent to the client. That allows preventing sensitive data leakage from SQL queries such as names, emails, personal identifiers or credit card numbers to logs. Example

<query_masking_rules>
    <rule>
        <name>hide SSN</name>
        <regexp>(^|\D)\d{3}-\d{2}-\d{4}($|\D)</regexp>
        <replace>000-00-0000</replace>
    </rule>
</query_masking_rules>

Config fields:

Setting	Description
`name`	name for the rule (optional)
`regexp`	RE2 compatible regular expression (mandatory)
`replace`	substitution string for sensitive data (optional, by default - six asterisks)

The masking rules are applied to the whole query (to prevent leaks of sensitive data from malformed / non-parseable queries). The system.events table has counter QueryMaskingRulesMatch which has an overall number of query masking rules matches. For distributed queries each server has to be configured separately, otherwise, subqueries passed to other nodes will be stored without masking.

query_metric_log

It is disabled by default. Enabling To manually turn on metrics history collection system.query_metric_log, create /etc/clickhouse-server/config.d/query_metric_log.xml with the following content:

<clickhouse>
    <query_metric_log>
        <database>system</database>
        <table>query_metric_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
        <collect_interval_milliseconds>1000</collect_interval_milliseconds>
        <max_size_rows>1048576</max_size_rows>
        <reserved_size_rows>8192</reserved_size_rows>
        <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
        <flush_on_crash>false</flush_on_crash>
    </query_metric_log>
</clickhouse>

Disabling To disable query_metric_log setting, you should create the following file /etc/clickhouse-server/config.d/disable_query_metric_log.xml with the following content:

<clickhouse>
    <query_metric_log remove="1" />
</clickhouse>

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
`database`	Name of the database.
`table`	Name of the system table.
`engine`	MergeTree Engine Definition for a system table.		Cannot be used if `partition_by` or `order_by` defined. If not specified `MergeTree` is selected by default
`partition_by`	Custom partitioning key for a system table.		If `engine` is specified for system table, `partition_by` parameter should be specified directly inside ‘engine’
`ttl`	Specifies the table TTL.		If `engine` is specified for system table, `ttl` parameter should be specified directly inside ‘engine’
`order_by`	Custom sorting key for a system table. Can’t be used if `engine` defined.		If `engine` is specified for system table, `order_by` parameter should be specified directly inside ‘engine’
`storage_policy`	Name of the storage policy to use for the table (optional).		If `engine` is specified for system table, `storage_policy` parameter should be specified directly inside ‘engine’
`settings`	Additional parameters that control the behavior of the MergeTree (optional).		If `engine` is specified for system table, `settings` parameter should be specified directly inside ‘engine’
`flush_interval_milliseconds`	Interval for flushing data from the buffer in memory to the table.	`7500`
`max_size_rows`	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	`1048576`
`reserved_size_rows`	Pre-allocated memory size in lines for the logs.	`8192`
`buffer_size_rows_flush_threshold`	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	`max_size_rows / 2`
`flush_on_crash`	Sets whether logs should be dumped to the disk in case of a crash.	`false`

Additionally, the following server-level setting controls the default flush policy for all system log tables:

<default_system_log_flush_policy>
    <skip_alias_columns>true</skip_alias_columns>
</default_system_log_flush_policy>

Setting	Description	Default
`skip_alias_columns`	When `true`, ALIAS columns are omitted from system log table schemas. Required for S3-backed system logs.	`false`

query_thread_log

Setting for logging threads of queries received with the log_query_threads=1 setting. Queries are logged in the system.query_thread_log table, not in a separate file. You can change the name of the table in the table parameter (see below). The following settings can be configured by sub-tags:

Setting	Description	Default	Note
`database`	Name of the database.
`table`	Name of the system table.
`engine`	MergeTree Engine Definition for a system table.		Cannot be used if `partition_by` or `order_by` defined. If not specified `MergeTree` is selected by default
`partition_by`	Custom partitioning key for a system table.		If `engine` is specified for system table, `partition_by` parameter should be specified directly inside ‘engine’
`ttl`	Specifies the table TTL.		If `engine` is specified for system table, `ttl` parameter should be specified directly inside ‘engine’
`order_by`	Custom sorting key for a system table. Can’t be used if `engine` defined.		If `engine` is specified for system table, `order_by` parameter should be specified directly inside ‘engine’
`storage_policy`	Name of the storage policy to use for the table (optional).		If `engine` is specified for system table, `storage_policy` parameter should be specified directly inside ‘engine’
`settings`	Additional parameters that control the behavior of the MergeTree (optional).		If `engine` is specified for system table, `settings` parameter should be specified directly inside ‘engine’
`flush_interval_milliseconds`	Interval for flushing data from the buffer in memory to the table.	`7500`
`max_size_rows`	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	`1048576`
`reserved_size_rows`	Pre-allocated memory size in lines for the logs.	`8192`
`buffer_size_rows_flush_threshold`	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	`max_size_rows / 2`
`flush_on_crash`	Sets whether logs should be dumped to the disk in case of a crash.	`false`

Additionally, the following server-level setting controls the default flush policy for all system log tables:

<default_system_log_flush_policy>
    <skip_alias_columns>true</skip_alias_columns>
</default_system_log_flush_policy>

Setting	Description	Default
`skip_alias_columns`	When `true`, ALIAS columns are omitted from system log table schemas. Required for S3-backed system logs.	`false`

If the table does not exist, ClickHouse will create it. If the structure of the query thread log changed when the ClickHouse server was updated, the table with the old structure is renamed, and a new table is created automatically. Example

<query_thread_log>
    <database>system</database>
    <table>query_thread_log</table>
    <partition_by>toMonday(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1048576</max_size_rows>
    <reserved_size_rows>8192</reserved_size_rows>
    <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
    <flush_on_crash>false</flush_on_crash>
</query_thread_log>

query_views_log

Setting for logging views (live, materialized etc) dependant of queries received with the log_query_views=1 setting. Queries are logged in the system.query_views_log table, not in a separate file. You can change the name of the table in the table parameter (see below). The following settings can be configured by sub-tags:

Setting	Description	Default	Note
`database`	Name of the database.
`table`	Name of the system table.
`engine`	MergeTree Engine Definition for a system table.		Cannot be used if `partition_by` or `order_by` defined. If not specified `MergeTree` is selected by default
`partition_by`	Custom partitioning key for a system table.		If `engine` is specified for system table, `partition_by` parameter should be specified directly inside ‘engine’
`ttl`	Specifies the table TTL.		If `engine` is specified for system table, `ttl` parameter should be specified directly inside ‘engine’
`order_by`	Custom sorting key for a system table. Can’t be used if `engine` defined.		If `engine` is specified for system table, `order_by` parameter should be specified directly inside ‘engine’
`storage_policy`	Name of the storage policy to use for the table (optional).		If `engine` is specified for system table, `storage_policy` parameter should be specified directly inside ‘engine’
`settings`	Additional parameters that control the behavior of the MergeTree (optional).		If `engine` is specified for system table, `settings` parameter should be specified directly inside ‘engine’
`flush_interval_milliseconds`	Interval for flushing data from the buffer in memory to the table.	`7500`
`max_size_rows`	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	`1048576`
`reserved_size_rows`	Pre-allocated memory size in lines for the logs.	`8192`
`buffer_size_rows_flush_threshold`	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	`max_size_rows / 2`
`flush_on_crash`	Sets whether logs should be dumped to the disk in case of a crash.	`false`

Additionally, the following server-level setting controls the default flush policy for all system log tables:

<default_system_log_flush_policy>
    <skip_alias_columns>true</skip_alias_columns>
</default_system_log_flush_policy>

Setting	Description	Default
`skip_alias_columns`	When `true`, ALIAS columns are omitted from system log table schemas. Required for S3-backed system logs.	`false`

If the table does not exist, ClickHouse will create it. If the structure of the query views log changed when the ClickHouse server was updated, the table with the old structure is renamed, and a new table is created automatically. Example

<query_views_log>
    <database>system</database>
    <table>query_views_log</table>
    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1048576</max_size_rows>
    <reserved_size_rows>8192</reserved_size_rows>
    <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
    <flush_on_crash>false</flush_on_crash>
</query_views_log>

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

query_cache

query_log

query_masking_rules

query_metric_log

query_thread_log

query_views_log

​query_cache

​query_log

​query_masking_rules

​query_metric_log

​query_thread_log

​query_views_log

query_cache

query_log

query_masking_rules

query_metric_log

query_thread_log

query_views_log