ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/SQLInsert.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✗ ✔  | | | | | |

## Description[​](#description "Direct link to Description")

Outputs data as a sequence of `INSERT INTO table (columns...) VALUES (...), (...) ...;` statements.

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

Example:

```
SELECT number AS x, number + 1 AS y, 'Hello' AS z FROM numbers(10) FORMAT SQLInsert SETTINGS output_format_sql_insert_max_batch_size = 2
```

```
INSERT INTO table (x, y, z) VALUES (0, 1, 'Hello'), (1, 2, 'Hello');
INSERT INTO table (x, y, z) VALUES (2, 3, 'Hello'), (3, 4, 'Hello');
INSERT INTO table (x, y, z) VALUES (4, 5, 'Hello'), (5, 6, 'Hello');
INSERT INTO table (x, y, z) VALUES (6, 7, 'Hello'), (7, 8, 'Hello');
INSERT INTO table (x, y, z) VALUES (8, 9, 'Hello'), (9, 10, 'Hello');
```

To read data output by this format you can use [MySQLDump](/docs/interfaces/formats/MySQLDump) input format.

## Format settings[​](#format-settings "Direct link to Format settings")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Description Default|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`output_format_sql_insert_max_batch_size`](/docs/operations/settings/formats#output_format_sql_insert_max_batch_size) The maximum number of rows in one INSERT statement. `65505`| [`output_format_sql_insert_table_name`](/docs/operations/settings/formats#output_format_sql_insert_table_name) The name of the table in the output INSERT query. `'table'`| [`output_format_sql_insert_include_column_names`](/docs/operations/settings/formats#output_format_sql_insert_include_column_names) Include column names in INSERT query. `true`| [`output_format_sql_insert_use_replace`](/docs/operations/settings/formats#output_format_sql_insert_use_replace) Use REPLACE statement instead of INSERT. `false`| [`output_format_sql_insert_quote_names`](/docs/operations/settings/formats#output_format_sql_insert_quote_names) Quote column names with "`" characters. `true` | | | | | | | | | | | | | | | | | |

