ClickHouse/clickhouse-docs-embeddings huggingface dataset:
# Knowledge Base

RecentGrouped by tags

Setup steps to expose a private MSK via MSK multi-VPC connectivity to ClickPipes.

Apr 15, 2025 · 2 min read

Article describing how to recover from a corrupt Keeper snapshot: how the problem manifests, what a snapshot is and where to find it and possible recovery strategies.

Jan 29, 2025 · 7 min read

Learn how to remove the default user when running ClickHouse Server.

Jan 16, 2025 · 2 min read

Learn why the ClickHouse developers chose to set a verbose logging level by default.

Jan 10, 2025 · 2 min read

In this guide, we'll learn how to add a column to an existing table.

Dec 18, 2024 · 4 min read

“Importing GeoJSON with a deeply nested object array“

Dec 18, 2024 · 5 min read

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

Dec 17, 2024 · 3 min read

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

Dec 17, 2024 · 2 min read

Covers a common reason why a primary key is not used in ordering and how we can confirm

Dec 12, 2024 · 6 min read

Learn how to set up SSL for a single ClickHouse server using Let's Encrypt, including certificate issuance, configuration, and validation.

Dec 11, 2024 · 6 min read
# Knowledge Base

RecentGrouped by tags

ClickHouse doesn't have a PIVOT clause, but we can get close to this functionality using aggregate function combinators. Let's see how to do this using the UK housing prices dataset.

Dec 11, 2024 · 7 min read

Setup steps to expose a private RDS via AWS PrivateLink to ClickPipes.

Nov 27, 2024 · 4 min read

How to use the exchange command to switch tables

Nov 21, 2024 · 2 min read

How to output send logs level traces to file using the clickhouse-client

Nov 21, 2024 · 3 min read

Learn how to profile ClickHouse using LLVM's XRay instrumentation profiler, visualize traces, and analyze performance.

Nov 13, 2024 · 4 min read

Learn how to load JSON messages from Apache Kafka directly into a single JSON column in ClickHouse using the Kafka table engine and JSON data type.

Nov 6, 2024 · 4 min read

How to connect to ClickHouse and ClickHouse Cloud using SSH Keys

Jul 25, 2024 · 2 min read

Mapping of metrics used in system.dashboards to Prometheus metrics in system.custom\_metrics

Jul 23, 2024 · 3 min read

ODBC authentication failed error when using the Power BI ClickHouse connector

Jul 10, 2024 · 2 min read

How to create a table that can query multiple remote clusters

Jul 3, 2024 · 3 min read
# Knowledge Base

RecentGrouped by tags

Tips and tricks on optimizing basic data types in ClickHouse

Jul 2, 2024 · 4 min read

Troubleshooting access issues with ClickHouse Cloud services, including IP Access List configuration

Jul 2, 2024 · One min read

Simple example to illustrate how to use array join to extract and query varying attributes using map keys and values

Jun 21, 2024 · 4 min read

How to set up ClickHouse on Docker with ODBC to connect to a Microsoft SQL Server (MSSQL) database

May 29, 2024 · 4 min read

Simple example flow for extracting JSON data using a landing table with a Materialized View

May 20, 2024 · 2 min read

Example of mapping Windows Active Directory security groups to ClickHouse roles

May 20, 2024 · 6 min read

Learn how to ingest data from a Kafka topic into ClickHouse using the Kafka table engine, materialized views, and MergeTree tables.

Apr 27, 2024 · 6 min read

Commands to build LLVM and clang on Linux.

Apr 22, 2024 · One min read

Learn how to search across nodes for tables with a wildcard.

Apr 17, 2024 · 3 min read

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

Apr 12, 2024 · 2 min read
# Knowledge Base

RecentGrouped by tags

How can I backup a specific partition in ClickHouse?

Feb 14, 2024 · 3 min read

Learn how to ensure data consistency when reading from ClickHouse, whether you're connected to the same node or a random node.

Feb 14, 2024 · 2 min read

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

Jan 9, 2024 · 2 min read

Learn how to use @clickhouse/client in a Node.js application to interact with ClickHouse and perform queries.

Nov 22, 2023 · 2 min read

This article explains how to change the prompt in your Clickhouse client and clickhouse-local terminal window from :) to a prefix followed by :)

Nov 16, 2023 · 3 min read

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

Nov 16, 2023 · One min read

