system.settings
Introduced or updated: v1.2.745
Stores the system settings of the current session.
SELECT * FROM system.settings;
name |value |default |level |description |type |
--------------------------------------------+------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
acquire_lock_timeout |30 |30 |SESSION|Sets the maximum timeout in seconds for acquiring a table lock. If the lock cannot be acquired within this time, the query fails. |UInt64|
allow_query_exceeded_limit |0 |0 |SESSION|Allows queries to overshoot the configured memory limit, deferring the error until actual memory contention occurs. |UInt64|
binary_input_format |utf-8 |utf-8 |SESSION|Controls how string literals are interpreted when inserted into BINARY columns. Values: "hex", "base64", "utf-8", "utf-8-lossy". |String|
binary_output_format |hex |hex |SESSION|Controls how BINARY columns are rendered in query results. Values: "hex", "base64", "utf-8", "utf-8-lossy". |String|
collation |binary |binary |SESSION|Sets the character collation. Available values include "binary" and "utf8". |String|
copy_dedup_full_path_by_default |0 |0 |SESSION|Enables full path matching by default for COPY deduplication. |UInt64|
data_retention_num_snapshots_to_keep |0 |0 |SESSION|Specifies how many snapshots to retain during VACUUM operations. Overrides data_retention_time_in_days when non-zero. Range: 0–500. |UInt64|
date_format_style |MySQL |MySQL |SESSION|Sets the date format style for datetime functions. Available values: "MySQL" (using %Y, %m format) and "Oracle" (using YYYY, MM format for standard SQL compatibility). |String|
ddl_column_type_nullable |1 |1 |SESSION|If columns are default nullable when create or alter table |UInt64|
deduplicate_label | | |SESSION|A SQL duplicate label used for write deduplication. When set, Databend uses this label to detect and skip duplicate write operations (idempotent writes). |String|
default_order_by_null |nulls_last |nulls_last |SESSION|Controls where NULL values appear in ORDER BY results when no explicit NULLS FIRST/NULLS LAST is specified. Values: "nulls_first", "nulls_last", "nulls_first_on_asc_last_on_desc",|String|
efficiently_memory_group_by |0 |0 |SESSION|Memory is used efficiently, but this may cause performance degradation. |UInt64|
enable_aggregating_index_scan |1 |1 |SESSION|Enable scanning aggregating index data while querying. |UInt64|
enable_auto_detect_datetime_format |0 |0 |SESSION|Enables automatic detection of datetime format for parsing datetime strings. |UInt64|
enable_block_stream_write |1 |1 |SESSION|Enables block stream write mode for data ingestion. |UInt64|
enable_bushy_join |0 |0 |SESSION|Enables generating a bushy join plan with the optimizer. |UInt64|
enable_cbo |1 |1 |SESSION|Enables cost-based optimization. |UInt64|
enable_dio |1 |1 |SESSION|Enables Direct I/O for storage reads, bypassing the OS page cache. Can improve performance for large sequential scans by avoiding double-buffering. |UInt64|
enable_distributed_compact |0 |0 |SESSION|Enable distributed execution of table compaction. |UInt64|
enable_distributed_copy_into |0 |0 |SESSION|Enable distributed execution of copy into. |UInt64|
enable_distributed_merge_into |0 |0 |SESSION|Enable distributed merge into. |UInt64|
enable_distributed_recluster |0 |0 |SESSION|Enable distributed execution of table recluster. |UInt64|
enable_distributed_replace_into |0 |0 |SESSION|Enable distributed execution of replace into. |UInt64|
enable_dphyp |1 |1 |SESSION|Enables dphyp join order algorithm. |UInt64|
enable_experimental_merge_into |0 |0 |SESSION|Enable experimental merge into. |UInt64|
enable_experimental_row_access_policy |0 |0 |SESSION|Enables the experimental row access policy feature. Required to use CREATE/DROP ROW ACCESS POLICY. |UInt64|
enable_experimental_virtual_column |0 |0 |SESSION|Enables the experimental virtual column feature. |UInt64|
enable_geo_create_table |0 |0 |SESSION|Enables the creation of geographic tables. |UInt64|
enable_hive_parquet_predict_pushdown |1 |1 |SESSION|Enable hive parquet predict pushdown by setting this variable to 1, default value: 1 |UInt64|
enable_last_snapshot_location_hint |1 |1 |SESSION|Enables writing a last_snapshot_location_hint object to storage after each write. This hint speeds up table opening by providing a shortcut to the latest snapshot. |UInt64|
enable_parquet_page_index |1 |1 |SESSION|Enables parquet page index |UInt64|
enable_parquet_prewhere |0 |0 |SESSION|Enables parquet prewhere |UInt64|
enable_parquet_rowgroup_pruning |1 |1 |SESSION|Enables parquet rowgroup pruning |UInt64|
enable_query_profiling |0 |0 |SESSION|Enables recording query profile |UInt64|
enable_query_result_cache |0 |0 |SESSION|Enables caching query results to improve performance for identical queries. |UInt64|
enable_recluster_after_write |1 |1 |SESSION|Enables re-clustering after write(copy/replace-into). |UInt64|
enable_refresh_aggregating_index_after_write|0 |0 |SESSION|Refresh aggregating index after new data written |UInt64|
enable_replace_into_bloom_pruning |1 |1 |SESSION|Enables bloom pruning for replace-into statement. |UInt64|
enable_replace_into_partitioning |1 |1 |SESSION|Enables partitioning for replace-into statement (if table has cluster keys). |UInt64|
enable_result_set_spilling |0 |0 |SESSION|Enables spilling result set data to storage when memory usage exceeds the threshold, avoiding OOM errors for large result sets. |UInt64|
enable_runtime_filter |0 |0 |SESSION|Enables runtime filter optimization for JOIN. |UInt64|
enable_strict_datetime_parser |1 |1 |SESSION|Enables strict datetime parsing. When enabled, invalid datetime strings will cause errors instead of being silently converted. |UInt64|
enable_table_lock |1 |1 |SESSION|Enables table lock if necessary (enabled by default). |UInt64|
error_on_nondeterministic_update |1 |1 |SESSION|When enabled, throws an error if an UPDATE statement contains non-deterministic expressions. |UInt64|
external_server_connect_timeout_secs |10 |10 |SESSION|Timeout in seconds for connecting to an external UDF server. |UInt64|
external_server_request_batch_rows |65536 |65536 |SESSION|Number of rows per batch sent to an external UDF server. |UInt64|
external_server_request_max_threads |256 |256 |SESSION|Maximum number of threads for external UDF server requests. |UInt64|
external_server_request_retry_times |8 |8 |SESSION|Number of retry attempts for failed external UDF server requests. |UInt64|
external_server_request_timeout_secs |180 |180 |SESSION|Timeout in seconds for a single request to an external UDF server. |UInt64|
flight_client_timeout |60 |60 |SESSION|Sets the maximum time in seconds that a flight client request can be processed. |UInt64|
format_null_as_str |1 |1 |SESSION|Formats NULL values as a string in query results. |UInt64|
group_by_shuffle_mode |before_merge|before_merge|SESSION|Group by shuffle mode, 'before_partial' is more balanced, but more data needs to exchange. |String|
group_by_two_level_threshold |20000 |20000 |SESSION|Sets the number of keys in a GROUP BY operation that will trigger a two-level aggregation. |UInt64|
hide_options_in_show_create_table |1 |1 |SESSION|Hides table-relevant information, such as SNAPSHOT_LOCATION and STORAGE_FORMAT, at the end of the result of SHOW TABLE CREATE. |UInt64|
hive_parquet_chunk_size |16384 |16384 |SESSION|the max number of rows each read from parquet to databend processor |UInt64|
http_handler_result_timeout_secs |60 |60 |SESSION|Timeout in seconds for HTTP query result handling. |UInt64|
http_json_result_mode |display |display |SESSION|Controls how HTTP query JSON results are encoded. Values: "display" (human-readable), "driver" (driver-optimized). |String|
idle_transaction_timeout_secs |14400 |14400 |SESSION|Timeout in seconds for idle transactions. A transaction exceeding this idle time will be automatically rolled back. |UInt64|
input_read_buffer_size |4194304 |4194304 |SESSION|Sets the memory size in bytes allocated to the buffer used by the buffered reader to read data from storage. |UInt64|
join_spilling_threshold |0 |0 |SESSION|Maximum amount of memory can use for hash join, 0 is unlimited. |UInt64|
lazy_read_threshold |1000 |1000 |SESSION|Sets the maximum LIMIT in a query to enable lazy read optimization. Setting it to 0 disables the optimization. |UInt64|
load_file_metadata_expire_hours |12 |12 |SESSION|Sets the hours that the metadata of files you load data from with COPY INTO will expire in. |UInt64|
max_block_size |65536 |65536 |SESSION|Sets the maximum byte size of a single data block that can be read. |UInt64|
max_cte_recursive_depth |1000 |1000 |SESSION|Sets the maximum recursion depth for CTE recursive queries. |UInt64|
max_execute_time_in_seconds |0 |0 |SESSION|Sets the maximum query execution time in seconds. Setting it to 0 means no limit. |UInt64|
max_inlist_to_or |3 |3 |SESSION|Sets the maximum number of values that can be included in an IN expression to be converted to an OR operator. |UInt64|
max_memory_usage |6871947673 |6871947673 |SESSION|Sets the maximum memory usage in bytes for processing a single query. |UInt64|
max_query_memory_usage |0 |0 |SESSION|Maximum memory usage for a single query. Set to 0 for unlimited. Note: max_memory_usage is also available for the same purpose. |UInt64|
max_result_rows |0 |0 |SESSION|Sets the maximum number of rows that can be returned in a query result when no specific row count is specified. Setting it to 0 means no limit. |UInt64|
max_storage_io_requests |48 |48 |SESSION|Sets the maximum number of concurrent I/O requests. |UInt64|
max_threads |8 |8 |SESSION|Sets the maximum number of threads to execute a request. |UInt64|
max_vacuum_temp_files_after_query |0 |u64::MAX |SESSION|Maximum number of temporary files removed after a query completes. Set to 0 to disable automatic temp file cleanup. Default: unlimited (u64::MAX). Requires the vacuum feature. |UInt64|
max_vacuum_threads |1 |1 |SESSION|Maximum number of threads used to execute VACUUM operations. Range: 1–3. |UInt64|
numeric_cast_option |rounding |rounding |SESSION|Set numeric cast mode as "rounding" or "truncating". |String|
parquet_fast_read_bytes |0 |0 |SESSION|Parquet file with smaller size will be read as a whole file, instead of column by column. |UInt64|
parquet_uncompressed_buffer_size |2097152 |2097152 |SESSION|Sets the byte size of the buffer used for reading Parquet files. |UInt64|
prefer_broadcast_join |1 |1 |SESSION|Enables broadcast join. |UInt64|
purge_duplicated_files_in_copy |0 |0 |SESSION|Purges duplicated files during COPY INTO operations. |UInt64|
query_flight_compression |LZ4 |LZ4 |SESSION|Sets the compression algorithm for Flight query results. Values: "LZ4", "ZSTD", "NONE". |String|
query_out_of_memory_behavior |throw |throw |SESSION|Controls what happens when a query exceeds its memory limit. Values: "throw" (immediate error), "spilling" (spill to disk). |String|
query_result_cache_allow_inconsistent |0 |0 |SESSION|Determines whether Databend will return cached query results that are inconsistent with the underlying data. |UInt64|
query_result_cache_max_bytes |1048576 |1048576 |SESSION|Sets the maximum byte size of cache for a single query result. |UInt64|
query_result_cache_ttl_secs |300 |300 |SESSION|Sets the time-to-live (TTL) in seconds for cached query results. Once the TTL for a cached result has expired, the result is considered stale and will not be used for new queries.|UInt64|
quoted_ident_case_sensitive |1 |1 |SESSION|Determines whether Databend treats quoted identifiers as case-sensitive. |UInt64|
random_function_seed |0 |0 |SESSION|Sets the initial seed value for random functions. Set to 0 to use a random seed. |UInt64|
recluster_block_size |2405181685 |2405181685 |SESSION|Sets the maximum byte size of blocks for recluster |UInt64|
recluster_timeout_secs |43200 |43200 |SESSION|Sets the seconds that recluster final will be timeout. |UInt64|
replace_into_bloom_pruning_max_column_number|4 |4 |SESSION|Max number of columns used by bloom pruning for replace-into statement. |UInt64|
replace_into_shuffle_strategy |0 |0 |SESSION|0 for Block level shuffle, 1 for segment level shuffle |UInt64|
retention_period |12 |12 |SESSION|Sets the retention period in hours. |UInt64|
s3_storage_class | | |SESSION|Sets the S3 storage class for data written to S3-compatible object storage. Values: "STANDARD", "INTELLIGENT_TIERING". Default: inherited from server config. |String|
sandbox_tenant | | |SESSION|Injects a custom 'sandbox_tenant' into this session. This is only for testing purposes and will take effect only when 'internal_enable_sandbox_tenant' is turned on. |String|
script_max_steps |10000 |10000 |SESSION|Maximum number of steps (statements) allowed in a single SQL script execution (EXECUTE IMMEDIATE / stored procedures). Prevents runaway scripts. |UInt64|
spilling_bytes_threshold_per_proc |0 |0 |SESSION|Sets the maximum amount of memory in bytes that an aggregator can use before spilling data to storage during query execution. |UInt64|
spilling_memory_ratio |0 |0 |SESSION|Sets the maximum memory ratio in bytes that an aggregator can use before spilling data to storage during query execution. |UInt64|
sql_dialect |PostgreSQL |PostgreSQL |SESSION|Sets the SQL dialect. Available values include "PostgreSQL", "MySQL", and "Hive". |String|
statement_queue_ttl_in_seconds |15 |15 |SESSION|Maximum time-to-live in seconds for a statement waiting in the query queue. |UInt64|
statement_queued_timeout_in_seconds |0 |0 |SESSION|Maximum timeout in seconds for a statement waiting to be queued. Set to 0 for no timeout. |UInt64|
storage_fetch_part_num |2 |2 |SESSION|Sets the number of partitions that are fetched in parallel from storage during query execution. |UInt64|
storage_io_max_page_bytes_for_read |524288 |524288 |SESSION|Sets the maximum byte size of data pages that can be read from storage in a single I/O operation. |UInt64|
storage_io_min_bytes_for_seek |48 |48 |SESSION|Sets the minimum byte size of data that must be read from storage in a single I/O operation when seeking a new location in the data file. |UInt64|
storage_read_buffer_size |1048576 |1048576 |SESSION|Sets the byte size of the buffer used for reading data into memory. |UInt64|
timezone |Japan |UTC |GLOBAL |Sets the timezone. |String|
stream_consume_batch_size_hint |0 |0 |SESSION|Batch size hint for stream consume operations. |UInt64|
table_lock_expire_secs |5 |5 |SESSION|Sets the seconds that the table lock will expire in. |UInt64|
trace_sample_rate |1 |1 |SESSION|Sampling rate percentage for distributed tracing. Range: 0–100. Set to 0 to disable tracing, 100 to trace all queries. |UInt64|
udf_cloud_import_presign_expire_secs |259200 |259200 |SESSION|Presigned URL expiry time in seconds for cloud UDF stage imports. |UInt64|
unquoted_ident_case_sensitive |0 |0 |SESSION|Determines whether Databend treats unquoted identifiers as case-sensitive. |UInt64|
use_parquet2 |1 |1 |SESSION|Use parquet2 instead of parquet_rs when infer_schema(). |UInt64|
week_start |1 |1 |SESSION|Specifies the first day of the week (Used by week-related date functions). 0 for Sunday, 1 for Monday. |UInt64|