ClickHouse/clickhouse-docs-embeddings huggingface dataset:
How to Configure Settings for a User in ClickHouse

Learn how to define settings in ClickHouse for individual queries, client sessions, or specific users using `SET` and `ALTER USER` commands.


## Introduction[​](#introduction "Direct link to Introduction")

There are several ways to define a setting for a user in ClickHouse, depending on the use case and how long you want the setting to be configured. Let's look at a few scenarios...

## Configure a setting for a single query[​](#configure-a-setting-for-a-single-query "Direct link to Configure a setting for a single query")

A `SELECT` query can contain a `SETTINGS` clause where you can define any number of settings. The settings are only applied for that particular query. For example:

```
SELECT *
FROM my_table
SETTINGS max_threads = 8;
```

The maximum number of threads will be 8 for this particular query.

## Configure a setting for a session[​](#configure-a-setting-for-a-session "Direct link to Configure a setting for a session")

You can define a setting for the lifetime of a client session using a `SET` clause. This is handy for ad-hoc testing or for when you want a setting to live for the lifetime of a few queries - but not longer.

```
SET max_threads = 8;

SELECT *
FROM my_table;
```

## Configure a setting for a particular user[​](#configure-a-setting-for-a-particular-user "Direct link to Configure a setting for a particular user")

Use `ALTER USER` to define a setting just for one user. For example:

```
ALTER USER my_user_name SETTINGS max_threads = 8;
```

You can verify it worked by logging out of your client, logging back in, then use the `getSetting` function:

```
SELECT getSetting('max_threads');
```

Note

Specifying the settings this way will overwrite the current settings of the user i.e if you had other settings applied to the user but did not specify them in the `ALTER USER` statement, they will be lost.

If you want to retain your settings use the `ALTER USER ... MODIFY SETTING ...` instead

Mar 1, 2023 · 2 min read
title_wip

Learn how to fix timeout errors when using `remote` or `remoteSecure` table functions in ClickHouse by adjusting the connection timeout settings.


## Code: 279. DB::NetException: All connection tries failed.[​](#code-279-dbnetexception-all-connection-tries-failed "Direct link to Code: 279. DB::NetException: All connection tries failed.")

