ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

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

Contains information about parts of [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree) tables.

Each row describes one data part.

Columns:

- `partition` ([String](/docs/sql-reference/data-types/string)) – The partition name. To learn what a partition is, see the description of the [ALTER](/docs/sql-reference/statements/alter) query.

  Formats:

  - `YYYYMM` for automatic partitioning by month.
  - `any_string` when partitioning manually.
- `name` ([String](/docs/sql-reference/data-types/string)) – Name of the data part. The part naming structure can be used to determine many aspects of the data, ingest, and merge patterns. The part naming format is the following:

```
<partition_id>_<minimum_block_number>_<maximum_block_number>_<level>_<data_version>
```

- Definitions:
  - `partition_id` - identifies the partition key
  - `minimum_block_number` - identifies the minimum block number in the part. ClickHouse always merges continuous blocks
  - `maximum_block_number` - identifies the maximum block number in the part
  - `level` - incremented by one with each additional merge on the part. A level of 0 indicates this is a new part that has not been merged. It is important to remember that all parts in ClickHouse are always immutable
  - `data_version` - optional value, incremented when a part is mutated (again, mutated data is always only written to a new part, since parts are immutable)

- `uuid` ([UUID](/docs/sql-reference/data-types/uuid)) - The UUID of data part.
- `part_type` ([String](/docs/sql-reference/data-types/string)) — The data part storing format.

  Possible Values:

  - `Wide` — Each column is stored in a separate file in a filesystem.
  - `Compact` — All columns are stored in one file in a filesystem.
title_wip

**Example**

```
SELECT * FROM system.parts LIMIT 1 FORMAT Vertical;
```

```
Row 1:
──────
partition:                             tuple()
name:                                  all_1_4_1_6
part_type:                             Wide
active:                                1
marks:                                 2
rows:                                  6
bytes_on_disk:                         310
data_compressed_bytes:                 157
data_uncompressed_bytes:               91
secondary_indices_compressed_bytes:    58
secondary_indices_uncompressed_bytes:  6
secondary_indices_marks_bytes:         48
marks_bytes:                           144
modification_time:                     2020-06-18 13:01:49
remove_time:                           1970-01-01 00:00:00
refcount:                              1
min_date:                              1970-01-01
max_date:                              1970-01-01
min_time:                              1970-01-01 00:00:00
max_time:                              1970-01-01 00:00:00
partition_id:                          all
min_block_number:                      1
max_block_number:                      4
level:                                 1
data_version:                          6
primary_key_bytes_in_memory:           8
primary_key_bytes_in_memory_allocated: 64
is_frozen:                             0
database:                              default
table:                                 months
engine:                                MergeTree
disk_name:                             default
path:                                  /var/lib/clickhouse/data/default/months/all_1_4_1_6/
hash_of_all_files:                     2d0657a16d9430824d35e327fcbd87bf
hash_of_uncompressed_files:            84950cc30ba867c77a408ae21332ba29
uncompressed_hash_of_compressed_files: 1ad78f1c6843bbfb99a2c931abe7df7d
delete_ttl_info_min:                   1970-01-01 00:00:00
delete_ttl_info_max:                   1970-01-01 00:00:00
move_ttl_info.expression:              []
move_ttl_info.min:                     []
move_ttl_info.max:                     []
```

**See Also**

- [MergeTree family](/docs/engines/table-engines/mergetree-family/mergetree)
- [TTL for Columns and Tables](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl)

Was this page helpful?
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
title_wip

**Example**

```
SELECT * FROM system.parts_columns LIMIT 1 FORMAT Vertical;
```

```
Row 1:
──────
partition:                             tuple()
name:                                  all_1_2_1
part_type:                             Wide
active:                                1
marks:                                 2
rows:                                  2
bytes_on_disk:                         155
data_compressed_bytes:                 56
data_uncompressed_bytes:               4
marks_bytes:                           96
modification_time:                     2020-09-23 10:13:36
remove_time:                           2106-02-07 06:28:15
refcount:                              1
min_date:                              1970-01-01
max_date:                              1970-01-01
partition_id:                          all
min_block_number:                      1
max_block_number:                      2
level:                                 1
data_version:                          1
primary_key_bytes_in_memory:           2
primary_key_bytes_in_memory_allocated: 64
database:                              default
table:                                 53r93yleapyears
engine:                                MergeTree
disk_name:                             default
path:                                  /var/lib/clickhouse/data/default/53r93yleapyears/all_1_2_1/
column:                                id
type:                                  Int8
column_position:                       1
default_kind:
default_expression:
column_bytes_on_disk:                  76
column_data_compressed_bytes:          28
column_data_uncompressed_bytes:        2
column_marks_bytes:                    48
```

**See Also**

