Docs

ByteHouse Cloud Data Warehouse Version

SQL reference

Parameter list

This article introduces the query parameters and table parameters that ByteHouse supports setting.

Background information

The following table provides an introduction to the terminology used in this article.

| | | \

Name	Introduction
		\
worker	Node of the user's virtual warehouse.
		\
Dedup	Deduplication (removing duplicate data) is used by the unique key engine to ensure data uniqueness, achieving the desired result through deduplication.
		\
Dedup task	Deduplication task, selected by dedup worker; the selected data blocks (data parts) will become visible after the dedup task is completed.
		\
Dedup worker	Deduplication compute node, used to select and run dedup task instances; by default, the account-level write virtual warehouse is selected.
		\
Staging area	Data temporary storage area, located in the default cache or local virtual warehouse disk; used for batching and batch processing deduplication data.
		\
Staged part	Data block in the temporary storage area, located in the default cache or local virtual warehouse disk; part data stored in the temporary storage area is not visible to queries.

Query parameters

Data query (SELECT)

| | | | | | \

Parameter name	Parameter type	Optional values	Default value if not configured	Parameter description
					\
enable_point_lookup_profile	Bool	true/false	false	Whether to use point lookup profile, used to optimize point lookup efficiency.
					\
max_threads	UInt64	0 ≤ X ≤ number of CPU cores	0	Degree of parallelism for single-machine query.
					\
max_plan_segment_num	UInt64	0 ≤ X	500	Maximum allowed number of plan segments; 0 means unlimited.
					\
enable_intermediate_result_cache	Bool	true/false	false	Whether to activate intermediate result cache.
					\
log_segment_profiles	Bool	true/false	false	Whether to activate ByteHouse SQL performance diagnosis function.
					\
