E-MapReduce:System tables

system.clusters

Stores information about the clusters defined in the configuration file and the servers in each cluster.

Example: list all replicas in a cluster

SELECT * FROM system.clusters LIMIT 2 FORMAT Vertical;

Output:

Row 1:
──────
cluster:                 cluster_emr
shard_num:               1
shard_weight:            1
replica_num:             1
host_name:               emr-header-1.cluster-24****
host_address:            192.168.**.**
port:                    9000
is_local:                1
user:                    default
default_database:
errors_count:            0
estimated_recovery_time: 0

Row 2:
──────
cluster:                 cluster_emr
shard_num:               1
shard_weight:            1
replica_num:             2
host_name:               emr-worker-1.cluster-24****
host_address:            192.168.**.**
port:                    9000
is_local:                0
user:                    default
default_database:
errors_count:            0
estimated_recovery_time: 0

system.query_log

Stores metadata for executed queries, including start time, duration, error messages, and resource usage. It does not store query results.

Each query creates one or two rows depending on the outcome:

• Successful query: two rows with type = QueryStart and QueryFinish

• Error during execution: two rows with type = QueryStart and ExceptionWhileProcessing

• Error before execution: one row with type = ExceptionBeforeStart

The table records two query categories:

• Initial queries: sent directly by a client

• Subqueries: initiated by another query for distributed execution; parent query details appear in the initial_* columns

Example: find slow queries

SELECT
    query_id,
    query_duration_ms,
    read_rows,
    memory_usage,
    substring(query, 1, 100) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10
FORMAT Vertical;

Example: find failed queries

SELECT
    type,
    query_start_time,
    exception_code,
    exception,
    substring(query, 1, 100) AS query_preview
FROM system.query_log
WHERE type IN ('ExceptionBeforeStart', 'ExceptionWhileProcessing')
ORDER BY query_start_time DESC
LIMIT 10;

Example: check a single log entry

SELECT * FROM system.query_log LIMIT 1 FORMAT Vertical;

Output:

Row 1:
──────
type:                 QueryStart
event_date:           2021-08-12
event_time:           2021-08-12 14:11:58
query_start_time:     2021-08-12 14:11:58
query_duration_ms:    0
read_rows:            0
read_bytes:           0
written_rows:         0
written_bytes:        0
result_rows:          0
result_bytes:         0
memory_usage:         0
current_database:     default
query:                SELECT * FROM system.query_log LIMIT 1 FORMAT Vertical;
exception_code:       0
exception:
stack_trace:
is_initial_query:     1
user:                 default
query_id:             08e1336c-696f-4fad-b01b-255b77e56b1f
address:              ::ffff:127.0.0.1
port:                 60500
initial_user:         default
initial_query_id:     08e1336c-696f-4fad-b01b-255b77e56b1f
initial_address:      ::ffff:127.0.0.1
initial_port:         60500
interface:            1
os_user:              root
client_hostname:      emr-header-1.cluster-235053
client_name:          ClickHouse
client_revision:      54438
client_version_major: 20
client_version_minor: 8
client_version_patch: 12
http_method:          0
http_user_agent:
quota_key:
revision:             54438
thread_ids:           []
ProfileEvents.Names:  []
ProfileEvents.Values: []
Settings.Names:       ['use_uncompressed_cache','load_balancing','max_memory_usage']
Settings.Values:      ['0','random','10000000000']

system.zookeeper

Stores information about nodes in ZooKeeper. This table exists only when ZooKeeper is configured.

Every query against system.zookeeper must include a WHERE clause with either a path = or path IN condition, corresponding to the ZooKeeper path whose child nodes you want to retrieve. If the specified path does not exist, an exception is thrown.

Example queries

Retrieve all child nodes under a specific path:

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

Retrieve child nodes under multiple paths in a single query:

SELECT *
FROM system.zookeeper
WHERE path IN ('/', '/clickhouse');