**Problem**
[`remote()` or `remoteSecure()`](https://clickhouse.com/docs/sql-reference/table-functions/remote/) table function allows the access of remote table from another ClickHouse node.

When using these functions on a node that is located more than 100ms (latency wise) away from the remote node, it is common to encounter a timeout error.

For example:

```
4776d4bd8190 :) SELECT * FROM remoteSecure('HOSTNAME.us-east-2.aws.clickhouse.cloud', DATABASE, TABLE, 'USER', 'USER_PASSWORD')

SELECT *
FROM remoteSecure('HOSTNAME.us-east-2.aws.clickhouse.cloud', DATABASE, TABLE, 'USER', 'USER_PASSWORD')

Query id: 2bd6ddd0-66d9-4d19-830f-87e3cec3724b


0 rows in set. Elapsed: 1.213 sec.

Received exception from server (version 22.6.9):
Code: 519. DB::Exception: Received from localhost:9000. DB::NetException. DB::NetException: All attempts to get table structure failed. Log:

Code: 279. DB::NetException: All connection tries failed. Log:

Code: 209. DB::NetException: Timeout: connect timed out: 18.218.245.169:9440 (hc7d963h1t.us-east-2.aws.clickhouse.cloud:9440, connection timeout 100 ms). (SOCKET_TIMEOUT) (version 22.6.9.11 (official build))
Code: 209. DB::NetException: Timeout: connect timed out: 18.218.245.169:9440 (hc7d963h1t.us-east-2.aws.clickhouse.cloud:9440, connection timeout 100 ms). (SOCKET_TIMEOUT) (version 22.6.9.11 (official build))
Code: 209. DB::NetException: Timeout: connect timed out: 18.218.245.169:9440 (hc7d963h1t.us-east-2.aws.clickhouse.cloud:9440, connection timeout 100 ms). (SOCKET_TIMEOUT) (version 22.6.9.11 (official build))

. (ALL_CONNECTION_TRIES_FAILED) (version 22.6.9.11 (official build))

. (NO_REMOTE_SHARD_AVAILABLE)
```

**Workaround**
To get increase the connection timeout, set [`connect_timeout_with_failover_secure_ms`](https://github.com/ClickHouse/ClickHouse/blob/master/src/Core/Settings.h#L67) to a higher value (e.g. 1 second) from the default 100ms.

```
4776d4bd8190 :) SELECT * FROM remoteSecure('HOSTNAME.us-east-2.aws.clickhouse.cloud:9440', DATABASE, TABLE, 'USER', 'USER_PASSWORD') SETTINGS connect_timeout_with_failover_secure_ms = 1000

SELECT *
FROM remoteSecure('HOSTNAME.us-east-2.aws.clickhouse.cloud:9440', DATABASE, TABLE, 'USER', 'USER_PASSWORD')
SETTINGS connect_timeout_with_failover_secure_ms = 1000

Query id: 8e2f4d41-307b-4e61-abb8-809190023247

┌─x─┐
│ 1 │
└───┘

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

Mar 20, 2023 · 2 min read
title_wip

Curious what ClickHouse means? Take a look inside this knowledge base article to find out!


## ClickHouse Meaning Explained[​](#clickhouse-meaning-explained "Direct link to ClickHouse Meaning Explained")

It’s a combination of "**Click**stream" and "Data ware**House**". It comes from the original use case at Yandex.Metrica, where ClickHouse was supposed to keep records of all clicks by people from all over the Internet, and it still does the job. You can read more about this use case on [ClickHouse history](https://clickhouse.com/docs/about-us/history) page.

This two-part meaning has two consequences:

- The only correct way to write `Click**H**ouse` is with capital H.
- If you need to abbreviate it, use **CH**. For some historical reasons, abbreviating as CK is also popular in China, mostly because one of the first talks about ClickHouse in Chinese used this form.

References

Many years after ClickHouse got its name, this approach of combining two words that are meaningful on their own has been highlighted as the best way to name a database in a [research by Andy Pavlo](https://www.cs.cmu.edu/~pavlo/blog/2020/03/on-naming-a-database-management-system.html), an Associate Professor of Databases at Carnegie Mellon University. ClickHouse shared his "best database name of all time" award with Postgres.

Sep 1, 2021 · One min read
title_wip

There is an issue where data in dictionaries may not be visible immediately after creation.


## Dictionaries in ClickHouse[​](#dictionaries-in-clickhouse "Direct link to Dictionaries in ClickHouse")

Dictionaries created in ClickHouse Cloud may experience inconsistency during the initial creation phase. This means that you may not see any data in the dictionary right after creation. However, after several retries, the creation query may land on different replicas, and data will be visible.

This sometimes occurs because the dictionary was created before the part reached the server. As an example:

```
2024-01-25 13:38:25.615837 - CREATE DICTIONARY received
2024-01-25 13:38:25.626468 - CREATE DICTIONARY finished
2024-01-25 13:38:25.733008 - Part all_0_0_0 downloaded
```

As you can see, the part only arrived after the dictionary was created. This can be a bigger problem if you are using `LIFETIME(MIN 0 MAX 0)` because this means that dictionary will never be refreshed automatically. Therefore, the dictionary will remain empty until the command `RELOAD DICTIONARIES` is executed.

The solution to this issue is to use a `SELECT` query instead of specifying a source table when creating the dictionary and enabling the setting `select_sequential_consistency=1`.

Instead of specifying a source table:

```
SOURCE(CLICKHOUSE(
    table 'test.temp_title_table_1706189903924'
    user default password 'PASSWORD'))
```

Use a `SELECT` query with `select_sequential_consistency=1`:

```
SOURCE(CLICKHOUSE(QUERY
    'SELECT songTitle, mappedTitle
    FROM test.temp_title_table_1706189903924
    SETTINGS select_sequential_consistency=1' USER default PASSWORD ''))
```

## Why does this issue occur?[​](#why-does-this-issue-occur "Direct link to Why does this issue occur?")

When you insert data and then create or reload a dictionary, the DDL may reach a replica before the data (or new data) does. This leads to the dictionaries being inconsistent between replicas. Then, depending on which replica receives the query, you may get different results.

Note that the same thing happens when you insert and immediately after read from a table. If you read from a replica that hasn't replicated the data yet, you won't see the newly inserted data. When you need sequential consistency, at the cost of performance (which is why it's generally not recommended to use) you can enable `select_sequential_consistency`.

The case of dictionaries is a bit trickier since dictionaries don't use the settings from the query, but the settings from the server. As a result, when loading data into the dictionary, even if you `SET select_sequential_consistency=1` data may load inconsistently across replicas. Specifying `select_sequential_consistency=1` in the dictionary source query allows the dictionary to adhere to this setting even if it's not globally enabled as a server setting.

Apr 12, 2024 · 2 min read
title_wip

Learn how to create a ClickHouse dictionary using string keys and values from a MergeTree table as the source, with examples of setup and usage.


## Question[​](#question "Direct link to Question")

How to create a ClickHouse dictionary using string keys and string values from a MergeTree table source

## Answer[​](#answer "Direct link to Answer")

- Create the source table for the dictionary

```
CREATE TABLE db1.table1_dict_source
(
  id UInt32,
  email String,
  name String
)
ENGINE = MergeTree()
ORDER BY id;
```

- Insert rows

```
INSERT INTO db1.table1_dict_source
(id, email, name)
VALUES
(1, '[email protected]', 'me'),
(2, '[email protected]', 'you');
```

- Create dictionary with key/value both as String

```
CREATE DICTIONARY db1.table1_dict
(
    email String,
    name String
)
PRIMARY KEY email
SOURCE(
CLICKHOUSE(
TABLE 'table1_dict_source'
USER 'default'
PASSWORD 'ClickHouse123!'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(MIN 0 MAX 1000);
```

- Test the dictionary

```
clickhouse-cloud :) SELECT * from db1.table1_dict;

SELECT *
FROM db1.table1_dict

Query id: 098396ce-11dd-4c71-a0e1-40723dd67ddc

┌─email──────────┬─name─┐
│ [email protected]  │ me   │
│ [email protected] │ you  │
└────────────────┴──────┘

2 rows in set. Elapsed: 0.001 sec.
```

You can also use dictGet function to retrieve values from it such as:

```
SELECT dictGet('db1.table1_dict', 'name', '[email protected]');
```

Response:

```
┌─dictGet('db1.table1_dict', 'name', '[email protected]')─┐
│ me                                                  │
└─────────────────────────────────────────────────────┘
```

More details - <https://clickhouse.com/docs/sql-reference/functions/ext-dict-functions>

Jul 10, 2023 · One min read
title_wip

Understand the key differences between official ClickHouse builds and 3rd-party builds, including updates, compatibility, and security considerations.


## Question[​](#question "Direct link to Question")

I see other vendors providing their own builds of ClickHouse. What is the difference between official ClickHouse builds and these 3rd-party builds?

## Answer[​](#answer "Direct link to Answer")

Here are some of the differences we have observed with other builds:

- The strings **"official"** are replaced with the name of the vendor
- They appear after several months of delay and ***don't include recent bug fixes***, which means these builds can contain vulnerabilities that have been fixed in the official versions
- The builds are not bit-identical, and the addresses in the code are different. As a result, stack traces from these builds cannot be analyzed, and the ClickHouse team cannot answer questions about these builds
- The builds are not auditable or reproducible - there is no publicly accessible CI system with the same build logs
- The ClickHouse test suite is not run on these builds, so they are not verified to work by the test suite
- They might not be available for all architectures (like ARM, etc.)
- Sometimes they include patches targeted for one particular customer that can break compatibility and introduce extra risk

We recommend running the latest version of ClickHouse using the official builds following the [install instructions](https://clickhouse.com/docs/install) in the documentation:

- We release a **stable version** every month, and three latest stable releases are supported in terms of diagnostics and backporting of bug fixes.
- We also release a **long-term support (LTS) version** twice a year that is supported for a year after its initial release, which is really only meant for companies that do not allow for frequent upgrades or using non-LTS software. (We are big fans of the monthly stable builds!)

We have [more details between stable vs. LTS releases](https://clickhouse.com/docs/faq/operations/production#how-to-choose-between-clickhouse-releases) in the docs.

Jun 26, 2023 · 2 min read
title_wip

How to use the exchange command to switch tables



---

## Question[​](#question "Direct link to Question")

How do I use the [`EXCHANGE`](/docs/sql-reference/statements/exchange) command to switch table names?

## Answer[​](#answer "Direct link to Answer")

The `EXCHANGE` command is useful when you need to switch a current table with another table that is temporary where possibly Primary Keys or other settings were updated.
This happens atomically vs with the `RENAME` command.
It is also useful when you have Materialized Views triggering on a source table and are trying to avoid rebuilding the view.

Below is a simple example on how it works and how to test:

- Create sample database

```
create database db1;
```

- Create example table

```
create table db1.table1_exchange
(
 id Int32,
 string_field String
)
engine = MergeTree()
order by id;
```

- Insert sample row

```
insert into db1.table1_exchange
values
(1, 'a');
```

- Create example temporary table that will be exchanged

```
create table db1.table1_exchange_temp
(
 id Int32,
 string_field String
)
engine = MergeTree()
order by id;
```

- Insert sample row into the temporary table

```
insert into db1.table1_exchange_temp
values
(2, 'b');
```

- Run the `EXCHANGE` command to switch the tables

```
exchange tables db1.table1_exchange and db1.table1_exchange_temp;
```

- Test that the tables are now exchanged and show the rows are switched

```
select * from db1.table1_exchange;
```

```
┌─id─┬─string_field─┐
│  2 │ b            │
└────┴──────────────┘

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

Nov 21, 2024 · 2 min read
title_wip

Learn how to use `ON CLUSTER` and `clusterAllReplicas` to execute SYSTEM statements and queries across all nodes in a ClickHouse Cloud service.


## Answer[​](#answer "Direct link to Answer")

In order to execute the same query on all nodes of a ClickHouse Cloud service, we can use [clusterAllReplicas](https://clickhouse.com/docs/sql-reference/table-functions/cluster/).

For example, in order to get entries from a (node-local) system table from all nodes, you can use:

```
SELECT ... FROM clusterAllReplicas(default, system.TABLE) ...;
```

Similarly, you can execute the same [SYSTEM statement](https://clickhouse.com/docs/sql-reference/statements/system/) on all nodes with a single statement, by using the [ON CLUSTER](https://clickhouse.com/docs/sql-reference/distributed-ddl/) clause:

```
SYSTEM ... ON CLUSTER default;
```

For example for [dropping the filesystem cache](https://clickhouse.com/docs/sql-reference/statements/system/#drop-filesystem-cache) from all nodes, you can use:

```
SYSTEM DROP FILESYSTEM CACHE ON CLUSTER default;
```

Mar 1, 2023 · One min read
title_wip

Learn various methods to export data from ClickHouse, including `INTO OUTFILE`, the File table engine, and command-line redirection.


## Using INTO OUTFILE Clause[​](#using-into-outfile-clause "Direct link to Using INTO OUTFILE Clause")

Add an [INTO OUTFILE](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile) clause to your query.

For example:

```
SELECT * FROM table INTO OUTFILE 'file'
```

By default, ClickHouse uses the file extension of the filename to deteremine the output format and compression. For example, all of the rows in `nyc_taxi` will be exported to the `nyc_taxi.parquet` using the Parquet format:

```
SELECT *
FROM nyc_taxi
INTO OUTFILE 'taxi_rides.parquet'
```

And the following file will be a compressed, tab-separated file:

```
SELECT *
FROM nyc_taxi
INTO OUTFILE 'taxi_rides.tsv.gz'
```

If ClickHouse can not determine the format from the file extension, then the output format defaults to [TabSeparated](https://clickhouse.com/docs/interfaces/formats) for output data. To specify the [output format](https://clickhouse.com/docs/interfaces/formats), use the [FORMAT clause](https://clickhouse.com/docs/sql-reference/statements/select/format).

For example:

```
SELECT *
FROM nyc_taxi
INTO OUTFILE 'taxi_rides.txt'
FORMAT CSV
```

## Using the File table engine[​](#using-a-file-engine-table "Direct link to Using the File table engine")

Another option is to use the [File](https://clickhouse.com/docs/engines/table-engines/special/file) table engine, where ClickHouse uses the file to store the data. You can perform queries and inserts directly on the file.

For example:

```
CREATE TABLE my_table (
   x UInt32,
   y String,
   z DateTime
)
ENGINE = File(Parquet)
```

Insert a few rows:

```
INSERT INTO my_table VALUES
   (1, 'Hello', now()),
   (2, 'World', now()),
   (3, 'Goodbye', now())
```

The file is stored in the `data` folder of your ClickHouse server - specifically in `/data/default/my_table` in a file named `data.Parquet`.

Note

Using the `File` table engine is incredibly handy for creating and querying files on your file system, but keep in mind that `File` tables are not `MergeTree` tables, so you don't get all the benefits that come with `MergeTree`. Use `File` for convenience when exporting data out of ClickHouse in convenient formats.

## Using Command-Line Redirection[​](#using-command-line-redirection "Direct link to Using Command-Line Redirection")

```
$ clickhouse-client --query "SELECT * from table" --format FormatName > result.txt
```

See [clickhouse-client](https://clickhouse.com/docs/interfaces/cli).

Mar 22, 2023 · 2 min read
title_wip

Learn how to use filtered aggregates in ClickHouse with `-If` and `-Distinct` aggregate combinators to simplify query syntax and enhance analytics.


## Using Filtered Aggregates[​](#using-filtered-aggregates "Direct link to Using Filtered Aggregates")

ClickHouse provides a simple and intuitive way to write *filtered aggregates*.

For example, compare the standard SQL way to write filtered aggregates (which work fine in ClickHouse) with the shorthand syntax using the `-If` [aggregate function combinator](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators/), which can be appended to any aggregate function:

```
--standard SQL
SELECT
   avg(number)
FILTER (WHERE number > 50)
FROM numbers(100)

--ClickHouse using an aggregate combinator
SELECT
   avgIf(number, number > 50)
FROM numbers(100)
```

Similarly, there is a `-Distinct` aggregate combinator:

```
--standard SQL
SELECT avg(DISTINCT number)

--ClickHouse using an aggregate combinator
SELECT avgDistinct(number)
```

Why are filtered aggregates are important? Because they allow you to implement the **"segment comparison"** feature in web analytics services.
For example:

```
WITH
   Region = 'us' AS segment1,
   Browser = 'Chrome' AS segment2
SELECT
   uniqIf(UserID, segment1),
   uniqIf(UserID, segment2)
WHERE segment1 OR segment2
```

Check out the [aggregate function combinator](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators/) page in the docs
for more details.

Mar 1, 2023 · One min read
title_wip

conversely, `query_id = initial_query_id` will return only the queries executed on the local node where the distributed query was first thrown at:

```
SELECT
    hostname(),
    type,
    event_time,
    initial_query_id,
    query_id,
    formatReadableSize(memory_usage) AS memory,
    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')] AS userCPU,
    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')] AS systemCPU,
    normalizedQueryHash(query) AS normalized_query_hash
FROM clusterAllReplicas(default, system.query_log)
WHERE (query_id = initial_query_id) AND (initial_query_id = 'a7262fa2-bd8b-4b51-a359-621ccf282417')
ORDER BY memory_usage DESC
LIMIT 10 FORMAT Pretty;
```

giving:

```
┏━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┓
┃ hostname()             ┃ type        ┃          event_time ┃ initial_query_id                     ┃ query_id                             ┃ memory     ┃ userCPU ┃ systemCPU ┃ normalized_query_hash ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━┩
│ server-2 │ QueryFinish │ 2023-04-26 06:25:53 │ a7262fa2-bd8b-4b51-a359-621ccf282417 │ a7262fa2-bd8b-4b51-a359-621ccf282417 │ 123.08 MiB │ 1849115 │    123412 │   4258439895846105173 │
├────────────────────────┼─────────────┼─────────────────────┼──────────────────────────────────────┼──────────────────────────────────────┼────────────┼─────────┼───────────┼───────────────────────┤
│ server-2 │ QueryStart  │ 2023-04-26 06:25:52 │ a7262fa2-bd8b-4b51-a359-621ccf282417 │ a7262fa2-bd8b-4b51-a359-621ccf282417 │ 0.00 B     │       0 │         0 │   4258439895846105173 │
└────────────────────────┴─────────────┴─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴────────────┴─────────┴───────────┴───────────────────────┘
```

As for other System Tables, you can find more details about the meaning of each field in our [docs](https://clickhouse.com/docs/operations/system-tables/query_log).

Mar 26, 2023 · 6 min read