Learn how to use ClickHouse for vector search, including storing embeddings and searching with distance functions like cosine similarity.

Oct 26, 2023 · 2 min read

Quotas and Query Complexity are powerful ways to limit and restrict what users can do in ClickHouse. This KB article shows examples on how to apply these two different approaches.

Oct 25, 2023 · 7 min read

Queries to help grant specific permissions to users.

Sep 28, 2023 · One min read

Let's learn how to change your billing address in ClickHouse Cloud.

Sep 8, 2023 · One min read
# Knowledge Base

RecentGrouped by tags

Learn how to query ClickHouse's `system.role\_grants` to find users assigned to roles and roles assigned to specific users.

Sep 7, 2023 · 3 min read

Handing the an exception thrown when altering user settings

Aug 26, 2023 · One min read

Learn about row-level and column-level access restrictions in ClickHouse and ClickHouse Cloud, and how to implement role-based access control (RBAC) with policies.

Aug 13, 2023 · One min read

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

Aug 1, 2023 · 2 min read

Resolve the TOO\_MANY\_PARTS error in ClickHouse during an `INSERT...SELECT` by tuning expert-level settings for larger blocks and increasing partition thresholds.

Jul 21, 2023 · 2 min read

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.

Jul 10, 2023 · One min read

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

Jun 26, 2023 · 2 min read

Learn how to check if query cache is being utilized in ClickHouse using `clickhouse-client` trace logs or SQL commands.

Jun 7, 2023 · 7 min read

Learn how to use the `FORMAT Null` option in ClickHouse to measure query processing time without returning any rows to the client.

Jun 7, 2023 · One min read

Learn how to start, stop, and resume a ClickHouse Cloud service using API endpoints and cURL commands.

Jun 7, 2023 · 2 min read
# Knowledge Base

RecentGrouped by tags

Learn how to resolve Docker capability warnings for `CAP\_IPC\_LOCK` and `CAP\_SYS\_NICE` when running ClickHouse in a container.

Jun 7, 2023 · 2 min read

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.

Jun 7, 2023 · 2 min read

Learn about schema migration tools for ClickHouse and how to manage changing database schemas over time.

Jun 7, 2023 · 7 min read

Monitor the number of active or queued mutations in ClickHouse, especially when performing `ALTER` or `UPDATE` operations. Use the `system.mutations` table for tracking mutations.

Jun 7, 2023 · One min read

ClickHouse Keeper improves upon ZooKeeper with features like reduced disk space usage, faster recovery, and less memory consumption, offering better performance for ClickHouse clusters.

Jun 7, 2023 · 2 min read

Learn how to calculate the ratio of empty or zero values in every column of a ClickHouse table to optimize sparse column serialization.

May 18, 2023 · 2 min read

ClickHouse is popular for logs and metrics analysis because of the real-time analytics capabilities provided. Ready to find out more?

May 10, 2023 · One min read

Learn how to connect to ClickHouse Cloud Service using Python with a step-by-step example using the clickhouse-connect driver.

May 8, 2023 · 2 min read

Learn how to validate that two ClickHouse queries produce identical result-sets using hash functions and comparison techniques.

May 4, 2023 · 2 min read

Learn how to resolve the SSL Exception CERTIFICATE\_VERIFY\_FAILED error.

May 2, 2023 · One min read
# Knowledge Base

RecentGrouped by tags

This article explains how to resolve the DB::Exception error related to intersecting parts in ClickHouse, often caused by a race condition or manual intervention in the ZooKeeper data.

May 2, 2023 · 2 min read

TTL rules in ClickHouse are eventually applied, and you can control when they are executed using the `merge\_with\_ttl\_timeout` setting. Learn how to force TTL application and manage background threads for TTL execution.

May 2, 2023 · 3 min read

Learn how to install and test ClickHouse on Windows 10 using WSL 2. Includes setup, troubleshooting, and running a test environment.

Apr 24, 2023 · 3 min read

Learn how to use the `query\_log` table in ClickHouse to identify the most memory and CPU-intensive queries across distributed nodes.

Mar 26, 2023 · 6 min read

Are you getting the error "Cannot append data in format Parquet to file" error in ClickHouse? Let's take a look at how to resolve it.

Mar 25, 2023 · 2 min read

Learn how to query ClickHouse logs to identify all queries involving Materialized Views within a specified time range.

