Session Settings
All below settings are also available in table system.settings. These settings are autogenerated from source.
add_http_cors_header
Type | Default value |
---|---|
Type Bool | Default value 0 |
additional_result_filter
An additional filter expression to apply to the result of SELECT
query.
This setting is not applied to any subquery.
Example
additional_table_filters
Type | Default value |
---|---|
Type Map | Default value {} |
Example
aggregate_functions_null_for_empty
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — Disabled.
- 1 — Enabled.
Example
Consider the following query with aggregate functions:
With aggregate_functions_null_for_empty = 0
it would produce:
With aggregate_functions_null_for_empty = 1
the result would be:
aggregation_in_order_max_block_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 50000000 |
aggregation_memory_efficient_merge_threads
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
allow_aggregate_partitions_independently
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_archive_path_syntax
Type | Default value |
---|---|
Type Bool | Default value 1 |
File/S3 engines/table function will parse paths with '::' as <archive> :: <file>\
if archive has correct extensio
allow_asynchronous_read_from_io_pool_for_merge_tree
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_changing_replica_until_first_data_packet
Type | Default value |
---|---|
Type Bool | Default value 0 |
receive_data_timeout
timeout), otherwise we disable changing replica after the first time we made progress.
allow_create_index_without_type
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_custom_error_code_in_throwif
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_ddl
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_deprecated_database_ordinary
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_deprecated_error_prone_window_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow usage of deprecated error prone window functions (neighbor, runningAccumulate, runningDifferenceStartingWithFirstValue, runningDifference)
allow_deprecated_snowflake_conversion_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
Functions snowflakeToDateTime
, snowflakeToDateTime64
, dateTimeToSnowflake
, and dateTime64ToSnowflake
are deprecated and disabled by default.
Please use functions snowflakeIDToDateTime
, snowflakeIDToDateTime64
, dateTimeToSnowflakeID
, and dateTime64ToSnowflakeID
instead.
To re-enable the deprecated functions (e.g., during a transition period), please set this setting to true
.
allow_deprecated_syntax_for_merge_tree
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_distributed_ddl
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_drop_detached
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_execute_multiif_columnar
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_experimental_analyzer
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allow new query analyzer.
allow_experimental_codecs
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_database_glue_catalog
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow experimental database engine DataLakeCatalog with catalog_type = 'glue'
allow_experimental_database_iceberg
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow experimental database engine DataLakeCatalog with catalog_type = 'iceberg'
allow_experimental_database_materialized_postgresql
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_database_unity_catalog
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow experimental database engine DataLakeCatalog with catalog_type = 'unity'
allow_experimental_dynamic_type
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allows creation of Dynamic data type.
allow_experimental_full_text_index
Type | Default value |
---|---|
Type Bool | Default value 0 |
If it is set to true, allow to use experimental full-text index.
allow_experimental_funnel_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_hash_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_inverted_index
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_join_condition
Type | Default value |
---|---|
Type Bool | Default value 0 |
Support join with inequal conditions which involve columns from both left and right table. e.g. t1.y < t2.y
.
allow_experimental_join_right_table_sorting
Type | Default value |
---|---|
Type Bool | Default value 0 |
If it is set to true, and the conditions of join_to_sort_minimum_perkey_rows
and join_to_sort_maximum_table_rows
are met, rerange the right table by key to improve the performance in left or inner hash join.
allow_experimental_json_type
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allows creation of JSON data type.
allow_experimental_kafka_offsets_storage_in_keeper
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow experimental feature to store Kafka related offsets in ClickHouse Keeper. When enabled a ClickHouse Keeper path and replica name can be specified to the Kafka table engine. As a result instead of the regular Kafka engine, a new type of storage engine will be used that stores the committed offsets primarily in ClickHouse Keeper
allow_experimental_kusto_dialect
Type | Default value |
---|---|
Type Bool | Default value 0 |
Enable Kusto Query Language (KQL) - an alternative to SQL.
allow_experimental_live_view
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — Working with live views is disabled.
- 1 — Working with live views is enabled.
allow_experimental_materialized_postgresql_table
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_nlp_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_object_type
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_parallel_reading_from_replicas
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_parallel_replicas
the number of replicas from each shard for SELECT query execution. Reading is parallelized and coordinated dynamically. 0 - disabled, 1 - enabled, silently disable them in case of failure, 2 - enabled, throw an exception in case of failure
allow_experimental_prql_dialect
Type | Default value |
---|---|
Type Bool | Default value 0 |
Enable PRQL - an alternative to SQL.
allow_experimental_query_deduplication
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_experimental_statistics
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows defining columns with statistics and manipulate statistics.
allow_experimental_time_series_table
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows creation of tables with the TimeSeries table engine.
Possible values:
- 0 — the TimeSeries table engine is disabled.
- 1 — the TimeSeries table engine is enabled.
allow_experimental_ts_to_grid_aggregate_function
Type | Default value |
---|---|
Type Bool | Default value 0 |
Experimental tsToGrid aggregate function for Prometheus-like timeseries resampling. Cloud only
allow_experimental_variant_type
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allows creation of Variant data type.
allow_experimental_vector_similarity_index
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow experimental vector similarity index
allow_experimental_window_view
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_general_join_planning
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allows a more general join planning algorithm that can handle more complex conditions, but only works with hash join. If hash join is not enabled, then the usual join planning algorithm is used regardless of the value of this setting.
allow_get_client_http_header
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow to use the function getClientHTTPHeader
which lets to obtain a value of an the current HTTP request's header. It is not enabled by default for security reasons, because some headers, such as Cookie
, could contain sensitive info. Note that the X-ClickHouse-*
and Authentication
headers are always restricted and cannot be obtained with this function.
allow_hyperscan
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_introspection_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 1 — Introspection functions enabled.
- 0 — Introspection functions disabled.
See Also
- Sampling Query Profiler
- System table trace_log
allow_materialized_view_with_bad_select
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow CREATE MATERIALIZED VIEW with SELECT query that references nonexistent tables or columns. It must still be syntactically valid. Doesn't apply to refreshable MVs. Doesn't apply if the MV schema needs to be inferred from the SELECT query (i.e. if the CREATE has no column list and no TO table). Can be used for creating MV before its source table.
allow_named_collection_override_by_default
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_non_metadata_alters
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_nonconst_timezone_arguments
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow non-const timezone arguments in certain time-related functions like toTimeZone(), fromUnixTimestamp*(), snowflakeToDateTime*()
allow_nondeterministic_mutations
Type | Default value |
---|---|
Type Bool | Default value 0 |
dictGet
.
Given that, for example, dictionaries, can be out of sync across nodes, mutations that pull values from them are disallowed on replicated tables by default. Enabling this setting allows this behavior, making it the user's responsibility to ensure that the data used is in sync across all nodes.
Example
allow_nondeterministic_optimize_skip_unused_shards
Type | Default value |
---|---|
Type Bool | Default value 0 |
rand
or dictGet
, since later has some caveats with updates) functions in sharding key.
Possible values:
- 0 — Disallowed.
- 1 — Allowed.
allow_not_comparable_types_in_comparison_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows or restricts using not comparable types (like JSON/Object/AggregateFunction) in comparison functions equal/less/greater/etc
.
allow_not_comparable_types_in_order_by
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows or restricts using not comparable types (like JSON/Object/AggregateFunction) in ORDER BY keys.
allow_prefetched_read_pool_for_local_filesystem
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_prefetched_read_pool_for_remote_filesystem
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_push_predicate_ast_for_distributed_subqueries
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allows push predicate on AST level for distributed subqueries with enabled anlyzer
allow_push_predicate_when_subquery_contains_with
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_reorder_prewhere_conditions
Type | Default value |
---|---|
Type Bool | Default value 1 |
When moving conditions from WHERE to PREWHERE, allow reordering them to optimize filtering
allow_settings_after_format_in_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
Control whether SETTINGS
after FORMAT
in INSERT
queries is allowed or not. It is not recommended to use this, since this may interpret part of SETTINGS
as values.
Example:
But the following query will work only with allow_settings_after_format_in_insert
:
Possible values:
- 0 — Disallow.
- 1 — Allow.
Use this setting only for backward compatibility if your use cases depend on old syntax.
allow_simdjson
Type | Default value |
---|---|
Type Bool | Default value 1 |
allow_statistics_optimize
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows using statistics to optimize queries
allow_suspicious_codecs
Type | Default value |
---|---|
Type Bool | Default value 0 |
If it is set to true, allow to specify meaningless compression codecs.
allow_suspicious_fixed_string_types
Type | Default value |
---|---|
Type Bool | Default value 0 |
allow_suspicious_indices
Type | Default value |
---|---|
Type Bool | Default value 0 |
Reject primary/secondary indexes and sorting keys with identical expressions
allow_suspicious_low_cardinality_types
Type | Default value |
---|---|
Type Bool | Default value 0 |
FixedString(8_bytes_or_less)
.
For small fixed values using of LowCardinality
is usually inefficient, because ClickHouse stores a numeric index for each row. As a result:
- Disk space usage can rise.
- RAM consumption can be higher, depending on a dictionary size.
- Some functions can work slower due to extra coding/encoding operations.
Merge times in MergeTree-engine tables can grow due to all the reasons described above.
Possible values:
- 1 — Usage of
LowCardinality
is not restricted. - 0 — Usage of
LowCardinality
is restricted.
allow_suspicious_primary_key
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow suspicious PRIMARY KEY
/ORDER BY
for MergeTree (i.e. SimpleAggregateFunction).
allow_suspicious_ttl_expressions
Type | Default value |
---|---|
Type Bool | Default value 0 |
Reject TTL expressions that don't depend on any of table's columns. It indicates a user error most of the time.
allow_suspicious_types_in_group_by
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows or restricts using Variant and Dynamic types in GROUP BY keys.
allow_suspicious_types_in_order_by
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows or restricts using Variant and Dynamic types in ORDER BY keys.
allow_suspicious_variant_types
Type | Default value |
---|---|
Type Bool | Default value 0 |
In CREATE TABLE statement allows specifying Variant type with similar variant types (for example, with different numeric or date types). Enabling this setting may introduce some ambiguity when working with values with similar types.
allow_unrestricted_reads_from_keeper
Type | Default value |
---|---|
Type Bool | Default value 0 |
alter_move_to_space_execute_async
Type | Default value |
---|---|
Type Bool | Default value 0 |
alter_partition_verbose_result
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — disable verbosity.
- 1 — enable verbosity.
Example
alter_sync
Type | Default value |
---|---|
Type UInt64 | Default value 1 |
Possible values:
- 0 — Do not wait.
- 1 — Wait for own execution.
- 2 — Wait for everyone.
Cloud default value: 0
.
alter_sync
is applicable to Replicated
tables only, it does nothing to alters of not Replicated
tables.
analyze_index_with_space_filling_curves
Type | Default value |
---|---|
Type Bool | Default value 1 |
ORDER BY mortonEncode(x, y)
or ORDER BY hilbertEncode(x, y)
, and the query has conditions on its arguments, e.g. x >= 10 AND x <= 20 AND y >= 20 AND y <= 30
, use the space-filling curve for index analysis.
analyzer_compatibility_join_using_top_level_identifier
Type | Default value |
---|---|
Type Bool | Default value 0 |
Force to resolve identifier in JOIN USING from projection (for example, in SELECT a + 1 AS b FROM t1 JOIN t2 USING (b)
join will be performed by t1.a + 1 = t2.b
, rather then t1.b = t2.b
).
any_join_distinct_right_table_keys
Type | Default value |
---|---|
Type Bool | Default value 0 |
Enables legacy ClickHouse server behaviour in ANY INNER|LEFT JOIN
operations.
Use this setting only for backward compatibility if your use cases depend on legacy JOIN
behaviour.
When the legacy behaviour is enabled:
- Results of
t1 ANY LEFT JOIN t2
andt2 ANY RIGHT JOIN t1
operations are not equal because ClickHouse uses the logic with many-to-one left-to-right table keys mapping. - Results of
ANY INNER JOIN
operations contain all rows from the left table like theSEMI LEFT JOIN
operations do.
When the legacy behaviour is disabled:
- Results of
t1 ANY LEFT JOIN t2
andt2 ANY RIGHT JOIN t1
operations are equal because ClickHouse uses the logic which provides one-to-many keys mapping inANY RIGHT JOIN
operations. - Results of
ANY INNER JOIN
operations contain one row per key from both the left and right tables.
Possible values:
- 0 — Legacy behaviour is disabled.
- 1 — Legacy behaviour is enabled.
See also:
apply_deleted_mask
Type | Default value |
---|---|
Type Bool | Default value 1 |
apply_mutations_on_fly
Type | Default value |
---|---|
Type Bool | Default value 0 |
apply_settings_from_server
Type | Default value |
---|---|
Type Bool | Default value 1 |
Whether the client should accept settings from server.
This only affects operations performed on the client side, in particular parsing the INSERT input data and formatting the query result. Most of query execution happens on the server and is not affected by this setting.
Normally this setting should be set in user profile (users.xml or queries like ALTER USER
), not through the client (client command line arguments, SET
query, or SETTINGS
section of SELECT
query). Through the client it can be changed to false, but can't be changed to true (because the server won't send the settings if user profile has apply_settings_from_server = false
).
Note that initially (24.12) there was a server setting (send_settings_to_client
), but latter it got replaced with this client setting, for better usability.
asterisk_include_alias_columns
Type | Default value |
---|---|
Type Bool | Default value 0 |
SELECT *
).
Possible values:
- 0 - disabled
- 1 - enabled
asterisk_include_materialized_columns
Type | Default value |
---|---|
Type Bool | Default value 0 |
SELECT *
).
Possible values:
- 0 - disabled
- 1 - enabled
async_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
async_insert_busy_timeout_decrease_rate
Type | Default value |
---|---|
Type Double | Default value 0.2 |
The exponential growth rate at which the adaptive asynchronous insert timeout decreases
async_insert_busy_timeout_increase_rate
Type | Default value |
---|---|
Type Double | Default value 0.2 |
The exponential growth rate at which the adaptive asynchronous insert timeout increases
async_insert_busy_timeout_max_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 200 |
Maximum time to wait before dumping collected data per query since the first data appeared.
async_insert_busy_timeout_min_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 50 |
If auto-adjusting is enabled through async_insert_use_adaptive_busy_timeout, minimum time to wait before dumping collected data per query since the first data appeared. It also serves as the initial value for the adaptive algorithm
async_insert_deduplicate
Type | Default value |
---|---|
Type Bool | Default value 0 |
async_insert_max_data_size
Type | Default value |
---|---|
Type UInt64 | Default value 10485760 |
Maximum size in bytes of unparsed data collected per query before being inserted
async_insert_max_query_number
Type | Default value |
---|---|
Type UInt64 | Default value 450 |
async_insert_poll_timeout_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 10 |
Timeout for polling data from asynchronous insert queue
async_insert_use_adaptive_busy_timeout
Type | Default value |
---|---|
Type Bool | Default value 1 |
If it is set to true, use adaptive busy timeout for asynchronous inserts
async_query_sending_for_remote
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables asynchronous connection creation and query sending while executing remote query.
Enabled by default.
async_socket_for_remote
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables asynchronous read from socket while executing remote query.
Enabled by default.
azure_allow_parallel_part_upload
Type | Default value |
---|---|
Type Bool | Default value 1 |
Use multiple threads for azure multipart upload.
azure_check_objects_after_upload
Type | Default value |
---|---|
Type Bool | Default value 0 |
Check each uploaded object in azure blob storage to be sure that upload was successful
azure_create_new_file_on_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
azure_ignore_file_doesnt_exist
Type | Default value |
---|---|
Type Bool | Default value 0 |
Ignore absence of file if it does not exist when reading certain keys.
Possible values:
- 1 —
SELECT
returns empty result. - 0 —
SELECT
throws an exception.
azure_list_object_keys_size
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
azure_max_blocks_in_multipart_upload
Type | Default value |
---|---|
Type UInt64 | Default value 50000 |
Maximum number of blocks in multipart upload for Azure.
azure_max_inflight_parts_for_one_file
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
The maximum number of a concurrent loaded parts in multipart upload request. 0 means unlimited.
azure_max_single_part_copy_size
Type | Default value |
---|---|
Type UInt64 | Default value 268435456 |
The maximum size of object to copy using single part copy to Azure blob storage.
azure_max_single_part_upload_size
Type | Default value |
---|---|
Type UInt64 | Default value 104857600 |
azure_max_single_read_retries
Type | Default value |
---|---|
Type UInt64 | Default value 4 |
azure_max_unexpected_write_error_retries
Type | Default value |
---|---|
Type UInt64 | Default value 4 |
The maximum number of retries in case of unexpected errors during Azure blob storage write
azure_max_upload_part_size
Type | Default value |
---|---|
Type UInt64 | Default value 5368709120 |
The maximum size of part to upload during multipart upload to Azure blob storage.
azure_min_upload_part_size
Type | Default value |
---|---|
Type UInt64 | Default value 16777216 |
The minimum size of part to upload during multipart upload to Azure blob storage.
azure_sdk_max_retries
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
Maximum number of retries in azure sdk
azure_sdk_retry_initial_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
Minimal backoff between retries in azure sdk
azure_sdk_retry_max_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Maximal backoff between retries in azure sdk
azure_skip_empty_files
Type | Default value |
---|---|
Type Bool | Default value 0 |
Enables or disables skipping empty files in S3 engine.
Possible values:
- 0 —
SELECT
throws an exception if empty file is not compatible with requested format. - 1 —
SELECT
returns empty result for empty file.
azure_strict_upload_part_size
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
The exact size of part to upload during multipart upload to Azure blob storage.
azure_throw_on_zero_files_match
Type | Default value |
---|---|
Type Bool | Default value 0 |
Throw an error if matched zero files according to glob expansion rules.
Possible values:
- 1 —
SELECT
throws an exception. - 0 —
SELECT
returns empty result.
azure_truncate_on_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
azure_upload_part_size_multiply_factor
Type | Default value |
---|---|
Type UInt64 | Default value 2 |
Multiply azure_min_upload_part_size by this factor each time azure_multiply_parts_count_threshold parts were uploaded from a single write to Azure blob storage.
azure_upload_part_size_multiply_parts_count_threshold
Type | Default value |
---|---|
Type UInt64 | Default value 500 |
Each time this number of parts was uploaded to Azure blob storage, azure_min_upload_part_size is multiplied by azure_upload_part_size_multiply_factor.
backup_restore_batch_size_for_keeper_multi
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
backup_restore_batch_size_for_keeper_multiread
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
backup_restore_failure_after_host_disconnected_for_seconds
Type | Default value |
---|---|
Type UInt64 | Default value 3600 |
If a host during a BACKUP ON CLUSTER or RESTORE ON CLUSTER operation doesn't recreate its ephemeral 'alive' node in ZooKeeper for this amount of time then the whole backup or restore is considered as failed. This value should be bigger than any reasonable time for a host to reconnect to ZooKeeper after a failure. Zero means unlimited.
backup_restore_finish_timeout_after_error_sec
Type | Default value |
---|---|
Type UInt64 | Default value 180 |
How long the initiator should wait for other host to react to the 'error' node and stop their work on the current BACKUP ON CLUSTER or RESTORE ON CLUSTER operation.
backup_restore_keeper_fault_injection_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
backup_restore_keeper_fault_injection_seed
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
backup_restore_keeper_max_retries
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Max retries for [Zoo]Keeper operations in the middle of a BACKUP or RESTORE operation. Should be big enough so the whole operation won't fail because of a temporary [Zoo]Keeper failure.
backup_restore_keeper_max_retries_while_handling_error
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
Max retries for [Zoo]Keeper operations while handling an error of a BACKUP ON CLUSTER or RESTORE ON CLUSTER operation.
backup_restore_keeper_max_retries_while_initializing
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
Max retries for [Zoo]Keeper operations during the initialization of a BACKUP ON CLUSTER or RESTORE ON CLUSTER operation.
backup_restore_keeper_retry_initial_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
backup_restore_keeper_retry_max_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 5000 |
backup_restore_keeper_value_max_size
Type | Default value |
---|---|
Type UInt64 | Default value 1048576 |
backup_restore_s3_retry_attempts
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Setting for Aws::Client::RetryStrategy, Aws::Client does retries itself, 0 means no retries. It takes place only for backup/restore.
cache_warmer_threads
Type | Default value |
---|---|
Type UInt64 | Default value 4 |
calculate_text_stack_trace
Type | Default value |
---|---|
Type Bool | Default value 1 |
cancel_http_readonly_queries_on_client_close
Type | Default value |
---|---|
Type Bool | Default value 0 |
Cloud default value: 1
.
cast_ipv4_ipv6_default_on_conversion_error
Type | Default value |
---|---|
Type Bool | Default value 0 |
CAST operator into IPv4, CAST operator into IPV6 type, toIPv4, toIPv6 functions will return default value instead of throwing exception on conversion error.
cast_keep_nullable
Type | Default value |
---|---|
Type Bool | Default value 0 |
Nullable
data type in CAST operations.
When the setting is enabled and the argument of CAST
function is Nullable
, the result is also transformed to Nullable
type. When the setting is disabled, the result always has the destination type exactly.
Possible values:
- 0 — The
CAST
result has exactly the destination type specified. - 1 — If the argument type is
Nullable
, theCAST
result is transformed toNullable(DestinationDataType)
.
Examples
The following query results in the destination data type exactly:
Result:
The following query results in the Nullable
modification on the destination data type:
Result:
See Also
- CAST functio
cast_string_to_dynamic_use_inference
Type | Default value |
---|---|
Type Bool | Default value 0 |
Use types inference during String to Dynamic conversio
cast_string_to_variant_use_inference
Type | Default value |
---|---|
Type Bool | Default value 1 |
Use types inference during String to Variant conversion.
check_query_single_value_result
Type | Default value |
---|---|
Type Bool | Default value 1 |
MergeTree
family engines .
Possible values:
- 0 — the query shows a check status for every individual data part of a table.
- 1 — the query shows the general table check status.
check_referential_table_dependencies
Type | Default value |
---|---|
Type Bool | Default value 0 |
check_table_dependencies
Type | Default value |
---|---|
Type Bool | Default value 1 |
checksum_on_read
Type | Default value |
---|---|
Type Bool | Default value 1 |
cloud_mode
Type | Default value |
---|---|
Type Bool | Default value 0 |
cloud_mode_database_engine
Type | Default value |
---|---|
Type UInt64 | Default value 1 |
The database engine allowed in Cloud. 1 - rewrite DDLs to use Replicated database, 2 - rewrite DDLs to use Shared database
cloud_mode_engine
Type | Default value |
---|---|
Type UInt64 | Default value 1 |
- 0 - allow everything
- 1 - rewrite DDLs to use *ReplicatedMergeTree
- 2 - rewrite DDLs to use SharedMergeTree
- 3 - rewrite DDLs to use SharedMergeTree except when explicitly passed remote disk is specified
UInt64 to minimize public part
cluster_for_parallel_replicas
Cluster for a shard in which current server is located
collect_hash_table_stats_during_aggregation
Type | Default value |
---|---|
Type Bool | Default value 1 |
collect_hash_table_stats_during_joins
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enable collecting hash table statistics to optimize memory allocatio
compatibility
The compatibility
setting causes ClickHouse to use the default settings of a previous version of ClickHouse, where the previous version is provided as the setting.
If settings are set to non-default values, then those settings are honored (only settings that have not been modified are affected by the compatibility
setting).
This setting takes a ClickHouse version number as a string, like 22.3
, 22.8
. An empty value means that this setting is disabled.
Disabled by default.
In ClickHouse Cloud the compatibility setting must be set by ClickHouse Cloud support. Please open a case to have it set.
compatibility_ignore_auto_increment_in_create_table
Type | Default value |
---|---|
Type Bool | Default value 0 |
compatibility_ignore_collation_in_create_table
Type | Default value |
---|---|
Type Bool | Default value 1 |
compile_aggregate_expressions
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 0 — Aggregation is done without JIT compilation.
- 1 — Aggregation is done using JIT compilation.
See Also
compile_expressions
Type | Default value |
---|---|
Type Bool | Default value 0 |
compile_sort_description
Type | Default value |
---|---|
Type Bool | Default value 1 |
connect_timeout
Type | Default value |
---|---|
Type Seconds | Default value 10 |
connect_timeout_with_failover_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 1000 |
The timeout in milliseconds for connecting to a remote server for a Distributed table engine, if the 'shard' and 'replica' sections are used in the cluster definition. If unsuccessful, several attempts are made to connect to various replicas.
connect_timeout_with_failover_secure_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 1000 |
Connection timeout for selecting first healthy replica (for secure connections).
connection_pool_max_wait_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 0 |
Possible values:
- Positive integer.
- 0 — Infinite timeout.
connections_with_failover_max_tries
Type | Default value |
---|---|
Type UInt64 | Default value 3 |
convert_query_to_cnf
Type | Default value |
---|---|
Type Bool | Default value 0 |
true
, a SELECT
query will be converted to conjuctive normal form (CNF). There are scenarios where rewriting a query in CNF may execute faster (view this Github issue for an explanation).
For example, notice how the following SELECT
query is not modified (the default behavior):
The result is:
Let's set convert_query_to_cnf
to true
and see what changes:
Notice the WHERE
clause is rewritten in CNF, but the result set is the identical - the Boolean logic is unchanged:
Possible values: true, false
count_distinct_implementation
Type | Default value |
---|---|
Type String | Default value uniqExact |
uniq*
functions should be used to perform the COUNT(DISTINCT ...) construction.
Possible values:
count_distinct_optimization
Type | Default value |
---|---|
Type Bool | Default value 0 |
create_if_not_exists
Type | Default value |
---|---|
Type Bool | Default value 0 |
Enable IF NOT EXISTS
for CREATE
statement by default. If either this setting or IF NOT EXISTS
is specified and a table with the provided name already exists, no exception will be thrown.
create_index_ignore_unique
Type | Default value |
---|---|
Type Bool | Default value 0 |
create_replicated_merge_tree_fault_injection_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
create_table_empty_primary_key_by_default
Type | Default value |
---|---|
Type Bool | Default value 0 |
cross_join_min_bytes_to_compress
Type | Default value |
---|---|
Type UInt64 | Default value 1073741824 |
Minimal size of block to compress in CROSS JOIN. Zero value means - disable this threshold. This block is compressed when any of the two thresholds (by rows or by bytes) are reached.
cross_join_min_rows_to_compress
Type | Default value |
---|---|
Type UInt64 | Default value 10000000 |
Minimal count of rows to compress block in CROSS JOIN. Zero value means - disable this threshold. This block is compressed when any of the two thresholds (by rows or by bytes) are reached.
data_type_default_nullable
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 1 — The data types in column definitions are set to
Nullable
by default. - 0 — The data types in column definitions are set to not
Nullable
by default.
database_atomic_wait_for_drop_and_detach_synchronously
Type | Default value |
---|---|
Type Bool | Default value 0 |
SYNC
to all DROP
and DETACH
queries.
Possible values:
- 0 — Queries will be executed with delay.
- 1 — Queries will be executed without delay.
database_replicated_allow_explicit_uuid
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
0 - Don't allow to explicitly specify UUIDs for tables in Replicated databases. 1 - Allow. 2 - Allow, but ignore the specified UUID and generate a random one instead.
database_replicated_allow_heavy_create
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow long-running DDL queries (CREATE AS SELECT and POPULATE) in Replicated database engine. Note that it can block DDL queue for a long time.
database_replicated_allow_only_replicated_engine
Type | Default value |
---|---|
Type Bool | Default value 0 |
database_replicated_allow_replicated_engine_arguments
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
0 - Don't allow to explicitly specify ZooKeeper path and replica name for *MergeTree tables in Replicated databases. 1 - Allow. 2 - Allow, but ignore the specified path and use default one instead. 3 - Allow and don't log a warning.
database_replicated_always_detach_permanently
Type | Default value |
---|---|
Type Bool | Default value 0 |
database_replicated_enforce_synchronous_settings
Type | Default value |
---|---|
Type Bool | Default value 0 |
database_replicated_initial_query_timeout_sec
Type | Default value |
---|---|
Type UInt64 | Default value 300 |
Possible values:
- Positive integer.
- 0 — Unlimited.
decimal_check_overflow
Type | Default value |
---|---|
Type Bool | Default value 1 |
deduplicate_blocks_in_dependent_materialized_views
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
0 — Disabled. 1 — Enabled.
Usage
By default, deduplication is not performed for materialized views but is done upstream, in the source table.
If an INSERTed block is skipped due to deduplication in the source table, there will be no insertion into attached materialized views. This behaviour exists to enable the insertion of highly aggregated data into materialized views, for cases where inserted blocks are the same after materialized view aggregation but derived from different INSERTs into the source table.
At the same time, this behaviour "breaks" INSERT
idempotency. If an INSERT
into the main table was successful and INSERT
into a materialized view failed (e.g. because of communication failure with ClickHouse Keeper) a client will get an error and can retry the operation. However, the materialized view won't receive the second insert because it will be discarded by deduplication in the main (source) table. The setting deduplicate_blocks_in_dependent_materialized_views
allows for changing this behaviour. On retry, a materialized view will receive the repeat insert and will perform a deduplication check by itself,
ignoring check result for the source table, and will insert rows lost because of the first failure.
default_materialized_view_sql_security
Type | Default value |
---|---|
Type SQLSecurityType | Default value DEFINER |
Allows to set a default value for SQL SECURITY option when creating a materialized view. More about SQL security.
The default value is DEFINER
.
default_max_bytes_in_join
Type | Default value |
---|---|
Type UInt64 | Default value 1000000000 |
max_bytes_in_join
is not set.
default_normal_view_sql_security
Type | Default value |
---|---|
Type SQLSecurityType | Default value INVOKER |
Allows to set default SQL SECURITY
option while creating a normal view. More about SQL security.
The default value is INVOKER
.
default_table_engine
Type | Default value |
---|---|
Type DefaultTableEngine | Default value MergeTree |
Default table engine to use when ENGINE
is not set in a CREATE
statement.
Possible values:
- a string representing any valid table engine name
Cloud default value: SharedMergeTree
.
Example
Query:
Result:
In this example, any new table that does not specify an Engine
will use the Log
table engine:
Query:
Result:
default_temporary_table_engine
Type | Default value |
---|---|
Type DefaultTableEngine | Default value Memory |
In this example, any new temporary table that does not specify an Engine
will use the Log
table engine:
Query:
Result:
default_view_definer
Type | Default value |
---|---|
Type String | Default value CURRENT_USER |
Allows to set default DEFINER
option while creating a view. More about SQL security.
The default value is CURRENT_USER
.
describe_compact_output
Type | Default value |
---|---|
Type Bool | Default value 0 |
describe_extend_object_types
Type | Default value |
---|---|
Type Bool | Default value 0 |
describe_include_subcolumns
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — Subcolumns are not included in
DESCRIBE
queries. - 1 — Subcolumns are included in
DESCRIBE
queries.
Example
See an example for the DESCRIBE statement.
describe_include_virtual_columns
Type | Default value |
---|---|
Type Bool | Default value 0 |
dialect
Type | Default value |
---|---|
Type Dialect | Default value clickhouse |
dictionary_validate_primary_key_type
Type | Default value |
---|---|
Type Bool | Default value 0 |
Validate primary key type for dictionaries. By default id type for simple layouts will be implicitly converted to UInt64.
distinct_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
Possible values:
throw
: throw an exception (default).break
: stop executing the query and return the partial result, as if the source data ran out.
distributed_aggregation_memory_efficient
Type | Default value |
---|---|
Type Bool | Default value 1 |
distributed_background_insert_batch
Type | Default value |
---|---|
Type Bool | Default value 0 |
When batch sending is enabled, the Distributed table engine tries to send multiple files of inserted data in one operation instead of sending them separately. Batch sending improves cluster performance by better-utilizing server and network resources.
Possible values:
- 1 — Enabled.
- 0 — Disabled.
distributed_background_insert_max_sleep_time_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 30000 |
Possible values:
- A positive integer number of milliseconds.
distributed_background_insert_sleep_time_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 100 |
Possible values:
- A positive integer number of milliseconds.
distributed_background_insert_split_batch_on_failure
Type | Default value |
---|---|
Type Bool | Default value 0 |
Sometimes sending particular batch to the remote shard may fail, because of some complex pipeline after (i.e. MATERIALIZED VIEW
with GROUP BY
) due to Memory limit exceeded
or similar errors. In this case, retrying will not help (and this will stuck distributed sends for the table) but sending files from that batch one by one may succeed INSERT.
So installing this setting to 1
will disable batching for such batches (i.e. temporary disables distributed_background_insert_batch
for failed batches).
Possible values:
- 1 — Enabled.
- 0 — Disabled.
This setting also affects broken batches (that may appears because of abnormal server (machine) termination and no fsync_after_insert
/fsync_directories
for Distributed table engine).
You should not rely on automatic batch splitting, since this may hurt performance.
distributed_background_insert_timeout
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
distributed_cache_bypass_connection_pool
Type | Default value |
---|---|
Type Bool | Default value 0 |
Only has an effect in ClickHouse Cloud. Allow to bypass distributed cache connection pool
distributed_cache_connect_max_tries
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
Only has an effect in ClickHouse Cloud. Number of tries to connect to distributed cache if unsuccessful
distributed_cache_data_packet_ack_window
Type | Default value |
---|---|
Type UInt64 | Default value 5 |
Only has an effect in ClickHouse Cloud. A window for sending ACK for DataPacket sequence in a single distributed cache read request
distributed_cache_discard_connection_if_unread_data
Type | Default value |
---|---|
Type Bool | Default value 1 |
Only has an effect in ClickHouse Cloud. Discard connection if some data is unread.
distributed_cache_fetch_metrics_only_from_current_az
Type | Default value |
---|---|
Type Bool | Default value 1 |
Only has an effect in ClickHouse Cloud. Fetch metrics only from current availability zone in system.distributed_cache_metrics, system.distributed_cache_events
distributed_cache_log_mode
Type | Default value |
---|---|
Type DistributedCacheLogMode | Default value on_error |
Only has an effect in ClickHouse Cloud. Mode for writing to system.distributed_cache_log
distributed_cache_max_unacked_inflight_packets
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
Only has an effect in ClickHouse Cloud. A maximum number of unacknowledged in-flight packets in a single distributed cache read request
distributed_cache_min_bytes_for_seek
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Only has an effect in ClickHouse Cloud. Minimum number of bytes to do seek in distributed cache.
distributed_cache_pool_behaviour_on_limit
Type | Default value |
---|---|
Type DistributedCachePoolBehaviourOnLimit | Default value wait |
Only has an effect in ClickHouse Cloud. Identifies behaviour of distributed cache connection on pool limit reached
distributed_cache_read_alignment
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Only has an effect in ClickHouse Cloud. A setting for testing purposes, do not change it
distributed_cache_read_request_max_tries
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
Only has an effect in ClickHouse Cloud. Number of tries to do distributed cache request if unsuccessful
distributed_cache_receive_response_wait_milliseconds
Type | Default value |
---|---|
Type UInt64 | Default value 60000 |
Only has an effect in ClickHouse Cloud. Wait time in milliseconds to receive data for request from distributed cache
distributed_cache_receive_timeout_milliseconds
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
Only has an effect in ClickHouse Cloud. Wait time in milliseconds to receive any kind of response from distributed cache
distributed_cache_throw_on_error
Type | Default value |
---|---|
Type Bool | Default value 0 |
Only has an effect in ClickHouse Cloud. Rethrow exception happened during communication with distributed cache or exception received from distributed cache. Otherwise fallback to skipping distributed cache on error
distributed_cache_wait_connection_from_pool_milliseconds
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
Only has an effect in ClickHouse Cloud. Wait time in milliseconds to receive connection from connection pool if distributed_cache_pool_behaviour_on_limit is wait
distributed_connections_pool_size
Type | Default value |
---|---|
Type UInt64 | Default value 1024 |
distributed_ddl_entry_format_version
Type | Default value |
---|---|
Type UInt64 | Default value 5 |
distributed_ddl_output_mode
Type | Default value |
---|---|
Type DistributedDDLOutputMode | Default value throw |
Possible values:
throw
— Returns result set with query execution status for all hosts where query is finished. If query has failed on some hosts, then it will rethrow the first exception. If query is not finished yet on some hosts and distributed_ddl_task_timeout exceeded, then it throwsTIMEOUT_EXCEEDED
exception.none
— Is similar to throw, but distributed DDL query returns no result set.null_status_on_timeout
— ReturnsNULL
as execution status in some rows of result set instead of throwingTIMEOUT_EXCEEDED
if query is not finished on the corresponding hosts.never_throw
— Do not throwTIMEOUT_EXCEEDED
and do not rethrow exceptions if query has failed on some hosts.none_only_active
- similar tonone
, but doesn't wait for inactive replicas of theReplicated
database. Note: with this mode it's impossible to figure out that the query was not executed on some replica and will be executed in background.null_status_on_timeout_only_active
— similar tonull_status_on_timeout
, but doesn't wait for inactive replicas of theReplicated
databasethrow_only_active
— similar tothrow
, but doesn't wait for inactive replicas of theReplicated
database
Cloud default value: none
.
distributed_ddl_task_timeout
Type | Default value |
---|---|
Type Int64 | Default value 180 |
Possible values:
- Positive integer.
- 0 — Async mode.
- Negative integer — infinite timeout.
distributed_foreground_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
By default, when inserting data into a Distributed
table, the ClickHouse server sends data to cluster nodes in background mode. When distributed_foreground_insert=1
, the data is processed synchronously, and the INSERT
operation succeeds only after all the data is saved on all shards (at least one replica for each shard if internal_replication
is true).
Possible values:
- 0 — Data is inserted in background mode.
- 1 — Data is inserted in synchronous mode.
Cloud default value: 1
.
See Also
distributed_group_by_no_merge
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
0
— Disabled (final query processing is done on the initiator node).1
- Do not merge aggregation states from different servers for distributed query processing (query completely processed on the shard, initiator only proxy the data), can be used in case it is for certain that there are different keys on different shards.2
- Same as1
but appliesORDER BY
andLIMIT
(it is not possible when the query processed completely on the remote node, like fordistributed_group_by_no_merge=1
) on the initiator (can be used for queries withORDER BY
and/orLIMIT
).
Example
distributed_insert_skip_read_only_replicas
Type | Default value |
---|---|
Type Bool | Default value 0 |
Enables skipping read-only replicas for INSERT queries into Distributed.
Possible values:
- 0 — INSERT was as usual, if it will go to read-only replica it will fail
- 1 — Initiator will skip read-only replicas before sending data to shards.
distributed_product_mode
Type | Default value |
---|---|
Type DistributedProductMode | Default value deny |
ClickHouse applies this setting when the query contains the product of distributed tables, i.e. when the query for a distributed table contains a non-GLOBAL subquery for the distributed table.
Restrictions:
- Only applied for IN and JOIN subqueries.
- Only if the FROM section uses a distributed table containing more than one shard.
- If the subquery concerns a distributed table containing more than one shard.
- Not used for a table-valued remote function.
Possible values:
deny
— Default value. Prohibits using these types of subqueries (returns the "Double-distributed in/JOIN subqueries is denied" exception).local
— Replaces the database and table in the subquery with local ones for the destination server (shard), leaving the normalIN
/JOIN.
global
— Replaces theIN
/JOIN
query withGLOBAL IN
/GLOBAL JOIN.
allow
— Allows the use of these types of subqueries.
distributed_push_down_limit
Type | Default value |
---|---|
Type UInt64 | Default value 1 |
This will allow to avoid:
- Sending extra rows over network;
- Processing rows behind the limit on the initiator.
Starting from 21.9 version you cannot get inaccurate results anymore, since distributed_push_down_limit
changes query execution only if at least one of the conditions met:
- distributed_group_by_no_merge > 0.
- Query does not have
GROUP BY
/DISTINCT
/LIMIT BY
, but it hasORDER BY
/LIMIT
. - Query has
GROUP BY
/DISTINCT
/LIMIT BY
withORDER BY
/LIMIT
and:- optimize_skip_unused_shards is enabled.
- optimize_distributed_group_by_sharding_key is enabled.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
See also:
- distributed_group_by_no_merge
- optimize_skip_unused_shards
- optimize_distributed_group_by_sharding_key
distributed_replica_error_cap
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
- Default value: 1000
The error count of each replica is capped at this value, preventing a single replica from accumulating too many errors.
See also:
- load_balancing
- Table engine Distributed
- distributed_replica_error_half_life
- distributed_replica_max_ignored_errors
distributed_replica_error_half_life
Type | Default value |
---|---|
Type Seconds | Default value 60 |
- Default value: 60 seconds
Controls how fast errors in distributed tables are zeroed. If a replica is unavailable for some time, accumulates 5 errors, and distributed_replica_error_half_life is set to 1 second, then the replica is considered normal 3 seconds after the last error.
See also:
- load_balancing
- Table engine Distributed
- distributed_replica_error_cap
- distributed_replica_max_ignored_errors
distributed_replica_max_ignored_errors
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
- Default value: 0
The number of errors that will be ignored while choosing replicas (according to load_balancing
algorithm).
See also:
- load_balancing
- Table engine Distributed
- distributed_replica_error_cap
- distributed_replica_error_half_life
do_not_merge_across_partitions_select_final
Type | Default value |
---|---|
Type Bool | Default value 0 |
empty_result_for_aggregation_by_constant_keys_on_empty_set
Type | Default value |
---|---|
Type Bool | Default value 1 |
empty_result_for_aggregation_by_empty_set
Type | Default value |
---|---|
Type Bool | Default value 0 |
enable_adaptive_memory_spill_scheduler
Type | Default value |
---|---|
Type Bool | Default value 0 |
Trigger processor to spill data into external storage adpatively. grace join is supported at present.
enable_blob_storage_log
Type | Default value |
---|---|
Type Bool | Default value 1 |
Write information about blob storage operations to system.blob_storage_log table
enable_deflate_qpl_codec
Type | Default value |
---|---|
Type Bool | Default value 0 |
enable_early_constant_folding
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_extended_results_for_datetime_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
Date32
with extended range (compared to typeDate
) for functions toStartOfYear, toStartOfISOYear, toStartOfQuarter, toStartOfMonth, toLastDayOfMonth, toStartOfWeek, toLastDayOfWeek and toMonday.DateTime64
with extended range (compared to typeDateTime
) for functions toStartOfDay, toStartOfHour, toStartOfMinute, toStartOfFiveMinutes, toStartOfTenMinutes, toStartOfFifteenMinutes and timeSlot.
Possible values:
- 0 — Functions return
Date
orDateTime
for all types of arguments. - 1 — Functions return
Date32
orDateTime64
forDate32
orDateTime64
arguments andDate
orDateTime
otherwise.
enable_filesystem_cache
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_filesystem_cache_log
Type | Default value |
---|---|
Type Bool | Default value 0 |
enable_filesystem_cache_on_write_operations
Type | Default value |
---|---|
Type Bool | Default value 0 |
enable_filesystem_read_prefetches_log
Type | Default value |
---|---|
Type Bool | Default value 0 |
enable_global_with_statement
Type | Default value |
---|---|
Type Bool | Default value 1 |
Propagate WITH statements to UNION queries and all subqueries
enable_hdfs_pread
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enable or disables pread for HDFS files. By default, hdfsPread
is used. If disabled, hdfsRead
and hdfsSeek
will be used to read hdfs files.
enable_http_compression
Type | Default value |
---|---|
Type Bool | Default value 0 |
For more information, read the HTTP interface description.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
enable_job_stack_trace
Type | Default value |
---|---|
Type Bool | Default value 1 |
Output stack trace of a job creator when job results in exceptio
enable_lightweight_delete
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_memory_bound_merging_of_aggregation_results
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_multiple_prewhere_read_steps
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_named_columns_in_function_tuple
Type | Default value |
---|---|
Type Bool | Default value 0 |
Generate named tuples in function tuple() when all names are unique and can be treated as unquoted identifiers.
enable_optimize_predicate_expression
Type | Default value |
---|---|
Type Bool | Default value 1 |
Turns on predicate pushdown in SELECT
queries.
Predicate pushdown may significantly reduce network traffic for distributed queries.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
Usage
Consider the following queries:
SELECT count() FROM test_table WHERE date = '2018-10-10'
SELECT count() FROM (SELECT * FROM test_table) WHERE date = '2018-10-10'
If enable_optimize_predicate_expression = 1
, then the execution time of these queries is equal because ClickHouse applies WHERE
to the subquery when processing it.
If enable_optimize_predicate_expression = 0
, then the execution time of the second query is much longer because the WHERE
clause applies to all the data after the subquery finishes.
enable_optimize_predicate_expression_to_final_subquery
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_order_by_all
Type | Default value |
---|---|
Type Bool | Default value 1 |
ORDER BY ALL
syntax, see ORDER BY.
Possible values:
- 0 — Disable ORDER BY ALL.
- 1 — Enable ORDER BY ALL.
Example
Query:
Result:
enable_parsing_to_custom_serialization
Type | Default value |
---|---|
Type Bool | Default value 1 |
If true then data can be parsed directly to columns with custom serialization (e.g. Sparse) according to hints for serialization got from the table.
enable_positional_arguments
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables or disables supporting positional arguments for GROUP BY, LIMIT BY, ORDER BY statements.
Possible values:
- 0 — Positional arguments aren't supported.
- 1 — Positional arguments are supported: column numbers can use instead of column names.
Example
Query:
Result:
enable_reads_from_query_cache
Type | Default value |
---|---|
Type Bool | Default value 1 |
SELECT
queries are retrieved from the query cache.
Possible values:
- 0 - Disabled
- 1 - Enabled
enable_s3_requests_logging
Type | Default value |
---|---|
Type Bool | Default value 0 |
enable_scalar_subquery_optimization
Type | Default value |
---|---|
Type Bool | Default value 1 |
If it is set to true, prevent scalar subqueries from (de)serializing large scalar values and possibly avoid running the same subquery more than once.
enable_sharing_sets_for_mutations
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_software_prefetch_in_aggregation
Type | Default value |
---|---|
Type Bool | Default value 1 |
enable_unaligned_array_join
Type | Default value |
---|---|
Type Bool | Default value 0 |
enable_url_encoding
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enabled by default.
enable_vertical_final
Type | Default value |
---|---|
Type Bool | Default value 1 |
If enable, remove duplicated rows during FINAL by marking rows as deleted and filtering them later instead of merging rows
enable_writes_to_query_cache
Type | Default value |
---|---|
Type Bool | Default value 1 |
SELECT
queries are stored in the query cache.
Possible values:
- 0 - Disabled
- 1 - Enabled
enable_zstd_qat_codec
Type | Default value |
---|---|
Type Bool | Default value 0 |
If turned on, the ZSTD_QAT codec may be used to compress columns.
enforce_strict_identifier_format
Type | Default value |
---|---|
Type Bool | Default value 0 |
If enabled, only allow identifiers containing alphanumeric characters and underscores.
engine_file_allow_create_multiple_files
Type | Default value |
---|---|
Type Bool | Default value 0 |
JSON
, ORC
, Parquet
, etc.). If enabled, on each insert a new file will be created with a name following this pattern:
data.Parquet
-> data.1.Parquet
-> data.2.Parquet
, etc.
Possible values:
- 0 —
INSERT
query appends new data to the end of the file. - 1 —
INSERT
query creates a new file.
engine_file_empty_if_not_exists
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
SELECT
throws exception. - 1 —
SELECT
returns empty result.
engine_file_skip_empty_files
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
SELECT
throws an exception if empty file is not compatible with requested format. - 1 —
SELECT
returns empty result for empty file.
engine_file_truncate_on_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
INSERT
query appends new data to the end of the file. - 1 —
INSERT
query replaces existing content of the file with the new data.
engine_url_skip_empty_files
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
SELECT
throws an exception if empty file is not compatible with requested format. - 1 —
SELECT
returns empty result for empty file.
except_default_mode
Type | Default value |
---|---|
Type SetOperationMode | Default value ALL |
external_storage_connect_timeout_sec
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
external_storage_max_read_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
external_storage_max_read_rows
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
external_storage_rw_timeout_sec
Type | Default value |
---|---|
Type UInt64 | Default value 300 |
external_table_functions_use_nulls
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 0 — The table function explicitly uses Nullable columns.
- 1 — The table function implicitly uses Nullable columns.
Usage
If the setting is set to 0
, the table function does not make Nullable columns and inserts default values instead of NULL. This is also applicable for NULL values inside arrays.
external_table_strict_query
Type | Default value |
---|---|
Type Bool | Default value 0 |
extract_key_value_pairs_max_pairs_per_row
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Max number of pairs that can be produced by the extractKeyValuePairs
function. Used as a safeguard against consuming too much memory.
extremes
Type | Default value |
---|---|
Type Bool | Default value 0 |
fallback_to_stale_replicas_for_distributed_queries
Type | Default value |
---|---|
Type Bool | Default value 1 |
ClickHouse selects the most relevant from the outdated replicas of the table.
Used when performing SELECT
from a distributed table that points to replicated tables.
By default, 1 (enabled).
filesystem_cache_boundary_alignment
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Filesystem cache boundary alignment. This setting is applied only for non-disk read (e.g. for cache of remote table engines / table functions, but not for storage configuration of MergeTree tables). Value 0 means no alignment.
filesystem_cache_enable_background_download_during_fetch
Type | Default value |
---|---|
Type Bool | Default value 1 |
Only has an effect in ClickHouse Cloud. Wait time to lock cache for space reservation in filesystem cache
filesystem_cache_enable_background_download_for_metadata_files_in_packed_storage
Type | Default value |
---|---|
Type Bool | Default value 1 |
Only has an effect in ClickHouse Cloud. Wait time to lock cache for space reservation in filesystem cache
filesystem_cache_max_download_size
Type | Default value |
---|---|
Type UInt64 | Default value 137438953472 |
filesystem_cache_name
Filesystem cache name to use for stateless table engines or data lakes
filesystem_cache_prefer_bigger_buffer_size
Type | Default value |
---|---|
Type Bool | Default value 1 |
Prefer bigger buffer size if filesystem cache is enabled to avoid writing small file segments which deteriorate cache performance. On the other hand, enabling this setting might increase memory usage.
filesystem_cache_reserve_space_wait_lock_timeout_milliseconds
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Wait time to lock cache for space reservation in filesystem cache
filesystem_cache_segments_batch_size
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
filesystem_cache_skip_download_if_exceeds_per_query_cache_write_limit
Type | Default value |
---|---|
Type Bool | Default value 1 |
Skip download from remote filesystem if exceeds query cache size
filesystem_prefetch_max_memory_usage
Type | Default value |
---|---|
Type UInt64 | Default value 1073741824 |
filesystem_prefetch_step_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
auto
- approximately the best prefetch step will be auto deduced, but might not be 100% the best. The actual value might be different because of setting filesystem_prefetch_min_bytes_for_single_read_task
filesystem_prefetch_step_marks
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
auto
- approximately the best prefetch step will be auto deduced, but might not be 100% the best. The actual value might be different because of setting filesystem_prefetch_min_bytes_for_single_read_task
filesystem_prefetches_limit
Type | Default value |
---|---|
Type UInt64 | Default value 200 |
filesystem_prefetches_max_memory_usage
is more recommended if you want to limit the number of prefetches
final
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 - disabled
- 1 - enabled
Example:
flatten_nested
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 1 — Nested column is flattened to separate arrays.
- 0 — Nested column stays a single array of tuples.
Usage
If the setting is set to 0
, it is possible to use an arbitrary level of nesting.
Examples
Query:
Result:
Query:
Result:
force_aggregate_partitions_independently
Type | Default value |
---|---|
Type Bool | Default value 0 |
force_aggregation_in_order
Type | Default value |
---|---|
Type Bool | Default value 0 |
force_data_skipping_indices
Disables query execution if passed data skipping indices wasn't used.
Consider the following example:
force_grouping_standard_compatibility
Type | Default value |
---|---|
Type Bool | Default value 1 |
Make GROUPING function to return 1 when argument is not used as an aggregation key
force_index_by_date
Type | Default value |
---|---|
Type Bool | Default value 0 |
Works with tables in the MergeTree family.
If force_index_by_date=1
, ClickHouse checks whether the query has a date key condition that can be used for restricting data ranges. If there is no suitable condition, it throws an exception. However, it does not check whether the condition reduces the amount of data to read. For example, the condition Date != ' 2000-01-01 '
is acceptable even when it matches all the data in the table (i.e., running the query requires a full scan). For more information about ranges of data in MergeTree tables, see MergeTree.
force_optimize_projection
Type | Default value |
---|---|
Type Bool | Default value 0 |
SELECT
queries, when projection optimization is enabled (see optimize_use_projections setting).
Possible values:
- 0 — Projection optimization is not obligatory.
- 1 — Projection optimization is obligatory.
force_optimize_projection_name
If it is set to a non-empty string, check that this projection is used in the query at least once.
Possible values:
- string: name of projection that used in a query
force_optimize_skip_unused_shards
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- 0 — Disabled. ClickHouse does not throw an exception.
- 1 — Enabled. Query execution is disabled only if the table has a sharding key.
- 2 — Enabled. Query execution is disabled regardless of whether a sharding key is defined for the table.
force_optimize_skip_unused_shards_nesting
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
force_optimize_skip_unused_shards
(hence still requires force_optimize_skip_unused_shards
) depends on the nesting level of the distributed query (case when you have Distributed
table that look into another Distributed
table).
Possible values:
- 0 - Disabled,
force_optimize_skip_unused_shards
works always. - 1 — Enables
force_optimize_skip_unused_shards
only for the first level. - 2 — Enables
force_optimize_skip_unused_shards
up to the second level.
force_primary_key
Type | Default value |
---|---|
Type Bool | Default value 0 |
Works with tables in the MergeTree family.
If force_primary_key=1
, ClickHouse checks to see if the query has a primary key condition that can be used for restricting data ranges. If there is no suitable condition, it throws an exception. However, it does not check whether the condition reduces the amount of data to read. For more information about data ranges in MergeTree tables, see MergeTree.
force_remove_data_recursively_on_drop
Type | Default value |
---|---|
Type Bool | Default value 0 |
formatdatetime_f_prints_scale_number_of_digits
Type | Default value |
---|---|
Type Bool | Default value 0 |
Formatter '%f' in function 'formatDateTime' prints only the scale amount of digits for a DateTime64 instead of fixed 6 digits.
formatdatetime_f_prints_single_zero
Type | Default value |
---|---|
Type Bool | Default value 0 |
Formatter '%f' in function 'formatDateTime' prints a single zero instead of six zeros if the formatted value has no fractional seconds.
formatdatetime_format_without_leading_zeros
Type | Default value |
---|---|
Type Bool | Default value 0 |
formatdatetime_parsedatetime_m_is_month_name
Type | Default value |
---|---|
Type Bool | Default value 1 |
Formatter '%M' in functions 'formatDateTime' and 'parseDateTime' print/parse the month name instead of minutes.
fsync_metadata
Type | Default value |
---|---|
Type Bool | Default value 1 |
.sql
files. Enabled by default.
It makes sense to disable it if the server has millions of tiny tables that are constantly being created and destroyed.
function_implementation
Choose function implementation for specific target or variant (experimental). If empty enable all of them.
function_json_value_return_type_allow_complex
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- true — Allow.
- false — Disallow.
function_json_value_return_type_allow_nullable
Type | Default value |
---|---|
Type Bool | Default value 0 |
NULL
when value is not exist for JSON_VALUE function.
Possible values:
- true — Allow.
- false — Disallow.
function_locate_has_mysql_compatible_argument_order
Type | Default value |
---|---|
Type Bool | Default value 1 |
Controls the order of arguments in function locate.
Possible values:
- 0 — Function
locate
accepts arguments(haystack, needle[, start_pos])
. - 1 — Function
locate
accepts arguments(needle, haystack, [, start_pos])
(MySQL-compatible behavior)
function_range_max_elements_in_block
Type | Default value |
---|---|
Type UInt64 | Default value 500000000 |
Possible values:
- Positive integer.
See Also
function_sleep_max_microseconds_per_block
Type | Default value |
---|---|
Type UInt64 | Default value 3000000 |
Maximum number of microseconds the function sleep
is allowed to sleep for each block. If a user called it with a larger value, it throws an exception. It is a safety threshold.
function_visible_width_behavior
Type | Default value |
---|---|
Type UInt64 | Default value 1 |
The version of visibleWidth
behavior. 0 - only count the number of code points; 1 - correctly count zero-width and combining characters, count full-width characters as two, estimate the tab width, count delete characters.
geo_distance_returns_float64_on_float64_arguments
Type | Default value |
---|---|
Type Bool | Default value 1 |
If all four arguments to geoDistance
, greatCircleDistance
, greatCircleAngle
functions are Float64, return Float64 and use double precision for internal calculations. In previous ClickHouse versions, the functions always returned Float32.
geotoh3_lon_lat_input_order
Type | Default value |
---|---|
Type Bool | Default value 0 |
Function 'geoToH3' accepts (lon, lat) if true, otherwise (lat, lon).
glob_expansion_max_elements
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
grace_hash_join_initial_buckets
Type | Default value |
---|---|
Type NonZeroUInt64 | Default value 1 |
grace_hash_join_max_buckets
Type | Default value |
---|---|
Type NonZeroUInt64 | Default value 1024 |
group_by_overflow_mode
Type | Default value |
---|---|
Type OverflowModeGroupBy | Default value throw |
throw
: throw an exceptionbreak
: stop executing the query and return the partial resultany
: continue aggregation for the keys that got into the set, but do not add new keys to the set.
Using the 'any' value lets you run an approximation of GROUP BY. The quality of this approximation depends on the statistical nature of the data.
group_by_two_level_threshold
Type | Default value |
---|---|
Type UInt64 | Default value 100000 |
group_by_two_level_threshold_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 50000000 |
group_by_use_nulls
Type | Default value |
---|---|
Type Bool | Default value 0 |
ROLLUP
, CUBE
, or GROUPING SETS
specifiers are used, some aggregation keys may not be used to produce some result rows.
Columns for these keys are filled with either default value or NULL
in corresponding rows depending on this setting.
Possible values:
- 0 — The default value for the aggregation key type is used to produce missing values.
- 1 — ClickHouse executes
GROUP BY
the same way as the SQL standard says. The types of aggregation keys are converted to Nullable. Columns for corresponding aggregation keys are filled with NULL for rows that didn't use it.
See also:
h3togeo_lon_lat_result_order
Type | Default value |
---|---|
Type Bool | Default value 0 |
Function 'h3ToGeo' returns (lon, lat) if true, otherwise (lat, lon).
handshake_timeout_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 10000 |
hdfs_create_new_file_on_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
initial: data.Parquet.gz
-> data.1.Parquet.gz
-> data.2.Parquet.gz
, etc.
Possible values:
- 0 —
INSERT
query appends new data to the end of the file. - 1 —
INSERT
query creates a new file.
hdfs_ignore_file_doesnt_exist
Type | Default value |
---|---|
Type Bool | Default value 0 |
Ignore absence of file if it does not exist when reading certain keys.
Possible values:
- 1 —
SELECT
returns empty result. - 0 —
SELECT
throws an exception.
hdfs_replication
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
hdfs_skip_empty_files
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
SELECT
throws an exception if empty file is not compatible with requested format. - 1 —
SELECT
returns empty result for empty file.
hdfs_throw_on_zero_files_match
Type | Default value |
---|---|
Type Bool | Default value 0 |
Throw an error if matched zero files according to glob expansion rules.
Possible values:
- 1 —
SELECT
throws an exception. - 0 —
SELECT
returns empty result.
hdfs_truncate_on_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
INSERT
query appends new data to the end of the file. - 1 —
INSERT
query replaces existing content of the file with the new data.
hedged_connection_timeout_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 50 |
Connection timeout for establishing connection with replica for Hedged requests
hnsw_candidate_list_size_for_search
Type | Default value |
---|---|
Type UInt64 | Default value 256 |
The size of the dynamic candidate list when searching the vector similarity index, also known as 'ef_search'.
hsts_max_age
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
http_connection_timeout
Type | Default value |
---|---|
Type Seconds | Default value 1 |
Possible values:
- Any positive integer.
- 0 - Disabled (infinite timeout).
http_headers_progress_interval_ms
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
http_make_head_request
Type | Default value |
---|---|
Type Bool | Default value 1 |
http_make_head_request
setting allows the execution of a HEAD
request while reading data from HTTP to retrieve information about the file to be read, such as its size. Since it's enabled by default, it may be desirable to disable this setting in cases where the server does not support HEAD
requests.
http_max_field_name_size
Type | Default value |
---|---|
Type UInt64 | Default value 131072 |
http_max_field_value_size
Type | Default value |
---|---|
Type UInt64 | Default value 131072 |
http_max_fields
Type | Default value |
---|---|
Type UInt64 | Default value 1000000 |
http_max_multipart_form_data_size
Type | Default value |
---|---|
Type UInt64 | Default value 1073741824 |
http_max_request_param_data_size
Type | Default value |
---|---|
Type UInt64 | Default value 10485760 |
http_max_tries
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
http_max_uri_size
Type | Default value |
---|---|
Type UInt64 | Default value 1048576 |
Possible values:
- Positive integer.
http_native_compression_disable_checksumming_on_decompress
Type | Default value |
---|---|
Type Bool | Default value 0 |
gzip
or deflate
).
For more information, read the HTTP interface description.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
http_receive_timeout
Type | Default value |
---|---|
Type Seconds | Default value 30 |
HTTP receive timeout (in seconds).
Possible values:
- Any positive integer.
- 0 - Disabled (infinite timeout).
http_response_buffer_size
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
http_response_headers
Type | Default value |
---|---|
Type Map | Default value {} |
Allows to add or override HTTP headers which the server will return in the response with a successful query result. This only affects the HTTP interface.
If the header is already set by default, the provided value will override it. If the header was not set by default, it will be added to the list of headers. Headers that are set by the server by default and not overridden by this setting, will remain.
The setting allows you to set a header to a constant value. Currently there is no way to set a header to a dynamically calculated value.
Neither names or values can contain ASCII control characters.
If you implement a UI application which allows users to modify settings but at the same time makes decisions based on the returned headers, it is recommended to restrict this setting to readonly.
Example: SET http_response_headers = '{"Content-Type": "image/png"}'
http_retry_initial_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
http_retry_max_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
http_send_timeout
Type | Default value |
---|---|
Type Seconds | Default value 30 |
HTTP send timeout (in seconds).
Possible values:
- Any positive integer.
- 0 - Disabled (infinite timeout).
It's applicable only to the default profile. A server reboot is required for the changes to take effect.
http_skip_not_found_url_for_globs
Type | Default value |
---|---|
Type Bool | Default value 1 |
http_wait_end_of_query
Type | Default value |
---|---|
Type Bool | Default value 0 |
http_write_exception_in_output_format
Type | Default value |
---|---|
Type Bool | Default value 1 |
Write exception in output format to produce valid output. Works with JSON and XML formats.
http_zlib_compression_level
Type | Default value |
---|---|
Type Int64 | Default value 3 |
Possible values: Numbers from 1 to 9.
iceberg_snapshot_id
Type | Default value |
---|---|
Type Int64 | Default value 0 |
Query Iceberg table using the specific snapshot id.
iceberg_timestamp_ms
Type | Default value |
---|---|
Type Int64 | Default value 0 |
Query Iceberg table using the snapshot that was current at a specific timestamp.
idle_connection_timeout
Type | Default value |
---|---|
Type UInt64 | Default value 3600 |
Possible values:
- Positive integer (0 - close immediately, after 0 seconds).
ignore_cold_parts_seconds
Type | Default value |
---|---|
Type Int64 | Default value 0 |
ignore_data_skipping_indices
Ignores the skipping indexes specified if used by the query.
Consider the following example:
The query without ignoring any indexes:
Ignoring the xy_idx
index:
Works with tables in the MergeTree family.
ignore_drop_queries_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
If enabled, server will ignore all DROP table queries with specified probability (for Memory and JOIN engines it will replcase DROP to TRUNCATE). Used for testing purposes
ignore_materialized_views_with_dropped_target_table
Type | Default value |
---|---|
Type Bool | Default value 0 |
Ignore MVs with dropped target table during pushing to views
ignore_on_cluster_for_replicated_access_entities_queries
Type | Default value |
---|---|
Type Bool | Default value 0 |
ignore_on_cluster_for_replicated_named_collections_queries
Type | Default value |
---|---|
Type Bool | Default value 0 |
Ignore ON CLUSTER clause for replicated named collections management queries.
ignore_on_cluster_for_replicated_udf_queries
Type | Default value |
---|---|
Type Bool | Default value 0 |
implicit_select
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow writing simple SELECT queries without the leading SELECT keyword, which makes it simple for calculator-style usage, e.g. 1 + 2
becomes a valid query.
In clickhouse-local
it is enabled by default and can be explicitly disabled.
implicit_table_at_top_level
If not empty, queries without FROM at the top level will read from this table instead of system.one.
This is used in clickhouse-local for input data processing. The setting could be set explicitly by a user but is not intended for this type of usage.
Subqueries are not affected by this setting (neither scalar, FROM, or IN subqueries). SELECTs at the top level of UNION, INTERSECT, EXCEPT chains are treated uniformly and affected by this setting, regardless of their grouping in parentheses. It is unspecified how this setting affects views and distributed queries.
The setting accepts a table name (then the table is resolved from the current database) or a qualified name in the form of 'database.table'. Both database and table names have to be unquoted - only simple identifiers are allowed.
implicit_transaction
Type | Default value |
---|---|
Type Bool | Default value 0 |
input_format_parallel_parsing
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 1 — Enabled.
- 0 — Disabled.
insert_allow_materialized_columns
Type | Default value |
---|---|
Type Bool | Default value 0 |
insert_deduplicate
Type | Default value |
---|---|
Type Bool | Default value 1 |
INSERT
(for Replicated* tables).
Possible values:
- 0 — Disabled.
- 1 — Enabled.
By default, blocks inserted into replicated tables by the INSERT
statement are deduplicated (see Data Replication).
For the replicated tables by default the only 100 of the most recent blocks for each partition are deduplicated (see replicated_deduplication_window, replicated_deduplication_window_seconds).
For not replicated tables see non_replicated_deduplication_window.
insert_deduplication_token
The setting allows a user to provide own deduplication semantic in MergeTree/ReplicatedMergeTree For example, by providing a unique value for the setting in each INSERT statement, user can avoid the same inserted data being deduplicated.
Possible values:
- Any string
insert_deduplication_token
is used for deduplication only when not empty.
For the replicated tables by default the only 100 of the most recent inserts for each partition are deduplicated (see replicated_deduplication_window, replicated_deduplication_window_seconds). For not replicated tables see non_replicated_deduplication_window.
insert_deduplication_token
works on a partition level (the same as insert_deduplication
checksum). Multiple partitions can have the same insert_deduplication_token
.
Example:
insert_keeper_fault_injection_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
insert_keeper_fault_injection_seed
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
insert_keeper_max_retries
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
The setting sets the maximum number of retries for ClickHouse Keeper (or ZooKeeper) requests during insert into replicated MergeTree. Only Keeper requests which failed due to network error, Keeper session timeout, or request timeout are considered for retries.
Possible values:
- Positive integer.
- 0 — Retries are disabled
Cloud default value: 20
.
Keeper request retries are done after some timeout. The timeout is controlled by the following settings: insert_keeper_retry_initial_backoff_ms
, insert_keeper_retry_max_backoff_ms
.
The first retry is done after insert_keeper_retry_initial_backoff_ms
timeout. The consequent timeouts will be calculated as follows:
For example, if insert_keeper_retry_initial_backoff_ms=100
, insert_keeper_retry_max_backoff_ms=10000
and insert_keeper_max_retries=8
then timeouts will be 100, 200, 400, 800, 1600, 3200, 6400, 10000
.
Apart from fault tolerance, the retries aim to provide a better user experience - they allow to avoid returning an error during INSERT execution if Keeper is restarted, for example, due to an upgrade.
insert_keeper_retry_initial_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
Possible values:
- Positive integer.
- 0 — No timeout
insert_keeper_retry_max_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
Possible values:
- Positive integer.
- 0 — Maximum timeout is not limited
insert_null_as_default
Type | Default value |
---|---|
Type Bool | Default value 1 |
NULL
causes an exception. If column type is nullable, then NULL
values are inserted as is, regardless of this setting.
This setting is applicable to INSERT ... SELECT queries. Note that SELECT
subqueries may be concatenated with UNION ALL
clause.
Possible values:
- 0 — Inserting
NULL
into a not nullable column causes an exception. - 1 — Default column value is inserted instead of
NULL
.
insert_quorum
Type | Default value |
---|---|
Type UInt64Auto | Default value 0 |
Enables the quorum writes.
- If
insert_quorum < 2
, the quorum writes are disabled. - If
insert_quorum >= 2
, the quorum writes are enabled. - If
insert_quorum = 'auto'
, use majority number (number_of_replicas / 2 + 1
) as quorum number.
Quorum writes
INSERT
succeeds only when ClickHouse manages to correctly write data to the insert_quorum
of replicas during the insert_quorum_timeout
. If for any reason the number of replicas with successful writes does not reach the insert_quorum
, the write is considered failed and ClickHouse will delete the inserted block from all the replicas where data has already been written.
When insert_quorum_parallel
is disabled, all replicas in the quorum are consistent, i.e. they contain data from all previous INSERT
queries (the INSERT
sequence is linearized). When reading data written using insert_quorum
and insert_quorum_parallel
is disabled, you can turn on sequential consistency for SELECT
queries using select_sequential_consistency.
ClickHouse generates an exception:
- If the number of available replicas at the time of the query is less than the
insert_quorum
. - When
insert_quorum_parallel
is disabled and an attempt to write data is made when the previous block has not yet been inserted ininsert_quorum
of replicas. This situation may occur if the user tries to perform anotherINSERT
query to the same table before the previous one withinsert_quorum
is completed.
See also:
insert_quorum_parallel
Type | Default value |
---|---|
Type Bool | Default value 1 |
This setting is not applicable to SharedMergeTree, see SharedMergeTree consistency for more information.
Enables or disables parallelism for quorum INSERT
queries. If enabled, additional INSERT
queries can be sent while previous queries have not yet finished. If disabled, additional writes to the same table will be rejected.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
See also:
insert_quorum_timeout
Type | Default value |
---|---|
Type Milliseconds | Default value 600000 |
See also:
insert_shard_id
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
0
, specifies the shard of Distributed table into which the data will be inserted synchronously.
If insert_shard_id
value is incorrect, the server will throw an exception.
To get the number of shards on requested_cluster
, you can check server config or use this query:
Possible values:
- 0 — Disabled.
- Any number from
1
toshards_num
of corresponding Distributed table.
Example
Query:
Result:
interactive_delay
Type | Default value |
---|---|
Type UInt64 | Default value 100000 |
intersect_default_mode
Type | Default value |
---|---|
Type SetOperationMode | Default value ALL |
join_algorithm
Type | Default value |
---|---|
Type JoinAlgorithm | Default value direct,parallel_hash,hash |
Specifies which JOIN algorithm is used.
Several algorithms can be specified, and an available one would be chosen for a particular query based on kind/strictness and table engine.
Possible values:
- grace_hash
Grace hash join is used. Grace hash provides an algorithm option that provides performant complex joins while limiting memory use.
The first phase of a grace join reads the right table and splits it into N buckets depending on the hash value of key columns (initially, N is grace_hash_join_initial_buckets
). This is done in a way to ensure that each bucket can be processed independently. Rows from the first bucket are added to an in-memory hash table while the others are saved to disk. If the hash table grows beyond the memory limit (e.g., as set by max_bytes_in_join
, the number of buckets is increased and the assigned bucket for each row. Any rows which don't belong to the current bucket are flushed and reassigned.
Supports INNER/LEFT/RIGHT/FULL ALL/ANY JOIN
.
- hash
Hash join algorithm is used. The most generic implementation that supports all combinations of kind and strictness and multiple join keys that are combined with OR
in the JOIN ON
section.
When using the hash
algorithm, the right part of JOIN
is uploaded into RAM.
- parallel_hash
A variation of hash
join that splits the data into buckets and builds several hashtables instead of one concurrently to speed up this process.
When using the parallel_hash
algorithm, the right part of JOIN
is uploaded into RAM.
- partial_merge
A variation of the sort-merge algorithm, where only the right table is fully sorted.
The RIGHT JOIN
and FULL JOIN
are supported only with ALL
strictness (SEMI
, ANTI
, ANY
, and ASOF
are not supported).
When using the partial_merge
algorithm, ClickHouse sorts the data and dumps it to the disk. The partial_merge
algorithm in ClickHouse differs slightly from the classic realization. First, ClickHouse sorts the right table by joining keys in blocks and creates a min-max index for sorted blocks. Then it sorts parts of the left table by the join key
and joins them over the right table. The min-max index is also used to skip unneeded right table blocks.
- direct
This algorithm can be applied when the storage for the right table supports key-value requests.
The direct
algorithm performs a lookup in the right table using rows from the left table as keys. It's supported only by special storage such as Dictionary or EmbeddedRocksDB and only the LEFT
and INNER
JOINs.
- auto
When set to auto
, hash
join is tried first, and the algorithm is switched on the fly to another algorithm if the memory limit is violated.
- full_sorting_merge
Sort-merge algorithm with full sorting joined tables before joining.
- prefer_partial_merge
ClickHouse always tries to use partial_merge
join if possible, otherwise, it uses hash
. Deprecated, same as partial_merge,hash
.
- default (deprecated)
Legacy value, please don't use anymore.
Same as direct,hash
, i.e. try to use direct join and hash join join (in this order).
join_any_take_last_row
Type | Default value |
---|---|
Type Bool | Default value 0 |
ANY
strictness.
This setting applies only for JOIN
operations with Join engine tables.
Possible values:
- 0 — If the right table has more than one matching row, only the first one found is joined.
- 1 — If the right table has more than one matching row, only the last one found is joined.
See also:
join_default_strictness
Type | Default value |
---|---|
Type JoinStrictness | Default value ALL |
Possible values:
ALL
— If the right table has several matching rows, ClickHouse creates a Cartesian product from matching rows. This is the normalJOIN
behaviour from standard SQL.ANY
— If the right table has several matching rows, only the first one found is joined. If the right table has only one matching row, the results ofANY
andALL
are the same.ASOF
— For joining sequences with an uncertain match.Empty string
— IfALL
orANY
is not specified in the query, ClickHouse throws an exception.
join_on_disk_max_files_to_merge
Type | Default value |
---|---|
Type UInt64 | Default value 64 |
The bigger the value of the setting, the more RAM is used and the less disk I/O is needed.
Possible values:
- Any positive integer, starting from 2.
join_output_by_rowlist_perkey_rows_threshold
Type | Default value |
---|---|
Type UInt64 | Default value 5 |
The lower limit of per-key average rows in the right table to determine whether to output by row list in hash join.
join_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
Possible values:
THROW
— ClickHouse throws an exception and breaks operation.BREAK
— ClickHouse breaks operation and does not throw an exception.
Default value: THROW
.
See Also
join_to_sort_maximum_table_rows
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
The maximum number of rows in the right table to determine whether to rerange the right table by key in left or inner join.
join_to_sort_minimum_perkey_rows
Type | Default value |
---|---|
Type UInt64 | Default value 40 |
The lower limit of per-key average rows in the right table to determine whether to rerange the right table by key in left or inner join. This setting ensures that the optimization is not applied for sparse table keys
join_use_nulls
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — The empty cells are filled with the default value of the corresponding field type.
- 1 —
JOIN
behaves the same way as in standard SQL. The type of the corresponding field is converted to Nullable, and empty cells are filled with NULL.
joined_subquery_requires_alias
Type | Default value |
---|---|
Type Bool | Default value 1 |
kafka_disable_num_consumers_limit
Type | Default value |
---|---|
Type Bool | Default value 0 |
kafka_max_wait_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 5000 |
Possible values:
- Positive integer.
- 0 — Infinite timeout.
See also:
keeper_map_strict_mode
Type | Default value |
---|---|
Type Bool | Default value 0 |
keeper_max_retries
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
Max retries for general keeper operations
keeper_retry_initial_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
Initial backoff timeout for general keeper operations
keeper_retry_max_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 5000 |
Max backoff timeout for general keeper operations
least_greatest_legacy_null_behavior
Type | Default value |
---|---|
Type Bool | Default value 0 |
If enabled, functions 'least' and 'greatest' return NULL if one of their arguments is NULL.
legacy_column_name_of_tuple_literal
Type | Default value |
---|---|
Type Bool | Default value 0 |
List all names of element of large tuple literals in their column names instead of hash. This settings exists only for compatibility reasons. It makes sense to set to 'true', while doing rolling update of cluster from version lower than 21.7 to higher.
lightweight_deletes_sync
Type | Default value |
---|---|
Type UInt64 | Default value 2 |
The same as mutations_sync
, but controls only execution of lightweight deletes.
Possible values:
- 0 - Mutations execute asynchronously.
- 1 - The query waits for the lightweight deletes to complete on the current server.
- 2 - The query waits for the lightweight deletes to complete on all replicas (if they exist).
See Also
limit
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- 0 — The number of rows is not limited.
- Positive integer.
live_view_heartbeat_interval
Type | Default value |
---|---|
Type Seconds | Default value 15 |
load_balancing
Type | Default value |
---|---|
Type LoadBalancing | Default value random |
ClickHouse supports the following algorithms of choosing replicas:
- Random (by default)
- Nearest hostname
- Hostname levenshtein distance
- In order
- First or random
- Round robin
See also:
Random (by Default)
The number of errors is counted for each replica. The query is sent to the replica with the fewest errors, and if there are several of these, to anyone of them. Disadvantages: Server proximity is not accounted for; if the replicas have different data, you will also get different data.
Nearest Hostname
The number of errors is counted for each replica. Every 5 minutes, the number of errors is integrally divided by 2. Thus, the number of errors is calculated for a recent time with exponential smoothing. If there is one replica with a minimal number of errors (i.e. errors occurred recently on the other replicas), the query is sent to it. If there are multiple replicas with the same minimal number of errors, the query is sent to the replica with a hostname that is most similar to the server's hostname in the config file (for the number of different characters in identical positions, up to the minimum length of both hostnames).
For instance, example01-01-1 and example01-01-2 are different in one position, while example01-01-1 and example01-02-2 differ in two places. This method might seem primitive, but it does not require external data about network topology, and it does not compare IP addresses, which would be complicated for our IPv6 addresses.
Thus, if there are equivalent replicas, the closest one by name is preferred. We can also assume that when sending a query to the same server, in the absence of failures, a distributed query will also go to the same servers. So even if different data is placed on the replicas, the query will return mostly the same results.
Hostname levenshtein distance
Just like nearest_hostname
, but it compares hostname in a levenshtein distance manner. For example:
In Order
Replicas with the same number of errors are accessed in the same order as they are specified in the configuration. This method is appropriate when you know exactly which replica is preferable.
First or Random
This algorithm chooses the first replica in the set or a random replica if the first is unavailable. It's effective in cross-replication topology setups, but useless in other configurations.
The first_or_random
algorithm solves the problem of the in_order
algorithm. With in_order
, if one replica goes down, the next one gets a double load while the remaining replicas handle the usual amount of traffic. When using the first_or_random
algorithm, the load is evenly distributed among replicas that are still available.
It's possible to explicitly define what the first replica is by using the setting load_balancing_first_offset
. This gives more control to rebalance query workloads among replicas.
Round Robin
This algorithm uses a round-robin policy across replicas with the same number of errors (only the queries with round_robin
policy is accounted).
load_balancing_first_offset
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
load_marks_asynchronously
Type | Default value |
---|---|
Type Bool | Default value 0 |
local_filesystem_read_method
Type | Default value |
---|---|
Type String | Default value pread_threadpool |
local_filesystem_read_prefetch
Type | Default value |
---|---|
Type Bool | Default value 0 |
lock_acquire_timeout
Type | Default value |
---|---|
Type Seconds | Default value 120 |
Locking timeout is used to protect from deadlocks while executing read/write operations with tables. When the timeout expires and the locking request fails, the ClickHouse server throws an exception "Locking attempt timed out! Possible deadlock avoided. Client should retry." with error code DEADLOCK_AVOIDED
.
Possible values:
- Positive integer (in seconds).
- 0 — No locking timeout.
log_comment
Specifies the value for the log_comment
field of the system.query_log table and comment text for the server log.
It can be used to improve the readability of server logs. Additionally, it helps to select queries related to the test from the system.query_log
after running clickhouse-test.
Possible values:
- Any string no longer than max_query_size. If the max_query_size is exceeded, the server throws an exception.
Example
Query:
Result:
log_formatted_queries
Type | Default value |
---|---|
Type Bool | Default value 0 |
formatted_query
column in the system.query_log).
Possible values:
- 0 — Formatted queries are not logged in the system table.
- 1 — Formatted queries are logged in the system table.
log_processors_profiles
Type | Default value |
---|---|
Type Bool | Default value 1 |
Write time that processor spent during execution/waiting for data to system.processors_profile_log
table.
See also:
log_profile_events
Type | Default value |
---|---|
Type Bool | Default value 1 |
log_queries
Type | Default value |
---|---|
Type Bool | Default value 1 |
Queries sent to ClickHouse with this setup are logged according to the rules in the query_log server configuration parameter.
Example:
log_queries_cut_to_length
Type | Default value |
---|---|
Type UInt64 | Default value 100000 |
log_queries_min_query_duration_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 0 |
long_query_time
for MySQL Slow Query Log), and this basically means that you will not find them in the following tables:
system.query_log
system.query_thread_log
Only the queries with the following type will get to the log:
-
QUERY_FINISH
-
EXCEPTION_WHILE_PROCESSING
-
Type: milliseconds
-
Default value: 0 (any query)
log_queries_min_type
Type | Default value |
---|---|
Type LogQueriesType | Default value QUERY_START |
query_log
minimal type to log.
Possible values:
QUERY_START
(=1
)QUERY_FINISH
(=2
)EXCEPTION_BEFORE_START
(=3
)EXCEPTION_WHILE_PROCESSING
(=4
)
Can be used to limit which entities will go to query_log
, say you are interested only in errors, then you can use EXCEPTION_WHILE_PROCESSING
:
log_queries_probability
Type | Default value |
---|---|
Type Float | Default value 1 |
Possible values:
- 0 — Queries are not logged in the system tables.
- Positive floating-point number in the range [0..1]. For example, if the setting value is
0.5
, about half of the queries are logged in the system tables. - 1 — All queries are logged in the system tables.
log_query_settings
Type | Default value |
---|---|
Type Bool | Default value 1 |
log_query_threads
Type | Default value |
---|---|
Type Bool | Default value 0 |
Query threads log into the system.query_thread_log table. This setting has effect only when log_queries is true. Queries' threads run by ClickHouse with this setup are logged according to the rules in the query_thread_log server configuration parameter.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
Example
log_query_views
Type | Default value |
---|---|
Type Bool | Default value 1 |
When a query run by ClickHouse with this setting enabled has associated views (materialized or live views), they are logged in the query_views_log server configuration parameter.
Example:
low_cardinality_allow_in_native_format
Type | Default value |
---|---|
Type Bool | Default value 1 |
If usage of LowCardinality
is restricted, ClickHouse server converts LowCardinality
-columns to ordinary ones for SELECT
queries, and convert ordinary columns to LowCardinality
-columns for INSERT
queries.
This setting is required mainly for third-party clients which do not support LowCardinality
data type.
Possible values:
- 1 — Usage of
LowCardinality
is not restricted. - 0 — Usage of
LowCardinality
is restricted.
low_cardinality_max_dictionary_size
Type | Default value |
---|---|
Type UInt64 | Default value 8192 |
Possible values:
- Any positive integer.
low_cardinality_use_single_dictionary_for_part
Type | Default value |
---|---|
Type Bool | Default value 0 |
By default, the ClickHouse server monitors the size of dictionaries and if a dictionary overflows then the server starts to write the next one. To prohibit creating several dictionaries set low_cardinality_use_single_dictionary_for_part = 1
.
Possible values:
- 1 — Creating several dictionaries for the data part is prohibited.
- 0 — Creating several dictionaries for the data part is not prohibited.
low_priority_query_wait_time_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 1000 |
When the query prioritization mechanism is employed (see setting priority
), low-priority queries wait for higher-priority queries to finish. This setting specifies the duration of waiting.
materialize_skip_indexes_on_insert
Type | Default value |
---|---|
Type Bool | Default value 1 |
If INSERTs build and store skip indexes. If disabled, skip indexes will be build and stored during merges or by explicit MATERIALIZE INDEX
materialize_statistics_on_insert
Type | Default value |
---|---|
Type Bool | Default value 1 |
If INSERTs build and insert statistics. If disabled, statistics will be build and stored during merges or by explicit MATERIALIZE STATISTICS
materialize_ttl_after_modify
Type | Default value |
---|---|
Type Bool | Default value 1 |
materialized_views_ignore_errors
Type | Default value |
---|---|
Type Bool | Default value 0 |
max_analyze_depth
Type | Default value |
---|---|
Type UInt64 | Default value 5000 |
max_ast_depth
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
At this time, it isn't checked during parsing, but only after parsing the query. This means that a syntactic tree that is too deep can be created during parsing, but the query will fail.
max_ast_elements
Type | Default value |
---|---|
Type UInt64 | Default value 50000 |
At this time, it isn't checked during parsing, but only after parsing the query. This means that a syntactic tree that is too deep can be created during parsing, but the query will fail.
max_autoincrement_series
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
The limit on the number of series created by the generateSeriesID
function.
As each series represents a node in Keeper, it is recommended to have no more than a couple of millions of them.
max_backup_bandwidth
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_block_size
Type | Default value |
---|---|
Type UInt64 | Default value 65409 |
The max_block_size
setting indicates the recommended maximum number of rows to include in a single block when loading data from tables. Blocks the size of max_block_size
are not always loaded from the table: if ClickHouse determines that less data needs to be retrieved, a smaller block is processed.
The block size should not be too small to avoid noticeable costs when processing each block. It should also not be too large to ensure that queries with a LIMIT clause execute quickly after processing the first block. When setting max_block_size
, the goal should be to avoid consuming too much memory when extracting a large number of columns in multiple threads and to preserve at least some cache locality.
max_bytes_before_external_group_by
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Enables or disables execution of GROUP BY
clauses in external memory.
(See GROUP BY in external memory)
Possible values:
- Maximum volume of RAM (in bytes) that can be used by the single GROUP BY operation.
0
—GROUP BY
in external memory disabled.
If memory usage during GROUP BY operations is exceeding this threshold in bytes, activate the 'external aggregation' mode (spill data to disk).
The recommended value is half of the available system memory.
max_bytes_before_external_sort
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Enables or disables execution of ORDER BY
clauses in external memory. See ORDER BY Implementation Details
If memory usage during ORDER BY operation exceeds this threshold in bytes, the 'external sorting' mode (spill data to disk) is activated.
Possible values:
- Maximum volume of RAM (in bytes) that can be used by the single ORDER BY operation. The recommended value is half of available system memory
0
—ORDER BY
in external memory disabled.
max_bytes_before_remerge_sort
Type | Default value |
---|---|
Type UInt64 | Default value 1000000000 |
max_bytes_in_distinct
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_bytes_in_join
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
This setting applies to SELECT ... JOIN operations and the Join table engine.
If the query contains joins, ClickHouse checks this setting for every intermediate result.
ClickHouse can proceed with different actions when the limit is reached. Use the join_overflow_mode settings to choose the action.
Possible values:
- Positive integer.
- 0 — Memory control is disabled.
max_bytes_in_set
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_bytes_ratio_before_external_group_by
Type | Default value |
---|---|
Type Double | Default value 0.5 |
The ratio of available memory that is allowed for GROUP BY
. Once reached,
external memory is used for aggregation.
For example, if set to 0.6
, GROUP BY
will allow using 60% of the available memory
(to server/user/merges) at the beginning of the execution, after that, it will
start using external aggregation.
max_bytes_ratio_before_external_sort
Type | Default value |
---|---|
Type Double | Default value 0.5 |
The ratio of available memory that is allowed for ORDER BY
. Once reached,
external sort is used.
For example, if set to 0.6
, ORDER BY
will allow using 60%
of available memory
(to server/user/merges) at the beginning of the execution, after that,
it will start using external sort.
max_bytes_to_read
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_bytes_to_read_leaf
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
For example, a cluster consists of 2 shards and each shard contains a table with
100 bytes of data. A distributed query which is supposed to read all the data
from both tables with setting max_bytes_to_read=150
will fail as in total it
will be 200 bytes. A query with max_bytes_to_read_leaf=150
will succeed since
leaf nodes will read 100 bytes at max.
The restriction is checked for each processed chunk of data.
This setting is unstable with prefer_localhost_replica=1
.
max_bytes_to_sort
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
sort_overflow_mode
which by default is set to throw
.
max_bytes_to_transfer
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_columns_to_read
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
This setting is useful for preventing overly complex queries.
0
value means unlimited.
max_compress_block_size
Type | Default value |
---|---|
Type UInt64 | Default value 1048576 |
This is an expert-level setting, and you shouldn't change it if you're just getting started with ClickHouse.
Don't confuse blocks for compression (a chunk of memory consisting of bytes) with blocks for query processing (a set of rows from a table).
max_concurrent_queries_for_all_users
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Example: max_concurrent_queries_for_all_users
can be set to 99 for all users and database administrator can set it to 100 for itself to run queries for investigation even when the server is overloaded.
Modifying the setting for one query or user does not affect other queries.
Possible values:
- Positive integer.
- 0 — No limit.
Example
See Also
max_concurrent_queries_for_user
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 — No limit.
Example
max_distributed_connections
Type | Default value |
---|---|
Type UInt64 | Default value 1024 |
The following parameters are only used when creating Distributed tables (and when launching a server), so there is no reason to change them at runtime.
max_distributed_depth
Type | Default value |
---|---|
Type UInt64 | Default value 5 |
If the value is exceeded, the server throws an exception.
Possible values:
- Positive integer.
- 0 — Unlimited depth.
max_download_buffer_size
Type | Default value |
---|---|
Type UInt64 | Default value 10485760 |
max_download_threads
Type | Default value |
---|---|
Type MaxThreads | Default value 4 |
max_estimated_execution_time
Type | Default value |
---|---|
Type Seconds | Default value 0 |
Maximum query estimate execution time in seconds. Checked on every data block
when timeout_before_checking_execution_speed
expires.
max_execution_speed
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
timeout_before_checking_execution_speed
expires. If the execution speed is high, the execution speed will be reduced.
max_execution_speed_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
timeout_before_checking_execution_speed
expires. If the execution speed is high, the execution speed will be reduced.
max_execution_time
Type | Default value |
---|---|
Type Seconds | Default value 0 |
The max_execution_time
parameter can be a bit tricky to understand.
It operates based on interpolation relative to the current query execution speed
(this behaviour is controlled by timeout_before_checking_execution_speed
).
ClickHouse will interrupt a query if the projected execution time exceeds the
specified max_execution_time
. By default, the timeout_before_checking_execution_speed
is set to 10 seconds. This means that after 10 seconds of query execution, ClickHouse
will begin estimating the total execution time. If, for example, max_execution_time
is set to 3600 seconds (1 hour), ClickHouse will terminate the query if the estimated
time exceeds this 3600-second limit. If you set timeout_before_checking_execution_speed
to 0, ClickHouse will use the clock time as the basis for max_execution_time
.
If query runtime exceeds the specified number of seconds, the behavior will be
determined by the 'timeout_overflow_mode', which by default is set to throw
.
The timeout is checked and the query can stop only in designated places during data processing. It currently cannot stop during merging of aggregation states or during query analysis, and the actual run time will be higher than the value of this setting.
max_execution_time_leaf
Type | Default value |
---|---|
Type Seconds | Default value 0 |
max_execution_time
but only
applied on leaf nodes for distributed or remote queries.
For example, if we want to limit the execution time on a leaf node to 10s
but
have no limit on the initial node, instead of having max_execution_time
in the
nested subquery settings:
We can use max_execution_time_leaf
as the query settings:
max_expanded_ast_elements
Type | Default value |
---|---|
Type UInt64 | Default value 500000 |
max_fetch_partition_retries_count
Type | Default value |
---|---|
Type UInt64 | Default value 5 |
max_final_threads
Type | Default value |
---|---|
Type MaxThreads | Default value 'auto(4)' |
SELECT
query data read phase with the FINAL modifier.
Possible values:
- Positive integer.
- 0 or 1 — Disabled.
SELECT
queries are executed in a single thread.
max_http_get_redirects
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_hyperscan_regexp_length
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 - The length is not limited.
Example
Query:
Result:
Query:
Result:
See Also
max_hyperscan_regexp_total_length
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 - The length is not limited.
Example
Query:
Result:
Query:
Result:
See Also
max_insert_block_size
Type | Default value |
---|---|
Type UInt64 | Default value 1048449 |
The default is slightly more than max_block_size
. The reason for this is that certain table engines (*MergeTree
) form a data part on the disk for each inserted block, which is a fairly large entity. Similarly, *MergeTree
tables sort data during insertion, and a large enough block size allow sorting more data in RAM.
max_insert_delayed_streams_for_parallel_write
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_insert_threads
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
INSERT SELECT
query.
Possible values:
- 0 (or 1) —
INSERT SELECT
no parallel execution. - Positive integer. Bigger than 1.
Cloud default value: from 2
to 4
, depending on the service size.
Parallel INSERT SELECT
has effect only if the SELECT
part is executed in parallel, see max_threads setting.
Higher values will lead to higher memory usage.
max_joined_block_size_rows
Type | Default value |
---|---|
Type UInt64 | Default value 65409 |
max_limit_for_ann_queries
Type | Default value |
---|---|
Type UInt64 | Default value 1000000 |
max_live_view_insert_blocks_before_refresh
Type | Default value |
---|---|
Type UInt64 | Default value 64 |
max_local_read_bandwidth
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_local_write_bandwidth
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_memory_usage
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
The maximum amount of RAM to use for running a query on a single server.
A value of 0
means unlimited.
This setting does not consider the volume of available memory or the total volume of memory on the machine. The restriction applies to a single query within a single server.
You can use SHOW PROCESSLIST
to see the current memory consumption for each query.
Peak memory consumption is tracked for each query and written to the log.
Memory usage is not fully tracked for states of the following aggregate functions
from String
and Array
arguments:
min
max
any
anyLast
argMin
argMax
Memory consumption is also restricted by the parameters max_memory_usage_for_user
and max_server_memory_usage
.
max_memory_usage_for_user
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
By default, the amount is not restricted (max_memory_usage_for_user = 0
).
Also see the description of max_memory_usage
.
For example if you want to set max_memory_usage_for_user
to 1000 bytes for a user named clickhouse_read
, you can use the statement
You can verify it worked by logging out of your client, logging back in, then use the getSetting
function:
max_network_bandwidth
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 — Bandwidth control is disabled.
max_network_bandwidth_for_all_users
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 — Control of the data speed is disabled.
max_network_bandwidth_for_user
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 — Control of the data speed is disabled.
max_network_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 — Data volume control is disabled.
max_number_of_partitions_for_independent_aggregation
Type | Default value |
---|---|
Type UInt64 | Default value 128 |
max_os_cpu_wait_time_ratio_to_throw
Type | Default value |
---|---|
Type Float | Default value 6 |
Max ratio between OS CPU wait (OSCPUWaitMicroseconds metric) and busy (OSCPUVirtualTimeMicroseconds metric) times to consider rejecting queries. Linear interpolation between min and max ratio is used to calculate the probability, the probability is 1 at this point.
max_parallel_replicas
Type | Default value |
---|---|
Type NonZeroUInt64 | Default value 1000 |
The maximum number of replicas for each shard when executing a query.
Possible values:
- Positive integer.
Additional Info
This options will produce different results depending on the settings used.
This setting will produce incorrect results when joins or subqueries are involved, and all tables don't meet certain requirements. See Distributed Subqueries and max_parallel_replicas for more details.
Parallel processing using SAMPLE
key
A query may be processed faster if it is executed on several servers in parallel. But the query performance may degrade in the following cases:
- The position of the sampling key in the partitioning key does not allow efficient range scans.
- Adding a sampling key to the table makes filtering by other columns less efficient.
- The sampling key is an expression that is expensive to calculate.
- The cluster latency distribution has a long tail, so that querying more servers increases the query overall latency.
Parallel processing using parallel_replicas_custom_key
This setting is useful for any replicated table.
max_parser_backtracks
Type | Default value |
---|---|
Type UInt64 | Default value 1000000 |
Maximum parser backtracking (how many times it tries different alternatives in the recursive descend parsing process).
max_parser_depth
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Possible values:
- Positive integer.
- 0 — Recursion depth is unlimited.
max_parsing_threads
Type | Default value |
---|---|
Type MaxThreads | Default value 'auto(4)' |
The maximum number of threads to parse data in input formats that support parallel parsing. By default, it is determined automatically
max_partition_size_to_drop
Type | Default value |
---|---|
Type UInt64 | Default value 50000000000 |
Cloud default value: 1 TB.
This query setting overwrites its server setting equivalent, see max_partition_size_to_drop
max_partitions_per_insert_block
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
Limits the maximum number of partitions in a single inserted block and an exception is thrown if the block contains too many partitions.
- Positive integer.
0
— Unlimited number of partitions.
Details
When inserting data, ClickHouse calculates the number of partitions in the
inserted block. If the number of partitions is more than
max_partitions_per_insert_block
, ClickHouse either logs a warning or throws an
exception based on throw_on_max_partitions_per_insert_block
. Exceptions have
the following text:
"Too many partitions for a single INSERT block (
partitions_count
partitions, limit is " + toString(max_partitions) + "). The limit is controlled by the 'max_partitions_per_insert_block' setting. A large number of partitions is a common misconception. It will lead to severe negative performance impact, including slow server startup, slow INSERT queries and slow SELECT queries. Recommended total number of partitions for a table is under 1000..10000. Please note, that partitioning is not intended to speed up SELECT queries (ORDER BY key is sufficient to make range queries fast). Partitions are intended for data manipulation (DROP PARTITION, etc)."
This setting is a safety threshold because using a large number of partitions is a common misconception.
max_partitions_to_read
Type | Default value |
---|---|
Type Int64 | Default value -1 |
The setting value specified when the table is created can be overridden via query-level setting.
Possible values:
- Positive integer
-1
- unlimited (default)
You can also specify the MergeTree setting max_partitions_to_read
in tables' setting.
max_parts_to_move
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Limit the number of parts that can be moved in one query. Zero means unlimited.
max_query_size
Type | Default value |
---|---|
Type UInt64 | Default value 262144 |
max_query_size
cannot be set within an SQL query (e.g., SELECT now() SETTINGS max_query_size=10000
) because ClickHouse needs to allocate a buffer to parse the query, and this buffer size is determined by the max_query_size
setting, which must be configured before the query is executed.
max_read_buffer_size
Type | Default value |
---|---|
Type NonZeroUInt64 | Default value 1048576 |
max_read_buffer_size_local_fs
Type | Default value |
---|---|
Type UInt64 | Default value 131072 |
max_read_buffer_size_remote_fs
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_recursive_cte_evaluation_depth
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Maximum limit on recursive CTE evaluation depth
max_remote_read_network_bandwidth
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_remote_write_network_bandwidth
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_replica_delay_for_distributed_queries
Type | Default value |
---|---|
Type UInt64 | Default value 300 |
Sets the time in seconds. If a replica's lag is greater than or equal to the set value, this replica is not used.
Possible values:
- Positive integer.
- 0 — Replica lags are not checked.
To prevent the use of any replica with a non-zero lag, set this parameter to 1.
Used when performing SELECT
from a distributed table that points to replicated tables.
max_result_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Caveats
The result size in memory is taken into account for this threshold. Even if the result size is small, it can reference larger data structures in memory, representing dictionaries of LowCardinality columns, and Arenas of AggregateFunction columns, so the threshold can be exceeded despite the small result size.
The setting is fairly low level and should be used with caution
max_result_rows
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
0
.
Limits the number of rows in the result. Also checked for subqueries, and on remote servers when running parts of a distributed query.
No limit is applied when the value is 0
.
The query will stop after processing a block of data if the threshold is met, but it will not cut the last block of the result, therefore the result size can be larger than the threshold.
max_rows_in_distinct
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_rows_in_join
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
This settings applies to SELECT ... JOIN operations and the Join table engine.
If a query contains multiple joins, ClickHouse checks this setting for every intermediate result.
ClickHouse can proceed with different actions when the limit is reached. Use the
join_overflow_mode
setting to choose the action.
Possible values:
- Positive integer.
0
— Unlimited number of rows.
max_rows_in_set
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_rows_in_set_to_optimize_join
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Maximal size of the set to filter joined tables by each other's row sets before joining.
Possible values:
- 0 — Disable.
- Any positive integer.
max_rows_to_group_by
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
If aggregation during GROUP BY is generating more than the specified number of
rows (unique GROUP BY keys), the behavior will be determined by the
'group_by_overflow_mode' which by default is throw
, but can be also switched
to an approximate GROUP BY mode.
max_rows_to_read
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_rows_to_read_leaf
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
For example, a cluster consists of 2 shards and each shard contains a table with
100 rows. The distributed query which is supposed to read all the data from both
tables with setting max_rows_to_read=150
will fail, as in total there will be
200 rows. A query with max_rows_to_read_leaf=150
will succeed, since leaf nodes
will read at max 100 rows.
The restriction is checked for each processed chunk of data.
This setting is unstable with prefer_localhost_replica=1
.
max_rows_to_sort
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
sort_overflow_mode
which by default is set to throw
.
max_rows_to_transfer
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_sessions_for_user
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Example:
Possible values:
- Positive integer
0
- infinite count of simultaneous sessions (default)
max_size_to_preallocate_for_aggregation
Type | Default value |
---|---|
Type UInt64 | Default value 1000000000000 |
For how many elements it is allowed to preallocate space in all hash tables in total before aggregatio
max_size_to_preallocate_for_joins
Type | Default value |
---|---|
Type UInt64 | Default value 1000000000000 |
For how many elements it is allowed to preallocate space in all hash tables in total before joi
max_streams_for_merge_tree_reading
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_streams_multiplier_for_merge_tables
Type | Default value |
---|---|
Type Float | Default value 5 |
max_streams_to_max_threads_ratio
Type | Default value |
---|---|
Type Float | Default value 1 |
max_subquery_depth
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
This allows you to have a sanity check to protect against the users of your cluster from writing overly complex queries.
max_table_size_to_drop
Type | Default value |
---|---|
Type UInt64 | Default value 50000000000 |
Cloud default value: 1 TB.
This query setting overwrites its server setting equivalent, see max_table_size_to_drop
max_temporary_columns
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
This setting is useful for preventing overly complex queries.
0
value means unlimited.
max_temporary_data_on_disk_size_for_query
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
0
— unlimited (default)
max_temporary_data_on_disk_size_for_user
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
0
— unlimited (default)
max_temporary_non_const_columns
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_temporary_columns
, the maximum number of temporary columns that must
be kept in RAM simultaneously when running a query, but without counting constant
columns.
Constant columns are formed fairly often when running a query, but they require approximately zero computing resources.
max_threads
Type | Default value |
---|---|
Type MaxThreads | Default value 'auto(4)' |
This parameter applies to threads that perform the same stages of the query processing pipeline in parallel. For example, when reading from a table, if it is possible to evaluate expressions with functions, filter with WHERE and pre-aggregate for GROUP BY in parallel using at least 'max_threads' number of threads, then 'max_threads' are used.
For queries that are completed quickly because of a LIMIT, you can set a lower 'max_threads'. For example, if the necessary number of entries are located in every block and max_threads = 8, then 8 blocks are retrieved, although it would have been enough to read just one.
The smaller the max_threads
value, the less memory is consumed.
max_threads_for_indexes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
max_untracked_memory
Type | Default value |
---|---|
Type UInt64 | Default value 4194304 |
memory_overcommit_ratio_denominator
Type | Default value |
---|---|
Type UInt64 | Default value 1073741824 |
It represents the soft memory limit when the hard limit is reached on the global level. This value is used to compute the overcommit ratio for the query. Zero means skip the query. Read more about memory overcommit.
memory_overcommit_ratio_denominator_for_user
Type | Default value |
---|---|
Type UInt64 | Default value 1073741824 |
It represents the soft memory limit when the hard limit is reached on the user level. This value is used to compute the overcommit ratio for the query. Zero means skip the query. Read more about memory overcommit.
memory_profiler_sample_max_allocation_size
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
memory_profiler_sample_probability
. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold work as expected.
memory_profiler_sample_min_allocation_size
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
memory_profiler_sample_probability
. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold work as expected.
memory_profiler_sample_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
memory_profiler_sample_min_allocation_size
and memory_profiler_sample_max_allocation_size
). Note that sampling happens only when the amount of untracked memory exceeds 'max_untracked_memory'. You may want to set 'max_untracked_memory' to 0 for extra fine-grained sampling.
memory_profiler_step
Type | Default value |
---|---|
Type UInt64 | Default value 4194304 |
Possible values:
-
A positive integer number of bytes.
-
0 for turning off the memory profiler.
memory_tracker_fault_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
exception safety
- throw an exception every time you allocate memory with the specified probability.
memory_usage_overcommit_max_wait_microseconds
Type | Default value |
---|---|
Type UInt64 | Default value 5000000 |
merge_table_max_tables_to_look_for_schema_inference
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
When creating a Merge
table without an explicit schema or when using the merge
table function, infer schema as a union of not more than the specified number of matching tables.
If there is a larger number of tables, the schema will be inferred from the first specified number of tables.
merge_tree_coarse_index_granularity
Type | Default value |
---|---|
Type UInt64 | Default value 8 |
merge_tree_coarse_index_granularity
subranges and searches the required keys there recursively.
Possible values:
- Any positive even integer.
merge_tree_compact_parts_min_granules_to_multibuffer_read
Type | Default value |
---|---|
Type UInt64 | Default value 16 |
merge_tree_determine_task_size_by_prewhere_columns
Type | Default value |
---|---|
Type Bool | Default value 1 |
merge_tree_max_bytes_to_use_cache
Type | Default value |
---|---|
Type UInt64 | Default value 2013265920 |
merge_tree_max_bytes_to_use_cache
bytes in one query, it does not use the cache of uncompressed blocks.
The cache of uncompressed blocks stores data extracted for queries. ClickHouse uses this cache to speed up responses to repeated small queries. This setting protects the cache from trashing by queries that read a large amount of data. The uncompressed_cache_size server setting defines the size of the cache of uncompressed blocks.
Possible values:
- Any positive integer.
merge_tree_max_rows_to_use_cache
Type | Default value |
---|---|
Type UInt64 | Default value 1048576 |
merge_tree_max_rows_to_use_cache
rows in one query, it does not use the cache of uncompressed blocks.
The cache of uncompressed blocks stores data extracted for queries. ClickHouse uses this cache to speed up responses to repeated small queries. This setting protects the cache from trashing by queries that read a large amount of data. The uncompressed_cache_size server setting defines the size of the cache of uncompressed blocks.
Possible values:
- Any positive integer.
merge_tree_min_bytes_for_concurrent_read
Type | Default value |
---|---|
Type UInt64 | Default value 251658240 |
merge_tree_min_bytes_for_concurrent_read
, then ClickHouse tries to concurrently read from this file in several threads.
Possible value:
- Positive integer.
merge_tree_min_bytes_for_concurrent_read_for_remote_filesystem
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
The minimum number of bytes to read from one file before MergeTree engine can parallelize reading, when reading from remote filesystem. We do not recommend using this setting.
Possible values:
- Positive integer.
merge_tree_min_bytes_for_seek
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
merge_tree_min_bytes_for_seek
bytes, then ClickHouse sequentially reads a range of file that contains both blocks, thus avoiding extra seek.
Possible values:
- Any positive integer.
merge_tree_min_bytes_per_task_for_remote_reading
Type | Default value |
---|---|
Type UInt64 | Default value 2097152 |
Min bytes to read per task.
merge_tree_min_read_task_size
Type | Default value |
---|---|
Type UInt64 | Default value 8 |
Hard lower limit on the task size (even when the number of granules is low and the number of available threads is high we won't allocate smaller tasks
merge_tree_min_rows_for_concurrent_read
Type | Default value |
---|---|
Type UInt64 | Default value 163840 |
merge_tree_min_rows_for_concurrent_read
then ClickHouse tries to perform a concurrent reading from this file on several threads.
Possible values:
- Positive integer.
merge_tree_min_rows_for_concurrent_read_for_remote_filesystem
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
The minimum number of lines to read from one file before the MergeTree engine can parallelize reading, when reading from remote filesystem. We do not recommend using this setting.
Possible values:
- Positive integer.
merge_tree_min_rows_for_seek
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
merge_tree_min_rows_for_seek
rows, then ClickHouse does not seek through the file but reads the data sequentially.
Possible values:
- Any positive integer.
merge_tree_read_split_ranges_into_intersecting_and_non_intersecting_injection_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
For testing of PartsSplitter
- split read ranges into intersecting and non intersecting every time you read from MergeTree with the specified probability.
merge_tree_use_const_size_tasks_for_remote_reading
Type | Default value |
---|---|
Type Bool | Default value 1 |
merge_tree_use_deserialization_prefixes_cache
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables caching of columns metadata from the file prefixes during reading from Wide parts in MergeTree.
merge_tree_use_prefixes_deserialization_thread_pool
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables usage of the thread pool for parallel prefixes reading in Wide parts in MergeTree. Size of that thread pool is controlled by server setting max_prefixes_deserialization_thread_pool_size
.
merge_tree_use_v1_object_and_dynamic_serialization
Type | Default value |
---|---|
Type Bool | Default value 0 |
When enabled, V1 serialization version of JSON and Dynamic types will be used in MergeTree instead of V2. Changing this setting takes affect only after server restart.
metrics_perf_events_enabled
Type | Default value |
---|---|
Type Bool | Default value 0 |
metrics_perf_events_list
Comma separated list of perf metrics that will be measured throughout queries' execution. Empty means all events. See PerfEventInfo in sources for the available events.
min_bytes_to_use_direct_io
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
ClickHouse uses this setting when reading data from tables. If the total storage volume of all the data to be read exceeds min_bytes_to_use_direct_io
bytes, then ClickHouse reads the data from the storage disk with the O_DIRECT
option.
Possible values:
- 0 — Direct I/O is disabled.
- Positive integer.
min_bytes_to_use_mmap_io
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer.
- 0 — Big files read with only copying data from kernel to userspace.
min_chunk_bytes_for_parallel_parsing
Type | Default value |
---|---|
Type NonZeroUInt64 | Default value 10485760 |
- Default value: 1 MiB
The minimum chunk size in bytes, which each thread will parse in parallel.
min_compress_block_size
Type | Default value |
---|---|
Type UInt64 | Default value 65536 |
min_compress_block_size
. By default, 65,536.
The actual size of the block, if the uncompressed data is less than max_compress_block_size
, is no less than this value and no less than the volume of data for one mark.
Let's look at an example. Assume that index_granularity
was set to 8192 during table creation.
We are writing a UInt32-type column (4 bytes per value). When writing 8192 rows, the total will be 32 KB of data. Since min_compress_block_size = 65,536, a compressed block will be formed for every two marks.
We are writing a URL column with the String type (average size of 60 bytes per value). When writing 8192 rows, the average will be slightly less than 500 KB of data. Since this is more than 65,536, a compressed block will be formed for each mark. In this case, when reading data from the disk in the range of a single mark, extra data won't be decompressed.
This is an expert-level setting, and you shouldn't change it if you're just getting started with ClickHouse.
min_count_to_compile_aggregate_expression
Type | Default value |
---|---|
Type UInt64 | Default value 3 |
Possible values:
- Positive integer.
- 0 — Identical aggregate expressions are always JIT-compiled.
min_count_to_compile_expression
Type | Default value |
---|---|
Type UInt64 | Default value 3 |
min_count_to_compile_sort_description
Type | Default value |
---|---|
Type UInt64 | Default value 3 |
min_execution_speed
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
timeout_before_checking_execution_speed
expires. If the execution speed is lower, an exception is thrown.
min_execution_speed_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
timeout_before_checking_execution_speed
expires. If the execution speed is lower, an exception is thrown.
min_external_sort_block_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 104857600 |
Minimal block size in bytes for external sort that will be dumped to disk, to avoid too many files.
min_external_table_block_size_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 268402944 |
Squash blocks passed to the external table to a specified size in bytes, if blocks are not big enough.
min_external_table_block_size_rows
Type | Default value |
---|---|
Type UInt64 | Default value 1048449 |
Squash blocks passed to external table to specified size in rows, if blocks are not big enough.
min_free_disk_bytes_to_perform_insert
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Minimum free disk space bytes to perform an insert.
min_free_disk_ratio_to_perform_insert
Type | Default value |
---|---|
Type Float | Default value 0 |
Minimum free disk space ratio to perform an insert.
min_free_disk_space_for_temporary_data
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
min_hit_rate_to_use_consecutive_keys_optimization
Type | Default value |
---|---|
Type Float | Default value 0.5 |
min_insert_block_size_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 268402944 |
INSERT
query. Smaller-sized blocks are squashed into bigger ones.
Possible values:
- Positive integer.
- 0 — Squashing disabled.
min_insert_block_size_bytes_for_materialized_views
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
INSERT
query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into materialized view. By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage.
Possible values:
- Any positive integer.
- 0 — Squashing disabled.
See also
min_insert_block_size_rows
Type | Default value |
---|---|
Type UInt64 | Default value 1048449 |
INSERT
query. Smaller-sized blocks are squashed into bigger ones.
Possible values:
- Positive integer.
- 0 — Squashing disabled.
min_insert_block_size_rows_for_materialized_views
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
INSERT
query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into materialized view. By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage.
Possible values:
- Any positive integer.
- 0 — Squashing disabled.
See Also
min_joined_block_size_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 524288 |
Minimum block size for JOIN result (if join algorithm supports it). 0 means unlimited.
min_os_cpu_wait_time_ratio_to_throw
Type | Default value |
---|---|
Type Float | Default value 2 |
Min ratio between OS CPU wait (OSCPUWaitMicroseconds metric) and busy (OSCPUVirtualTimeMicroseconds metric) times to consider rejecting queries. Linear interpolation between min and max ratio is used to calculate the probability, the probability is 0 at this point.
mongodb_throw_on_unsupported_query
Type | Default value |
---|---|
Type Bool | Default value 1 |
If enabled, MongoDB tables will return an error when a MongoDB query cannot be built. Otherwise, ClickHouse reads the full table and processes it locally. This option does not apply when 'allow_experimental_analyzer=0'.
move_all_conditions_to_prewhere
Type | Default value |
---|---|
Type Bool | Default value 1 |
move_primary_key_columns_to_end_of_prewhere
Type | Default value |
---|---|
Type Bool | Default value 1 |
multiple_joins_try_to_keep_original_names
Type | Default value |
---|---|
Type Bool | Default value 0 |
mutations_execute_nondeterministic_on_initiator
Type | Default value |
---|---|
Type Bool | Default value 0 |
now()
) are executed on initiator and replaced to literals in UPDATE
and DELETE
queries. It helps to keep data in sync on replicas while executing mutations with constant nondeterministic functions. Default value: false
.
mutations_execute_subqueries_on_initiator
Type | Default value |
---|---|
Type Bool | Default value 0 |
UPDATE
and DELETE
queries. Default value: false
.
mutations_max_literal_size_to_replace
Type | Default value |
---|---|
Type UInt64 | Default value 16384 |
UPDATE
and DELETE
queries. Takes effect only if at least one the two settings above is enabled. Default value: 16384 (16 KiB).
mutations_sync
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
ALTER TABLE ... UPDATE|DELETE|MATERIALIZE INDEX|MATERIALIZE PROJECTION|MATERIALIZE COLUMN|MATERIALIZE STATISTICS
queries (mutations) synchronously.
Possible values:
- 0 - Mutations execute asynchronously.
- 1 - The query waits for all mutations to complete on the current server.
- 2 - The query waits for all mutations to complete on all replicas (if they exist).
mysql_datatypes_support_level
Defines how MySQL types are converted to corresponding ClickHouse types. A comma separated list in any combination of decimal
, datetime64
, date2Date32
or date2String
.
decimal
: convertNUMERIC
andDECIMAL
types toDecimal
when precision allows it.datetime64
: convertDATETIME
andTIMESTAMP
types toDateTime64
instead ofDateTime
when precision is not0
.date2Date32
: convertDATE
toDate32
instead ofDate
. Takes precedence overdate2String
.date2String
: convertDATE
toString
instead ofDate
. Overridden bydatetime64
.
mysql_map_fixed_string_to_text_in_show_columns
Type | Default value |
---|---|
Type Bool | Default value 1 |
When enabled, FixedString ClickHouse data type will be displayed as TEXT
in SHOW COLUMNS.
Has an effect only when the connection is made through the MySQL wire protocol.
- 0 - Use
BLOB
. - 1 - Use
TEXT
.
mysql_map_string_to_text_in_show_columns
Type | Default value |
---|---|
Type Bool | Default value 1 |
When enabled, String ClickHouse data type will be displayed as TEXT
in SHOW COLUMNS.
Has an effect only when the connection is made through the MySQL wire protocol.
- 0 - Use
BLOB
. - 1 - Use
TEXT
.
mysql_max_rows_to_insert
Type | Default value |
---|---|
Type UInt64 | Default value 65536 |
network_compression_method
Type | Default value |
---|---|
Type String | Default value LZ4 |
Possible values:
LZ4
— sets LZ4 compression method.ZSTD
— sets ZSTD compression method.
See Also
network_zstd_compression_level
Type | Default value |
---|---|
Type Int64 | Default value 1 |
ZSTD
.
Possible values:
- Positive integer from 1 to 15.
normalize_function_names
Type | Default value |
---|---|
Type Bool | Default value 1 |
Normalize function names to their canonical names
number_of_mutations_to_delay
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
number_of_mutations_to_throw
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
odbc_bridge_connection_pool_size
Type | Default value |
---|---|
Type UInt64 | Default value 16 |
odbc_bridge_use_connection_pooling
Type | Default value |
---|---|
Type Bool | Default value 1 |
offset
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- 0 — No rows are skipped .
- Positive integer.
Example
Input table:
Query:
Result:
opentelemetry_start_trace_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
Possible values:
- 0 — The trace for all executed queries is disabled (if no parent trace context is supplied).
- Positive floating-point number in the range [0..1]. For example, if the setting value is
0,5
, ClickHouse can start a trace on average for half of the queries. - 1 — The trace for all executed queries is enabled.
opentelemetry_trace_processors
Type | Default value |
---|---|
Type Bool | Default value 0 |
optimize_aggregation_in_order
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
GROUP BY
optimization is disabled. - 1 —
GROUP BY
optimization is enabled.
See Also
optimize_aggregators_of_group_by_keys
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_and_compare_chain
Type | Default value |
---|---|
Type Bool | Default value 1 |
Populate constant comparison in AND chains to enhance filtering ability. Support operators <
, <=
, >
, >=
, =
and mix of them. For example, (a < b) AND (b < c) AND (c < 5)
would be (a < b) AND (b < c) AND (c < 5) AND (b < 5) AND (a < 5)
.
optimize_append_index
Type | Default value |
---|---|
Type Bool | Default value 0 |
false
.
Possible values:
- true, false
optimize_arithmetic_operations_in_aggregate_functions
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_count_from_files
Type | Default value |
---|---|
Type Bool | Default value 1 |
file
/s3
/url
/hdfs
/azureBlobStorage
.
Possible values:
- 0 — Optimization disabled.
- 1 — Optimization enabled.
optimize_distinct_in_order
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_distributed_group_by_sharding_key
Type | Default value |
---|---|
Type Bool | Default value 1 |
GROUP BY sharding_key
queries, by avoiding costly aggregation on the initiator server (which will reduce memory usage for the query on the initiator server).
The following types of queries are supported (and all combinations of them):
SELECT DISTINCT [..., ]sharding_key[, ...] FROM dist
SELECT ... FROM dist GROUP BY sharding_key[, ...]
SELECT ... FROM dist GROUP BY sharding_key[, ...] ORDER BY x
SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1
SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1 BY x
The following types of queries are not supported (support for some of them may be added later):
SELECT ... GROUP BY sharding_key[, ...] WITH TOTALS
SELECT ... GROUP BY sharding_key[, ...] WITH ROLLUP
SELECT ... GROUP BY sharding_key[, ...] WITH CUBE
SELECT ... GROUP BY sharding_key[, ...] SETTINGS extremes=1
Possible values:
- 0 — Disabled.
- 1 — Enabled.
See also:
Right now it requires optimize_skip_unused_shards
(the reason behind this is that one day it may be enabled by default, and it will work correctly only if data was inserted via Distributed table, i.e. data is distributed according to sharding_key).
optimize_extract_common_expressions
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allow extracting common expressions from disjunctions in WHERE, PREWHERE, ON, HAVING and QUALIFY expressions. A logical expression like (A AND B) OR (A AND C)
can be rewritten to A AND (B OR C)
, which might help to utilize:
- indices in simple filtering expressions
- cross to inner join optimizatio
optimize_functions_to_subcolumns
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables or disables optimization by transforming some functions to reading subcolumns. This reduces the amount of data to read.
These functions can be transformed:
- length to read the size0 subcolumn.
- empty to read the size0 subcolumn.
- notEmpty to read the size0 subcolumn.
- isNull to read the null subcolumn.
- isNotNull to read the null subcolumn.
- count to read the null subcolumn.
- mapKeys to read the keys subcolumn.
- mapValues to read the values subcolumn.
Possible values:
- 0 — Optimization disabled.
- 1 — Optimization enabled.
optimize_group_by_constant_keys
Type | Default value |
---|---|
Type Bool | Default value 1 |
Optimize GROUP BY when all keys in block are constant
optimize_group_by_function_keys
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_if_chain_to_multiif
Type | Default value |
---|---|
Type Bool | Default value 0 |
optimize_if_transform_strings_to_enum
Type | Default value |
---|---|
Type Bool | Default value 0 |
optimize_injective_functions_in_group_by
Type | Default value |
---|---|
Type Bool | Default value 1 |
Replaces injective functions by it's arguments in GROUP BY sectio
optimize_injective_functions_inside_uniq
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_min_equality_disjunction_chain_length
Type | Default value |
---|---|
Type UInt64 | Default value 3 |
expr = x1 OR ... expr = xN
for optimizatio
optimize_min_inequality_conjunction_chain_length
Type | Default value |
---|---|
Type UInt64 | Default value 3 |
expr <> x1 AND ... expr <> xN
for optimizatio
optimize_move_to_prewhere
Type | Default value |
---|---|
Type Bool | Default value 1 |
Works only for *MergeTree tables.
Possible values:
- 0 — Automatic
PREWHERE
optimization is disabled. - 1 — Automatic
PREWHERE
optimization is enabled.
optimize_move_to_prewhere_if_final
Type | Default value |
---|---|
Type Bool | Default value 0 |
Works only for *MergeTree tables.
Possible values:
- 0 — Automatic
PREWHERE
optimization inSELECT
queries withFINAL
modifier is disabled. - 1 — Automatic
PREWHERE
optimization inSELECT
queries withFINAL
modifier is enabled.
See Also
- optimize_move_to_prewhere setting
optimize_multiif_to_if
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_normalize_count_variants
Type | Default value |
---|---|
Type Bool | Default value 1 |
Rewrite aggregate functions that semantically equals to count() as count().
optimize_on_insert
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables or disables data transformation before the insertion, as if merge was done on this block (according to table engine).
Possible values:
- 0 — Disabled.
- 1 — Enabled.
Example
The difference between enabled and disabled:
Query:
Result:
Note that this setting influences Materialized view behaviour.
optimize_or_like_chain
Type | Default value |
---|---|
Type Bool | Default value 0 |
optimize_read_in_order
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 0 —
ORDER BY
optimization is disabled. - 1 —
ORDER BY
optimization is enabled.
See Also
optimize_read_in_window_order
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_redundant_functions_in_order_by
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_respect_aliases
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_rewrite_aggregate_function_with_if
Type | Default value |
---|---|
Type Bool | Default value 1 |
avg(if(cond, col, null))
can be rewritten to avgOrNullIf(cond, col)
. It may improve performance.
Supported only with the analyzer (enable_analyzer = 1
).
optimize_rewrite_array_exists_to_has
Type | Default value |
---|---|
Type Bool | Default value 0 |
optimize_rewrite_sum_if_to_count_if
Type | Default value |
---|---|
Type Bool | Default value 1 |
Rewrite sumIf() and sum(if()) function countIf() function when logically equivalent
optimize_skip_merged_partitions
Type | Default value |
---|---|
Type Bool | Default value 0 |
OPTIMIZE TABLE ... FINAL SETTINGS optimize_skip_merged_partitions=1
By default, OPTIMIZE TABLE ... FINAL
query rewrites the one part even if there is only a single part.
Possible values:
- 1 - Enable optimization.
- 0 - Disable optimization.
optimize_skip_unused_shards
Type | Default value |
---|---|
Type Bool | Default value 0 |
WHERE/PREWHERE
(assuming that the data is distributed by sharding key, otherwise a query yields incorrect result).
Possible values:
- 0 — Disabled.
- 1 — Enabled.
optimize_skip_unused_shards_limit
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
optimize_skip_unused_shards
if the limit is reached.
Too many values may require significant amount for processing, while the benefit is doubtful, since if you have huge number of values in IN (...)
, then most likely the query will be sent to all shards anyway.
optimize_skip_unused_shards_nesting
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
optimize_skip_unused_shards
(hence still requires optimize_skip_unused_shards
) depends on the nesting level of the distributed query (case when you have Distributed
table that look into another Distributed
table).
Possible values:
- 0 — Disabled,
optimize_skip_unused_shards
works always. - 1 — Enables
optimize_skip_unused_shards
only for the first level. - 2 — Enables
optimize_skip_unused_shards
up to the second level.
optimize_skip_unused_shards_rewrite_in
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 0 — Disabled.
- 1 — Enabled.
optimize_sorting_by_input_stream_properties
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_substitute_columns
Type | Default value |
---|---|
Type Bool | Default value 0 |
false
.
Possible values:
- true, false
optimize_syntax_fuse_functions
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — Functions with identical argument are not fused.
- 1 — Functions with identical argument are fused.
Example
Query:
Result:
optimize_throw_if_noop
Type | Default value |
---|---|
Type Bool | Default value 0 |
By default, OPTIMIZE
returns successfully even if it didn't do anything. This setting lets you differentiate these situations and get the reason in an exception message.
Possible values:
- 1 — Throwing an exception is enabled.
- 0 — Throwing an exception is disabled.
optimize_time_filter_with_preimage
Type | Default value |
---|---|
Type Bool | Default value 1 |
Optimize Date and DateTime predicates by converting functions into equivalent comparisons without conversions (e.g. toYear(col) = 2023 -> col >= '2023-01-01' AND col <= '2023-12-31'
)
optimize_trivial_approximate_count_query
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — Optimization disabled.
- 1 — Optimization enabled.
optimize_trivial_count_query
Type | Default value |
---|---|
Type Bool | Default value 1 |
SELECT count() FROM table
using metadata from MergeTree. If you need to use row-level security, disable this setting.
Possible values:
- 0 — Optimization disabled.
- 1 — Optimization enabled.
See also:
optimize_trivial_insert_select
Type | Default value |
---|---|
Type Bool | Default value 0 |
Optimize trivial 'INSERT INTO table SELECT ... FROM TABLES' query
optimize_uniq_to_count
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_use_implicit_projections
Type | Default value |
---|---|
Type Bool | Default value 1 |
optimize_use_projections
Type | Default value |
---|---|
Type Bool | Default value 1 |
SELECT
queries.
Possible values:
- 0 — Projection optimization disabled.
- 1 — Projection optimization enabled.
optimize_using_constraints
Type | Default value |
---|---|
Type Bool | Default value 0 |
false
.
Possible values:
- true, false
os_thread_priority
Type | Default value |
---|---|
Type Int64 | Default value 0 |
To use this setting, you need to set the CAP_SYS_NICE
capability. The clickhouse-server
package sets it up during installation. Some virtual environments do not allow you to set the CAP_SYS_NICE
capability. In this case, clickhouse-server
shows a message about it at the start.
Possible values:
- You can set values in the range
[-20, 19]
.
Lower values mean higher priority. Threads with low nice
priority values are executed more frequently than threads with high values. High values are preferable for long-running non-interactive queries because it allows them to quickly give up resources in favour of short interactive queries when they arrive.
output_format_compression_level
Type | Default value |
---|---|
Type UInt64 | Default value 3 |
Default compression level if query output is compressed. The setting is applied when SELECT
query has INTO OUTFILE
or when writing to table functions file
, url
, hdfs
, s3
, or azureBlobStorage
.
Possible values: from 1
to 22
output_format_compression_zstd_window_log
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Can be used when the output compression method is zstd
. If greater than 0
, this setting explicitly sets compression window size (power of 2
) and enables a long-range mode for zstd compression. This can help to achieve a better compression ratio.
Possible values: non-negative numbers. Note that if the value is too small or too big, zstdlib
will throw an exception. Typical values are from 20
(window size = 1MB
) to 30
(window size = 1GB
).
output_format_parallel_formatting
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 1 — Enabled.
- 0 — Disabled.
page_cache_inject_eviction
Type | Default value |
---|---|
Type Bool | Default value 0 |
Userspace page cache will sometimes invalidate some pages at random. Intended for testing.
parallel_distributed_insert_select
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
INSERT ... SELECT
query.
If we execute INSERT INTO distributed_table_a SELECT ... FROM distributed_table_b
queries and both tables use the same cluster, and both tables are either replicated or non-replicated, then this query is processed locally on every shard.
Possible values:
- 0 — Disabled.
- 1 —
SELECT
will be executed on each shard from the underlying table of the distributed engine. - 2 —
SELECT
andINSERT
will be executed on each shard from/to the underlying table of the distributed engine.
parallel_replica_offset
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
parallel_replicas_allow_in_with_subquery
Type | Default value |
---|---|
Type Bool | Default value 1 |
If true, subquery for IN will be executed on every follower replica.
parallel_replicas_count
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
parallel_replicas_custom_key
An arbitrary integer expression that can be used to split work between replicas for a specific table. The value can be any integer expression.
Simple expressions using primary keys are preferred.
If the setting is used on a cluster that consists of a single shard with multiple replicas, those replicas will be converted into virtual shards.
Otherwise, it will behave same as for SAMPLE
key, it will use multiple replicas of each shard.
parallel_replicas_custom_key_range_lower
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Allows the filter type range
to split the work evenly between replicas based on the custom range [parallel_replicas_custom_key_range_lower, INT_MAX]
.
When used in conjunction with parallel_replicas_custom_key_range_upper, it lets the filter evenly split the work over replicas for the range [parallel_replicas_custom_key_range_lower, parallel_replicas_custom_key_range_upper]
.
Note: This setting will not cause any additional data to be filtered during query processing, rather it changes the points at which the range filter breaks up the range [0, INT_MAX]
for parallel processing.
parallel_replicas_custom_key_range_upper
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Allows the filter type range
to split the work evenly between replicas based on the custom range [0, parallel_replicas_custom_key_range_upper]
. A value of 0 disables the upper bound, setting it the max value of the custom key expression.
When used in conjunction with parallel_replicas_custom_key_range_lower, it lets the filter evenly split the work over replicas for the range [parallel_replicas_custom_key_range_lower, parallel_replicas_custom_key_range_upper]
.
Note: This setting will not cause any additional data to be filtered during query processing, rather it changes the points at which the range filter breaks up the range [0, INT_MAX]
for parallel processing
parallel_replicas_for_cluster_engines
Type | Default value |
---|---|
Type Bool | Default value 1 |
Replace table function engines with their -Cluster alternatives
parallel_replicas_for_non_replicated_merge_tree
Type | Default value |
---|---|
Type Bool | Default value 0 |
parallel_replicas_index_analysis_only_on_coordinator
Type | Default value |
---|---|
Type Bool | Default value 1 |
Index analysis done only on replica-coordinator and skipped on other replicas. Effective only with enabled parallel_replicas_local_pla
parallel_replicas_local_plan
Type | Default value |
---|---|
Type Bool | Default value 1 |
Build local plan for local replica
parallel_replicas_mark_segment_size
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Parts virtually divided into segments to be distributed between replicas for parallel reading. This setting controls the size of these segments. Not recommended to change until you're absolutely sure in what you're doing. Value should be in range [128; 16384]
parallel_replicas_min_number_of_rows_per_replica
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
parallel_replicas_mode
Type | Default value |
---|---|
Type ParallelReplicasMode | Default value read_tasks |
Type of filter to use with custom key for parallel replicas. default - use modulo operation on the custom key, range - use range filter on custom key using all possible values for the value type of custom key.
parallel_replicas_only_with_analyzer
Type | Default value |
---|---|
Type Bool | Default value 1 |
The analyzer should be enabled to use parallel replicas. With disabled analyzer query execution fallbacks to local execution, even if parallel reading from replicas is enabled. Using parallel replicas without the analyzer enabled is not supported
parallel_replicas_prefer_local_join
Type | Default value |
---|---|
Type Bool | Default value 1 |
If true, and JOIN can be executed with parallel replicas algorithm, and all storages of right JOIN part are *MergeTree, local JOIN will be used instead of GLOBAL JOIN.
parallel_view_processing
Type | Default value |
---|---|
Type Bool | Default value 0 |
parallelize_output_from_storages
Type | Default value |
---|---|
Type Bool | Default value 1 |
Parallelize output for reading step from storage. It allows parallelization of query processing right after reading from storage if possible
parsedatetime_parse_without_leading_zeros
Type | Default value |
---|---|
Type Bool | Default value 1 |
Formatters '%c', '%l' and '%k' in function 'parseDateTime' parse months and hours without leading zeros.
partial_merge_join_left_table_buffer_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
partial_merge_join_rows_in_right_blocks
Type | Default value |
---|---|
Type UInt64 | Default value 65536 |
ClickHouse server:
- Splits right-hand join data into blocks with up to the specified number of rows.
- Indexes each block with its minimum and maximum values.
- Unloads prepared blocks to disk if it is possible.
Possible values:
- Any positive integer. Recommended range of values: [1000, 100000].
partial_result_on_first_cancel
Type | Default value |
---|---|
Type Bool | Default value 0 |
parts_to_delay_insert
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
parts_to_throw_insert
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
periodic_live_view_refresh
Type | Default value |
---|---|
Type Seconds | Default value 60 |
poll_interval
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
postgresql_connection_attempt_timeout
Type | Default value |
---|---|
Type UInt64 | Default value 2 |
Connection timeout in seconds of a single attempt to connect PostgreSQL end-point.
The value is passed as a connect_timeout
parameter of the connection URL.
postgresql_connection_pool_auto_close_connection
Type | Default value |
---|---|
Type Bool | Default value 0 |
postgresql_connection_pool_retries
Type | Default value |
---|---|
Type UInt64 | Default value 2 |
Connection pool push/pop retries number for PostgreSQL table engine and database engine.
postgresql_connection_pool_size
Type | Default value |
---|---|
Type UInt64 | Default value 16 |
postgresql_connection_pool_wait_timeout
Type | Default value |
---|---|
Type UInt64 | Default value 5000 |
postgresql_fault_injection_probability
Type | Default value |
---|---|
Type Float | Default value 0 |
Approximate probability of failing internal (for replication) PostgreSQL queries. Valid value is in interval [0.0f, 1.0f]
prefer_column_name_to_alias
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 — The column name is substituted with the alias.
- 1 — The column name is not substituted with the alias.
Example
The difference between enabled and disabled:
Query:
Result:
Query:
Result:
prefer_external_sort_block_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 16744704 |
Prefer maximum block bytes for external sort, reduce the memory usage during merging.
prefer_global_in_and_join
Type | Default value |
---|---|
Type Bool | Default value 0 |
IN
/JOIN
operators with GLOBAL IN
/GLOBAL JOIN
.
Possible values:
- 0 — Disabled.
IN
/JOIN
operators are not replaced withGLOBAL IN
/GLOBAL JOIN
. - 1 — Enabled.
IN
/JOIN
operators are replaced withGLOBAL IN
/GLOBAL JOIN
.
Usage
Although SET distributed_product_mode=global
can change the queries behavior for the distributed tables, it's not suitable for local tables or tables from external resources. Here is when the prefer_global_in_and_join
setting comes into play.
For example, we have query serving nodes that contain local tables, which are not suitable for distribution. We need to scatter their data on the fly during distributed processing with the GLOBAL
keyword — GLOBAL IN
/GLOBAL JOIN
.
Another use case of prefer_global_in_and_join
is accessing tables created by external engines. This setting helps to reduce the number of calls to external sources while joining such tables: only one call per query.
See also:
- Distributed subqueries for more information on how to use
GLOBAL IN
/GLOBAL JOIN
prefer_localhost_replica
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 1 — ClickHouse always sends a query to the localhost replica if it exists.
- 0 — ClickHouse uses the balancing strategy specified by the load_balancing setting.
Disable this setting if you use max_parallel_replicas without parallel_replicas_custom_key. If parallel_replicas_custom_key is set, disable this setting only if it's used on a cluster with multiple shards containing multiple replicas. If it's used on a cluster with a single shard and multiple replicas, disabling this setting will have negative effects.
prefer_warmed_unmerged_parts_seconds
Type | Default value |
---|---|
Type Int64 | Default value 0 |
preferred_block_size_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 1000000 |
preferred_max_column_in_block_size_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
preferred_optimize_projection_name
If it is set to a non-empty string, ClickHouse will try to apply specified projection in query.
Possible values:
- string: name of preferred projectio
prefetch_buffer_size
Type | Default value |
---|---|
Type UInt64 | Default value 1048576 |
print_pretty_type_names
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allows to print deep-nested type names in a pretty way with indents in DESCRIBE
query and in toTypeName()
function.
Example:
priority
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
push_external_roles_in_interserver_queries
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enable pushing user roles from originator to other nodes while performing a query.
query_cache_compress_entries
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 0 - Disabled
- 1 - Enabled
query_cache_max_entries
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer >= 0.
query_cache_max_size_in_bytes
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
Possible values:
- Positive integer >= 0.
query_cache_min_query_duration
Type | Default value |
---|---|
Type Milliseconds | Default value 0 |
Possible values:
- Positive integer >= 0.
query_cache_min_query_runs
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
SELECT
query must run before its result is stored in the query cache.
Possible values:
- Positive integer >= 0.
query_cache_nondeterministic_function_handling
Type | Default value |
---|---|
Type QueryResultCacheNondeterministicFunctionHandling | Default value throw |
SELECT
queries with non-deterministic functions like rand()
or now()
.
Possible values:
'throw'
- Throw an exception and don't cache the query result.'save'
- Cache the query result.'ignore'
- Don't cache the query result and don't throw an exception.
query_cache_share_between_users
Type | Default value |
---|---|
Type Bool | Default value 0 |
SELECT
queries cached in the query cache can be read by other users.
It is not recommended to enable this setting due to security reasons.
Possible values:
- 0 - Disabled
- 1 - Enabled
query_cache_squash_partial_results
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 0 - Disabled
- 1 - Enabled
query_cache_system_table_handling
Type | Default value |
---|---|
Type QueryResultCacheSystemTableHandling | Default value throw |
Controls how the query cache handles SELECT
queries against system tables, i.e. tables in databases system.*
and information_schema.*
.
Possible values:
'throw'
- Throw an exception and don't cache the query result.'save'
- Cache the query result.'ignore'
- Don't cache the query result and don't throw an exception.
query_cache_tag
A string which acts as a label for query cache entries. The same queries with different tags are considered different by the query cache.
Possible values:
- Any string
query_cache_ttl
Type | Default value |
---|---|
Type Seconds | Default value 60 |
Possible values:
- Positive integer >= 0.
query_condition_cache_store_conditions_as_plaintext
Type | Default value |
---|---|
Type Bool | Default value 0 |
Stores the filter condition for the query condition cache in plaintext. If enabled, system.query_condition_cache shows the verbatim filter condition which makes it easier to debug issues with the cache. Disabled by default because plaintext filter conditions may expose sensitive information.
Possible values:
- 0 - Disabled
- 1 - Enabled
query_metric_log_interval
Type | Default value |
---|---|
Type Int64 | Default value -1 |
The interval in milliseconds at which the query_metric_log for individual queries is collected.
If set to any negative value, it will take the value collect_interval_milliseconds
from the query_metric_log setting or default to 1000 if not present.
To disable the collection of a single query, set query_metric_log_interval
to 0.
Default value: -1
query_plan_aggregation_in_order
Type | Default value |
---|---|
Type Bool | Default value 1 |
Toggles the aggregation in-order query-plan-level optimization. Only takes effect if setting query_plan_enable_optimizations is 1.
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_convert_join_to_in
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow to convert JOIN to subquery with IN if output columns tied to only left table
query_plan_convert_outer_join_to_inner_join
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allow to convert OUTER JOIN to INNER JOIN if filter after JOIN always filters default values
query_plan_enable_multithreading_after_window_functions
Type | Default value |
---|---|
Type Bool | Default value 1 |
query_plan_enable_optimizations
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable all optimizations at the query plan level
- 1 - Enable optimizations at the query plan level (but individual optimizations may still be disabled via their individual settings)
query_plan_execute_functions_after_sorting
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_filter_push_down
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_join_shard_by_pk_ranges
Type | Default value |
---|---|
Type Bool | Default value 0 |
Apply sharding for JOIN if join keys contain a prefix of PRIMARY KEY for both tables. Supported for hash, parallel_hash and full_sorting_merge algorithms
query_plan_join_swap_table
Type | Default value |
---|---|
Type BoolAuto | Default value auto |
Determine which side of the join should be the build table (also called inner, the one inserted into the hash table for a hash join) in the query plan. This setting is supported only for ALL
join strictness with the JOIN ON
clause. Possible values are:
- 'auto': Let the planner decide which table to use as the build table.
- 'false': Never swap tables (the right table is the build table).
- 'true': Always swap tables (the left table is the build table).
query_plan_lift_up_array_join
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_lift_up_union
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_max_limit_for_lazy_materialization
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
Control maximum limit value that allows to use query plan for lazy materialization optimization. If zero, there is no limit
query_plan_max_optimizations_to_apply
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
query_plan_merge_expressions
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_merge_filter_into_join_condition
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allow to merge filter into JOIN condition and convert CROSS JOIN to INNER.
query_plan_merge_filters
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allow to merge filters in the query pla
query_plan_optimize_lazy_materialization
Type | Default value |
---|---|
Type Bool | Default value 1 |
Use query plan for lazy materialization optimizatio
query_plan_optimize_prewhere
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allow to push down filter to PREWHERE expression for supported storages
query_plan_push_down_limit
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_read_in_order
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_remove_redundant_distinct
Type | Default value |
---|---|
Type Bool | Default value 1 |
Toggles a query-plan-level optimization which removes redundant DISTINCT steps. Only takes effect if setting query_plan_enable_optimizations is 1.
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_remove_redundant_sorting
Type | Default value |
---|---|
Type Bool | Default value 1 |
Toggles a query-plan-level optimization which removes redundant sorting steps, e.g. in subqueries. Only takes effect if setting query_plan_enable_optimizations is 1.
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_reuse_storage_ordering_for_window_functions
Type | Default value |
---|---|
Type Bool | Default value 1 |
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_split_filter
Type | Default value |
---|---|
Type Bool | Default value 1 |
Toggles a query-plan-level optimization which splits filters into expressions. Only takes effect if setting query_plan_enable_optimizations is 1.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_try_use_vector_search
Type | Default value |
---|---|
Type Bool | Default value 1 |
Toggles a query-plan-level optimization which tries to use the vector similarity index. Only takes effect if setting query_plan_enable_optimizations is 1.
This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.
Possible values:
- 0 - Disable
- 1 - Enable
query_plan_use_new_logical_join_step
Type | Default value |
---|---|
Type Bool | Default value 1 |
Use new logical join step in query pla
query_profiler_cpu_time_period_ns
Type | Default value |
---|---|
Type UInt64 | Default value 1000000000 |
Possible values:
-
A positive integer number of nanoseconds.
Recommended values:
- 10000000 (100 times a second) nanoseconds and more for single queries.
- 1000000000 (once a second) for cluster-wide profiling.
-
0 for turning off the timer.
Temporarily disabled in ClickHouse Cloud.
See also:
- System table trace_log
query_profiler_real_time_period_ns
Type | Default value |
---|---|
Type UInt64 | Default value 1000000000 |
Possible values:
-
Positive integer number, in nanoseconds.
Recommended values:
- 10000000 (100 times a second) nanoseconds and less for single queries.
- 1000000000 (once a second) for cluster-wide profiling.
-
0 for turning off the timer.
Temporarily disabled in ClickHouse Cloud.
See also:
- System table trace_log
queue_max_wait_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 0 |
rabbitmq_max_wait_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 5000 |
read_backoff_max_throughput
Type | Default value |
---|---|
Type UInt64 | Default value 1048576 |
read_backoff_min_concurrency
Type | Default value |
---|---|
Type UInt64 | Default value 1 |
read_backoff_min_events
Type | Default value |
---|---|
Type UInt64 | Default value 2 |
read_backoff_min_interval_between_events_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 1000 |
read_backoff_min_latency_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 1000 |
read_from_filesystem_cache_if_exists_otherwise_bypass_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
read_from_page_cache_if_exists_otherwise_bypass_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
Use userspace page cache in passive mode, similar to read_from_filesystem_cache_if_exists_otherwise_bypass_cache.
read_in_order_two_level_merge_threshold
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
read_in_order_use_buffering
Type | Default value |
---|---|
Type Bool | Default value 1 |
Use buffering before merging while reading in order of primary key. It increases the parallelism of query executio
read_in_order_use_virtual_row
Type | Default value |
---|---|
Type Bool | Default value 0 |
Use virtual row while reading in order of primary key or its monotonic function fashion. It is useful when searching over multiple parts as only relevant ones are touched.
read_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
read_overflow_mode_leaf
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
Possible options:
throw
: throw an exception (default).break
: stop executing the query and return the partial result.
read_priority
Type | Default value |
---|---|
Type Int64 | Default value 0 |
threadpool
method for remote filesystem.
read_through_distributed_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
Only has an effect in ClickHouse Cloud. Allow reading from distributed cache
readonly
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
receive_data_timeout_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 2000 |
receive_timeout
Type | Default value |
---|---|
Type Seconds | Default value 300 |
regexp_max_matches_per_row
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Possible values:
- Positive integer.
reject_expensive_hyperscan_regexps
Type | Default value |
---|---|
Type Bool | Default value 1 |
remerge_sort_lowered_memory_bytes_ratio
Type | Default value |
---|---|
Type Float | Default value 2 |
remote_filesystem_read_method
Type | Default value |
---|---|
Type String | Default value threadpool |
remote_filesystem_read_prefetch
Type | Default value |
---|---|
Type Bool | Default value 1 |
remote_fs_read_backoff_max_tries
Type | Default value |
---|---|
Type UInt64 | Default value 5 |
remote_fs_read_max_backoff_ms
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
remote_read_min_bytes_for_seek
Type | Default value |
---|---|
Type UInt64 | Default value 4194304 |
rename_files_after_processing
-
Type: String
-
Default value: Empty string
This setting allows to specify renaming pattern for files processed by file
table function. When option is set, all files read by file
table function will be renamed according to specified pattern with placeholders, only if files processing was successful.
Placeholders
%a
— Full original filename (e.g., "sample.csv").%f
— Original filename without extension (e.g., "sample").%e
— Original file extension with dot (e.g., ".csv").%t
— Timestamp (in microseconds).%%
— Percentage sign ("%").
Example
-
Option:
--rename_files_after_processing="processed_%f_%t%e"
-
Query:
SELECT * FROM file('sample.csv')
If reading sample.csv
is successful, file will be renamed to processed_sample_1683473210851438.csv
replace_running_query
Type | Default value |
---|---|
Type Bool | Default value 0 |
0
(default) – Throw an exception (do not allow the query to run if a query with the same 'query_id' is already running).
1
– Cancel the old query and start running the new one.
Set this parameter to 1 for implementing suggestions for segmentation conditions. After entering the next character, if the old query hasn't finished yet, it should be cancelled.
replace_running_query_max_wait_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 5000 |
query_id
to finish, when the replace_running_query setting is active.
Possible values:
- Positive integer.
- 0 — Throwing an exception that does not allow to run a new query if the server already executes a query with the same
query_id
.
replication_wait_for_inactive_replica_timeout
Type | Default value |
---|---|
Type Int64 | Default value 120 |
Possible values:
- 0 — Do not wait.
- Negative integer — Wait for unlimited time.
- Positive integer — The number of seconds to wait.
restore_replace_external_dictionary_source_to_null
Type | Default value |
---|---|
Type Bool | Default value 0 |
Replace external dictionary sources to Null on restore. Useful for testing purposes
restore_replace_external_engines_to_null
Type | Default value |
---|---|
Type Bool | Default value 0 |
For testing purposes. Replaces all external engines to Null to not initiate external connections.
restore_replace_external_table_functions_to_null
Type | Default value |
---|---|
Type Bool | Default value 0 |
For testing purposes. Replaces all external table functions to Null to not initiate external connections.
restore_replicated_merge_tree_to_shared_merge_tree
Type | Default value |
---|---|
Type Bool | Default value 0 |
Replace table engine from ReplicatedMergeTree -> SharedMergeTree during RESTORE.
result_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
throw
Sets what to do if the volume of the result exceeds one of the limits.
Possible values:
throw
: throw an exception (default).break
: stop executing the query and return the partial result, as if the source data ran out.
Using 'break' is similar to using LIMIT. Break
interrupts execution only at the
block level. This means that amount of returned rows is greater than
max_result_rows
, multiple of max_block_size
and depends on max_threads
.
Example
rewrite_count_distinct_if_with_count_distinct_implementation
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows you to rewrite countDistcintIf
with count_distinct_implementation setting.
Possible values:
- true — Allow.
- false — Disallow.
s3_allow_multipart_copy
Type | Default value |
---|---|
Type Bool | Default value 1 |
Allow multipart copy in S3.
s3_allow_parallel_part_upload
Type | Default value |
---|---|
Type Bool | Default value 1 |
s3_check_objects_after_upload
Type | Default value |
---|---|
Type Bool | Default value 0 |
s3_connect_timeout_ms
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Connection timeout for host from s3 disks.
s3_create_new_file_on_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
initial: data.Parquet.gz
-> data.1.Parquet.gz
-> data.2.Parquet.gz
, etc.
Possible values:
- 0 —
INSERT
query creates a new file or fail if file exists and s3_truncate_on_insert is not set. - 1 —
INSERT
query creates a new file on each insert using suffix (from the second one) if s3_truncate_on_insert is not set.
See more details here.
s3_disable_checksum
Type | Default value |
---|---|
Type Bool | Default value 0 |
s3_ignore_file_doesnt_exist
Type | Default value |
---|---|
Type Bool | Default value 0 |
Ignore absence of file if it does not exist when reading certain keys.
Possible values:
- 1 —
SELECT
returns empty result. - 0 —
SELECT
throws an exception.
s3_list_object_keys_size
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
s3_max_connections
Type | Default value |
---|---|
Type UInt64 | Default value 1024 |
s3_max_get_burst
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
s3_max_get_rps
s3_max_get_rps
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
s3_max_inflight_parts_for_one_file
Type | Default value |
---|---|
Type UInt64 | Default value 20 |
s3_max_part_number
Type | Default value |
---|---|
Type UInt64 | Default value 10000 |
Maximum part number number for s3 upload part.
s3_max_put_burst
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
s3_max_put_rps
s3_max_put_rps
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
s3_max_redirects
Type | Default value |
---|---|
Type UInt64 | Default value 10 |
s3_max_single_operation_copy_size
Type | Default value |
---|---|
Type UInt64 | Default value 33554432 |
Maximum size for single-operation copy in s3. This setting is used only if s3_allow_multipart_copy is true.
s3_max_single_part_upload_size
Type | Default value |
---|---|
Type UInt64 | Default value 33554432 |
s3_max_single_read_retries
Type | Default value |
---|---|
Type UInt64 | Default value 4 |
s3_max_unexpected_write_error_retries
Type | Default value |
---|---|
Type UInt64 | Default value 4 |
s3_max_upload_part_size
Type | Default value |
---|---|
Type UInt64 | Default value 5368709120 |
s3_min_upload_part_size
Type | Default value |
---|---|
Type UInt64 | Default value 16777216 |
s3_request_timeout_ms
Type | Default value |
---|---|
Type UInt64 | Default value 30000 |
s3_retry_attempts
Type | Default value |
---|---|
Type UInt64 | Default value 100 |
s3_skip_empty_files
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables or disables skipping empty files in S3 engine tables.
Possible values:
- 0 —
SELECT
throws an exception if empty file is not compatible with requested format. - 1 —
SELECT
returns empty result for empty file.
s3_strict_upload_part_size
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
s3_throw_on_zero_files_match
Type | Default value |
---|---|
Type Bool | Default value 0 |
s3_truncate_on_insert
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 0 —
INSERT
query creates a new file or fail if file exists and s3_create_new_file_on_insert is not set. - 1 —
INSERT
query replaces existing content of the file with the new data.
See more details here.
s3_upload_part_size_multiply_factor
Type | Default value |
---|---|
Type UInt64 | Default value 2 |
s3_upload_part_size_multiply_parts_count_threshold
Type | Default value |
---|---|
Type UInt64 | Default value 500 |
s3_use_adaptive_timeouts
Type | Default value |
---|---|
Type Bool | Default value 1 |
true
than for all s3 requests first two attempts are made with low send and receive timeouts.
When set to false
than all attempts are made with identical timeouts.
s3_validate_request_settings
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables s3 request settings validation.
Possible values:
- 1 — validate settings.
- 0 — do not validate settings.
s3queue_default_zookeeper_path
Type | Default value |
---|---|
Type String | Default value /clickhouse/s3queue/ |
s3queue_enable_logging_to_s3queue_log
Type | Default value |
---|---|
Type Bool | Default value 0 |
s3queue_migrate_old_metadata_to_buckets
Type | Default value |
---|---|
Type Bool | Default value 0 |
Migrate old metadata structure of S3Queue table to a new one
schema_inference_cache_require_modification_time_for_url
Type | Default value |
---|---|
Type Bool | Default value 1 |
schema_inference_use_cache_for_azure
Type | Default value |
---|---|
Type Bool | Default value 1 |
schema_inference_use_cache_for_file
Type | Default value |
---|---|
Type Bool | Default value 1 |
schema_inference_use_cache_for_hdfs
Type | Default value |
---|---|
Type Bool | Default value 1 |
schema_inference_use_cache_for_s3
Type | Default value |
---|---|
Type Bool | Default value 1 |
schema_inference_use_cache_for_url
Type | Default value |
---|---|
Type Bool | Default value 1 |
secondary_indices_enable_bulk_filtering
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enable the bulk filtering algorithm for indices. It is expected to be always better, but we have this setting for compatibility and control.
select_sequential_consistency
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
select_sequential_consistency
in SharedMergeTree.
:::
Enables or disables sequential consistency for SELECT
queries. Requires insert_quorum_parallel
to be disabled (enabled by default).
Possible values:
- 0 — Disabled.
- 1 — Enabled.
Usage
When sequential consistency is enabled, ClickHouse allows the client to execute the SELECT
query only for those replicas that contain data from all previous INSERT
queries executed with insert_quorum
. If the client refers to a partial replica, ClickHouse will generate an exception. The SELECT query will not include data that has not yet been written to the quorum of replicas.
When insert_quorum_parallel
is enabled (the default), then select_sequential_consistency
does not work. This is because parallel INSERT
queries can be written to different sets of quorum replicas so there is no guarantee a single replica will have received all writes.
See also:
send_logs_level
Type | Default value |
---|---|
Type LogsLevel | Default value fatal |
send_logs_source_regexp
Send server text logs with specified regexp to match log source name. Empty means all sources.
send_progress_in_http_headers
Type | Default value |
---|---|
Type Bool | Default value 0 |
X-ClickHouse-Progress
HTTP response headers in clickhouse-server
responses.
For more information, read the HTTP interface description.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
send_timeout
Type | Default value |
---|---|
Type Seconds | Default value 300 |
serialize_query_plan
Type | Default value |
---|---|
Type Bool | Default value 0 |
Serialize query plan for distributed processing
session_timezone
Sets the implicit time zone of the current session or query. The implicit time zone is the time zone applied to values of type DateTime/DateTime64 which have no explicitly specified time zone. The setting takes precedence over the globally configured (server-level) implicit time zone. A value of '' (empty string) means that the implicit time zone of the current session or query is equal to the server time zone.
You can use functions timeZone()
and serverTimeZone()
to get the session time zone and server time zone.
Possible values:
- Any time zone name from
system.time_zones
, e.g.Europe/Berlin
,UTC
orZulu
Examples:
Assign session time zone 'America/Denver' to the inner DateTime without explicitly specified time zone:
Not all functions that parse DateTime/DateTime64 respect session_timezone
. This can lead to subtle errors.
See the following example and explanation.
This happens due to different parsing pipelines:
toDateTime()
without explicitly given time zone used in the firstSELECT
query honors settingsession_timezone
and the global time zone.- In the second query, a DateTime is parsed from a String, and inherits the type and time zone of the existing column
d
. Thus, settingsession_timezone
and the global time zone are not honored.
See also
set_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
Possible values:
throw
: throw an exception (default).break
: stop executing the query and return the partial result, as if the source data ran out.
shared_merge_tree_sync_parts_on_partition_operations
Type | Default value |
---|---|
Type Bool | Default value 1 |
Automatically synchronize set of data parts after MOVE|REPLACE|ATTACH partition operations in SMT tables. Cloud only
short_circuit_function_evaluation
Type | Default value |
---|---|
Type ShortCircuitFunctionEvaluation | Default value enable |
Possible values:
enable
— Enables short-circuit function evaluation for functions that are suitable for it (can throw an exception or computationally heavy).force_enable
— Enables short-circuit function evaluation for all functions.disable
— Disables short-circuit function evaluation.
short_circuit_function_evaluation_for_nulls
Type | Default value |
---|---|
Type Bool | Default value 1 |
Optimizes evaluation of functions that return NULL when any argument is NULL. When the percentage of NULL values in the function's arguments exceeds the short_circuit_function_evaluation_for_nulls_threshold, the system skips evaluating the function row-by-row. Instead, it immediately returns NULL for all rows, avoiding unnecessary computation.
short_circuit_function_evaluation_for_nulls_threshold
Type | Default value |
---|---|
Type Double | Default value 1 |
Ratio threshold of NULL values to execute functions with Nullable arguments only on rows with non-NULL values in all arguments. Applies when setting short_circuit_function_evaluation_for_nulls is enabled. When the ratio of rows containing NULL values to the total number of rows exceeds this threshold, these rows containing NULL values will not be evaluated.
show_table_uuid_in_table_create_query_if_not_nil
Type | Default value |
---|---|
Type Bool | Default value 0 |
Sets the SHOW TABLE
query display.
Possible values:
- 0 — The query will be displayed without table UUID.
- 1 — The query will be displayed with table UUID.
single_join_prefer_left_table
Type | Default value |
---|---|
Type Bool | Default value 1 |
skip_redundant_aliases_in_udf
Type | Default value |
---|---|
Type Bool | Default value 0 |
Redundant aliases are not used (substituted) in user-defined functions in order to simplify it's usage.
Possible values:
- 1 — The aliases are skipped (substituted) in UDFs.
- 0 — The aliases are not skipped (substituted) in UDFs.
Example
The difference between enabled and disabled:
Query:
Result:
Query:
Result:
skip_unavailable_shards
Type | Default value |
---|---|
Type Bool | Default value 0 |
Shard is considered unavailable if all its replicas are unavailable. A replica is unavailable in the following cases:
-
ClickHouse can't connect to replica for any reason.
When connecting to a replica, ClickHouse performs several attempts. If all these attempts fail, the replica is considered unavailable.
-
Replica can't be resolved through DNS.
If replica's hostname can't be resolved through DNS, it can indicate the following situations:
-
Replica's host has no DNS record. It can occur in systems with dynamic DNS, for example, Kubernetes, where nodes can be unresolvable during downtime, and this is not an error.
-
Configuration error. ClickHouse configuration file contains a wrong hostname.
-
Possible values:
-
1 — skipping enabled.
If a shard is unavailable, ClickHouse returns a result based on partial data and does not report node availability issues.
-
0 — skipping disabled.
If a shard is unavailable, ClickHouse throws an exception.
sleep_after_receiving_query_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 0 |
sleep_in_send_data_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 0 |
sleep_in_send_tables_status_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 0 |
sort_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
Possible values:
throw
: throw an exception.break
: stop executing the query and return the partial result.
split_intersecting_parts_ranges_into_layers_final
Type | Default value |
---|---|
Type Bool | Default value 1 |
Split intersecting parts ranges into layers during FINAL optimizatio
split_parts_ranges_into_intersecting_and_non_intersecting_final
Type | Default value |
---|---|
Type Bool | Default value 1 |
Split parts ranges into intersecting and non intersecting during FINAL optimizatio
splitby_max_substrings_includes_remaining_string
Type | Default value |
---|---|
Type Bool | Default value 0 |
max_substrings
> 0 will include the remaining string in the last element of the result array.
Possible values:
0
- The remaining string will not be included in the last element of the result array.1
- The remaining string will be included in the last element of the result array. This is the behavior of Spark'ssplit()
function and Python's 'string.split()' method.
stop_refreshable_materialized_views_on_startup
Type | Default value |
---|---|
Type Bool | Default value 0 |
SYSTEM START VIEWS
or SYSTEM START VIEW <name>
afterwards. Also applies to newly created views. Has no effect on non-refreshable materialized views.
storage_file_read_method
Type | Default value |
---|---|
Type LocalFSReadMethod | Default value pread |
read
, pread
, mmap
. The mmap method does not apply to clickhouse-server (it's intended for clickhouse-local).
storage_system_stack_trace_pipe_read_timeout_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 100 |
system.stack_trace
table. This setting is used for testing purposes and not meant to be changed by users.
stream_flush_interval_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 7500 |
The default value is 7500.
The smaller the value, the more often data is flushed into the table. Setting the value too low leads to poor performance.
stream_like_engine_allow_direct_select
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allow direct SELECT query for Kafka, RabbitMQ, FileLog, Redis Streams, and NATS engines. In case there are attached materialized views, SELECT query is not allowed even if this setting is enabled.
stream_like_engine_insert_queue
When stream-like engine reads from multiple queues, the user will need to select one queue to insert into when writing. Used by Redis Streams and NATS.
stream_poll_timeout_ms
Type | Default value |
---|---|
Type Milliseconds | Default value 500 |
system_events_show_zero_values
Type | Default value |
---|---|
Type Bool | Default value 0 |
system.events
.
Some monitoring systems require passing all the metrics values to them for each checkpoint, even if the metric value is zero.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
Examples
Query
Result
Query
Result
table_function_remote_max_addresses
Type | Default value |
---|---|
Type UInt64 | Default value 1000 |
Possible values:
- Positive integer.
tcp_keep_alive_timeout
Type | Default value |
---|---|
Type Seconds | Default value 290 |
temporary_data_in_cache_reserve_space_wait_lock_timeout_milliseconds
Type | Default value |
---|---|
Type UInt64 | Default value 600000 |
Wait time to lock cache for space reservation for temporary data in filesystem cache
temporary_files_codec
Type | Default value |
---|---|
Type String | Default value LZ4 |
Possible values:
- LZ4 — LZ4 compression is applied.
- NONE — No compression is applied.
throw_if_deduplication_in_dependent_materialized_views_enabled_with_async_insert
Type | Default value |
---|---|
Type Bool | Default value 1 |
Throw exception on INSERT query when the setting deduplicate_blocks_in_dependent_materialized_views
is enabled along with async_insert
. It guarantees correctness, because these features can't work together.
throw_if_no_data_to_insert
Type | Default value |
---|---|
Type Bool | Default value 1 |
clickhouse-client
or using the gRPC interface.
throw_on_error_from_cache_on_write_operations
Type | Default value |
---|---|
Type Bool | Default value 0 |
throw_on_max_partitions_per_insert_block
Type | Default value |
---|---|
Type Bool | Default value 1 |
max_partitions_per_insert_block
is reached.
Possible values:
true
- When an insert block reachesmax_partitions_per_insert_block
, an exception is raised.false
- Logs a warning whenmax_partitions_per_insert_block
is reached.
This can be useful if you're trying to understand the impact on users when changing max_partitions_per_insert_block
.
throw_on_unsupported_query_inside_transaction
Type | Default value |
---|---|
Type Bool | Default value 1 |
timeout_before_checking_execution_speed
Type | Default value |
---|---|
Type Seconds | Default value 10 |
min_execution_speed
),
after the specified time in seconds has expired.
timeout_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
max_execution_time
or the
estimated running time is longer than max_estimated_execution_time
.
Possible values:
throw
: throw an exception (default).break
: stop executing the query and return the partial result, as if the source data ran out.
timeout_overflow_mode_leaf
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
max_execution_time_leaf
.
Possible values:
throw
: throw an exception (default).break
: stop executing the query and return the partial result, as if the source data ran out.
totals_auto_threshold
Type | Default value |
---|---|
Type Float | Default value 0.5 |
totals_mode = 'auto'
.
See the section "WITH TOTALS modifier".
totals_mode
Type | Default value |
---|---|
Type TotalsMode | Default value after_having_exclusive |
trace_profile_events
Type | Default value |
---|---|
Type Bool | Default value 0 |
Possible values:
- 1 — Tracing of profile events enabled.
- 0 — Tracing of profile events disabled.
transfer_overflow_mode
Type | Default value |
---|---|
Type OverflowMode | Default value throw |
Possible values:
throw
: throw an exception (default).break
: stop executing the query and return the partial result, as if the source data ran out.
transform_null_in
Type | Default value |
---|---|
Type Bool | Default value 0 |
By default, NULL
values can't be compared because NULL
means undefined value. Thus, comparison expr = NULL
must always return false
. With this setting NULL = NULL
returns true
for IN
operator.
Possible values:
- 0 — Comparison of
NULL
values inIN
operator returnsfalse
. - 1 — Comparison of
NULL
values inIN
operator returnstrue
.
Example
Consider the null_in
table:
Query:
Result:
Query:
Result:
See Also
traverse_shadow_remote_data_paths
Type | Default value |
---|---|
Type Bool | Default value 0 |
Traverse frozen data (shadow directory) in addition to actual table data when query system.remote_data_paths
union_default_mode
Sets a mode for combining SELECT
query results. The setting is only used when shared with UNION without explicitly specifying the UNION ALL
or UNION DISTINCT
.
Possible values:
'DISTINCT'
— ClickHouse outputs rows as a result of combining queries removing duplicate rows.'ALL'
— ClickHouse outputs all rows as a result of combining queries including duplicate rows.''
— ClickHouse generates an exception when used withUNION
.
See examples in UNION.
unknown_packet_in_send_data
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
use_async_executor_for_materialized_views
Type | Default value |
---|---|
Type Bool | Default value 0 |
Use async and potentially multithreaded execution of materialized view query, can speedup views processing during INSERT, but also consume more memory.
use_cache_for_count_from_files
Type | Default value |
---|---|
Type Bool | Default value 1 |
file
/s3
/url
/hdfs
/azureBlobStorage
.
Enabled by default.
use_client_time_zone
Type | Default value |
---|---|
Type Bool | Default value 0 |
use_compact_format_in_distributed_parts_names
Type | Default value |
---|---|
Type Bool | Default value 1 |
Uses compact format for storing blocks for background (distributed_foreground_insert
) INSERT into tables with Distributed
engine.
Possible values:
- 0 — Uses
user[:password]@host:port#default_database
directory format. - 1 — Uses
[shard{shard_index}[_replica{replica_index}]]
directory format.
- with
use_compact_format_in_distributed_parts_names=0
changes from cluster definition will not be applied for background INSERT. - with
use_compact_format_in_distributed_parts_names=1
changing the order of the nodes in the cluster definition, will change theshard_index
/replica_index
so be aware.
use_concurrency_control
Type | Default value |
---|---|
Type Bool | Default value 1 |
Respect the server's concurrency control (see the concurrent_threads_soft_limit_num
and concurrent_threads_soft_limit_ratio_to_cores
global server settings). If disabled, it allows using a larger number of threads even if the server is overloaded (not recommended for normal usage, and needed mostly for tests).
use_hedged_requests
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables hedged requests logic for remote queries. It allows to establish many connections with different replicas for query.
New connection is enabled in case existent connection(s) with replica(s) were not established within hedged_connection_timeout
or no data was received within receive_data_timeout
. Query uses the first connection which send non empty progress packet (or data packet, if allow_changing_replica_until_first_data_packet
);
other connections are cancelled. Queries with max_parallel_replicas > 1
are supported.
Enabled by default.
Disabled by default on Cloud.
use_hive_partitioning
Type | Default value |
---|---|
Type Bool | Default value 1 |
When enabled, ClickHouse will detect Hive-style partitioning in path (/name=value/
) in file-like table engines File/S3/URL/HDFS/AzureBlobStorage and will allow to use partition columns as virtual columns in the query. These virtual columns will have the same names as in the partitioned path, but starting with _
.
use_iceberg_metadata_files_cache
Type | Default value |
---|---|
Type Bool | Default value 1 |
If turned on, iceberg table function and iceberg storage may utilize the iceberg metadata files cache.
use_iceberg_partition_pruning
Type | Default value |
---|---|
Type Bool | Default value 0 |
Use Iceberg partition pruning for Iceberg tables
use_index_for_in_with_subqueries
Type | Default value |
---|---|
Type Bool | Default value 1 |
use_index_for_in_with_subqueries_max_values
Type | Default value |
---|---|
Type UInt64 | Default value 0 |
use_json_alias_for_old_object_type
Type | Default value |
---|---|
Type Bool | Default value 0 |
When enabled, JSON
data type alias will be used to create an old Object('json') type instead of the new JSON type.
use_page_cache_for_disks_without_file_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
Use userspace page cache for remote disks that don't have filesystem cache enabled.
use_page_cache_with_distributed_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
Use userspace page cache when distributed cache is used.
use_query_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
SELECT
queries may utilize the query cache. Parameters enable_reads_from_query_cache
and enable_writes_to_query_cache control in more detail how the cache is used.
Possible values:
- 0 - Disabled
- 1 - Enabled
use_query_condition_cache
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enable the query condition cache. The cache stores ranges of granules in data parts which do not satisfy the condition in the WHERE
clause,
and reuse this information as an ephemeral index for subsequent queries.
Possible values:
- 0 - Disabled
- 1 - Enabled
use_skip_indexes
Type | Default value |
---|---|
Type Bool | Default value 1 |
Possible values:
- 0 — Disabled.
- 1 — Enabled.
use_skip_indexes_if_final
Type | Default value |
---|---|
Type Bool | Default value 0 |
By default, this setting is disabled because skip indexes may exclude rows (granules) containing the latest data, which could lead to incorrect results. When enabled, skipping indexes are applied even with the FINAL modifier, potentially improving performance but with the risk of missing recent updates.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
use_skip_indexes_if_final_exact_mode
Type | Default value |
---|---|
Type Bool | Default value 0 |
Controls whether granules returned by a skipping index are expanded in newer parts to return correct results when executing a query with the FINAL modifier.
Using skip indexes may exclude rows (granules) containing the latest data which could lead to incorrect results. This setting can ensure that correct results are returned by scanning newer parts that have overlap with the ranges returned by the skip index.
Possible values:
- 0 — Disabled.
- 1 — Enabled.
use_structure_from_insertion_table_in_table_functions
Type | Default value |
---|---|
Type UInt64 | Default value 2 |
Use structure from insertion table instead of schema inference from data. Possible values: 0 - disabled, 1 - enabled, 2 - auto
use_uncompressed_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
For queries that read at least a somewhat large volume of data (one million rows or more), the uncompressed cache is disabled automatically to save space for truly small queries. This means that you can keep the 'use_uncompressed_cache' setting always set to 1.
use_variant_as_common_type
Type | Default value |
---|---|
Type Bool | Default value 0 |
Allows to use Variant
type as a result type for if/multiIf/array/map functions when there is no common type for argument types.
Example:
use_with_fill_by_sorting_prefix
Type | Default value |
---|---|
Type Bool | Default value 1 |
Columns preceding WITH FILL columns in ORDER BY clause form sorting prefix. Rows with different values in sorting prefix are filled independently
validate_enum_literals_in_operators
Type | Default value |
---|---|
Type Bool | Default value 0 |
If enabled, validate enum literals in operators like IN
, NOT IN
, ==
, !=
against the enum type and throw an exception if the literal is not a valid enum value.
validate_mutation_query
Type | Default value |
---|---|
Type Bool | Default value 1 |
Validate mutation queries before accepting them. Mutations are executed in the background, and running an invalid query will cause mutations to get stuck, requiring manual intervention.
Only change this setting if you encounter a backward-incompatible bug.
validate_polygons
Type | Default value |
---|---|
Type Bool | Default value 1 |
Enables or disables throwing an exception in the pointInPolygon function, if the polygon is self-intersecting or self-tangent.
Possible values:
- 0 — Throwing an exception is disabled.
pointInPolygon
accepts invalid polygons and returns possibly incorrect results for them. - 1 — Throwing an exception is enabled.
wait_changes_become_visible_after_commit_mode
Type | Default value |
---|---|
Type TransactionsWaitCSNMode | Default value wait_unknown |
wait_for_async_insert
Type | Default value |
---|---|
Type Bool | Default value 1 |
wait_for_async_insert_timeout
Type | Default value |
---|---|
Type Seconds | Default value 120 |
wait_for_window_view_fire_signal_timeout
Type | Default value |
---|---|
Type Seconds | Default value 10 |
window_view_clean_interval
Type | Default value |
---|---|
Type Seconds | Default value 60 |
window_view_heartbeat_interval
Type | Default value |
---|---|
Type Seconds | Default value 15 |
workload
Type | Default value |
---|---|
Type String | Default value default |
write_through_distributed_cache
Type | Default value |
---|---|
Type Bool | Default value 0 |
Only has an effect in ClickHouse Cloud. Allow writing to distributed cache (writing to s3 will also be done by distributed cache)
zstd_window_log_max
Type | Default value |
---|---|
Type Int64 | Default value 0 |