- [MergeTree family](/docs/engines/table-engines/mergetree-family/mergetree)
- [Calculating the number and size of compact and wide parts](/docs/knowledgebase/count-parts-by-type)

Was this page helpful?
system.primes

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

This table contains a single UInt64 column named `prime` that contains prime numbers in ascending order, starting from 2.

You can use this table for tests, or if you need to do a brute force search over prime numbers.

Reads from this table are not parallelized.

This is similar to the [`primes`](/docs/sql-reference/table-functions/primes) table function.

You can also limit the output by predicates.

**Example**

The first 10 primes.

```
SELECT * FROM system.primes LIMIT 10;
```

```
  ┌─prime─┐
  │     2 │
  │     3 │
  │     5 │
  │     7 │
  │    11 │
  │    13 │
  │    17 │
  │    19 │
  │    23 │
  │    29 │
  └───────┘
```

The first prime greater than 1e15.

```
SELECT prime FROM system.primes WHERE prime > 1e15 LIMIT 1;
```

```
  ┌────────────prime─┐
  │ 1000000000000037 │ -- 1.00 quadrillion
  └──────────────────┘
```

Was this page helpful?
title_wip

```
SELECT * FROM system.processes LIMIT 10 FORMAT Vertical;
```

```
Row 1:
──────
is_initial_query:     1
user:                 default
query_id:             35a360fa-3743-441d-8e1f-228c938268da
address:              ::ffff:172.23.0.1
port:                 47588
initial_user:         default
initial_query_id:     35a360fa-3743-441d-8e1f-228c938268da
initial_address:      ::ffff:172.23.0.1
initial_port:         47588
interface:            1
os_user:              bharatnc
client_hostname:      tower
client_name:          ClickHouse
client_revision:      54437
client_version_major: 20
client_version_minor: 7
client_version_patch: 2
http_method:          0
http_user_agent:
quota_key:
elapsed:              0.000582537
is_cancelled:         0
is_all_data_sent:     0
read_rows:            0
read_bytes:           0
total_rows_approx:    0
written_rows:         0
written_bytes:        0
memory_usage:         0
peak_memory_usage:    0
query:                SELECT * from system.processes LIMIT 10 FORMAT Vertical;
thread_ids:           [67]
ProfileEvents:        {'Query':1,'SelectQuery':1,'ReadCompressedBytes':36,'CompressedReadBufferBlocks':1,'CompressedReadBufferBytes':10,'IOBufferAllocs':1,'IOBufferAllocBytes':89,'ContextLock':15,'RWLockAcquiredReadLocks':1}
Settings:             {'background_pool_size':'32','load_balancing':'random','allow_suspicious_low_cardinality_types':'1','distributed_aggregation_memory_efficient':'1','skip_unavailable_shards':'1','log_queries':'1','max_bytes_before_external_group_by':'20000000000','max_bytes_before_external_sort':'20000000000','allow_introspection_functions':'1'}

1 rows in set. Elapsed: 0.002 sec.
```

Was this page helpful?
title_wip

- `group_by_ttl_info.max` ([Array(DateTime)](/docs/sql-reference/data-types)) — The maximum value of the calculated TTL expression within this part. Used to understand whether we have all rows with expired TTL.
- `rows_where_ttl_info.expression` ([Array(String)](/docs/sql-reference/data-types)) — The TTL expression.
- `rows_where_ttl_info.min` ([Array(DateTime)](/docs/sql-reference/data-types)) — The minimum value of the calculated TTL expression within this part. Used to understand whether we have at least one row with expired TTL.
- `rows_where_ttl_info.max` ([Array(DateTime)](/docs/sql-reference/data-types)) — The maximum value of the calculated TTL expression within this part. Used to understand whether we have all rows with expired TTL.
- `is_broken` ([UInt8](/docs/sql-reference/data-types)) — Whether projection part is broken
- `exception_code` ([Int32](/docs/sql-reference/data-types)) — Exception message explaining broken state of the projection part
- `exception` ([String](/docs/sql-reference/data-types)) — Exception code explaining broken state of the projection part
title_wip

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

Contains information about existing projections in all tables.

Columns:

- `database` ([String](/docs/sql-reference/data-types)) — Database name.
- `table` ([String](/docs/sql-reference/data-types)) — Table name.
- `name` ([String](/docs/sql-reference/data-types)) — Projection name.
- `type` ([Enum8('Normal' = 0, 'Aggregate' = 1)](/docs/sql-reference/data-types)) — Projection type.
- `sorting_key` ([Array(String)](/docs/sql-reference/data-types)) — Projection sorting key.
- `query` ([String](/docs/sql-reference/data-types)) — Projection query.
- `settings` ([Map(String, String)](/docs/sql-reference/data-types)) — Projection settings.

**Example**

```
SELECT * FROM system.projections LIMIT 2 FORMAT Vertical;
```