Mar 24, 2023 · 2 min read

Learn how to import JSON array objects into ClickHouse and perform advanced queries using JSON functions and array operations.

Mar 24, 2023 · 6 min read

Learn how to quickly recreate a small table and its data across different terminals using copy/paste for development environments.

Mar 24, 2023 · 2 min read

Learn how to write partitioned data by year and month to an S3 bucket in ClickHouse, using a custom path structure for organizing the data.

Mar 24, 2023 · One min read

ClickHouse provides introspection tools like `system.stack\_trace` for inspecting what code is currently running on each server thread, helping with debugging and performance monitoring.

Mar 24, 2023 · 3 min read
# Knowledge Base

RecentGrouped by tags

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

Mar 22, 2023 · 2 min read

Learn the basics of using the S3 table engine in ClickHouse to ingest and query Parquet files from an S3 bucket, including setup, access permissions, and data import examples.

Mar 22, 2023 · 3 min read

Learn how to use ClickHouse's `clickhouse-local` tool to easily convert Parquet files to CSV or JSON formats.

Mar 22, 2023 · 3 min read

Learn how to export PostgreSQL data to Parquet, CSV, or JSON formats using `clickhouse-local` with various examples.

Mar 22, 2023 · 3 min read

Learn how to use the `clickhouse-local` tool to export MySQL data into formats like Parquet, CSV, or JSON quickly and efficiently.

Mar 21, 2023 · 2 min read

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

Mar 20, 2023 · 2 min read

Learn how to address the "Too many parts" error in ClickHouse by optimizing insert rates, configuring MergeTree settings, and managing partitions effectively.

Mar 20, 2023 · 3 min read

There are multiple JSON variations among them, but the most commonly used for data ingestion is JSONEachRow. It expects one JSON object per row, each object separated by a newline.

Mar 20, 2023 · One min read

A collection of handy queries for troubleshooting ClickHouse, including monitoring table sizes, long-running queries, and errors.

Mar 17, 2023 · 4 min read

It's Pi Day! Let's calculate pi using ClickHouse SQL

Mar 14, 2023 · 3 min read
title_wip

This article explains how to resolve the DB::Exception error related to intersecting parts in ClickHouse, often caused by a race condition or manual intervention in the ZooKeeper data.


## Why it occurs[​](#why-it-occurs "Direct link to Why it occurs")

When this error occurs, a table shows as readonly and the error states intersecting parts.

You can see the error in the logs or by

```
SELECT *
FROM system.replicas
WHERE is_readonly = 1
```

The error message looks like:

```
Code: 49. DB::Exception: Part XXXXX intersects previous part YYYYY. It is a bug or a result of manual intervention in the ZooKeeper data. (LOGICAL_ERROR) (version 21.12.4.1 (official build))
```

## Cause of the Error[​](#cause-of-the-error "Direct link to Cause of the Error")

This error can be caused by a race condition between `mergeSelectingTask` and queue reinitialization.

## Solution[​](#solution "Direct link to Solution")

Execute the following queries on all replicas:

```
DETACH TABLE table_name;  -- Required for DROP REPLICA

SYSTEM DROP REPLICA 'replica_name' FROM ZKPATH '/table_path_in_zk/'; -- It will remove everything from /table_path_in_zk

ATTACH TABLE table_name;  -- Table will be in readonly mode, because there is no metadata in ZK
```

Then execute the following on all replicas:

```
SYSTEM RESTORE REPLICA table_name;  -- It will detach all partitions, re-create metadata in ZK (like it's new empty table), and then attach all partitions back

SYSTEM SYNC REPLICA table_name; -- Wait for replicas to synchronize parts. Also it's recommended to check `system.detached_parts` on all replicas after recovery is finished.
```

Tip

You should upgrade to the latest version of ClickHouse

## Additional resources[​](#additional-resources "Direct link to Additional resources")

Related PRs and GitHub issues:

