ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/operations_/backup_restore/03_azure_blob_storage.md)

## Syntax[​](#syntax "Direct link to Syntax")

```
-- core commands
BACKUP | RESTORE [ASYNC]
--- what to backup/restore (or exclude)
TABLE [db.]table_name           [AS [db.]table_name_in_backup] |
DICTIONARY [db.]dictionary_name [AS [db.]name_in_backup] |
DATABASE database_name          [AS database_name_in_backup] |
TEMPORARY TABLE table_name      [AS table_name_in_backup] |
VIEW view_name                  [AS view_name_in_backup] |
[EXCEPT TABLES ...] |
ALL [EXCEPT {TABLES|DATABASES}...] } [,...]
--- 
[ON CLUSTER 'cluster_name']
--- where to backup or restore to or from
TO|FROM 
File('<path>/<filename>') | 
Disk('<disk_name>', '<path>/') | 
S3('<S3 endpoint>/<path>', '<Access key ID>', '<Secret access key>', '<extra_credentials>') |
AzureBlobStorage('<connection string>/<url>', '<container>', '<path>', '<account name>', '<account key>')
--- additional settings
[SETTINGS ...]
```

**See ["command summary"](/docs/operations/backup/overview#command-summary) for more details
of each command.**

## Configuring BACKUP / RESTORE to use an AzureBlobStorage endpoint[​](#configuring-backuprestore-to-use-an-azureblobstorage-endpoint "Direct link to Configuring BACKUP / RESTORE to use an AzureBlobStorage endpoint")

To write backups to an AzureBlobStorage container you need the following pieces of information:

- AzureBlobStorage endpoint connection string / url,
- Container,
- Path,
- Account name (if url is specified)
- Account Key (if url is specified)

The destination for a backup will be specified as:

```
AzureBlobStorage('<connection string>/<url>', '<container>', '<path>', '<account name>', '<account key>')
```

```
BACKUP TABLE data TO AzureBlobStorage('DefaultEndpointsProtocol=http;AccountName=devstoreaccount1;AccountKey=Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==;BlobEndpoint=http://azurite1:10000/devstoreaccount1/;',
    'testcontainer', 'data_backup');
RESTORE TABLE data AS data_restored FROM AzureBlobStorage('DefaultEndpointsProtocol=http;AccountName=devstoreaccount1;AccountKey=Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==;BlobEndpoint=http://azurite1:10000/devstoreaccount1/;',
    'testcontainer', 'data_backup');
```

- [Syntax](#syntax)- [Configuring BACKUP / RESTORE to use an AzureBlobStorage endpoint](#configuring-backuprestore-to-use-an-azureblobstorage-endpoint)

Was this page helpful?
Cache types

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

When performing queries, ClickHouse uses different caches to speed up queries
and reduce the need to read from or write to disk.

The main cache types are:

- `mark_cache` — Cache of [marks](/docs/development/architecture#merge-tree) used by table engines of the [`MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) family.
- `uncompressed_cache` — Cache of uncompressed data used by table engines of the [`MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) family.
- Operating system page cache (used indirectly, for files with actual data).

There are also a host of additional cache types:

- DNS cache.
- [Regexp](/docs/interfaces/formats/Regexp) cache.
- Compiled expressions cache.
- [Vector similarity index](/docs/engines/table-engines/mergetree-family/annindexes) cache.
- [Text index](/docs/engines/table-engines/mergetree-family/textindexes#caching) cache.
- [Avro format](/docs/interfaces/formats/Avro) schemas cache.
- [Dictionaries](/docs/sql-reference/statements/create/dictionary) data cache.
- Schema inference cache.
- [Filesystem cache](/docs/operations/storing-data) over S3, Azure, Local and other disks.
- [Userspace page cache](/docs/operations/userspace-page-cache)
- [Query cache](/docs/operations/query-cache).
- [Query condition cache](/docs/operations/query-condition-cache).
- Format schema cache.

Should you wish to clear one of the caches, for performance tuning, troubleshooting, or data consistency reasons,
you can use the [`SYSTEM CLEAR ... CACHE`](/docs/sql-reference/statements/system) statement.

Was this page helpful?
title_wip

The fourth node is participating in the cluster, but table `event_table` still exists only on the first three nodes:

```
SELECT hostname(), database, table FROM clusterAllReplicas(default, system.tables) WHERE table = 'event_table' FORMAT PrettyCompactMonoBlock

┌─hostname()───┬─database─┬─table───────┐
│ a6a68731c21b │ default  │ event_table │
│ 92d3c04025e8 │ default  │ event_table │
│ 8e62b9cb17a1 │ default  │ event_table │
└──────────────┴──────────┴─────────────┘
```

If you need to have tables replicated on all the nodes, you may use the [Replicated](/docs/engines/database-engines/replicated) database engine in alternative to cluster discovery.

- [Overview](#overview)- [Remote servers configuration](#remote-servers-configuration)
    - [Traditional manual configuration](#traditional-manual-configuration)- [Using cluster discovery](#using-cluster-discovery)- [Observer mode](#observer-mode)- [Discovery of clusters](#discovery-of-clusters)- [Use cases and limitations](#use-cases-and-limitations)

Was this page helpful?
title_wip

## Implementation details[​](#implementation-details "Direct link to Implementation details")

For each config file, the server also generates `file-preprocessed.xml` files when starting. These files contain all the completed substitutions and overrides, and they are intended for informational use. If ZooKeeper substitutions were used in the config files but ZooKeeper is not available on the server start, the server loads the configuration from the preprocessed file.

The server tracks changes in config files, as well as files and ZooKeeper nodes that were used when performing substitutions and overrides, and reloads the settings for users and clusters on the fly. This means that you can modify the cluster, users, and their settings without restarting the server.

- [Merging configuration](#merging)
  - [Substitution by environment variables and ZooKeeper nodes](#from_env_zk)- [Substitution with file content](#substitution-with-file-content)- [Encrypting and hiding configuration](#encryption)- [User settings](#user-settings)- [XML example](#example)- [YAML examples](#example-1)- [Implementation details](#implementation-details)

Was this page helpful?
External User Authenticators and Directories

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/external-authenticators/index.md)

Not supported in ClickHouse Cloud

Note

This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The feature documented here isn't available in ClickHouse Cloud services.
See the ClickHouse [Cloud Compatibility](/docs/whats-new/cloud-compatibility) guide for more information.

ClickHouse supports authenticating and managing users using external services.

The following external authenticators and directories are supported:

- [LDAP](/docs/operations/external-authenticators/ldap#ldap-external-authenticator) [Authenticator](/docs/operations/external-authenticators/ldap#ldap-external-authenticator) and [Directory](/docs/operations/external-authenticators/ldap#ldap-external-user-directory)
- Kerberos [Authenticator](/docs/operations/external-authenticators/kerberos#kerberos-as-an-external-authenticator-for-existing-users)
- [SSL X.509 authentication](/docs/operations/external-authenticators/ssl-x509)
- HTTP [Authenticator](/docs/operations/external-authenticators/http)

Was this page helpful?
LDAP

**`role_mapping` sub-parameters**

Section with LDAP search parameters and mapping rules. When a user authenticates, while still bound to LDAP, an LDAP search is performed using `search_filter` and the name of the logged-in user. For each entry found during that search, the value of the specified attribute is extracted. For each attribute value that has the specified prefix, the prefix is removed, and the rest of the value becomes the name of a local role defined in ClickHouse, which is expected to be created beforehand by the [CREATE ROLE](/docs/sql-reference/statements/create/role) statement. There can be multiple `role_mapping` sections defined inside the same `ldap` section. All of them will be applied.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `base_dn` — Template used to construct the base DN for the LDAP search. The resulting DN will be constructed by replacing all `{user_name}`, `{bind_dn}`, and `{user_dn}` substrings of the template with the actual user name, bind DN, and user DN during each LDAP search.| `scope` `subtree` Scope of the LDAP search. Accepted values: `base`, `one_level`, `children`, `subtree`.| `search_filter` — Template used to construct the search filter for the LDAP search. The resulting filter will be constructed by replacing all `{user_name}`, `{bind_dn}`, `{user_dn}`, and `{base_dn}` substrings of the template with the actual user name, bind DN, user DN, and base DN during each LDAP search. Note that special characters must be escaped properly in XML.| `attribute` `cn` Attribute name whose values will be returned by the LDAP search.|  |  |  | | --- | --- | --- | | `prefix` empty Prefix expected in front of each string in the original list of strings returned by the LDAP search. The prefix will be removed from the original strings and the resulting strings will be treated as local role names. | | | | | | | | | | | | | | | | | |

- [LDAP server definition](#ldap-server-definition)- [LDAP external authenticator](#ldap-external-authenticator)- [LDAP external user directory](#ldap-external-user-directory)

Was this page helpful?
SSL X.509 certificate authentication

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/external-authenticators/ssl-x509.md)

Not supported in ClickHouse Cloud

Note

This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The feature documented here isn't available in ClickHouse Cloud services.
See the ClickHouse [Cloud Compatibility](/docs/whats-new/cloud-compatibility) guide for more information.

[SSL 'strict' option](/docs/operations/server-configuration-parameters/settings#openssl) enables mandatory certificate validation for the incoming connections. In this case, only connections with trusted certificates can be established. Connections with untrusted certificates will be rejected. Thus, certificate validation allows to uniquely authenticate an incoming connection. `Common Name` or `subjectAltName extension` field of the certificate is used to identify the connected user. `subjectAltName extension` supports the usage of one wildcard '\*' in the server configuration. This allows to associate multiple certificates with the same user. Additionally, reissuing and revoking of the certificates does not affect the ClickHouse configuration.

To enable SSL certificate authentication, a list of `Common Name`'s or `Subject Alt Name`'s for each ClickHouse user must be specified in the settings file `users.xml` :

**Example**

```
<clickhouse>
    <!- ... -->
    <users>
        <user_name_1>
            <ssl_certificates>
                <common_name>host.domain.com:example_user</common_name>
                <common_name>host.domain.com:example_user_dev</common_name>
                <!-- More names -->
            </ssl_certificates>
            <!-- Other settings -->
        </user_name_1>
        <user_name_2>
            <ssl_certificates>
                <subject_alt_name>DNS:host.domain.com</subject_alt_name>
                <!-- More names -->
            </ssl_certificates>
            <!-- Other settings -->
        </user_name_2>
        <user_name_3>
            <ssl_certificates>
                <!-- Wildcard support -->
                <subject_alt_name>URI:spiffe://foo.com/*/bar</subject_alt_name>
            </ssl_certificates>
        </user_name_3>
    </users>
</clickhouse>
```

For the SSL [`chain of trust`](https://en.wikipedia.org/wiki/Chain_of_trust) to work correctly, it is also important to make sure that the [`caConfig`](/docs/operations/server-configuration-parameters/settings#openssl) parameter is configured properly.

Was this page helpful?
In case of any errors, the part of the log data for which the error has occurred will be silently lost. Check the server log for error messages if the data does not arrive.

## 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)

- [Supplying trace context to ClickHouse](#supplying-trace-context-to-clickhouse)- [Propagating the trace context](#propagating-the-trace-context)- [Tracing ClickHouse Keeper Requests](#tracing-clickhouse-keeper-requests)
      - [Enabling Keeper Tracing](#enabling-keeper-tracing)- [Keeper Span Types](#keeper-span-types)- [Sampling and Performance](#sampling-and-performance)- [Tracing the ClickHouse Itself](#tracing-the-clickhouse-itself)- [Log-query-settings](#log-query-settings)- [Integration with monitoring systems](#integration-with-monitoring-systems)- [Related content](#related-content)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/optimizing-performance/profile-guided-optimization.md)

Profile-Guided Optimization (PGO) is a compiler optimization technique where a program is optimized based on the runtime profile.

According to the tests, PGO helps with achieving better performance for ClickHouse. According to the tests, we see improvements up to 15% in QPS on the ClickBench test suite. The more detailed results are available [here](https://pastebin.com/xbue3HMU). The performance benefits depend on your typical workload - you can get better or worse results.

More information about PGO in ClickHouse you can read in the corresponding GitHub [issue](https://github.com/ClickHouse/ClickHouse/issues/44567).

## How to build ClickHouse with PGO?[​](#how-to-build-clickhouse-with-pgo "Direct link to How to build ClickHouse with PGO?")

There are two major kinds of PGO: [Instrumentation](https://clang.llvm.org/docs/UsersManual.html#using-sampling-profilers) and [Sampling](https://clang.llvm.org/docs/UsersManual.html#using-sampling-profilers) (also known as AutoFDO). In this guide is described the Instrumentation PGO with ClickHouse.

1. Build ClickHouse in Instrumented mode. In Clang it can be done via passing `-fprofile-generate` option to `CXXFLAGS`.
2. Run instrumented ClickHouse on a sample workload. Here you need to use your usual workload. One of the approaches could be using [ClickBench](https://github.com/ClickHouse/ClickBench) as a sample workload. ClickHouse in the instrumentation mode could work slowly so be ready for that and do not run instrumented ClickHouse in performance-critical environments.
3. Recompile ClickHouse once again with `-fprofile-use` compiler flags and profiles that are collected from the previous step.

A more detailed guide on how to apply PGO is in the Clang [documentation](https://clang.llvm.org/docs/UsersManual.html#profile-guided-optimization).

If you are going to collect a sample workload directly from a production environment, we recommend trying to use Sampling PGO.

- [How to build ClickHouse with PGO?](#how-to-build-clickhouse-with-pgo)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/settings/memory-overcommit.md)

Memory overcommit is an experimental technique intended to allow to set more flexible memory limits for queries.

The idea of this technique is to introduce settings which can represent guaranteed amount of memory a query can use.
When memory overcommit is enabled and the memory limit is reached ClickHouse will select the most overcommitted query and try to free memory by killing this query.

When memory limit is reached any query will wait some time during attempt to allocate new memory.
If timeout is passed and memory is freed, the query continues execution.
Otherwise an exception will be thrown and the query is killed.

Selection of query to stop or kill is performed by either global or user overcommit trackers depending on what memory limit is reached.
If overcommit tracker can't choose query to stop, MEMORY\_LIMIT\_EXCEEDED exception is thrown.

## User overcommit tracker[​](#user-overcommit-tracker "Direct link to User overcommit tracker")

User overcommit tracker finds a query with the biggest overcommit ratio in the user's query list.
Overcommit ratio for a query is computed as number of allocated bytes divided by value of `memory_overcommit_ratio_denominator_for_user` setting.

If `memory_overcommit_ratio_denominator_for_user` for the query is equals to zero, overcommit tracker won't choose this query.

Waiting timeout is set by `memory_usage_overcommit_max_wait_microseconds` setting.

**Example**

```
SELECT number FROM numbers(1000) GROUP BY number SETTINGS memory_overcommit_ratio_denominator_for_user=4000, memory_usage_overcommit_max_wait_microseconds=500
```

## Global overcommit tracker[​](#global-overcommit-tracker "Direct link to Global overcommit tracker")

Global overcommit tracker finds a query with the biggest overcommit ratio in the list of all queries.
In this case overcommit ratio is computed as number of allocated bytes divided by value of `memory_overcommit_ratio_denominator` setting.

If `memory_overcommit_ratio_denominator` for the query is equals to zero, overcommit tracker won't choose this query.

Waiting timeout is set by `memory_usage_overcommit_max_wait_microseconds` parameter in the configuration file.

- [User overcommit tracker](#user-overcommit-tracker)- [Global overcommit tracker](#global-overcommit-tracker)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/settings/overview.md)

## Overview[​](#overview "Direct link to Overview")

Note

XML-based Settings Profiles and [configuration files](/docs/operations/configuration-files) are currently not
supported for ClickHouse Cloud. To specify settings for your ClickHouse Cloud
service, you must use [SQL-driven Settings Profiles](/docs/operations/access-rights#settings-profiles-management).

There are following main groups of ClickHouse settings:

- Global server settings
- Session settings
- Query settings
- Background operations settings

Global settings apply by default unless overridden at further levels. Session settings can be specified via profiles, user configuration and SET commands. Query settings can be provided via SETTINGS clause and are applied to individual queries. Background operations settings are applied to Mutations, Merges and potentially other operations, executed asynchronously in the background.

## Viewing non-default settings[​](#see-non-default-settings "Direct link to Viewing non-default settings")

To view which settings have been changed from their default value you can query the
`system.settings` table:

```
SELECT name, value FROM system.settings WHERE changed
```

If no settings have been changed from their default value, then ClickHouse will
return nothing.

To check the value of a particular setting, you can specify the `name` of the
setting in your query:

```
SELECT name, value FROM system.settings WHERE name = 'max_threads'
```

Which will return something like this:

```
┌─name────────┬─value─────┐
│ max_threads │ 'auto(8)' │
└─────────────┴───────────┘

1 row in set. Elapsed: 0.002 sec.
```

## Further reading[​](#further-reading "Direct link to Further reading")

- See [global server settings](/docs/operations/server-configuration-parameters/settings) to learn more about configuring your
  ClickHouse server at the global server level.
- See [session settings](/docs/operations/settings/query-level) to learn more about configuring your ClickHouse
  server at the session level.
- See [context hierarchy](/docs/development/architecture#context) to learn more about configuration processing by Clickhouse.

- [Overview](#overview)- [Viewing non-default settings](#see-non-default-settings)- [Further reading](#further-reading)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/settings/permissions-for-queries.md)

Queries in ClickHouse can be divided into several types:

1. Read data queries: `SELECT`, `SHOW`, `DESCRIBE`, `EXISTS`.
2. Write data queries: `INSERT`, `OPTIMIZE`.
3. Change settings query: `SET`, `USE`.
4. [DDL](https://en.wikipedia.org/wiki/Data_definition_language) queries: `CREATE`, `ALTER`, `RENAME`, `ATTACH`, `DETACH`, `DROP` `TRUNCATE`.
5. `KILL QUERY`.

The following settings regulate user permissions by the type of query:

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

Restricts permissions for read data, write data, and change settings queries.

When set to 1, allows:

- All types of read queries (like SELECT and equivalent queries).
- Queries that modify only session context (like USE).

When set to 2, allows the above plus:

- SET and CREATE TEMPORARY TABLE

  Tip

  Queries like EXISTS, DESCRIBE, EXPLAIN, SHOW PROCESSLIST, etc are equivalent to SELECT, because they just do select from system tables.

Possible values:

- 0 — Read, Write, and Change settings queries are allowed.
- 1 — Only Read data queries are allowed.
- 2 — Read data and Change settings queries are allowed.

Default value: 0

Note

After setting `readonly = 1`, the user can't change `readonly` and `allow_ddl` settings in the current session.

When using the `GET` method in the [HTTP interface](/docs/interfaces/http), `readonly = 1` is set automatically. To modify data, use the `POST` method.

Setting `readonly = 1` prohibits the user from changing settings. There is a way to prohibit the user from changing only specific settings. Also there is a way to allow changing only specific settings under `readonly = 1` restrictions. For details see [constraints on settings](/docs/operations/settings/constraints-on-settings).

## allow\_ddl[​](#allow_ddl "Direct link to allow_ddl")

Allows or denies [DDL](https://en.wikipedia.org/wiki/Data_definition_language) queries.

Possible values:

- 0 — DDL queries are not allowed.
- 1 — DDL queries are allowed.

Default value: 1

Note

You cannot run `SET allow_ddl = 1` if `allow_ddl = 0` for the current session.

KILL QUERY

`KILL QUERY` can be performed with any combination of readonly and allow\_ddl settings.

- [readonly](#readonly)- [allow\_ddl](#allow_ddl)

Was this page helpful?