ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

Learn how to use the `system.query\_log` table to find the most memory-intensive queries in ClickHouse, with examples for clustered and standalone setups.


## Using the `system.query_log` table[​](#using-the-systemquery_log-table "Direct link to using-the-systemquery_log-table")

The following useful query shows which of your executed queries used the most memory.

A couple of comments about this query:

- the results are computed from the past day (`now() - toIntervalDay(1))`) but you can easily modify the time interval
- it assumes you have a cluster named `default`, which is the name of your cluster in [ClickHouse Cloud](https://console.clickhouse.cloud). Change `default` to the name of your cluster
- if you do not have a cluster, see the query listed at the end of this article

```
SELECT
    count() as nb_query,
    user,
    query,
    sum(memory_usage) AS memory,
    normalized_query_hash
FROM
    clusterAllReplicas(default, system.query_log)
WHERE
    (event_time >= (now() - toIntervalDay(1)))
    AND query_kind = 'Select'
    AND type = 'QueryFinish'
    and user != 'monitoring-internal'
GROUP BY
    normalized_query_hash,
    query,
    user
ORDER BY
    memory DESC;
```

The response looks like:

```
┌─nb_query─┬─user────┬─query─────────────────────────────────────────────────────────┬───memory─┬─normalized_query_hash─┐
│       11 │ default │ select version()                                              │ 46178924 │   7202516440347714159 │
│        2 │ default │ SELECT * FROM "system"."table_functions" LIMIT 31 OFFSET 0    │  8391544 │  12830067173062987695 │
└──────────┴─────────┴───────────────────────────────────────────────────────────────┴──────────┴───────────────────────┘
```

Note

If you do not have a `system.query_log` table, then you likely do not have query logging enabled. View the details of the [`query_log` setting](https://clickhouse.com/docs/operations/server-configuration-parameters/settings#server_configuration_parameters-query-log) for details on how to enable it.

If you do not have a cluster, use can just query your one `system.query_log` table directly:

```
SELECT
    count() as nb_query,
    user,
    query,
    sum(memory_usage) AS memory,
    normalized_query_hash
FROM
    system.query_log
WHERE
    (event_time >= (now() - toIntervalDay(1)))
    AND query_kind = 'Select'
    AND type = 'QueryFinish'
    and user != 'monitoring-internal'
GROUP BY
    normalized_query_hash,
    query,
    user
ORDER BY
    memory DESC;
```

Jun 7, 2023 · 2 min read
title_wip

Learn how to resolve the MacOS developer verification error when running ClickHouse commands, using either System Settings or the terminal.



---

## Fix the Developer Verification Error in MacOS[​](#fix-the-developer-verification-error-in-macos "Direct link to Fix the Developer Verification Error in MacOS")

If you install ClickHouse using `brew`, you may encounter an error from MacOS.
By default, MacOS will not run applications or tools created by a developer who cannot be verified.

When attempting to run any `clickhouse` command, you may see this error:

To get around this verification error, you need to remove the app from MacOS' quarantine bin either by finding the appropriate setting in your System Settings window, using the terminal, or by re-installing ClickHouse.

## System settings process[​](#system-settings-process "Direct link to System settings process")

The easiest way to remove the `clickhouse` executable from the quarantine bin is to:

1. Open **System settings**.
2. Navigate to **Privacy & Security**:
3. Scroll to the bottom of the window to find a message saying \_"clickhouse-macos-aarch64" was blocked from use because it is not from an identified developer".
4. Click **Allow Anyway**.
5. Enter your MacOS user password.

You should now be able to run `clickhouse` commands in your terminal.

## Terminal process[​](#terminal-process "Direct link to Terminal process")

Sometimes pressing the `Allow Anyway` button doesn't doesn't fix this issue, in which case you can also perform this process using the command-line.
Or you might just prefer using the command line!

First find out where Homebrew installed the `clickhouse` executable:

```
which clickhouse
```

This should output something like:

```
/opt/homebrew/bin/clickhouse
```

Remove `clickhouse` from the quarantine bin by running `xattr -d com.apple.quarantine` following by the path from the previous command:

```
xattr -d com.apple.quarantine /opt/homebrew/bin/clickhouse
```

You should now be able to run the `clickhouse` executable:

```
clickhouse
```

This should output something like:

```
Use one of the following commands:
clickhouse local [args]
clickhouse client [args]
clickhouse benchmark [args]
...
```

## Fix the Issue by Reinstalling ClickHouse[​](#fix-the-issue-by-reinstalling-clickhouse "Direct link to Fix the Issue by Reinstalling ClickHouse")

Brew has a command-line option which avoids quarantining installed binaries in the first place.

First, uninstall ClickHouse:

```
brew uninstall clickhouse
```

Now reinstall ClickHouse with `--no-quarantine`:

```
brew install --no-quarantine clickhouse
```

Jan 9, 2024 · 2 min read
title_wip

A HAR (HTTP Archive) file captures the network activity in your browser. It can help our support team diagnose slow page loads, failed requests, or other network issues.


## From Google Chrome[​](#from-google-chrome "Direct link to From Google Chrome")

1. Open Developer Tools by pressing `F12` or `Ctrl` + `Shift` + `I` (Windows/Linux) / `Cmd` + `Option` + `I` (Mac).
2. Click the "Network" tab.
3. Reload the page and reproduce the issue.
4. From Developer Tools, click the download button.

## From Mozilla Firefox[​](#from-mozilla-firefox "Direct link to From Mozilla Firefox")

1. Open Developer Tools by pressing `F12` or `Ctrl` + `Shift` + `E` (Windows/Linux) / `Cmd` + `Option` + `E` (Mac).
2. Click the "Network" tab.
3. Reload the page and reproduce the issue.
4. From Developer Tools, right-click in the request list and select "Save All As HAR".

## From Microsoft Edge[​](#from-microsoft-edge "Direct link to From Microsoft Edge")

1. Open Developer Tools by pressing `F12` or `Ctrl` + `Shift` + `I` (Windows/Linux) / `Cmd` + `Option` + `I` (Mac).
2. Click the "Network" tab.
3. Reload the page and reproduce the issue.
4. From Developer Tools, right-click in the network list and choose "Save all as HAR with content".

## From Safari[​](#from-safari "Direct link to From Safari")

1. Enable Developer Tools (if not already enabled):
   - Go to Safari > Settings > Advanced.
   - Check "Show Develop menu in menu bar" at the bottom.
2. Click Develop > Show Web Inspector.
3. Click the "Network" tab.
4. Reload the page and reproduce the issue.
5. From Developer Tools, click the "Export" button.

## Sending the .HAR file[​](#sending-har-file "Direct link to Sending the .HAR file")

1. Rename the file to something short and descriptive (e.g., login-issue.har).
2. Compress the file (optional but recommended).
3. Attach it to your support case or email it to your assigned support contact.

Note

HAR files may contain session cookies or other sensitive data.
Only share them with authorized support personnel.

Nov 11, 2025 · 2 min read
title_wip

ClickHouse is an open-source project developed on GitHub. As customary, contribution instructions are published in CONTRIBUTING file in the root of the source code repository.


## How to contribute in ClickHouse[​](#how-to-contribute-in-clickhouse "Direct link to How to contribute in ClickHouse")

ClickHouse is an open-source project [developed on GitHub](https://github.com/ClickHouse/ClickHouse).

As customary, contribution instructions are published in [CONTRIBUTING](https://github.com/ClickHouse/ClickHouse/blob/master/CONTRIBUTING.md) file in the root of the source code repository.

If you want to suggest a substantial change to ClickHouse, consider [opening a GitHub issue](https://github.com/ClickHouse/ClickHouse/issues/new/choose) explaining what you want to do, to discuss it with maintainers and community first. [Examples of such RFC issues](https://github.com/ClickHouse/ClickHouse/issues?q=is%3Aissue+is%3Aopen+rfc).

If your contributions are security related, please check out [our security policy](https://github.com/ClickHouse/ClickHouse/security/policy/) too.

Sep 1, 2021 · One min read
title_wip

Learn how to use the ClickHouse Cloud API to check if your service is stopped, idle, or running without waking it up.


## How to Check Your ClickHouse Cloud Service State[​](#how-to-check-your-clickhouse-cloud-service-state "Direct link to How to Check Your ClickHouse Cloud Service State")

How do I check my ClickHouse Cloud Service state? I want to check if the Service is stopped, idle, or running, but I don't want to wake the Service up in doing so.

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

The [ClickHouse Cloud API](/docs/cloud/manage/api/api-overview) is great for checking the status of a cloud service. You need to create an API Key in your service before you can use the Cloud API. You can do this in ClickHouse Cloud [clickhouse.cloud](https://console.clickhouse.cloud):

- [API Overview](/docs/cloud/manage/api/api-overview)
- [Swagger](https://clickhouse.com/docs/cloud/manage/api/swagger)

1. To check the status of a service, run the following. Make sure to replace `Key-ID` and `Key-Secret` with your respective details:

   ```
   curl --user '[Key-ID]:[Key-Secret]' https://api.clickhouse.cloud/v1/organizations/[Org-ID]/services/[Service-ID]
   ```

   This will output something like:

   ```
   result":{"id":"[Service-ID]","name":"[Service-Name]","provider":"aws","region":"us-east-1","state":"**idle**","endpoints":[{"protocol":"nativesecure","host":"[Connect-URL]","port":9440},{"protocol":"https","host":"[Connect-URL]","port":8443}],"tier":"development","idleScaling":true,"idleTimeoutMinutes":15,"ipAccessList":[{"source":"[my-IP]","description":"[my-IP-name]"}],"createdAt":"2023-04-13T23:47:47Z"},"status":200}
   ```
2. You can use the [JQ utility](https://jqlang.github.io/jq/) to extract the `state` key:

   ```
   curl --user '[Key-ID]:[Key-Secret]' https://api.clickhouse.cloud/v1/organizations/[Org-ID]/services/[Service-ID] | jq '.state'
   ```

   This will output something like:

   ```
   **idle**
   ```
3. Running the same command against an actively running service will output:

   ```
   **running**
   ```

Nov 16, 2023 · One min read
title_wip

How to connect to ClickHouse and ClickHouse Cloud using SSH Keys


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

How can I connect to ClickHouse using SSH Key Authentication?

Note

We use ClickHouse Cloud as an example here, but this example should also work on oss ClickHouse.

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

1. Use ssh-keygen to create the keypair. Example:

```
➜  new ssh-keygen \
-t ed25519 \
> -f /Users/testuser/.ssh/ch_key
Generating public/private ed25519 key pair.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /Users/testuser/.ssh/ch_key
Your public key has been saved in /Users/testuser/.ssh/ch_key.pub
.....
```

2. Use the public key (ch\_key.pub in above example) to create the USER.

```
clickhouse-cloud :) CREATE USER abcuser IDENTIFIED WITH ssh_key BY KEY 'AAAABBBcdE1lZDI1NTE5AAAAIISdl4CrGM8mckXBUXLjL3ef9XwnycDWEvBPu3toB40m' TYPE 'ssh-ed25519';

CREATE USER abcuser IDENTIFIED WITH ssh_key BY KEY AAAABBBcdE1lZDI1NTE5AAAAIISdl4CrGM8mckXBUXLjL3ef9XwnycDWEvBPu3toB40m TYPE `ssh-ed25519`

Query id: 34c6aad6-5f88-4c80-af7a-7d37c91ba7d5

Ok.
```

3. Run `SHOW users` to confirm the user creation.
4. Grant default\_role to the user (optional).

```
clickhouse-cloud :) grant default_role to abcuser;

GRANT default_role TO abcuser

Query id: 4a054003-220a-4dea-8e8d-eb1f08ee7b10

Ok.

0 rows in set. Elapsed: 0.137 sec.
```

5. Use the private key now to authenticate against the service.

```
➜  new ./clickhouse client --host myhost.us-central1.gcp.clickhouse.cloud --secure --user abcuser --ssh-key-file '/Users/testuser/.ssh/ch_key'
ClickHouse client version 23.12.1.863 (official build).
Enter your private key passphrase (leave empty for no passphrase):
Connecting to myhost.us-central1.gcp.clickhouse.cloud:9440 as user abcuser.
Connected to ClickHouse server version 23.9.2.

clickhouse-cloud :) select currentUser();

SELECT currentUser()

Query id: d4b6bb60-ef45-47d3-8740-db9f2941dcd2

┌─currentUser()─┐
│ abcuser       │
└───────────────┘

1 row in set. Elapsed: 0.001 sec.

clickhouse-cloud :)
```

Jul 25, 2024 · 2 min read
title_wip

Knowledgebase article on how to filter a ClickHouse table by an array-column.


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

Filtering a ClickHouse table by an array-column is a common task and the product offers a lot of [functions](/docs/sql-reference/functions/array-functions) to work with array-columns.

In this article, we're going to focus on filtering a table by an array-column, but the video below covers a lot of other array-related functions:

## Example[​](#example "Direct link to Example")

We'll use an example of a table with two columns `tags_string` and `tags_int` that contain an array of strings and integers respectively.

- Create a sample database and table.

```
CREATE DATABASE db1;
```

- Create a sample table

```
CREATE TABLE db1.tags_table
(
    `id` UInt64,
    `tags_string` Array(String),
    `tags_int` Array(UInt64),
)
ENGINE = MergeTree
ORDER BY id;
```

- Insert some sample data into the table.

```
INSERT INTO db1.tags_table VALUES (1, ['tag1', 'tag2', 'tag3'], [1, 2, 3]), (2, ['tag2', 'tag3', 'tag4'], [2, 3, 4]), (3, ['tag1', 'tag3', 'tag5'], [1, 3, 5]);
```

Filter the table using the `has(arr, elem)` function to return the rows where the `arr` array contains the `elem` element.

Filter the table to return the rows where the `tags_string` array contains the `tag1` element.

```
SELECT * FROM db1.tags_table WHERE has(tags_string, 'tag1');
```

```
┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘
```

Use the `hasAll(arr, elems)` function to return the rows where all the elements in the `elems` array are present in the `arr` array.

Filter the table to return the rows where all the elements in the `tags_string` array are present in the `['tag1', 'tag2']` array.

```
SELECT * FROM db1.tags_table WHERE hasAll(tags_string, ['tag1', 'tag2']);
```

```
┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
└────┴────────────────────────┴──────────┘
```

Use the `hasAny(arr, elems)` function to return the rows where at least one element in the `elems` array is present in the `arr` array.

Filter the table to return the rows where at least one element in the `tags_string` array is present in the `['tag1', 'tag2']` array.

```
SELECT * FROM db1.tags_table WHERE hasAny(tags_string, ['tag1', 'tag2']);
```

```
┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘
```

We can use a lambda function to filter the table using the `arrayExists(lambda, arr)` function.

Filter the table to return the rows where at least one element in the `tags_int` array is greater than 3.

```
SELECT * FROM db1.tags_table WHERE arrayExists(x -> x > 3, tags_int);
```

```
┌─id─┬─tags_string────────────┬─tags_int─┐
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘
```

Dec 17, 2024 · 3 min read
title_wip

Learn how to configure the Global Thread pool in ClickHouse by adjusting settings like `max\_thread\_pool\_size`, `thread\_pool\_queue\_size`, and `max\_thread\_pool\_free\_size`.


## Increasing the Number of Threads in ClickHouse[​](#increasing-the-number-of-threads-in-clickhouse "Direct link to Increasing the Number of Threads in ClickHouse")

ClickHouse uses threads from the **Global Thread pool** to process queries and also perform background operations like merges and mutations. If there is no idle thread to process a query, then a new thread is created in the pool.

The maximum size of the global thread pool is determined by the `max_thread_pool_size` setting, which defaults to 10,000. You can modify this value in your config - here we set it to 20,000:

```
<max_thread_pool_size>20000</max_thread_pool_size>
```

If you modify `max_thread_pool_size`, we recommend changing `thread_pool_queue_size` to be the same value. The `thread_pool_queue_size` setting is the maximum number of jobs that can be scheduled on the Global Thread pool:

```
<thread_pool_queue_size>20000</thread_pool_queue_size>
```

You can also free up resources if your server has a lot of idle threads - using the `max_thread_pool_free_size` setting. The default is 1,000, which means your Global Thread pool will never have more than 1,000 idle threads. The following example increases the value to 2,000:

```
<max_thread_pool_free_size>2000</max_thread_pool_free_size>
```

Check out the [docs](https://clickhouse.com/docs/operations/server-configuration-parameters/settings#max-thread-pool-size) for more details on the settings above and other settings that affect the Global Thread pool.

Mar 1, 2023 · One min read
title_wip

Knowledgebase article on how to insert all rows from one table to another.


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

Sometimes you need to reingest all the data from one table to another.

For example, you might want to reingest data from a staging table to a production table. This article shows how to do this using the `INSERT INTO` statement.

## Example[​](#example "Direct link to Example")

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

- Create a sample database

```
CREATE DATABASE db1;
```

- Create a sample table

```
CREATE TABLE db1.source_table
(
    city VARCHAR,
    country VARCHAR,
    continent VARCHAR
)
engine = MergeTree()
ORDER BY continent;
```

- Insert some data into the source table

```
INSERT INTO db1.source_table (city, country, continent)
VALUES
    ('New York', 'USA', 'North America'),
    ('Tokyo', 'Japan', 'Asia'),
    ('Berlin', 'Germany', 'Europe'),
    ('Paris', 'France', 'Europe'),
    ('Cairo', 'Egypt', 'Africa'),
    ('Sydney', 'Australia', 'Australia');
```

- Check the number of rows in the source table

```
SELECT COUNT(*) FROM db1.source_table;
```

```
┌─count()─┐
│      6  │
└─────────┘
```

- Create a new table with the same structure as the source table.

```
CREATE TABLE db1.target_table AS db1.source_table;
```

- Insert all rows from the source table to the target table.

```
INSERT INTO db1.target_table SELECT * FROM db1.source_table;
```

- Check the number of rows in the target table

```
SELECT COUNT(*) FROM db1.target_table;
```

```
┌─count()─┐
│      6  │
└─────────┘
```

If you want to modify the structure of the new table, you can first display the structure of the source table.

```
SHOW CREATE TABLE db1.source_table;
```

Then create the new table with the modified structure. In our case we want to add a new column `population` to the target table.

```
CREATE TABLE db1.target_table_population
(
    `city` String,
    `country` String,
    `continent` String,
    `population` UInt16,
)
ENGINE = MergeTree
ORDER BY continent;
```

- Insert all rows from the source table to the target table, including the new column. The population field is set to 0 for all rows.

```
INSERT INTO db1.target_table_population (city, country, continent, population)
SELECT city, country, continent, 0 FROM db1.source_table;
```

- Check the data in the target table

```
SELECT * FROM db1.target_table_population LIMIT 3;
```

```
┌─city──────┬─country───┬─continent──────┬─population─┐
│ New York  │ USA       │ North America  │          0 │
│ Tokyo     │ Japan     │ Asia           │          0 │
│ Berlin    │ Germany   │ Europe         │          0 │
└───────────┴───────────┴────────────────┴────────────┘
```

Dec 17, 2024 · 2 min read
title_wip

Learn how to create and query parameterized views in ClickHouse for dynamic data slicing based on query-time parameters.


## Using Parameterized Views in ClickHouse[​](#using-parameterized-views-in-clickhouse "Direct link to Using Parameterized Views in ClickHouse")

Parameterized views can be handy to slice and dice data on the fly based on some parameters that can be fed at query execution time.

See this basic example:

1. create a table

```
clickhouse-cloud :) CREATE TABLE raw_data (id UInt32, data String) ENGINE = MergeTree ORDER BY id

CREATE TABLE raw_data
(
`id` UInt32,
`data` String
)
ENGINE = MergeTree
ORDER BY id

Query id: aa21e614-1e10-4bba-88ce-4c7183a9148e

Ok.

0 rows in set. Elapsed: 0.332 sec.
```

2. insert some sample random data

```
clickhouse-cloud :) INSERT INTO raw_data SELECT * FROM generateRandom('`id` UInt32,
`data` String',1,1) LIMIT 1000000;

INSERT INTO raw_data SELECT *
FROM generateRandom('`id` UInt32,
`data` String', 1, 1)
LIMIT 1000000

Query id: c552a34a-b72f-45e1-bed0-778923e1b5c9

Ok.

0 rows in set. Elapsed: 0.438 sec. Processed 1.05 million rows, 10.99 MB (2.39 million rows/s., 25.11 MB/s.)
```

3. create the parameterized view:

```
clickhouse-cloud :) CREATE VIEW raw_data_parametrized AS SELECT * FROM raw_data WHERE id BETWEEN {id_from:UInt32} AND {id_to:UInt32}

CREATE VIEW raw_data_parametrized AS
SELECT *
FROM raw_data
WHERE (id >= {id_from:UInt32}) AND (id <= {id_to:UInt32})

Query id: 45fb83a6-aa55-4197-a7cd-9e1ad2c76d48

Ok.

0 rows in set. Elapsed: 0.102 sec.
```

4. query the parameterized view by feeding the expected parameters in your `FROM` clause:

```
clickhouse-cloud :) SELECT count() FROM raw_data_parametrized(id_from=0, id_to=50000);

SELECT count()
FROM raw_data_parametrized(id_from = 0, id_to = 50000)

Query id: 5731aae1-3e68-4e63-b57f-d50f29055744

┌─count()─┐
│ 317019 │
└─────────┘

1 row in set. Elapsed: 0.004 sec. Processed 319.49 thousand rows, 319.49 KB (76.29 million rows/s., 76.29 MB/s.)
```

For more info, please refer to <https://clickhouse.com/docs/sql-reference/statements/create/view#parameterized-view>

Aug 1, 2023 · 2 min read
title_wip

Learn how to use the `skip\_check\_for\_incorrect\_settings` option to allow ClickHouse to start even when user-level settings are specified incorrectly.


## Ignoring Incorrect Settings in ClickHouse[​](#ignoring-incorrect-settings-in-clickhouse "Direct link to Ignoring Incorrect Settings in ClickHouse")

When a user-level setting is specified in the wrong place, the server won't start and an exception message is sent to the log. However, you can tell ClickHouse to ignore the incorrect setting using the `skip_check_for_incorrect_settings` setting.

Add the following to `config.xml`:

```
<skip_check_for_incorrect_settings>1</skip_check_for_incorrect_settings>
```

Note

User-level settings should be specified in `users.xml` inside a `<profile>` section for the specific user profile, (or in `<default>` for default settings.

Mar 1, 2023 · One min read