- [ClickHouse/ClickHouse#34096](https://github.com/ClickHouse/ClickHouse/pull/34096)
- [ClickHouse/ClickHouse#30651](https://github.com/ClickHouse/ClickHouse/pull/30651)
- [ClickHouse/ClickHouse#31060](https://github.com/ClickHouse/ClickHouse/pull/31060)
- [ClickHouse/ClickHouse#35863](https://github.com/ClickHouse/ClickHouse/issues/35863)

## Versions affected:[​](#versions-affected "Direct link to Versions affected:")

ClickHouse v 22.12 and prior

May 2, 2023 · 2 min read
title_wip

How to enforce limit on max query execution time


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

How do I enforce a time limit on my queries?

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

You can use [`max_execution_time`](/docs/operations/settings/settings#max_execution_time) setting (expressed in seconds):

```
clickhouse-cloud :) SELECT 1 SETTINGS max_execution_time=0.0001

SELECT 1
SETTINGS max_execution_time = 0.0001

Query id: 3db752a7-b94f-4456-b3b9-ccbf290d1394


0 rows in set. Elapsed: 0.113 sec.

Received exception from server (version 23.5.1):
Code: 159. DB::Exception: Received from service.aws.clickhouse.cloud:9440. DB::Exception: Timeout exceeded: elapsed 0.000557862 seconds, maximum: 0.0001. (TIMEOUT_EXCEEDED)
```

Jul 10, 2022 · One min read
title_wip

Learn how to ensure data consistency when reading from ClickHouse, whether you're connected to the same node or a random node.


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

I'm writing data into ClickHouse Cloud and need to be able, when reading data, to guarantee that I'm getting the latest complete information.

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

### Talking to the same node[​](#talking-to-the-same-node "Direct link to Talking to the same node")

If you are using the native protocol, or a session to do your write/read, you should then be connected to the same replica: in this scenario, you're reading directly from the node where you're writing, and so your read will always be consistent.

### Talking to a random node[​](#talking-to-a-random-node "Direct link to Talking to a random node")

If you can't guarantee you're talking to the same node (for example, talking to the node via HTTPS calls which get shuffled via a load balancer), you can either:

A)

1. write your data
2. connect to a new replica
3. run `SYSTEM SYNC REPLICA db.table_name LIGHTWEIGHT`
4. read the latest data

See `SYSTEM` commands [reference](https://clickhouse.com/docs/sql-reference/statements/system#sync-replica)

OR

B) read anytime with sequential consistency

```
SELECT 
...
SETTINGS select_sequential_consistency = 1
```

Note that when using ClickHouse Cloud and its default [SharedMergeTree](https://clickhouse.com/docs/cloud/reference/shared-merge-tree) table engine, using `insert_quorum_parallel` is not required — all inserts to SharedMergeTree are quorum inserts (by design).

Using [`SYSTEM SYNC REPLICAS`](https://clickhouse.com/docs/sql-reference/statements/system#sync-replica) or [`select_sequential_consistency`](https://clickhouse.com/docs/operations/settings/settings#select_sequential_consistency) will increase the load on ClickHouse Keeper and might have slower performance depending on the load on the service.

The recommended approach is to do the write/read using the same session or the native protocol (sticky connection).

Feb 14, 2024 · 2 min read
title_wip

Learn how to quickly recreate a small table and its data across different terminals using copy/paste for development environments.


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

How can I quickly recreate a table and its data using just copy/paste across different terminals?

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

This is NOT a recommended practice to migrate data from one database to another and it should NOT be used for production data migration.

This is simply intended as a quick and dirty way to recreate small amount of data when developing across multiple environments.

1. Get the CREATE TABLE statement with `SHOW CREATE table`:

```
SHOW CREATE TABLE cookies;

SHOW CREATE TABLE cookies

Query id: 248ec8e2-5bce-45b3-97d9-ed68edf445a5

┌─statement────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.cookies
(
    `id` String,
    `timestamp` DateTime
)
ENGINE = MergeTree
ORDER BY id
│
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

2. Get the data export using `FORMAT SQLInsert`

```
SELECT * FROM cookies FORMAT SQLInsert;

SELECT *
FROM cookies
FORMAT SQLInsert

Query id: 383759b8-69c0-4561-ab95-f8224abc0071

INSERT INTO table (`id`, `timestamp`) VALUES ('4', '2023-03-15 16:28:46')
, ('2', '2023-03-15 16:28:41')
, ('1', '2023-03-15 16:11:02'), ('1', '2023-03-15 16:11:40'), ('1', '2023-03-15 16:11:48'), ('1', '2023-03-15 16:16:05'), ('2', '2023-03-15 16:11:06'), ('3', '2023-03-15 16:11:12'), ('3', '2023-03-15 16:11:45'), ('3', '2023-03-15 16:16:08'), ('4', '2023-03-15 16:11:14'), ('4', '2023-03-15 16:11:50'), ('4', '2023-03-15 16:16:01'), ('5', '2023-03-15 16:11:18'), ('5', '2023-03-15 16:16:11')
;

15 rows in set. Elapsed: 0.023 sec.
```

Note you will need to replace the name `table` at point 2 with the actual table name (`cookies` in this example)

Mar 24, 2023 · 2 min read
title_wip

Learn how to remove the default user when running ClickHouse Server.


## Removing the default user[​](#removing-the-default-user "Direct link to Removing the default user")

Note

This guide is not applicable to ClickHouse Cloud.

In this guide, we're going to learn how to remove the `default` user from ClickHouse Server.

We can do this by creating a YAML file (let's call it `remove_default_user.yaml`) that has the following content

```
users:
  default:
    "@remove": remove
```

The location of this file depends on how we have ClickHouse installed.

## Running the executable directly[​](#running-the-executable-directly "Direct link to Running the executable directly")

If we're running the ClickHouse directly (`clickhouse server`), we need to put the file under the `config.d` directory.

When we run ClickHouse Server:

```
clickhouse server
```

We'll see the following line in the logs:

```
{} <Debug> ConfigProcessor: Merging configuration file 'config.d/remove_default_user.yaml'.
```

And we'll be unable to connect using `clickhouse client`:

```
ClickHouse client version 24.11.1.2557 (official build).
Connecting to localhost:9000 as user default.
Password for user (default):
Connecting to localhost:9000 as user default.
Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.
```

## Docker or installed[​](#docker-or-installed "Direct link to Docker or installed")

If we're running ClickHouse via Docker or have it installed on our machine, we need to put the file under the `/etc/clickhouse-server/users.d` directory instead.

So if we're running with Docker, we can mount the `config.d` directory that we created earlier to `/etc/clickhouse-server/users.d`:

```
docker run \
  -v ./config.d:/etc/clickhouse-server/users.d \
  -p 8123:8123 -p9000:9000 \
  clickhouse/clickhouse-server:24.12
```

```
Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
```

We can then search the server logs to check that it gets picked up:

```
docker ps --format "table {{.ID}}\t{{.Image}}\t{{.Names}}\t{{.Command}}"
```

```
CONTAINER ID   IMAGE                                NAMES               COMMAND
383e8ed89431   clickhouse/clickhouse-server:24.12   trusting_rosalind   "/entrypoint.sh"
```

```
docker exec -it trusting_rosalind grep "users\.d" /var/log/clickhouse-server/clickhouse-server.log
```

We should see the following line:

```
{} <Debug> ConfigProcessor: Merging configuration file '/etc/clickhouse-server/users.d/remove_default_user.yaml'.
```

Jan 16, 2025 · 2 min read
title_wip

Learn about row-level and column-level access restrictions in ClickHouse and ClickHouse Cloud, and how to implement role-based access control (RBAC) with policies.


## ClickHouse and ClickHouse Cloud both support row and column-level access restrictions[​](#clickhouse-and-clickhouse-cloud-both-support-row-and-column-level-access-restrictions "Direct link to ClickHouse and ClickHouse Cloud both support row and column-level access restrictions")

ClickHouse and ClickHouse Cloud both support row and column-level access restrictions for read-only users necessary to achieve a role-based access control (RBAC) model.

[Row Policies](/docs/operations/access-rights#row-policy-management) can be used to specify which rows will be returned to a read-only user when they query a table. ClickHouse Cloud is configured to enable the SQL-driven workflow by default. To leverage this workflow [CREATE](/docs/sql-reference/statements/create/user) a user, [GRANT](/docs/sql-reference/statements/grant) the user privileges on a table, then set the appropriate [ROW POLICY](/docs/operations/access-rights#row-policy-management). When the user performs a `SELECT * FROM table`, only rows allowed by the policy will be displayed.

Column-level restrictions may be specified directly using the [GRANT statement](/docs/sql-reference/statements/grant) to enable table-level access for users and roles. Users *may only* include columns for which they have access in a query. Selecting restricted columns from a table in which the user does not have access to all the specified columns, such as in the query `SELECT * FROM table`, will return an error stating the user has insufficient permissions.

Aug 13, 2023 · One min read