ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/users.md)

Contains a list of [user accounts](/docs/operations/access-rights#user-account-management) configured on the server.

Columns:

- `name` ([String](/docs/sql-reference/data-types)) — User name.
- `id` ([UUID](/docs/sql-reference/data-types)) — User ID.
- `storage` ([String](/docs/sql-reference/data-types)) — Path to the storage of users. Configured in the access\_control\_path parameter.
- `auth_type` ([Array(Enum8('no\_password' = 0, 'plaintext\_password' = 1, 'sha256\_password' = 2, 'double\_sha1\_password' = 3, 'ldap' = 4, 'kerberos' = 5, 'ssl\_certificate' = 6, 'bcrypt\_password' = 7, 'ssh\_key' = 8, 'http' = 9, 'jwt' = 10, 'scram\_sha256\_password' = 11, 'no\_authentication' = 12))](/docs/sql-reference/data-types)) — Shows the authentication types. There are multiple ways of user identification: with no password, with plain text password, with SHA256-encoded password, with double SHA-1-encoded password or with bcrypt-encoded password.
- `auth_params` ([Array(String)](/docs/sql-reference/data-types)) — Authentication parameters in the JSON format depending on the auth\_type.
- `valid_until` ([Array(DateTime)](/docs/sql-reference/data-types)) — The expiration date and time for user credentials.
- `host_ip` ([Array(String)](/docs/sql-reference/data-types)) — IP addresses of hosts that are allowed to connect to the ClickHouse server.
- `host_names` ([Array(String)](/docs/sql-reference/data-types)) — Names of hosts that are allowed to connect to the ClickHouse server.
- `host_names_regexp` ([Array(String)](/docs/sql-reference/data-types)) — Regular expression for host names that are allowed to connect to the ClickHouse server.
- `host_names_like` ([Array(String)](/docs/sql-reference/data-types)) — Names of hosts that are allowed to connect to the ClickHouse server, set using the LIKE predicate.
- `default_roles_all` ([UInt8](/docs/sql-reference/data-types)) — Shows that all granted roles set for user by default.
- `default_roles_list` ([Array(String)](/docs/sql-reference/data-types)) — List of granted roles provided by default.
- `default_roles_except` ([Array(String)](/docs/sql-reference/data-types)) — All the granted roles set as default excepting of the listed ones.
- `grantees_any` ([UInt8](/docs/sql-reference/data-types)) — The flag that indicates whether a user with any grant option can grant it to anyone.
- `grantees_list` ([Array(String)](/docs/sql-reference/data-types)) — The list of users or roles to which this user is allowed to grant options to.
- `grantees_except` ([Array(String)](/docs/sql-reference/data-types)) — The list of users or roles to which this user is forbidden from grant options to.
- `default_database` ([String](/docs/sql-reference/data-types)) — The name of the default database for this user.

## See Also[​](#see-also "Direct link to See Also")

- [SHOW USERS](/docs/sql-reference/statements/show#show-users)

- [See Also](#see-also)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/workloads.md)

Contains information for [workloads](/docs/operations/workload-scheduling#workload_entity_storage) residing on the local server. The table contains a row for every workload.

Example:

```
SELECT *
FROM system.workloads
FORMAT Vertical
```

```
Row 1:
──────
name:         production
parent:       all
create_query: CREATE WORKLOAD production IN `all` SETTINGS weight = 9

Row 2:
──────
name:         development
parent:       all
create_query: CREATE WORKLOAD development IN `all`

Row 3:
──────
name:         all
parent:
create_query: CREATE WORKLOAD `all`
```

Columns:

- `name` ([String](/docs/sql-reference/data-types)) — The name of the workload.
- `parent` ([String](/docs/sql-reference/data-types)) — The name of the parent workload.
- `create_query` ([String](/docs/sql-reference/data-types)) — CREATE query of the workload.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/zookeeper.md)

The table does not exist unless ClickHouse Keeper or ZooKeeper is configured. The `system.zookeeper` table exposes data from the Keeper clusters defined in the config.
The query must either have a `path =` condition or a `path IN` condition set with the `WHERE` clause as shown below. This corresponds to the path of the children that you want to get data for.

The query `SELECT * FROM system.zookeeper WHERE path = '/clickhouse'` outputs data for all children on the `/clickhouse` node.
To output data for all root nodes, write path = '/'.
If the path specified in 'path' does not exist, an exception will be thrown.

The query `SELECT * FROM system.zookeeper WHERE path IN ('/', '/clickhouse')` outputs data for all children on the `/` and `/clickhouse` node.
If in the specified 'path' collection has does not exist path, an exception will be thrown.
It can be used to do a batch of Keeper path queries.

The query `SELECT * FROM system.zookeeper WHERE path = '/clickhouse' AND zookeeperName = 'auxiliary_cluster'` outputs data in `auxiliary_cluster` ZooKeeper cluster.
If the specified 'auxiliary\_cluster' does not exists, an exception will be thrown.

Columns:

- `name` (String) — The name of the node.
- `path` (String) — The path to the node.
- `value` (String) — Node value.
- `zookeeperName` (String) — The name of default or one of auxiliary ZooKeeper cluster.
- `dataLength` (Int32) — Size of the value.
- `numChildren` (Int32) — Number of descendants.
- `czxid` (Int64) — ID of the transaction that created the node.
- `mzxid` (Int64) — ID of the transaction that last changed the node.
- `pzxid` (Int64) — ID of the transaction that last deleted or added descendants.
- `ctime` (DateTime) — Time of node creation.
- `mtime` (DateTime) — Time of the last modification of the node.
- `version` (Int32) — Node version: the number of times the node was changed.
- `cversion` (Int32) — Number of added or removed descendants.
- `aversion` (Int32) — Number of changes to the ACL.
- `ephemeralOwner` (Int64) — For ephemeral nodes, the ID of the session that owns this node.

Example:

```
SELECT *
FROM system.zookeeper
WHERE path = '/clickhouse/tables/01-08/visits/replicas'
FORMAT Vertical
```

```
Row 1:
──────
name:           example01-08-1
value:
czxid:          932998691229
mzxid:          932998691229
ctime:          2015-03-27 16:49:51
mtime:          2015-03-27 16:49:51
version:        0
cversion:       47
aversion:       0
ephemeralOwner: 0
dataLength:     0
numChildren:    7
pzxid:          987021031383
path:           /clickhouse/tables/01-08/visits/replicas

Row 2:
──────
name:           example01-08-2
value:
czxid:          933002738135
mzxid:          933002738135
ctime:          2015-03-27 16:57:01
mtime:          2015-03-27 16:57:01
version:        0
cversion:       37
aversion:       0
ephemeralOwner: 0
dataLength:     0
numChildren:    7
pzxid:          987021252247
path:           /clickhouse/tables/01-08/visits/replicas
```

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/utilities/index.md)

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Tool/Utility Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [clickhouse-local](/docs/operations/utilities/clickhouse-local) Allows running SQL queries on data without starting the ClickHouse server, similar to how `awk` does this.| [clickhouse-benchmark](/docs/operations/utilities/clickhouse-benchmark) Loads server with the custom queries and settings.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [clickhouse-format](/docs/operations/utilities/clickhouse-format) Enables formatting input queries.|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [ClickHouse obfuscator](/docs/operations/utilities/clickhouse-obfuscator) Obfuscates data.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [ClickHouse compressor](/docs/operations/utilities/clickhouse-compressor) Compresses and decompresses data.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [clickhouse-disks](/docs/operations/utilities/clickhouse-disks) Provides filesystem-like operations on files among different ClickHouse disks.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [clickhouse-odbc-bridge](/docs/operations/utilities/odbc-bridge) A proxy server for ODBC driver.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [clickhouse\_backupview](/docs/operations/utilities/backupview) A python module to analyze ClickHouse backups.|  |  |  |  | | --- | --- | --- | --- | | [clickhouse-keeper-client](/docs/operations/utilities/clickhouse-keeper-client) A client application to interact with ClickHouse Keeper.|  |  | | --- | --- | | [Keeper HTTP API](/docs/operations/utilities/clickhouse-keeper-http-api) HTTP API and embedded dashboard for ClickHouse Keeper. | | | | | | | | | | | | | | | | | | | | | |

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/utilities/backupview.md)

Python module to help analyzing backups made by the [BACKUP](/docs/operations/backup/overview) command.
The main motivation was to allows getting some information from a backup without actually restoring it.

This module provides functions to

- enumerate files contained in a backup
- read files from a backup
- get useful information in readable form about databases, tables, parts contained in a backup
- check integrity of a backup

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

```
from clickhouse_backupview import open_backup, S3, FileInfo

# Open a backup. We could also use a local path:
# backup = open_backup("/backups/my_backup_1/")
backup = open_backup(S3("uri", "access_key_id", "secret_access_key"))

# Get a list of databasess inside the backup.
print(backup.get_databases()))

# Get a list of tables inside the backup,
# and for each table its create query and a list of parts and partitions.
for db in backup.get_databases():
    for tbl in backup.get_tables(database=db):
        print(backup.get_create_query(database=db, table=tbl))
        print(backup.get_partitions(database=db, table=tbl))
        print(backup.get_parts(database=db, table=tbl))

# Extract everything from the backup.
backup.extract_all(table="mydb.mytable", out='/tmp/my_backup_1/all/')

# Extract the data of a specific table.
backup.extract_table_data(table="mydb.mytable", out='/tmp/my_backup_1/mytable/')

# Extract a single partition.
backup.extract_table_data(table="mydb.mytable", partition="202201", out='/tmp/my_backup_1/202201/')

# Extract a single part.
backup.extract_table_data(table="mydb.mytable", part="202201_100_200_3", out='/tmp/my_backup_1/202201_100_200_3/')
```

For more examples see the [test](https://github.com/ClickHouse/ClickHouse/blob/master/utils/backupview/test/test.py).

- [Example:](#example)

Was this page helpful?
clickhouse-benchmark

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

```
$ echo "SELECT * FROM system.numbers LIMIT 10000000 OFFSET 10000000" | clickhouse-benchmark --host=localhost --port=9001 --host=localhost --port=9000 -i 10
```

```
Loaded 1 queries.

Queries executed: 5.

localhost:9001, queries 2, QPS: 3.764, RPS: 75446929.370, MiB/s: 575.614, result RPS: 37639659.982, result MiB/s: 287.168.
localhost:9000, queries 3, QPS: 3.815, RPS: 76466659.385, MiB/s: 583.394, result RPS: 38148392.297, result MiB/s: 291.049.

0.000%          0.258 sec.      0.250 sec.
10.000%         0.258 sec.      0.250 sec.
20.000%         0.258 sec.      0.250 sec.
30.000%         0.258 sec.      0.267 sec.
40.000%         0.258 sec.      0.267 sec.
50.000%         0.273 sec.      0.267 sec.
60.000%         0.273 sec.      0.267 sec.
70.000%         0.273 sec.      0.267 sec.
80.000%         0.273 sec.      0.269 sec.
90.000%         0.273 sec.      0.269 sec.
95.000%         0.273 sec.      0.269 sec.
99.000%         0.273 sec.      0.269 sec.
99.900%         0.273 sec.      0.269 sec.
99.990%         0.273 sec.      0.269 sec.

No difference proven at 99.5% confidence
```

- [Command-line options](#clickhouse-benchmark-command-line-options)- [Environment variable options](#clickhouse-benchmark-environment-variable-options)- [Output](#clickhouse-benchmark-output)- [Comparison Mode](#clickhouse-benchmark-comparison-mode)- [Example](#clickhouse-benchmark-example)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/utilities/clickhouse-compressor.md)

Simple program for data compression and decompression.

### Examples[​](#examples "Direct link to Examples")

Compress data with LZ4:

```
$ ./clickhouse-compressor < input_file > output_file
```

Decompress data from LZ4 format:

```
$ ./clickhouse-compressor --decompress < input_file > output_file
```

Compress data with ZSTD at level 5:

```
$ ./clickhouse-compressor --codec 'ZSTD(5)' < input_file > output_file
```

Compress data with Delta of four bytes and ZSTD level 10.

```
$ ./clickhouse-compressor --codec 'Delta(4)' --codec 'ZSTD(10)' < input_file > output_file
```

- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/utilities/clickhouse-format.md)

Allows formatting input queries.

Keys:

- `--help` or`-h` — Produce help message.
- `--query` — Format queries of any length and complexity.
- `--hilite` or `--highlight` — Add syntax highlight with ANSI terminal escape sequences.
- `--oneline` — Format in single line.
- `--max_line_length` — Format in single line queries with length less than specified.
- `--comments` — Keep comments in the output.
- `--quiet` or `-q` — Just check syntax, no output on success.
- `--multiquery` or `-n` — Allow multiple queries in the same file.
- `--obfuscate` — Obfuscate instead of formatting.
- `--seed <string>` — Seed arbitrary string that determines the result of obfuscation.
- `--backslash` — Add a backslash at the end of each line of the formatted query. Can be useful when you copy a query from web or somewhere else with multiple lines, and want to execute it in command line.
- `--semicolons_inline` — In multiquery mode, write semicolons on last line of query instead of a new line.

## Examples[​](#examples "Direct link to Examples")

1. Formatting a query:

```
$ clickhouse-format --query "select number from numbers(10) where number%2 order by number desc;"
```

Result:

```
SELECT number
FROM numbers(10)
WHERE number % 2
ORDER BY number DESC
```

2. Highlighting and single line:

```
$ clickhouse-format --oneline --hilite <<< "SELECT sum(number) FROM numbers(5);"
```

Result:

```
SELECT sum(number) FROM numbers(5)
```

3. Multiqueries:

```
$ clickhouse-format -n <<< "SELECT min(number) FROM numbers(5); SELECT max(number) FROM numbers(5);"
```

Result:

```
SELECT min(number)
FROM numbers(5)
;

SELECT max(number)
FROM numbers(5)
;
```

4. Obfuscating:

```
$ clickhouse-format --seed Hello --obfuscate <<< "SELECT cost_first_screen BETWEEN a AND b, CASE WHEN x >= 123 THEN y ELSE NULL END;"
```

Result:

```
SELECT treasury_mammoth_hazelnut BETWEEN nutmeg AND span, CASE WHEN chive >= 116 THEN switching ELSE ANYTHING END;
```

Same query and another seed string:

```
$ clickhouse-format --seed World --obfuscate <<< "SELECT cost_first_screen BETWEEN a AND b, CASE WHEN x >= 123 THEN y ELSE NULL END;"
```

Result:

```
SELECT horse_tape_summer BETWEEN folklore AND moccasins, CASE WHEN intestine >= 116 THEN nonconformist ELSE FORESTRY END;
```

5. Adding backslash:

```
$ clickhouse-format --backslash <<< "SELECT * FROM (SELECT 1 AS x UNION ALL SELECT 1 UNION DISTINCT SELECT 3);"
```

Result:

```
SELECT * \
FROM  \
( \
    SELECT 1 AS x \
    UNION ALL \
    SELECT 1 \
    UNION DISTINCT \
    SELECT 3 \
)
```

- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/utilities/clickhouse-obfuscator.md)

A simple tool for table data obfuscation.

It reads an input table and produces an output table, that retains some properties of input, but contains different data.
It allows publishing almost real production data for usage in benchmarks.

It is designed to retain the following properties of data:

- cardinalities of values (number of distinct values) for every column and every tuple of columns;
- conditional cardinalities: number of distinct values of one column under the condition on the value of another column;
- probability distributions of the absolute value of integers; the sign of signed integers; exponent and sign for floats;
- probability distributions of the length of strings;
- probability of zero values of numbers; empty strings and arrays, `NULL`s;
- data compression ratio when compressed with LZ77 and entropy family of codecs;
- continuity (magnitude of difference) of time values across the table; continuity of floating-point values;
- date component of `DateTime` values;
- UTF-8 validity of string values;
- string values look natural.

Most of the properties above are viable for performance testing:

reading data, filtering, aggregation, and sorting will work at almost the same speed
as on original data due to saved cardinalities, magnitudes, compression ratios, etc.

It works in a deterministic fashion: you define a seed value and the transformation is determined by input data and by seed.
Some transformations are one to one and could be reversed, so you need to have a large seed and keep it in secret.

It uses some cryptographic primitives to transform data but from the cryptographic point of view, it does not do it properly, that is why you should not consider the result as secure unless you have another reason. The result may retain some data you don't want to publish.

It always leaves 0, 1, -1 numbers, dates, lengths of arrays, and null flags exactly as in source data.
For example, you have a column `IsMobile` in your table with values 0 and 1. In transformed data, it will have the same value.

So, the user will be able to count the exact ratio of mobile traffic.

Let's give another example. When you have some private data in your table, like user email, and you don't want to publish any single email address.
If your table is large enough and contains multiple different emails and no email has a very high frequency than all others, it will anonymize all data. But if you have a small number of different values in a column, it can reproduce some of them.
You should look at the working algorithm of this tool works, and fine-tune its command line parameters.

This tool works fine only with at least a moderate amount of data (at least 1000s of rows).

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/utilities/odbc-bridge.md)

Simple HTTP-server which works like a proxy for ODBC driver. The main motivation
was possible segfaults or another faults in ODBC implementations, which can
crash whole clickhouse-server process.

This tool works via HTTP, not via pipes, shared memory, or TCP because:

- It's simpler to implement
- It's simpler to debug
- jdbc-bridge can be implemented in the same way

## Usage[​](#usage "Direct link to Usage")

`clickhouse-server` use this tool inside odbc table function and StorageODBC.
However it can be used as standalone tool from command line with the following
parameters in POST-request URL:

- `connection_string` -- ODBC connection string.
- `sample_block` -- columns description in ClickHouse NamesAndTypesList format, name in backticks,
  type as string. Name and type are space separated, rows separated with
  newline.
- `max_block_size` -- optional parameter, sets maximum size of single block.
  Query is send in post body. Response is returned in RowBinary format.

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

```
$ clickhouse-odbc-bridge --http-port 9018 --daemon

$ curl -d "query=SELECT PageID, ImpID, AdType FROM Keys ORDER BY PageID, ImpID" --data-urlencode "connection_string=DSN=ClickHouse;DATABASE=stat" --data-urlencode "sample_block=columns format version: 1
3 columns:
\`PageID\` String
\`ImpID\` String
\`AdType\` String
"  "http://localhost:9018/" > result.txt

$ cat result.txt
12246623837185725195925621517
```

- [Usage](#usage)- [Example:](#example)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/best-practices/avoidnullablecolumns.md)

[`Nullable` column](/docs/sql-reference/data-types/nullable) (e.g. `Nullable(String)`) creates a separate column of `UInt8` type. This additional column has to be processed every time a user works with a Nullable column. This leads to additional storage space used and almost always negatively affects performance.

To avoid `Nullable` columns, consider setting a default value for that column. For example, instead of:

```
CREATE TABLE default.sample
(
    `x` Int8,
    -- highlight-next-line
    `y` Nullable(Int8)
)
ENGINE = MergeTree
ORDER BY x
```

use

```
CREATE TABLE default.sample2
(
    `x` Int8,
    -- highlight-next-line
    `y` Int8 DEFAULT 0
)
ENGINE = MergeTree
ORDER BY x
```

Consider your use case; a default value may be inappropriate.

Was this page helpful?
Bulk inserts

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/best-practices/bulkinserts.md)

The above mechanics illustrate a constant overhead regardless of the insert size, making batch size the single most important optimization for ingest throughput. Batching inserts reduce the overhead as a proportion of total insert time and improves processing efficiency.

We recommend inserting data in batches of at least 1,000 rows, and ideally between 10,000–100,000 rows. Fewer, larger inserts reduce the number of parts written, minimize merge load, and lower overall system resource usage.

**For a synchronous insert strategy to be effective this client-side batching is required.**

If you're unable to batch data client-side, ClickHouse supports asynchronous inserts that shift batching to the server ([see Asynchronous inserts](/docs/best-practices/selecting-an-insert-strategy#asynchronous-inserts)).

Tip

Regardless of the size of your inserts, we recommend keeping the number of insert queries around one insert query per second. The reason for this recommendation is that the created parts are merged to larger parts in the background (in order to optimize your data for read queries), and sending too many insert queries per second can lead to situations where the background merging can't keep up with the number of new parts. However, you can use a higher rate of insert queries per second when you use asynchronous inserts (see [Asynchronous inserts](/docs/best-practices/selecting-an-insert-strategy#asynchronous-inserts)).

Was this page helpful?