max_execution_time	UInt64	1 ≤ X ≤ 8000	You can log in to the ByteHouse cloud data warehouse console, go to Tenant management > Parameter settings > virtual warehouse parameters, search for the parameter name, view reference values; for details on using this feature, see [Set virtual warehouse parameters](/docs/6517/1455940#71e811a6.	Maximumexecutiontime,inseconds.
					\
max_query_cpu_seconds	UInt64	0 ≤ X ≤ 20000		Limits the maximum CPU resources a query segment can consume, in seconds.
					\
distributed_max_parallel_size	UInt64	0 ≤ X ≤ number of virtual warehouse workers	0	Degree of parallelism for non-source plan segments; 0 represents the number of all nodes in the virtual warehouse. If parallelism is high or the number of virtual warehouse workers is greater than 10, it is recommended to reduce it.
					\
join_algorithm	JoinAlgorithm	'grace_hash' 'auto', 'hash', 'partial_merge', 'prefer_partial_merge', 'parallel_hash', 'auto'	auto	Select join algorithm.
					\
max_memory_usage_ratio	Float	0.01 ≤ X ≤ 0.99	None	Maximum memory usage ratio for a single query.

You can log in to the ByteHouse cloud data warehouse console, go to Tenant management > Parameter settings > virtual warehouse parameters, and view reference values in the virtual warehouse module.

Data insertion (INSERT)

| | | | | | \

Parameter name	Parameter type	Optional values	No configuration default value	Parameter description
					\
enable_unique_partial_update	Bool	true/false	true	\
				Whether to use partial column update mode. Requires use together with the table-level parameter of the same name; partial column update mode is effective only when both this parameter and the table-level parameter are enabled.
					\
partial_update_enable_row_level_defaults	Bool	true/false	true	When performing partial column update, columns not updated are filled with row-level default values.
					\
staging_area_wait_mode	StagingAreaWaitMode	no_wait/wait_txn_visible	no_wait	After enabling the staging area, data write mode. `no_wait` indicates asynchronous write mode, `wait_txn_visible` indicates synchronous write mode.
					\
enable_staging_area_for_write	Bool	true/false	false	Whether to enable the staging area, supports asynchronous write or concurrent write optimization scenarios.
					\
optimize_unique_table_write	Bool	true/false	false	Optimization switch for importing into unique key tables via `INSERT SELECT`. It is recommended to enable only when the staging area is not enabled and `dedup_impl_version='dedup_in_txn_commit'`.
					\
max_insert_threads	UInt64	Positive integer	0	Configures the thread pool size for write operations when worker nodes execute `INSERT SELECT` requests.
					\
min_insert_block_size_bytes	UInt64	Positive integer	1073710080	Controls the minimum size, in bytes, of batch blocks for data in `INSERT SELECT` operations.
					\
min_insert_block_size_rows	UInt64	Positive integer	1048545	Controls the minimum number of rows for batch blocks in `INSERT SELECT` operations.

Table parameters

| | | | | | \

Parameter name	Parameter type	Optional values	No configuration default value	Parameter description
			\
Unique key table deduplication related
					\
unique_table_dedup_threads	MaxThreads	Integer value not exceeding the number of cores	8	Each deduplication task is split into subtasks of different deduplication granularity; this parameter configures the thread pool size for these subtasks.
					\
cloud_enable_staging_area	Bool	true/false	false	Whether to enable the staging area, supports asynchronous write or concurrent write optimization scenarios.
					\
cloud_staging_area_wait_mode	StagingAreaWaitMode	no_wait/wait_txn_visible	no_wait	After enabling the staging area, data write mode. `no_wait` indicates asynchronous write mode, `wait_txn_visible` indicates synchronous write mode.
					\
min_check_txn_staging_area_visible_interval	UInt64	Integer	50	When the staging area is enabled and the write mode is wait_txn_visible, the minimum polling interval for checking whether data is visible, in milliseconds (ms).
					\
max_check_txn_staging_area_visible_interval	UInt64	Integer	1000	When the staging area is enabled and the write mode is wait_txn_visible, the maximum polling interval for checking whether data is visible, in milliseconds (ms).
					\
max_dedup_worker_number	UInt64	Integer	1	Number of dedup workers; it is recommended to keep consistent with the number of pods set in cnch_vw_write compute group.
					\
pick_dedup_worker_by_mod	Bool	true/false	false	Dedup worker distribution mode: when set to true, workers are distributed by modulo of the total number of nodes in the compute group, ensuring even distribution; when false (default), workers are distributed by hash value, which may not be even.
					\
max_staged_part_number_per_task	UInt64	Integer	100	The Max number of staged parts for each selected dedup Mission.
					\
max_staged_part_rows_per_task	UInt64	Integer	15000000	The Max total number of rows in staged parts for each selected dedup Mission.
					\
dedup_impl_version	DedupImplVersion	dedup_in_write_suffix/	\
		dedup_in_txn_commit	dedup_in_write_suffix	Dedup implementation, `dedup_in_write_suffix` runs deduplication logic at the end of the write phase, while `dedup_in_txn_commit` runs deduplication logic during the transaction pre-commit phase, which is an optimized Version.
					\
allow_nullable_key	UInt8	0/1	0	When activated, Support project nullable Key as a unique Key.
			\
Background Mission related
					\
enable_addition_bg_task	Bool	true/false	false	Whether to allow more merge Missions to run concurrently.
					\
max_partition_for_multi_select	UInt64	Integer	10	Merge Missions first select partitions, then select ranges from the partitions. If there are many partitions, this Value usually needs to be increased.
					\
max_addition_bg_task_num	UInt64	Integer	32	Limits the Max number of concurrent background Missions.
					\
cnch_merge_enable_batch_select	Bool	true/false	true	Batch_select selects a large batch of ranges in each round, and subsequent rounds directly submit these ranges, shortening the cycle for triggering merge Actions, so that the total number of Missions truly reaches the concurrency Cap.
					\
cnch_merge_max_total_rows_to_merge	UInt64	Integer	50000000	The Max number of rows in a single merged part.
			\
Part Column Update Function related
					\
enable_unique_partial_update	Bool	true/false	false	Whether the table has activated Part Column Update capability.
					\
partial_update_max_process_parts	UInt64	Integer	100	When running Part Column Update Missions, sub-Missions are split according to this Param.
					\
partial_update_query_parts_thread_size	MaxThreads	Numerical value not exceeding the number of cores	8	The thread pool Size for reading Historical data during Part Column Update Missions.
					\
partial_update_replace_columns_thread_size	MaxThreads	Numerical value not exceeding the number of cores	8	The thread pool Size for the replace column stage in Part Column Update Mode.
					\
partial_update_replace_if_not_null	Bool	true/false	false	In Part Column Update Mode, update Actions are only performed when the Value is Not null.

Last updated: 2026.03.31 16:16:45

ByteHouse Cloud Data Warehouse Version

Data query (SELECT) #

Data insertion (INSERT) #

Data query (SELECT)

Data insertion (INSERT)