ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Users and roles settings

### user\_name/quota[​](#user-namequota "Direct link to user_name/quota")

Quotas allow you to track or limit resource usage over a period of time. Quotas are configured in the `quotas`
section of the `users.xml` configuration file.

You can assign a quotas set for the user. For a detailed description of quotas configuration, see [Quotas](/docs/operations/quotas).

### user\_name/databases[​](#user-namedatabases "Direct link to user_name/databases")

In this section, you can limit rows that are returned by ClickHouse for `SELECT` queries made by the current user, thus implementing basic row-level security.

**Example**

The following configuration forces that user `user1` can only see the rows of `table1` as the result of `SELECT` queries, where the value of the `id` field is 1000.

```
<user1>
    <databases>
        <database_name>
            <table1>
                <filter>id = 1000</filter>
            </table1>
        </database_name>
    </databases>
</user1>
```

The `filter` can be any expression resulting in a [UInt8](/docs/sql-reference/data-types/int-uint)-type value. It usually contains comparisons and logical operators. Rows from `database_name.table1` where filter results to 0 are not returned for this user. The filtering is incompatible with `PREWHERE` operations and disables `WHERE→PREWHERE` optimization.

## Roles[​](#roles "Direct link to Roles")

You can create any predefined roles using the `roles` section of the `user.xml` configuration file.

Structure of the `roles` section:

```
<roles>
    <test_role>
        <grants>
            <query>GRANT SHOW ON *.*</query>
            <query>REVOKE SHOW ON system.*</query>
            <query>GRANT CREATE ON *.* WITH GRANT OPTION</query>
        </grants>
    </test_role>
</roles>
```

These roles can also be granted to users from the `users` section:

```
<users>
    <user_name>
        ...
        <grants>
            <query>GRANT test_role</query>
        </grants>
    </user_name>
<users>
```

- [user\_name/password](#user-namepassword)- [TOTP Authentication Configuration](#totp-authentication-configuration)- [username/ssh-key](#user-sshkey)- [Multiple Authentication Methods](#multiple-authentication-methods)- [access\_management](#access_management-user-setting)- [grants](#grants-user-setting)- [user\_name/networks](#user-namenetworks)- [user\_name/profile](#user-nameprofile)- [user\_name/quota](#user-namequota)- [user\_name/databases](#user-namedatabases)- [Roles](#roles)

Was this page helpful?
system.quota_limits

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/quota_limits.md)

Contains information about maximums for all intervals of all quotas. Any number of rows or zero can correspond to one quota.

Columns:

- `quota_name` ([String](/docs/sql-reference/data-types/string)) — Quota name.
- `duration` ([UInt32](/docs/sql-reference/data-types/int-uint)) — Length of the time interval for calculating resource consumption, in seconds.
- `is_randomized_interval` ([UInt8](/docs/sql-reference/data-types/int-uint#integer-ranges)) — Logical value. It shows whether the interval is randomized. Interval always starts at the same time if it is not randomized. For example, an interval of 1 minute always starts at an integer number of minutes (i.e. it can start at 11:20:00, but it never starts at 11:20:01), an interval of one day always starts at midnight UTC. If interval is randomized, the very first interval starts at random time, and subsequent intervals starts one by one. Values:
- `0` — Interval is not randomized.
- `1` — Interval is randomized.
- `max_queries` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of queries.
- `max_query_selects` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of select queries.
- `max_query_inserts` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of insert queries.
- `max_errors` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of errors.
- `max_result_rows` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of result rows.
- `max_result_bytes` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of RAM volume in bytes used to store a queries result.
- `max_read_rows` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of rows read from all tables and table functions participated in queries.
- `max_read_bytes` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of bytes read from all tables and table functions participated in queries.
- `max_execution_time` ([Nullable](/docs/sql-reference/data-types/nullable)([Float64](/docs/sql-reference/data-types/float))) — Maximum of the query execution time, in seconds.
- `max_queries_per_normalized_hash` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — Maximum number of executions of any single normalized query.

Was this page helpful?
system.remote_data_paths

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/remote_data_paths.md)

Contains information about data files stored on remote disks (e.g. S3, Azure Blob Storage), including the mapping between local metadata paths and remote blob paths.

Each row represents one remote blob object associated with a data file.

Columns:

- `disk_name` ([String](/docs/sql-reference/data-types/string)) — Name of the remote disk as defined in the storage configuration.
- `path` ([String](/docs/sql-reference/data-types/string)) — Root path of the remote disk as configured in storage configuration.
- `cache_base_path` ([String](/docs/sql-reference/data-types/string)) — Base directory for cache files associated with the remote disk.
- `local_path` ([String](/docs/sql-reference/data-types/string)) — Local metadata file path relative to the ClickHouse data directory, pointing to the file that maps to the remote blob.
- `remote_path` ([String](/docs/sql-reference/data-types/string)) — Blob path in the remote object storage that the local metadata file maps to.
- `size` ([UInt64](/docs/sql-reference/data-types/int-uint)) — Compressed size of the file in bytes.
- `common_prefix_for_blobs` ([String](/docs/sql-reference/data-types/string)) — Common prefix for blobs in the remote object storage, applicable when multiple blobs share a path prefix.
- `cache_paths` ([Array(String)](/docs/sql-reference/data-types/array)) — Local cache file paths corresponding to the remote blob.

**Settings**

- [`traverse_shadow_remote_data_paths`](/docs/operations/settings/settings#traverse_shadow_remote_data_paths) — When enabled, the table also includes data from frozen partitions (the `shadow/` directory used by `ALTER TABLE ... FREEZE`). Disabled by default.

**Example**

```
SELECT * FROM system.remote_data_paths LIMIT 1 FORMAT Vertical;
```

```
Row 1:
──────
disk_name:              s3
path:                   /var/lib/clickhouse/disks/s3/
cache_base_path:        /var/lib/clickhouse/disks/s3_cache/
local_path:             store/123/1234abcd-1234-1234-1234-1234abcd1234/all_0_0_0/data.bin
remote_path:            abc123/all_0_0_0/data.bin
size:                   1048576
common_prefix_for_blobs:
cache_paths:            ['/var/lib/clickhouse/disks/s3_cache/a1/b2/c3d4e5f6']
```

**See Also**

- [Using external storage for data storage](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-s3)
- [Configuring external storage](/docs/operations/storing-data#configuring-external-storage)
- [system.disks](/docs/operations/system-tables/disks)

Was this page helpful?
system.tokenizers

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/tokenizers.md)

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the `clusterAllReplicas` function. See [here](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Shows all available tokenizers.
These can be used in functions [tokens](/docs/sql-reference/functions/splitting-merging-functions#tokens), [hasAllTokens](/docs/sql-reference/functions/string-search-functions#hasAllTokens), [hasAnyTokens](/docs/sql-reference/functions/string-search-functions#hasAnyTokens), and the [text index](/docs/engines/table-engines/mergetree-family/textindexes).

Columns:

- `name` ([String](/docs/sql-reference/data-types)) — Name of the tokenizer

**Example**

```
SELECT * FROM system.tokenizers;
```

```
┌─name────────────┐
│ ngrams          │
│ splitByNonAlpha │
│ sparseGrams     │
│ tokenbf_v1      │
│ ngrambf_v1      │
│ array           │
│ splitByString   │
│ sparse_grams    │
│ asciiCJK        │
└─────────────────┘
```

Was this page helpful?
OSS usage recommendations

ZOOCFG="$ZOOCFGDIR/zoo.cfg"
ZOO_LOG_DIR=/var/log/$NAME
USER=zookeeper
GROUP=zookeeper
PIDDIR=/var/run/$NAME
PIDFILE=$PIDDIR/$NAME.pid
SCRIPTNAME=/etc/init.d/$NAME
JAVA=/usr/local/jdk-11/bin/java
ZOOMAIN="org.apache.zookeeper.server.quorum.QuorumPeerMain"
ZOO_LOG4J_PROP="INFO,ROLLINGFILE"
JMXLOCALONLY=false
JAVA_OPTS="-Xms{{ '{{' }} cluster.get('xms','128M') {{ '}}' }} \
    -Xmx{{ '{{' }} cluster.get('xmx','1G') {{ '}}' }} \
    -Xlog:safepoint,gc*=info,age*=debug:file=/var/log/$NAME/zookeeper-gc.log:time,level,tags:filecount=16,filesize=16M
    -verbose:gc \
    -XX:+UseG1GC \
    -Djute.maxbuffer=8388608 \
    -XX:MaxGCPauseMillis=50"
```

Salt initialization:

```
description "zookeeper-{{ '{{' }} cluster['name'] {{ '}}' }} centralized coordination service"

start on runlevel [2345]
stop on runlevel [!2345]

respawn

limit nofile 8192 8192

pre-start script
    [ -r "/etc/zookeeper-{{ '{{' }} cluster['name'] {{ '}}' }}/conf/environment" ] || exit 0
    . /etc/zookeeper-{{ '{{' }} cluster['name'] {{ '}}' }}/conf/environment
    [ -d $ZOO_LOG_DIR ] || mkdir -p $ZOO_LOG_DIR
    chown $USER:$GROUP $ZOO_LOG_DIR
end script

script
    . /etc/zookeeper-{{ '{{' }} cluster['name'] {{ '}}' }}/conf/environment
    [ -r /etc/default/zookeeper ] && . /etc/default/zookeeper
    if [ -z "$JMXDISABLE" ]; then
        JAVA_OPTS="$JAVA_OPTS -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.local.only=$JMXLOCALONLY"
    fi
    exec start-stop-daemon --start -c $USER --exec $JAVA --name zookeeper-{{ '{{' }} cluster['name'] {{ '}}' }} \
        -- -cp $CLASSPATH $JAVA_OPTS -Dzookeeper.log.dir=${ZOO_LOG_DIR} \
        -Dzookeeper.root.logger=${ZOO_LOG4J_PROP} $ZOOMAIN $ZOOCFG
end script
```

## Antivirus software[​](#antivirus-software "Direct link to Antivirus software")

If you use antivirus software configure it to skip folders with ClickHouse datafiles (`/var/lib/clickhouse`) otherwise performance may be reduced and you may experience unexpected errors during data ingestion and background merges.

## Related Content[​](#related-content "Direct link to Related Content")

- [Getting started with ClickHouse? Here are 13 "Deadly Sins" and how to avoid them](https://clickhouse.com/blog/common-getting-started-issues-with-clickhouse)

- [CPU Scaling Governor](#cpu-scaling-governor)- [CPU Limitations](#cpu-limitations)- [RAM](#ram)
      - [Using less than 16GB of RAM](#using-less-than-16gb-of-ram)- [Storage Subsystem](#storage-subsystem)- [RAID](#raid)- [File System](#file-system)- [Linux Kernel](#linux-kernel)- [Network](#network)- [Huge Pages](#huge-pages)- [Hypervisor configuration](#hypervisor-configuration)- [ClickHouse Keeper and ZooKeeper](#zookeeper)- [Antivirus software](#antivirus-software)- [Related Content](#related-content)

Was this page helpful?
estimateCompressionRatio

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/estimateCompressionRatio.md)

## estimateCompressionRatio[​](#estimateCompressionRatio "Direct link to estimateCompressionRatio")

Introduced in: v25.4.0

Estimates the compression ratio of a given column without compressing it.

Note

For the examples below, the result will differ based on the default compression codec of the server.
See [Column Compression Codecs](/docs/sql-reference/statements/create/table#column_compression_codec).

**Syntax**

```
estimateCompressionRatio([codec, block_size_bytes])(column)
```

**Parameters**

- `codec` — String containing a compression codec or multiple comma-separated codecs in a single string. [`String`](/docs/sql-reference/data-types/string)
- `block_size_bytes` — Block size of compressed data. This is similar to setting both [`max_compress_block_size`](/docs/operations/settings/merge-tree-settings#max_compress_block_size) and [`min_compress_block_size`](/docs/operations/settings/merge-tree-settings#min_compress_block_size). The default value is 1 MiB (1048576 bytes). Maximum allowed value is 256 MiB (268435456 bytes). [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `column` — Column of any type. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns an estimate compression ratio for the given column. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic usage with default codec**

```
CREATE TABLE compression_estimate_example
(
    `number` UInt64
)
ENGINE = MergeTree()
ORDER BY number
SETTINGS min_bytes_for_wide_part = 0;

INSERT INTO compression_estimate_example
SELECT number FROM system.numbers LIMIT 100_000;

SELECT estimateCompressionRatio(number) AS estimate FROM compression_estimate_example
```

```
┌───────────estimate─┐
│ 1.9988506608699999 │
└────────────────────┘
```

**Using a specific codec**

```
SELECT estimateCompressionRatio('T64')(number) AS estimate FROM compression_estimate_example
```

```
┌──────────estimate─┐
│ 3.762758101688538 │
└───────────────────┘
```

**Using multiple codecs**

```
SELECT estimateCompressionRatio('T64, ZSTD')(number) AS estimate FROM compression_estimate_example
```

```
┌───────────estimate─┐
│ 143.60078980434392 │
└────────────────────┘
```

- [estimateCompressionRatio](#estimateCompressionRatio)

Was this page helpful?
Map(K, V)

Manual sharding is beneficial when vertical merges are important for reducing memory usage during merges of tables with many columns, or when the number of shards must be fixed and controlled explicitly. For most use cases, automatic bucketed serialization is simpler and sufficient.

**See Also**

- [map()](/docs/sql-reference/functions/tuple-map-functions#map) function
- [CAST()](/docs/sql-reference/functions/type-conversion-functions#CAST) function
- [-Map combinator for Map datatype](/docs/sql-reference/aggregate-functions/combinators#-map)

## Related content[​](#related-content "Direct link to Related content")

- Blog: [Building an Observability Solution with ClickHouse - Part 2 - Traces](https://clickhouse.com/blog/storing-traces-and-spans-open-telemetry-in-clickhouse)

- [Converting Tuple to Map](#converting-tuple-to-map)- [Reading subcolumns of Map](#reading-subcolumns-of-map)- [Bucketed Map Serialization in MergeTree](#bucketed-map-serialization)
      - [Enabling Bucketed Serialization](#enabling-bucketed-serialization)- [How It Works](#how-it-works)- [Settings](#bucketed-map-settings)- [Performance Trade-offs](#performance-trade-offs)- [Recommendations](#recommendations)- [Alternative Approaches](#map-alternatives)- [Related content](#related-content)

Was this page helpful?
Natural Language Processing (NLP) functions

Introduced in: v21.9.0

Finds synonyms of a given word.

There are two types of synonym extensions:

- `plain`
- `wordnet`

With the `plain` extension type you need to provide a path to a simple text file, where each line corresponds to a certain synonym set.
Words in this line must be separated with space or tab characters.

With the `wordnet` extension type you need to provide a path to a directory with the WordNet thesaurus in it.
The thesaurus must contain a WordNet sense index.

**Syntax**

```
synonyms(ext_name, word)
```

**Arguments**

- `ext_name` — Name of the extension in which search will be performed. [`String`](/docs/sql-reference/data-types/string)
- `word` — Word that will be searched in extension. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns array of synonyms for the given word. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Find synonyms**

```
SELECT synonyms('list', 'important')
```

```
['important','big','critical','crucial']
```

- [detectCharset](#detectCharset)- [detectLanguage](#detectLanguage)- [detectLanguageMixed](#detectLanguageMixed)- [detectLanguageUnknown](#detectLanguageUnknown)- [detectTonality](#detectTonality)- [lemmatize](#lemmatize)- [stem](#stem)- [synonyms](#synonyms)

Was this page helpful?
UDFs User Defined Functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/udf.md)

ClickHouse supports several types of user defined functions (UDFs):

- [Executable UDFs](#executable-user-defined-functions) start an external program or script (Python, Bash, etc.) and stream blocks of data to it over STDIN / STDOUT. Use them to integrate existing code or tooling without recompiling ClickHouse. They have higher per‑call overhead compared to in‑process options and are best for heavier logic or where a different runtime is required.
- [SQL UDFs](#sql-user-defined-functions) are defined with `CREATE FUNCTION` purely in SQL. They are inlined/expanded into the query plan (no process boundary), making them lightweight and ideal for reusing expression logic or simplifying complex calculated columns.
- [Experimental WebAssembly UDFs](#webassembly-user-defined-functions) run code compiled to WebAssembly inside a sandbox within the server process. They offer lower per‑call overhead than external executables with better isolation than native extensions, making them suitable for custom algorithms written in languages that can target WASM (e.g. C/C++/Rust).

## Executable User Defined Functions[​](#executable-user-defined-functions "Direct link to Executable User Defined Functions")

Private preview

Note

This feature is supported in private preview in ClickHouse Cloud.
Please contact ClickHouse Support at <https://clickhouse.cloud/support> to access.

ClickHouse can call any external executable program or script to process data.

The configuration of executable user defined functions can be located in one or more xml-files.
The path to the configuration is specified in the [`user_defined_executable_functions_config`](/docs/operations/server-configuration-parameters/settings#user_defined_executable_functions_config) parameter.

A function configuration contains the following settings:
ALTER QUOTA

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/quota.md)

Changes quotas.

Syntax:

```
ALTER QUOTA [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [KEYED BY {user_name | ip_address | client_key | client_key,user_name | client_key,ip_address | normalized_query_hash} | NOT KEYED]
    [FOR [RANDOMIZED] INTERVAL number {second | minute | hour | day | week | month | quarter | year}
        {MAX { {queries | query_selects | query_inserts | errors | result_rows | result_bytes | read_rows | read_bytes | execution_time | queries_per_normalized_hash} = number } [,...] |
        NO LIMITS | TRACKING ONLY} [,...]]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]
```

Keys `user_name`, `ip_address`, `client_key`, `client_key, user_name`, `client_key, ip_address`, and `normalized_query_hash` correspond to the fields in the [system.quotas](/docs/operations/system-tables/quotas) table.

Parameters `queries`, `query_selects`, `query_inserts`, `errors`, `result_rows`, `result_bytes`, `read_rows`, `read_bytes`, `execution_time`, `queries_per_normalized_hash` correspond to the fields in the [system.quotas\_usage](/docs/operations/system-tables/quotas_usage) table.

`ON CLUSTER` clause allows creating quotas on a cluster, see [Distributed DDL](/docs/sql-reference/distributed-ddl).

**Examples**

Limit the maximum number of queries for the current user with 123 queries in 15 months constraint:

```
ALTER QUOTA IF EXISTS qA FOR INTERVAL 15 month MAX queries = 123 TO CURRENT_USER;
```

For the default user limit the maximum execution time with half a second in 30 minutes, and limit the maximum number of queries with 321 and the maximum number of errors with 10 in 5 quarters:

```
ALTER QUOTA IF EXISTS qB FOR INTERVAL 30 minute MAX execution_time = 0.5, FOR INTERVAL 5 quarter MAX queries = 321, errors = 10 TO default;
```

Was this page helpful?
CREATE QUOTA

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/create/quota.md)

Creates a [quota](/docs/operations/access-rights#quotas-management) that can be assigned to a user or a role.

Syntax:

```
CREATE QUOTA [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [IN access_storage_type]
    [KEYED BY {user_name | ip_address | client_key | client_key,user_name | client_key,ip_address | normalized_query_hash} | NOT KEYED]
    [FOR [RANDOMIZED] INTERVAL number {second | minute | hour | day | week | month | quarter | year}
        {MAX { {queries | query_selects | query_inserts | errors | result_rows | result_bytes | read_rows | read_bytes | written_bytes | execution_time | failed_sequential_authentications | queries_per_normalized_hash} = number } [,...] |
         NO LIMITS | TRACKING ONLY} [,...]]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]
```

Keys `user_name`, `ip_address`, `client_key`, `client_key, user_name`, `client_key, ip_address`, and `normalized_query_hash` correspond to the fields in the [system.quotas](/docs/operations/system-tables/quotas) table.

Parameters `queries`, `query_selects`, `query_inserts`, `errors`, `result_rows`, `result_bytes`, `read_rows`, `read_bytes`, `written_bytes`, `execution_time`, `failed_sequential_authentications`, `queries_per_normalized_hash` correspond to the fields in the [system.quotas\_usage](/docs/operations/system-tables/quotas_usage) table.

`ON CLUSTER` clause allows creating quotas on a cluster, see [Distributed DDL](/docs/sql-reference/distributed-ddl).

**Examples**

Limit the maximum number of queries for the current user with 123 queries in 15 months constraint:

```
CREATE QUOTA qA FOR INTERVAL 15 month MAX queries = 123 TO CURRENT_USER;
```

For the default user limit the maximum execution time with half a second in 30 minutes, and limit the maximum number of queries with 321 and the maximum number of errors with 10 in 5 quarters:

```
CREATE QUOTA qB FOR INTERVAL 30 minute MAX execution_time = 0.5, FOR INTERVAL 5 quarter MAX queries = 321, errors = 10 TO default;
```

Create a quota where each distinct normalized query pattern gets its own bucket, limited to 100 executions per hour:

```
CREATE QUOTA qC KEYED BY normalized_query_hash FOR INTERVAL 1 hour MAX queries = 100 TO default;
```

Limit any single normalized query pattern to at most 50 executions per hour (regardless of the quota key type):

```
CREATE QUOTA qD FOR INTERVAL 1 hour MAX queries_per_normalized_hash = 50 TO default;
```

Further examples, using the xml configuration (not supported in ClickHouse Cloud), can be found in the [Quotas guide](/docs/operations/quotas).

## Related Content[​](#related-content "Direct link to Related Content")

- Blog: [Building single page applications with ClickHouse](https://clickhouse.com/blog/building-single-page-applications-with-clickhouse-and-http)

- [Related Content](#related-content)

Was this page helpful?