Output for the first query:

Row 1:
──────
name:           example01-08-1.yandex.ru
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.yandex.ru
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

system.replicas

Stores status information for all replicated tables on the local server. Use this table to monitor replica health.

Only one replica can hold the leader role at a time. The leader schedules background merge operations. Write operations can run on any replica that is available and has an active ZooKeeper session, regardless of whether it is the leader.

A replica enters read-only mode in any of these situations:

• The configuration has no ZooKeeper session defined

• An unknown error occurs when reinitializing a ZooKeeper session

• ZooKeeper is reinitialized mid-session

Example: check replica status for a table

SELECT * FROM system.replicas WHERE table = 'visits' FORMAT Vertical;

Example: identify lagging replicas

SELECT
    database,
    table,
    replica_name,
    absolute_delay,
    queue_size,
    inserts_in_queue,
    merges_in_queue
FROM system.replicas
WHERE absolute_delay > 0
ORDER BY absolute_delay DESC;

Output for the first query:

Row 1:
──────
database:                   cltest
table:                      flat_xl_customer_local
engine:                     ReplicatedMergeTree
is_leader:                  1
can_become_leader:          1
is_readonly:                0
is_session_expired:         0
future_parts:               0
parts_to_check:             0
zookeeper_path:             /cltest/cluster_emr-1/flat_xl_customer_local
replica_name:               emr-header-1.cluster-235053
replica_path:               /cltest/cluster_emr-1/flat_xl_customer_local/replicas/emr-header-1.cluster-235053
columns_version:            -1
queue_size:                 0
inserts_in_queue:           0
merges_in_queue:            0
part_mutations_in_queue:    0
queue_oldest_time:          1970-01-01 08:00:00
inserts_oldest_time:        1970-01-01 08:00:00
merges_oldest_time:         1970-01-01 08:00:00
part_mutations_oldest_time: 1970-01-01 08:00:00
oldest_part_to_get:
oldest_part_to_merge_to:
oldest_part_to_mutate_to:
log_max_index:              100157
log_pointer:                100158
last_queue_update:          1970-01-01 08:00:00
absolute_delay:             0
total_replicas:             2
active_replicas:            2
zookeeper_exception:

system.storage_policies

Stores the storage policies and volume priorities defined in the server configuration file.

Example: list all storage policies

SELECT * FROM system.storage_policies;

Output:

┌policy_name─┬─volume_name─┬─volume_priority─┬─disks────────────┬─volume_type─┬─max_data_part_size┬─move_factor─┐
│ default      │ default     │               1 │ ['default']                       │ JBOD        │                  0 │           0 │
│ hdd_in_order │ single      │               1 │ ['disk1','disk2','disk3','disk4'] │ JBOD        │                  0 │         0.1 │
└───────┴──────┴─────────┴─────────────────┴───────┴─────────┴─────────┘

system.disks

Stores information about the disks defined in the server configuration file.

Example: check disk space across all configured disks

SELECT
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    round(free_space / total_space * 100, 1) AS free_pct
FROM system.disks;

Example: list raw disk information

SELECT * FROM system.disks;

Output:

┌─name─┬─path─────────┬──free_space┬─total_space┬─keep_free_space┬─type──┐
│ default │ /var/lib/clickhouse/   │ 17236594688 │ 84014424064 │               0 │ local │
│ disk1   │ /mnt/disk1/clickhouse/ │ 17226108928 │ 84003938304 │        10485760 │ local │
│ disk2   │ /mnt/disk2/clickhouse/ │ 28623364096 │ 84003938304 │        10485760 │ local │
│ disk3   │ /mnt/disk3/clickhouse/ │ 34770505728 │ 84003938304 │        10485760 │ local │
│ disk4   │ /mnt/disk4/clickhouse/ │ 59107045376 │ 84003938304 │        10485760 │ local │
└────┴─────────────┴───────┴──────┴────────┴─────┘