```
Row 1:
──────
database:    default
table:       landing
name:        improved_sorting_key
type:        Normal
sorting_key: ['user_id','date']
query:       SELECT * ORDER BY user_id, date
settings:     {}

Row 2:
──────
database:    default
table:       landing
name:        agg_no_key
type:        Aggregate
sorting_key: []
query:       SELECT count()
settings:     {}
```

Was this page helpful?
title_wip

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

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the `clusterAllReplicas` function. See [here](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Shows the content of the [query cache](/docs/operations/query-cache).

Columns:

- `query` ([String](/docs/sql-reference/data-types)) — Query string.
- `query_id` ([String](/docs/sql-reference/data-types)) — ID of the query.
- `result_size` ([UInt64](/docs/sql-reference/data-types)) — Size of the query cache entry.
- `tag` ([LowCardinality(String)](/docs/sql-reference/data-types)) — Tag of the query cache entry.
- `stale` ([UInt8](/docs/sql-reference/data-types)) — If the query cache entry is stale.
- `shared` ([UInt8](/docs/sql-reference/data-types)) — If the query cache entry is shared between multiple users.
- `compressed` ([UInt8](/docs/sql-reference/data-types)) — If the query cache entry is compressed.
- `expires_at` ([DateTime](/docs/sql-reference/data-types)) — When the query cache entry becomes stale.
- `key_hash` ([UInt64](/docs/sql-reference/data-types)) — A hash of the query string, used as a key to find query cache entries.

**Example**

```
SELECT * FROM system.query_cache FORMAT Vertical;
```

```
Row 1:
──────
query:       SELECT 1 SETTINGS use_query_cache = 1
query_id:    7c28bbbb-753b-4eba-98b1-efcbe2b9bdf6
result_size: 128
tag:
stale:       0
shared:      0
compressed:  1
expires_at:  2023-10-13 13:35:45
key_hash:    12188185624808016954

1 row in set. Elapsed: 0.004 sec.
```

Was this page helpful?
title_wip

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

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the `clusterAllReplicas` function. See [here](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Shows the content of the [query condition cache](/docs/operations/query-condition-cache).

Columns:

- `key_hash` ([UInt128](/docs/sql-reference/data-types)) — Hash of (table\_uuid, part\_name, condition\_hash).
- `entry_size` ([UInt64](/docs/sql-reference/data-types)) — The size of the entry in bytes.
- `matching_marks` ([String](/docs/sql-reference/data-types)) — Matching marks.

**Example**

```
SELECT * FROM system.query_condition_cache FORMAT Vertical;
```

```
Row 1:
──────
table_uuid:     28270a24-ea27-49f6-99cd-97b9bee976ac
part_name:      all_1_1_0
condition:      or(equals(b, 10000_UInt16), equals(c, 10000_UInt16))
condition_hash: 5456494897146899690 -- 5.46 quintillion
entry_size:     40
matching_marks: 111111110000000000000000000000000000000000000000000000000111111110000000000000000

1 row in set. Elapsed: 0.004 sec.
```

Was this page helpful?
title_wip

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

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the `clusterAllReplicas` function. See [here](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Contains a history of memory and metric values from table `system.events` for individual queries, periodically flushed to disk.

Once a query starts, data is collected at periodic intervals of `query_metric_log_interval` milliseconds (which is set to 1000
by default). The data is also collected when the query finishes if the query takes longer than `query_metric_log_interval`.

Columns:

- `query_id` ([String](/docs/sql-reference/data-types/string)) — ID of the query.
- `hostname` ([LowCardinality(String)](/docs/sql-reference/data-types/string)) — Hostname of the server executing the query.
- `event_date` ([Date](/docs/sql-reference/data-types/date)) — Event date.
- `event_time` ([DateTime](/docs/sql-reference/data-types/datetime)) — Event time.
- `event_time_microseconds` ([DateTime64](/docs/sql-reference/data-types/datetime64)) — Event time with microseconds resolution.

**Example**

```
SELECT * FROM system.query_metric_log LIMIT 1 FORMAT Vertical;
```

```
Row 1:
──────
query_id:                                                        97c8ba04-b6d4-4bd7-b13e-6201c5c6e49d
hostname:                                                        clickhouse.eu-central1.internal
event_date:                                                      2020-09-05
event_time:                                                      2020-09-05 16:22:33
event_time_microseconds:                                         2020-09-05 16:22:33.196807
memory_usage:                                                    313434219
peak_memory_usage:                                               598951986
ProfileEvent_Query:                                              0
ProfileEvent_SelectQuery:                                        0
ProfileEvent_InsertQuery:                                        0
ProfileEvent_FailedQuery:                                        0
ProfileEvent_FailedSelectQuery:                                  0
...
```

**See also**

- [query\_metric\_log setting](/docs/operations/server-configuration-parameters/settings#query_metric_log) — Enabling and disabling the setting.
- [query\_metric\_log\_interval](/docs/operations/settings/settings#query_metric_log_interval)
- [system.asynchronous\_metrics](/docs/operations/system-tables/asynchronous_metrics) — Contains periodically calculated metrics.
- [system.events](/docs/operations/system-tables/events) — Contains a number of events that occurred.
- [system.metrics](/docs/operations/system-tables/metrics) — Contains instantly calculated metrics.
- [Monitoring](/docs/operations/monitoring) — Base concepts of ClickHouse monitoring.

Was this page helpful?
title_wip

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

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

Example:

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

```
Row 1:
──────
name:         io_read
read_disks:   ['s3']
write_disks:  []
create_query: CREATE RESOURCE io_read (READ DISK s3)

Row 2:
──────
name:         io_write
read_disks:   []
write_disks:  ['s3']
create_query: CREATE RESOURCE io_write (WRITE DISK s3)
```

Columns:

- `name` ([String](/docs/sql-reference/data-types)) — The name of the resource.
- `read_disks` ([Array(String)](/docs/sql-reference/data-types)) — The list of disk names that uses this resource for read operations.
- `write_disks` ([Array(String)](/docs/sql-reference/data-types)) — The list of disk names that uses this resource for write operations.
- `unit` ([String](/docs/sql-reference/data-types)) — Resource unit used for cost measurements.
- `create_query` ([String](/docs/sql-reference/data-types)) — CREATE query of the resource.

Was this page helpful?
title_wip

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

Contains the role grants for users and roles. To add entries to this table, use `GRANT role TO user`.

Columns:

- `user_name` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/string))) — User name.
- `role_name` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/string))) — Role name.
- `granted_role_name` ([String](/docs/sql-reference/data-types/string)) — Name of role granted to the `role_name` role. To grant one role to another one use `GRANT role1 TO role2`.
- `granted_role_is_default` ([UInt8](/docs/sql-reference/data-types/int-uint#integer-ranges)) — Flag that shows whether `granted_role` is a default role. Possible values:

  - 1 — `granted_role` is a default role.
  - 0 — `granted_role` is not a default role.
- `with_admin_option` ([UInt8](/docs/sql-reference/data-types/int-uint#integer-ranges)) — Flag that shows whether `granted_role` is a role with [ADMIN OPTION](/docs/sql-reference/statements/grant#admin-option) privilege. Possible values:

  - 1 — The role has `ADMIN OPTION` privilege.
  - 0 — The role without `ADMIN OPTION` privilege.

Was this page helpful?
title_wip

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

Contains information about configured [roles](/docs/operations/access-rights#role-management).

Columns:

- `name` ([String](/docs/sql-reference/data-types)) — Role name.
- `id` ([UUID](/docs/sql-reference/data-types)) — Role ID.
- `storage` ([String](/docs/sql-reference/data-types)) — Path to the storage of roles. Configured in the `access_control_path` parameter.

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

- [SHOW ROLES](/docs/sql-reference/statements/show#show-roles)

- [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/row_policies.md)

Contains filters for one particular table, as well as a list of roles and/or users which should use this row policy.

Columns:

- `name` ([String](/docs/sql-reference/data-types)) — Name of a row policy.
- `short_name` ([String](/docs/sql-reference/data-types)) — Short name of a row policy. Names of row policies are compound, for example: myfilter ON mydb.mytable. Here 'myfilter ON mydb.mytable' is the name of the row policy, 'myfilter' is it's short name.
- `database` ([String](/docs/sql-reference/data-types)) — Database name.
- `table` ([String](/docs/sql-reference/data-types)) — Table name. Empty if policy for database.
- `id` ([UUID](/docs/sql-reference/data-types)) — Row policy ID.
- `storage` ([String](/docs/sql-reference/data-types)) — Name of the directory where the row policy is stored.
- `select_filter` ([Nullable(String)](/docs/sql-reference/data-types)) — Expression which is used for filtering in SELECT queries.
- `is_restrictive` ([UInt8](/docs/sql-reference/data-types)) — Shows whether the row policy restricts access to rows. Value: • 0 — The row policy is defined with `AS PERMISSIVE` clause, • 1 — The row policy is defined with AS RESTRICTIVE clause.
- `apply_to_all` ([UInt8](/docs/sql-reference/data-types)) — Shows that the row policies set for all roles and/or users.
- `apply_to_list` ([Array(String)](/docs/sql-reference/data-types)) — List of the roles and/or users to which the row policies is applied.
- `apply_to_except` ([Array(String)](/docs/sql-reference/data-types)) — The row policies is applied to all roles and/or users excepting of the listed ones.

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

- [SHOW POLICIES](/docs/sql-reference/statements/show#show-policies)

- [See Also](#see-also)

Was this page helpful?