- [Description](#description)- [Example usage](#example-usage)- [Format settings](#format-settings)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/Template/TemplateIgnoreSpaces.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✔ ✗  | | | | | |

## Description[​](#description "Direct link to Description")

Similar to [`Template`], but skips whitespace characters between delimiters and values in the input stream.
However, if format strings contain whitespace characters, these characters will be expected in the input stream.
Also allows specifying empty placeholders (`${}` or `${:None}`) to split some delimiter into separate parts to ignore spaces between them.
Such placeholders are used only for skipping whitespace characters.
It's possible to read `JSON` using this format if the values of columns have the same order in all rows.

Note

This format is suitable only for input.

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

The following request can be used for inserting data from its output example of format [JSON](/docs/interfaces/formats/JSON):

```
INSERT INTO table_name 
SETTINGS
    format_template_resultset = '/some/path/resultset.format',
    format_template_row = '/some/path/row.format',
    format_template_rows_between_delimiter = ','
FORMAT TemplateIgnoreSpaces
```

```
{${}"meta"${}:${:JSON},${}"data"${}:${}[${data}]${},${}"totals"${}:${:JSON},${}"extremes"${}:${:JSON},${}"rows"${}:${:JSON},${}"rows_before_limit_at_least"${}:${:JSON}${}}
```

```
{${}"SearchPhrase"${}:${}${phrase:JSON}${},${}"c"${}:${}${cnt:JSON}${}}
```

## Format settings[​](#format-settings "Direct link to Format settings")

- [Description](#description)- [Example usage](#example-usage)- [Format settings](#format-settings)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/Vertical.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✗ ✔  | | | | | |

## Description[​](#description "Direct link to Description")

Prints each value on a separate line with the column name specified. This format is convenient for printing just one or a few rows if each row consists of a large number of columns.

Note that [`NULL`](/docs/sql-reference/syntax) is output as `ᴺᵁᴸᴸ` to make it easier to distinguish between the string value `NULL` and no value. JSON columns will be pretty printed, and `NULL` is output as `null`, because it is a valid JSON value and easily distinguishable from `"null"`.

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

Example:

```
SELECT * FROM t_null FORMAT Vertical
```

```
Row 1:
──────
x: 1
y: ᴺᵁᴸᴸ
```

Rows are not escaped in Vertical format:

```
SELECT 'string with \'quotes\' and \t with some special \n characters' AS test FORMAT Vertical
```

```
Row 1:
──────
test: string with 'quotes' and      with some special
 characters
```

This format is only appropriate for outputting a query result, but not for parsing (retrieving data to insert in a table).

## Format settings[​](#format-settings "Direct link to Format settings")

- [Description](#description)- [Example usage](#example-usage)- [Format settings](#format-settings)

Was this page helpful?
MySQL Interface

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
```

For compatibility with all MySQL clients, it is recommended to specify user password with [double SHA1](/docs/operations/settings/settings-users#user-namepassword) in configuration file.
If user password is specified using [SHA256](/docs/sql-reference/functions/hash-functions#SHA256), some clients won't be able to authenticate (mysqljs and old versions of command-line tool MySQL and MariaDB).

Restrictions:

- prepared queries aren't supported
- some data types are sent as strings

To cancel a long query use `KILL QUERY connection_id` statement (it is replaced with `KILL QUERY WHERE query_id = connection_id` while proceeding). For example:

```
$ mysql --protocol tcp -h mysql_server -P 9004 default -u default --password=123 -e "KILL QUERY 123456;"
```

- [Enabling the MySQL Interface On ClickHouse Cloud](#enabling-the-mysql-interface-on-clickhouse-cloud)- [Creating multiple MySQL users in ClickHouse Cloud](#creating-multiple-mysql-users-in-clickhouse-cloud)
    - [Troubleshooting multiple MySQL users in ClickHouse Cloud](#troubleshooting-multiple-mysql-users-in-clickhouse-cloud)- [Enabling the MySQL interface on self-managed ClickHouse](#enabling-the-mysql-interface-on-self-managed-clickhouse)- [Connect MySQL to ClickHouse](#connect-mysql-to-clickhouse)

Was this page helpful?
Drivers and Interfaces

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/interfaces/overview.md)

ClickHouse provides two network interfaces (they can be optionally wrapped in TLS for additional security):

- [HTTP](/docs/interfaces/http), which is documented and easy to use directly.
- [Native TCP](/docs/interfaces/tcp), which has less overhead.

In most cases it is recommended to use an appropriate tool or library instead of interacting with those directly. The following are officially supported by ClickHouse:

- [Command-line client](/docs/interfaces/cli)
- [JDBC driver](/docs/interfaces/jdbc)
- [ODBC driver](/docs/interfaces/odbc)
- [C++ client library](/docs/interfaces/cpp)

ClickHouse also supports two RPC protocols:

- [gRPC protocol](/docs/interfaces/grpc) specially designed for ClickHouse.
- [Apache Arrow Flight](/docs/interfaces/arrowflight).

ClickHouse server provides embedded visual interfaces for power users:

- Play UI: open `/play` in the browser;
- Advanced Dashboard: open `/dashboard` in the browser;
- Binary symbols viewer for ClickHouse engineers: open `/binary` in the browser;
- ClickStack UI for observability: open `/clickstack` in the browser;

ClickStack

This version of ClickStack is great for local debugging and quick investigation, but isn't meant for production environments. For production ready full-featured ClickStack, see [Open Source ClickStack](/docs/use-cases/observability/clickstack/getting-started/oss).

There are also a wide range of third-party libraries for working with ClickHouse:

- [Client libraries](/docs/interfaces/third-party/client-libraries)
- [Integrations](/docs/interfaces/third-party/integrations)
- [Visual interfaces](/docs/interfaces/third-party/gui)

Was this page helpful?
Native interface (TCP)

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/interfaces/tcp.md)

The native protocol is used in the [command-line client](/docs/interfaces/cli), for inter-server communication during distributed query processing, and also in other C++ programs. Unfortunately, native ClickHouse protocol doesn't have formal specification yet, but it can be reverse-engineered from ClickHouse source code (starting [around here](https://github.com/ClickHouse/ClickHouse/tree/master/src/Client)) and/or by intercepting and analyzing TCP traffic.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/third-party/index.md)

This is a collection of links to third-party tools that provide some sort of interface to ClickHouse. It can be either visual interface, command-line interface or an API:

- [Client libraries](/docs/interfaces/third-party/client-libraries)
- [Integrations](/docs/interfaces/third-party/integrations)
- [GUI](/docs/interfaces/third-party/gui)
- [Proxies](/docs/interfaces/third-party/proxy)

Note

Generic tools that support common API like [ODBC](/docs/interfaces/odbc) or [JDBC](/docs/interfaces/jdbc) usually can work with ClickHouse as well, but are not listed here because there are way too many of them.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/third-party/proxy.md)

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

[chproxy](https://github.com/Vertamedia/chproxy), is an HTTP proxy and load balancer for ClickHouse database.

Features:

- Per-user routing and response caching.
- Flexible limits.
- Automatic SSL certificate renewal.

Implemented in Go.

## KittenHouse[​](#kittenhouse "Direct link to KittenHouse")

[KittenHouse](https://github.com/VKCOM/kittenhouse) is designed to be a local proxy between ClickHouse and application server in case it's impossible or inconvenient to buffer INSERT data on your application side.

Features:

- In-memory and on-disk data buffering.
- Per-table routing.
- Load-balancing and health checking.

Implemented in Go.

## ClickHouse-Bulk[​](#clickhouse-bulk "Direct link to ClickHouse-Bulk")

[ClickHouse-Bulk](https://github.com/nikepan/clickhouse-bulk) is a simple ClickHouse insert collector.

Features:

- Group requests and send by threshold or interval.
- Multiple remote servers.
- Basic authentication.

Implemented in Go.

- [chproxy](#chproxy)- [KittenHouse](#kittenhouse)- [ClickHouse-Bulk](#clickhouse-bulk)

Was this page helpful?
title_wip

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

Welcome to ClickHouse! Check out the pages below to learn how to get up and running with ClickHouse - the fastest and most resource efficient real-time data warehouse and open-source database.

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [What is ClickHouse?](/docs/intro) Learn more about what ClickHouse is.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Quick Start](/docs/get-started/quick-start) Quick start guide to get you up and running in no time.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Advanced Tutorial](/docs/tutorial) Comfortable with the basics? Let's do something more interesting.|  |  |  |  | | --- | --- | --- | --- | | [Install](/docs/install) Learn about the various ways you can install ClickHouse.|  |  | | --- | --- | | [Deployment modes](/docs/deployment-modes) This guide explores the four main ways to deploy and use ClickHouse. | | | | | | | | | | | |

Was this page helpful?
title_wip

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


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

When executing a `INSERT...SELECT` statement, I am getting too many parts (TOO\_MANY\_PARTS) error.

How can I solve this?

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

Below are some of the settings to tune to avoid this error, this is expert level tuning of ClickHouse and these values should be set only after understanding the specifications of the ClickHouse cloud service or on-prem cluster where these will be used, so do not take these values as "one size fits all".

[max\_insert\_block\_size](https://clickhouse.com/docs/operations/settings/settings#settings-max_insert_block_size) = `100_000_000` (default `1_048_576`)

Increase from ~1M to 100M would allow larger blocks to form

Note: This setting only applies when the server forms the blocks. i.e. INSERT via the HTTP interface, and not for clickhouse-client

[min\_insert\_block\_size\_rows](https://clickhouse.com/docs/operations/settings/settings#min-insert-block-size-rows) = `100_000_000` (default `1_048_576`)

Increase from ~1M to 100M would allow larger blocks to form.

[min\_insert\_block\_size\_bytes](https://clickhouse.com/docs/operations/settings/settings#min-insert-block-size-bytes) = `500_000_000` (default `268_435_456`)

Increase from 268.44 MB to 500 MB would allow larger blocks to form.

[parts\_to\_delay\_insert](https://clickhouse.com/docs/operations/settings/merge-tree-settings#parts-to-delay-insert) = `500` (default `150`)

Increasing this so that INSERTs are not artificially slowed down when the number of active parts in a single partition is reached.

[parts\_to\_throw\_insert](https://clickhouse.com/docs/operations/settings/merge-tree-settings#parts-to-throw-insert) = `1500` (default `3000`)

Increasing this would generally affect query performance to the table, but this would be fine for data migration.

Jul 21, 2023 · 2 min read
title_wip

ODBC authentication failed error when using the Power BI ClickHouse connector


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

When trying to connect from PowerBI to ClickHouse using the connector, you receive a authentication error.

This error usually looks like the following:

```
We encountered an error while trying to connect.
Details: "ODBC: ERROR [HY000] HTTP status code: 403
Received error:
Code: 516. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.
If you have installed ClickHouse and forgot password you can reset it in the configuration file.
The password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml and deleting this file will reset the password.
See also /etc/clickhouse-server/users.ml on the server where
ClickHouse is installed.
```

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

Update the ClickHouse ODBC Driver to version [1.4.1](https://github.com/ClickHouse/clickhouse-odbc/releases/tag/1.4.1.20250523).

If updating is not possible, check whether the password contains a tilde (~), as this can cause issues in versions prior to [1.4.1](https://github.com/ClickHouse/clickhouse-odbc/releases/tag/1.4.1.20250523).

We recommend using a dedicated user for the connection and setting the password manually. If you're using ClickHouse Cloud and require admin-level access equivalent of the `default` user, then create a new user and assign the `default_role` to it.

For more information:  
<https://clickhouse.com/docs/operations/access-rights#user-account-management>
<https://clickhouse.com/docs/cloud/security/cloud-access-management#database-roles>

Jul 10, 2024 · 2 min read
title_wip

-- Ok.

-- 0 rows in set. Elapsed: 0.097 sec.


-- grant role to the user
clickhouse-cloud :) GRANT role_with_query_complexity TO user_with_query_complexity

-- GRANT role_with_query_complexity TO user_with_query_complexity

-- Query id: f28c7c7b-61f7-48a8-a281-1f3784764b47

-- Ok.

-- 0 rows in set. Elapsed: 0.096 sec.
```

Now login as the user `user_with_query_complexity`:

```
-- login as the user where query complexity is applied through the role
clickhouse-cloud :) SELECT user();

-- SELECT user()

-- Query id: 196c91fc-abff-464d-acce-6af961c233a3

-- ┌─currentUser()──────────────┐
-- │ user_with_query_complexity │
-- └────────────────────────────┘

-- 1 row in set. Elapsed: 0.001 sec.


-- list grants
clickhouse-cloud :) SHOW GRANTS

-- SHOW GRANTS

-- Query id: 87657b99-c3d9-4ffd-90e8-488f04f7f93b

-- ┌─GRANTS─────────────────────────────────────────────────────────┐
-- │ GRANT role_with_query_complexity TO user_with_query_complexity │
-- └────────────────────────────────────────────────────────────────┘

-- 1 row in set. Elapsed: 0.001 sec.

-- attempt to query with 1 row in resultset
clickhouse-cloud :) SELECT * FROM default.test_table LIMIT 1;

-- SELECT *
-- FROM default.test_table
-- LIMIT 1

-- Query id: 7266891b-8611-4342-81b0-fe04766e62fa

-- ┌─name─┬─age─┐
-- │      │ 200 │
-- └──────┴─────┘

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


-- attempt to query with more than 1 row in resultset
clickhouse-cloud :) SELECT * FROM default.test_table LIMIT 2;

-- SELECT *
-- FROM default.test_table
-- LIMIT 2

-- Query id: ec8ecff3-f731-45bd-bb27-894ba358c7c8

-- 0 rows in set. Elapsed: 0.091 sec.

--Received exception from server (version 23.8.1):
--Code: 396. DB::Exception: Received from dxqjx1s5lt.eu-west-1.aws.clickhouse.cloud:9440. 
--DB::Exception: Limit for result exceeded, max rows: 1.00, current rows: 2.00. (TOO_MANY_ROWS_OR_BYTES)
```

Whenever attempting to get more than 1 row in resultset the query complexity constraint will kick in.

Oct 25, 2023 · 7 min read
title_wip

Handing the an exception thrown when altering user settings


## DB::Exception: Cannot update user `default` in users.xml because this storage is readonly. (ACCESS\_STORAGE\_READONLY)[​](#dbexception-cannot-update-user-default-in-usersxml-because-this-storage-is-readonly-access_storage_readonly "Direct link to dbexception-cannot-update-user-default-in-usersxml-because-this-storage-is-readonly-access_storage_readonly")

When you try to alter a user's settings, you may encounter the above exception.

Here are a few options to troubleshoot this error:

## Edit users.xml directly[​](#edit-usersxml-directly "Direct link to Edit users.xml directly")

You can edit or add the desired settings for a specific user in `users.xml` directly in the file `/etc/clickhouse-server/users.d`.

Read more about `users.xml` [here](/docs/operations/settings/settings-profiles).

## Create another user[​](#create-another-user "Direct link to Create another user")

You can create another user with the specified settings, then connect to ClickHouse using that new user.

View [this page](/docs/sql-reference/statements/create/user) to learn how to create users.

## Enable SQL-driven access control[​](#enable-sql-driven-access-control "Direct link to Enable SQL-driven access control")

You can enable SQL-drive access control and account management for the default user. The steps to enable this are specified in this [page](/docs/operations/access-rights#enabling-access-control).

Aug 26, 2023 · One min read
title_wip

This KB article explores whether Materialized Views are inserted synchronously


## Are Materialized Views inserted synchronously?[​](#are-materialized-views-inserted-synchronously "Direct link to Are Materialized Views inserted synchronously?")

**Question:** When a source table has new rows inserted into it, those new rows are also sent to all of the materialized views of that source table. Are inserts into Materialized Views performed synchronously, meaning that once the insert is acknowledged successfully from the server to the client, it means that all Materialized Views have been fully updated and available for queries?

**Answer:**

1. When an `INSERT` succeeds, the data is inserted both to the table and all materialized views.
2. The insert is not atomic with respect to materialized views. At the moment of time when the `INSERT` is in progress, concurrent clients may see the intermediate state, when the data is inserted to the main table, but not to materialized views, or vice versa.
3. If you are using [async inserts](https://clickhouse.com/docs/optimize/asynchronous-inserts/), they collect the data and perform a regular insert under the hood, returning the same type of answer to the client as for regular inserts. If the client received success from an async insert with the option `wait_for_async_insert` (as by default), the data is inserted into both the table and all of its materialized views.

**Question:** How about chained/cascaded materialized views?

**Answer:**
The same rules apply - an `INSERT` with a successful response means that the data was inserted into every materialized view in the chain. The insert is non-atomic.

Mar 1, 2023 · 2 min read