ClickHouse/clickhouse-docs-embeddings huggingface dataset:
DETACH Statement

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

Makes the server "forget" about the existence of a table, a materialized view, a dictionary, or a database.

**Syntax**

```
DETACH TABLE|VIEW|DICTIONARY|DATABASE [IF EXISTS] [db.]name [ON CLUSTER cluster] [PERMANENTLY] [SYNC]
```

Detaching does not delete the data or metadata of a table, a materialized view, a dictionary or a database. If an entity was not detached `PERMANENTLY`, on the next server launch the server will read the metadata and recall the table/view/dictionary/database again. If an entity was detached `PERMANENTLY`, there will be no automatic recall.

Whether a table, a dictionary or a database was detached permanently or not, in both cases you can reattach them using the [ATTACH](/docs/sql-reference/statements/attach) query.
System log tables can be also attached back (e.g. `query_log`, `text_log`, etc.). Other system tables can't be reattached. On the next server launch the server will recall those tables again.

`ATTACH MATERIALIZED VIEW` does not work with short syntax (without `SELECT`), but you can attach it using the `ATTACH TABLE` query.

Note that you can not detach permanently the table which is already detached (temporary). But you can attach it back and then detach permanently again.

Also, you can not [DROP](/docs/sql-reference/statements/drop#drop-table) the detached table, or [CREATE TABLE](/docs/sql-reference/statements/create/table) with the same name as detached permanently, or replace it with the other table with [RENAME TABLE](/docs/sql-reference/statements/rename) query.

The `SYNC` modifier executes the action without delay.

**Example**

Creating a table:

Query:

```
CREATE TABLE test ENGINE = MergeTree ORDER BY () AS SELECT * FROM numbers(10);
SELECT * FROM test;
```

Result:

```
┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘
```

Detaching the table:

Query:

```
DETACH TABLE test;
SELECT * FROM test;
```

Result:

```
Received exception from server (version 21.4.1):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.test does not exist.
```

Note

In ClickHouse Cloud users should use the `PERMANENTLY` clause e.g. `DETACH TABLE <table> PERMANENTLY`. If this clause is not used, tables will be reattached on cluster restart e.g. during upgrades.

**See Also**

- [Materialized View](/docs/sql-reference/statements/create/view#materialized-view)
- [Dictionaries](/docs/sql-reference/statements/create/dictionary)

Was this page helpful?
UNION Clause

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

You can use `UNION` with explicitly specifying `UNION ALL` or `UNION DISTINCT`.

If you don't specify `ALL` or `DISTINCT`, it will depend on the `union_default_mode` setting. The difference between `UNION ALL` and `UNION DISTINCT` is that `UNION DISTINCT` will do a distinct transform for union result, it is equivalent to `SELECT DISTINCT` from a subquery containing `UNION ALL`.

You can use `UNION` to combine any number of `SELECT` queries by extending their results. Example:

```
SELECT CounterID, 1 AS table, toInt64(count()) AS c
    FROM test.hits
    GROUP BY CounterID

UNION ALL

SELECT CounterID, 2 AS table, sum(Sign) AS c
    FROM test.visits
    GROUP BY CounterID
    HAVING c > 0
```

Result columns are matched by their index (order inside `SELECT`). If column names do not match, names for the final result are taken from the first query.

Type casting is performed for unions. For example, if two queries being combined have the same field with non-`Nullable` and `Nullable` types from a compatible type, the resulting `UNION` has a `Nullable` type field.

Queries that are parts of `UNION` can be enclosed in `()`. [ORDER BY](/docs/sql-reference/statements/select/order-by) and [LIMIT](/docs/sql-reference/statements/select/limit) are applied to separate queries, not to the final result. If you need to apply a conversion to the final result, you can put all the queries with `UNION` in a subquery in the [FROM](/docs/sql-reference/statements/select/from) clause.

If you use `UNION` without explicitly specifying `UNION ALL` or `UNION DISTINCT`, you can specify the union mode using the [union\_default\_mode](/docs/operations/settings/settings#union_default_mode) setting. The setting values can be `ALL`, `DISTINCT` or an empty string. However, if you use `UNION` with `union_default_mode` setting to empty string, it will throw an exception. The following examples demonstrate the results of queries with different values setting.

Query:

```
SET union_default_mode = 'DISTINCT';
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 2;
```

Result:

```
┌─1─┐
│ 1 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 3 │
└───┘
```

Query:

```
SET union_default_mode = 'ALL';
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 2;
```

Result:

```
┌─1─┐
│ 1 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 3 │
└───┘
```

Queries that are parts of `UNION/UNION ALL/UNION DISTINCT` can be run simultaneously, and their results can be mixed together.

**See Also**

- [insert\_null\_as\_default](/docs/operations/settings/settings#insert_null_as_default) setting.
- [union\_default\_mode](/docs/operations/settings/settings#union_default_mode) setting.

Was this page helpful?
Syntax

### Notes on Usage[​](#notes-on-usage "Direct link to Notes on Usage")

- Aliases are global for a query or subquery, and you can define an alias in any part of a query for any expression. For example:

```
SELECT (1 AS n) + 2, n`.
```

- Aliases are not visible in subqueries and between subqueries. For example, while executing the following query ClickHouse generates the exception `Unknown identifier: num`:

```
`SELECT (SELECT sum(b.a) + num FROM b) - a.a AS num FROM a`
```

- If an alias is defined for the result columns in the `SELECT` clause of a subquery, these columns are visible in the outer query. For example:

```
SELECT n + m FROM (SELECT 1 AS n, 2 AS m)`.
```

- Be careful with aliases that are the same as column or table names. Let's consider the following example:

```
CREATE TABLE t
(
    a Int,
    b Int
)
ENGINE = TinyLog();

SELECT
    argMax(a, b),
    sum(b) AS b
FROM t;

Received exception from server (version 18.14.17):
Code: 184. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Aggregate function sum(b) is found inside another aggregate function in query.
```

In the preceding example, we declared table `t` with column `b`.
Then, when selecting data, we defined the `sum(b) AS b` alias.
As aliases are global,
ClickHouse substituted the literal `b` in the expression `argMax(a, b)` with the expression `sum(b)`.
This substitution caused the exception.

Note

You can change this default behavior by setting [prefer\_column\_name\_to\_alias](/docs/operations/settings/settings#prefer_column_name_to_alias) to `1`.

## Asterisk[​](#asterisk "Direct link to Asterisk")

In a `SELECT` query, an asterisk can replace the expression.
For more information, see the section [SELECT](/docs/sql-reference/statements/select#asterisk).

- [Query Parsing](#query-parsing)- [Spaces](#spaces)- [Comments](#comments)- [Keywords](#keywords)- [Identifiers](#identifiers)- [Literals](#literals)
            - [String](#string)- [Numeric](#numeric)- [Compound](#compound)- [NULL](#null)- [Heredoc](#heredoc)- [Defining and Using Query Parameters](#defining-and-using-query-parameters)- [Functions](#functions)- [Operators](#operators)- [Data Types and Database Table Engines](#data-types-and-database-table-engines)- [Expressions](#expressions)- [Expression Aliases](#expression-aliases)
                        - [Notes on Usage](#notes-on-usage)- [Asterisk](#asterisk)

Was this page helpful?
remote, remoteSecure Table Function

#### Back on the source deployment[​](#back-on-the-source-deployment "Direct link to Back on the source deployment")

Insert into the new database and table created on the remote system. You will need the host, port, username, password, destination database, and destination table.

```
INSERT INTO FUNCTION
remoteSecure('remote.clickhouse.cloud:9440', 'imdb.actors', 'USER', 'PASSWORD')
SELECT * from imdb.actors
```

## Globbing[​](#globs-in-addresses "Direct link to Globbing")

Patterns in `{ }` are used to generate a set of shards and to specify replicas. If there are multiple pairs of `{ }`, then the direct product of the corresponding sets is generated.

The following pattern types are supported.

- `{a,b,c}` - Represents any of alternative strings `a`, `b` or `c`. The pattern is replaced with `a` in the first shard address and replaced with `b` in the second shard address and so on. For instance, `example0{1,2}-1` generates addresses `example01-1` and `example02-1`.
- `{N..M}` - A range of numbers. This pattern generates shard addresses with incrementing indices from `N` to (and including) `M`. For instance, `example0{1..2}-1` generates `example01-1` and `example02-1`.
- `{0n..0m}` - A range of numbers with leading zeroes. This pattern preserves leading zeroes in indices. For instance, `example{01..03}-1` generates `example01-1`, `example02-1` and `example03-1`.
- `{a|b}` - Any number of variants separated by a `|`. The pattern specifies replicas. For instance, `example01-{1|2}` generates replicas `example01-1` and `example01-2`.

The query will be sent to the first healthy replica. However, for `remote` the replicas are iterated in the order currently set in the [load\_balancing](/docs/operations/settings/settings#load_balancing) setting.
The number of generated addresses is limited by [table\_function\_remote\_max\_addresses](/docs/operations/settings/settings#table_function_remote_max_addresses) setting.

- [Syntax](#syntax)- [Parameters](#parameters)- [Returned value](#returned-value)- [Usage](#usage)
        - [Addresses](#addresses)- [Examples](#examples)
          - [Selecting data from a remote server:](#selecting-data-from-a-remote-server)- [Inserting data into a table on a remote server:](#inserting-data-into-a-table-on-a-remote-server)- [Migration of tables from one system to another:](#migration-of-tables-from-one-system-to-another)- [Globbing](#globs-in-addresses)

Was this page helpful?
Updating Data

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/managing-data/updating-data/index.md)

In this section of the documentation, you will learn how you can update your data.

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  | | --- | --- | --- | --- | | [Working with updates](/docs/updating-data/overview) Provides an overview of the differences in updating data between ClickHouse and OLTP databases, as well as the various methods available to do so in ClickHouse.|  |  | | --- | --- | | [Working with ReplacingMergeTree](/docs/guides/replacing-merge-tree) Learn how to update using the ReplacingMergeTree. | | | | | |

Was this page helpful?
MCP guides

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/AI_ML/MCP/index.md)

[Model Context Protocol](https://www.anthropic.com/news/model-context-protocol) (MCP) is a standard developed by Anthropic that enables AI assistants to seamlessly integrate with external systems.
This protocol allows AI assistants to connect to data sources, APIs, databases, and more in a secure and standardized way.

MCP creates a universal interface between AI models and various services, eliminating the need for custom tool implementations for each integration.
You can think of it as a universal API standard designed specifically for AI systems.

The key advantage of MCP is that AI libraries only need to implement support for the protocol once.
After that, all MCP-compatible services become immediately accessible, saving AI library maintainers a lot of time.

## What is MCP's architecture?[​](#mcp-architecture "Direct link to What is MCP's architecture?")

MCP follows a client-server architecture:

- Clients (like Claude Desktop, Cursor, or VS Code) establish connections with MCP servers. You can see a collection of clients in the [awesome-mcp-clients](https://github.com/punkpeye/awesome-mcp-clients?tab=readme-ov-file#windsurf) GitHub repository.
- Servers expose tools and capabilities through standardized interfaces. You can see a collection of servers in the [awesome-mcp-servers](https://github.com/punkpeye/awesome-mcp-servers) GitHub repository.
- AI models can then use these tools to access external data and functionality when needed

A diagram showing the architecture is below:

## Does ClickHouse have an MCP server?[​](#clickhouse-mcp-server "Direct link to Does ClickHouse have an MCP server?")

It does!
The [ClickHouse MCP server](https://github.com/ClickHouse/mcp-clickhouse) offers the following tools:

- `run_select_query` - Execute SQL queries on your ClickHouse cluster.
- `list_databases` - List all databases on your ClickHouse cluster.
- `list_tables` - List all tables in a database.

If you're looking for a remote MCP server in ClickHouse Cloud, see page ["Remote MCP server in Cloud"](/docs/cloud/features/ai-ml/remote-mcp)

Agent Skills

The [ClickHouse Agent Skills repo](https://github.com/ClickHouse/agent-skills) provides packaged instructions that extend AI coding agents (Claude Code, Cursor, Copilot, etc.) with domain-specific expertise. This repository provides skills for ClickHouse databases—covering schema design, query optimization, and data ingestion patterns.

## Guides for using the ClickHouse MCP server[​](#clickhouse-mcp-server-guides "Direct link to Guides for using the ClickHouse MCP server")

Below are some guides showing how to use the ClickHouse MCP server.
MCP guides

| --- | --- | --- | --- | --- | | [Set up ClickHouse MCP server with Claude Desktop](/docs/use-cases/AI/MCP/claude-desktop) This guide explains how to set up Claude Desktop with a ClickHouse MCP server.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Set up ClickHouse MCP server with Jan.ai](/docs/use-cases/AI/MCP/janai) This guide explains how to set up Jan.ai with a ClickHouse MCP server.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Set up ClickHouse MCP server with LibreChat and ClickHouse Cloud](/docs/use-cases/AI/MCP/librechat) This guide explains how to set up LibreChat with a ClickHouse MCP server using Docker.|  |  |  |  | | --- | --- | --- | --- | | [Set up ClickHouse MCP server with Ollama](/docs/use-cases/AI/MCP/ollama) This guide explains how to set up Ollama with a ClickHouse MCP server.|  |  | | --- | --- | | [Set up ClickHouse MCP server with Open WebUI and ClickHouse Cloud](/docs/use-cases/AI/MCP/open-webui) This guide explains how to set up Open WebUI with a ClickHouse MCP server using Docker. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
How to build an AI agent with Chainlit and the ClickHouse MCP server

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/AI_ML/MCP/ai_agent_libraries/chainlit.md)

This guide explores how to combine Chainlit's powerful chat interface framework
with the ClickHouse Model Context Protocol (MCP) Server to create interactive data
applications. Chainlit enables you to build conversational interfaces for AI
applications with minimal code, while the ClickHouse MCP server provides seamless
integration with ClickHouse's high-performance columnar database.

## Prerequisites[​](#prerequisites "Direct link to Prerequisites")

- You'll need an Anthropic API key
- You'll need to have [`uv`](https://docs.astral.sh/uv/getting-started/installation/) installed

## Basic Chainlit app[​](#basic-chainlit-app "Direct link to Basic Chainlit app")

You can see an example of a basic chat app by running the following:

```
uv run --with anthropic --with chainlit chainlit run chat_basic.py -w -h
```

Then navigate to `http://localhost:8000`

## Adding ClickHouse MCP server[​](#adding-clickhouse-mcp-server "Direct link to Adding ClickHouse MCP server")

Things get more interesting if we add the ClickHouse MCP server.
You'll need to update your `.chainlit/config.toml` file to allow the `uv` command
to be used:

```
[features.mcp.stdio]
    enabled = true
    # Only the executables in the allow list can be used for MCP stdio server.
    # Only need the base name of the executable, e.g. "npx", not "/usr/bin/npx".
    # Please don't comment this line for now, we need it to parse the executable name.
    allowed_executables = [ "npx", "uvx", "uv" ]
```

config.toml

Find the full `config.toml` file in the [examples repository](https://github.com/ClickHouse/examples/blob/main/ai/mcp/chainlit/.chainlit/config.toml)

There's some glue code to get MCP servers working with Chainlit, so we'll need to
run this command to launch Chainlit instead:

```
uv run --with anthropic --with chainlit chainlit run chat_mcp.py -w -h
```

To add the MCP server, click on the plug icon in the chat interface, and then
add the following command to connect to use the ClickHouse SQL Playground:

```
CLICKHOUSE_HOST=sql-clickhouse.clickhouse.com CLICKHOUSE_USER=demo CLICKHOUSE_PASSWORD= CLICKHOUSE_SECURE=true uv run --with mcp-clickhouse --python 3.13 mcp-clickhouse
```

If you want to use your own ClickHouse instance, you can adjust the values of
the environment variables.

You can then ask it questions like this:

- Tell me about the tables that you have to query
- What's something interesting about New York taxis?

- [Prerequisites](#prerequisites)- [Basic Chainlit app](#basic-chainlit-app)- [Adding ClickHouse MCP server](#adding-clickhouse-mcp-server)

Was this page helpful?
Using ClickHouse MCP server with Jan.ai

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/AI_ML/MCP/07_janai.md)

> This guide explains how to use the ClickHouse MCP server with [Jan.ai](https://jan.ai/docs).

Install Jan.ai

## Install Jan.ai[​](#install-janai "Direct link to Install Jan.ai")

Jan.ai is an open source ChatGPT-alternative that runs 100% offline.
You can download Jan.ai for [Mac](https://jan.ai/docs/desktop/mac), [Windows](https://jan.ai/docs/desktop/windows), or [Linux](https://jan.ai/docs/desktop/linux).

It's a native app, so once it's downloaded, you can launch it.

Add LLM to Jan.ai

## Add LLM to Jan.ai[​](#add-llm-to-janai "Direct link to Add LLM to Jan.ai")

We can enabled models via the settings menu.

To enable OpenAI, we need to provide an API key, as shown below:

Enable MCP servers

## Enable MCP servers[​](#enable-mcp-servers "Direct link to Enable MCP servers")

At the time of writing, MCP servers are an experimental feature in Jan.ai.
We can enable them by toggling experimental features:

Once that toggle is pressed, we'll see `MCP servers` on the left menu.

Configure ClickHouse MCP server

## Configure ClickHouse MCP server[​](#configure-clickhouse-mcp-server "Direct link to Configure ClickHouse MCP server")

If we click on the `MCP servers` menu, we'll see a list of MCP servers that we can connect to:

There servers are all disabled by default, but we can able them by clicking the toggle.

To install the ClickHouse MCP server, we need to click on the `+` icon and then populate the form with the following:

Once we've done that, we'll need to toggle the ClickHouse Server if it's not already toggled:

The ClickHouse MCP server's tools will now be visible on the chat dialog:

Chat to ClickHouse MCP server with Jan.ai

## Chat to ClickHouse MCP server with Jan.ai[​](#chat-to-clickhouse-mcp-server "Direct link to Chat to ClickHouse MCP server with Jan.ai")

It's time to have a conversation about some data stored in ClickHouse!
Let's ask a question:

Jan.ai will ask confirmation before calling a tool:

It will then show us the list of tool calls that were made:

If we click on the tool call, we can see the details of the call:

And then underneath, we have our result:

- [Install Jan.ai](#install-janai)- [Add LLM to Jan.ai](#add-llm-to-janai)- [Enable MCP servers](#enable-mcp-servers)- [Configure ClickHouse MCP server](#configure-clickhouse-mcp-server)- [Chat to ClickHouse MCP server with Jan.ai](#chat-to-clickhouse-mcp-server)

Was this page helpful?
Using ClickHouse MCP server with Open WebUI

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/AI_ML/MCP/05_open-webui.md)

> This guide explains how to set up [Open WebUI](https://github.com/open-webui/open-webui) with a ClickHouse MCP server
> and connect it to the ClickHouse example datasets.

Install uv

## Install uv[​](#install-uv "Direct link to Install uv")

You will need to install [uv](https://docs.astral.sh/uv/) to follow the instructions in this guide.
If you don't want to use uv, you will need to update the MCP server config to use an alternative package manager.

Launch Open WebUI

## Launch Open WebUI[​](#launch-open-webui "Direct link to Launch Open WebUI")

To launch Open WebUI, you can run the following command:

```
uv run --with open-webui open-webui serve
```

Navigate to <http://localhost:8080/> to see the UI.

Configure ClickHouse MCP server

## Configure ClickHouse MCP server[​](#configure-clickhouse-mcp-server "Direct link to Configure ClickHouse MCP server")

To setup the ClickHouse MCP server, we'll need to convert the MCP server to Open API endpoints.
Let's first set environmental variables that will let us connect to the ClickHouse SQL Playground:

```
export CLICKHOUSE_HOST="sql-clickhouse.clickhouse.com"
export CLICKHOUSE_USER="demo"
export CLICKHOUSE_PASSWORD=""
```

And, then, we can run `mcpo` to create the Open API endpoints:

```
uvx mcpo --port 8000 -- uv run --with mcp-clickhouse --python 3.10 mcp-clickhouse
```

You can see a list of the endpoints created by navigating to <http://localhost:8000/docs>

To use these endpoints with Open WebUI, we need to navigate to settings:

Click on `Tools`:

Add <http://localhost:8000> as the tool URL:

Once we've done this, we should see a `1` next to the tool icon on the chat bar:

If we click on the tool icon, we can then list the available tools:

Configure OpenAI

## Configure OpenAI[​](#configure-openai "Direct link to Configure OpenAI")

By default, Open WebUI works with Ollama models, but we can add OpenAI compatible endpoints as well.
These are configured via the settings menu, but this time we need to click on the `Connections` tab:

Let's add the endpoint and our OpenAI key:

The OpenAI models will then be available on the top menu:

Chat to ClickHouse MCP server with Open WebUI

## Chat to ClickHouse MCP server with Open WebUI[​](#chat-to-clickhouse-mcp-server "Direct link to Chat to ClickHouse MCP server with Open WebUI")

We can then have a conversation and Open WebUI will call the MCP server if necessary:

- [Install uv](#install-uv)- [Launch Open WebUI](#launch-open-webui)- [Configure ClickHouse MCP server](#configure-clickhouse-mcp-server)- [Configure OpenAI](#configure-openai)- [Chat to ClickHouse MCP server with Open WebUI](#chat-to-clickhouse-mcp-server)

Was this page helpful?