System tables are stored in the system database. You can read data from system tables and detach system tables from other types of tables. However, you cannot drop or alter system tables. Most system tables are stored in random access memory (RAM). These system tables are created by a ClickHouse server when the server is started. This topic describes the common system tables in E-MapReduce (EMR).

Background information

system.clusters

This table records information about the clusters that are available in the configuration file and the servers in the clusters.

Parameter Data type Description
cluster String The name of the cluster.
shard_num UInt32 The serial number of the shard in the cluster. The serial number starts from 1.
shard_weight UInt32 The relative weight of the shard when data is written.
replica_num UInt32 The serial number of the replica in the shard. The serial number starts from 1.
host_name String The hostname that is specified in the configuration file.
host_address String The host IP address that is obtained from the Domain Name System (DNS) server.
port UInt16 The port that is used to connect to the host.
user String The username that is used to connect to the host.
errors_count UInt32 The number of times that the host failed to access the replica.
slowdowns_count UInt32 The number of slowdowns that resulted in replica changes when a connection is established by using peer-end requests.
estimated_recovery_time UInt32 The number of seconds that remain before the replica error count becomes zero and is considered normal.
Sample command: 
SELECT * FROM system.clusters LIMIT 2 FORMAT Vertical;
The following output is returned: 
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

This table records information about executed queries such as the start time, processing duration, and error messages.

The system.query_log table records the following two types of queries:
  • Initial queries that are directly run by a client.
  • Subqueries that are initiated by other queries for distributed query execution. For these types of queries, information about the parent queries is shown in the initial_* columns.
Each query creates one or two rows in the query_log table based on the query status that is specified by the type parameter.
  • If the query execution is successful, two events of the QueryStart and QueryFinish types are created.
  • If an error occurs during query processing, two events of the QueryStart and ExceptionWhileProcessing types are created.
  • If an error occurs before the query is initiated, a single event of the ExceptionBeforeStart type is created.
Parameter Data type Description
type Enum8 The type of the event that occurs when the query is executed. Valid values:
  • 'QueryStart' = 1: The query execution is started.
  • 'QueryFinish' = 2: The query execution is complete.
  • 'ExceptionBeforeStart' = 3: An error occurs before the query is initiated.
  • 'ExceptionWhileProcessing' = 4 : An error occurs when the query is being executed.
event_date Date The start date of the event.
event_time DateTime The start time of the event.
event_time_microseconds DateTime64 The start time of the event. The time is accurate to the microsecond.
query_start_time DateTime The start time of the query execution.
query_start_time_microseconds DateTime64 The start time of the query execution. The time is accurate to the microsecond.
query_duration_ms UInt64 The duration of the query execution. Unit: milliseconds.
read_rows UInt64 The total number of rows that are read from all tables and table functions that participated in query. The table query includes common subqueries and subqueries for IN and JOIN. For distributed queries, read_rows includes the total number of rows that are read from all replicas. Each replica sends a read_rows value, and the server initiator of the query summarizes all received and local values. The cache volumes do not affect this value.
read_bytes UInt64 The total amount of data that is read from all tables and table functions that participated in query. Unit: bytes. The table query includes common subqueries and subqueries for IN and JOIN. For distributed queries, read_bytes includes the total amount of data that is read from all replicas. Unit: bytes. Each replica sends a read_bytes value, and the server initiator of the query summarizes all received and local values. The cache volumes do not affect this value.
written_rows UInt64 The number of rows that are written for INSERT queries. For other queries, the value of this parameter is 0.
written_bytes UInt64 The amount of data that is written for INSERT queries. Unit: bytes. For other queries, the value of this parameter is 0.
result_rows UInt64 The number of rows in the result of the SELECT or INSERT query.
result_bytes UInt64 The amount of RAM that is used to store a query result. Unit: bytes.
memory_usage UInt64 The memory that is consumed by the query.
query String The query statement.
exception String The exception message.
exception_code Int32 The exception code.
stack_trace String The stack trace. If the query execution is complete, an empty string is returned.
is_initial_query UInt8 The type of the query. Valid values:
  • 0: The query is initiated by another query as part of distributed query execution.
  • 1: The query is initiated by a client.
user String The name of the user who initiates the query.
query_id String The ID of the query.
address Ipv6 The IP address of the client that initiates the query.
port UInt16 The port number of the client that initiates the query.
initial_user String The name of the user who runs the initial query for distributed query execution.
initial_query_id String The ID of the initial query for distributed query execution.
initial_address Ipv6 The IP address at which the parent query is initiated.
initial_port UInt16 The port number of the client that initiates the parent query.
interface UInt8 The interface over which the query is initiated. Valid values:
  • 1: TCP
  • 2: HTTP
os_user String The username of the operating system on which clickhouse-client runs.
client_hostname String The hostname of the machine on which clickhouse-client or another TCP client runs.
client_name String The name of the clickhouse-client or another TCP client.
client_revision UInt32 The revision of the clickhouse-client or another TCP client.
client_version_major UInt32 The major version of the clickhouse-client or another TCP client.
client_version_minor UInt32 The minor version of the clickhouse-client or another TCP client.
client_version_patch UInt32 The patch version of the clickhouse-client or another TCP client.
http_method UInt8 The HTTP method that is used to initiate the query. Valid values:
  • 0: The query is initiated over the TCP interface.
  • 1: The GET method is used.
  • 2: The POST method is used.
http_user_agent String The HTTP header UserAgent that is passed in the HTTP query.
quota_key String The quota key that is specified in the configuration of quotas.
revision UInt32 The ClickHouse revision.
ProfileEvents Map(String, UInt64) The profile events that measure different metrics. The description of the profile events can be found in the system.events table.
Settings Map(String, String) The settings that are changed when the client runs the query. If you want to record changes of settings, set the log_query_settings parameter to 1.
thread_ids Array(UInt64) The IDs of the threads that participate in query execution.
Settings.Names Array(String) The names of the settings that are changed when the client runs the query. If you want to record changes of settings, set the log_query_settings parameter to 1.
Settings.Values Array(String) The values of the settings that are listed in the Settings.Names column.
Sample command: 
SELECT * FROM system.query_log LIMIT 1 FORMAT Vertical;
The following output is returned: 
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

This table records information about nodes in ZooKeeper.

This table exists only if ZooKeeper is configured. You can read data from a ZooKeeper cluster that is defined in the configuration file. The query that you perform must either contain a path = condition or a path IN condition in a WHERE clause. The condition corresponds to the path of the child nodes in ZooKeeper for which you want to obtain data.

You can execute the query statement SELECT * FROM system.zookeeper WHERE path = '/clickhouse' to obtain data for all child nodes of the /clickhouse node. To obtain data for all root nodes, include path = '/' in the statement. If the path that is specified in path does not exist, an exception occurs.

You can execute the query statement SELECT * FROM system.zookeeper WHERE path IN ('/', '/clickhouse') to obtain data for all child nodes of the / and /clickhouse nodes. If the path that is specified in path does not exist, an exception occurs. The statement can be used to process a batch of ZooKeeper path queries.

Parameter Data type Description
name String The name of the node.
path String The path that stores the node.
value String The value of the node.
dataLength Int32 The length of the node value.
numChildren Int32 The number of child nodes.
czxid Int64 The ID of the transaction that creates the node.
mzxid Int64 The ID of the transaction that last changes the node.
pzxid Int64 The ID of the transaction that last deletes or adds child nodes.
ctime DateTime The point in time at which the node is created.
mtime DateTime The point in time at which the node is last modified.
version Int32 The version of the node. This parameter also specifies the number of times that the node is changed.
cversion Int32 The ID of the transaction that last deletes or adds child nodes.
aversion Int32 The number of changes to the ACL.
ephemeralOwner Int64 The ID of the transaction that owns a temporary node.
Sample command: 
SELECT *
FROM system.zookeeper
WHERE path = '/clickhouse/tables/01-08/visits/replicas'
FORMAT Vertical
The following output is returned: 
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

This table records information about and status of replicated tables that are stored in the local server. This table can be used for monitoring.

Parameter Data type Description
database String The name of the database.
table String The name of the table.
engine String The name of the table engine.
is_leader UInt8 Specifies whether the replica is the leader.
You can specify only one replica as the leader at a time. The leaders are responsible for scheduling background merge operations.
Important Write operations can be performed on a replica that is available and has a session in ZooKeeper, regardless of whether the replica is a leader or not.
can_become_leader UInt8 Specifies whether the replica can be a leader.
is_readonly UInt8 Specifies whether the replica is in read-only mode.
We recommend that you enable the read-only mode for a replica in the following scenarios:
  • The configuration does not contain sessions with ZooKeeper.
  • An unknown error occurs when sessions are reinitialized in ZooKeeper.
  • Zookeeper is reinitialized in a session.
is_session_expired UInt8 Specifies whether the session with ZooKeeper has expired. The usage of this parameter is the same as that of the is_readonly parameter.
future_parts UInt32 The number of data parts that appear because of pending insert or merge operations.
parts_to_check UInt32 The number of data parts in the queue for verification. A data part that is suspected of being damaged is put in the verification queue.
zookeeper_path String The path that stores table data in ZooKeeper.
replica_name String The name of the replica in ZooKeeper. Different replicas of the same table have different names.
replica_path String The path that stores replica data in ZooKeeper. This path contains the same content as zookeeper_path/replicas/replica_path.
columns_version Int32 The version number of the table schema. This parameter specifies the number of ALTER operations that are performed. If replicas have different versions, some of the ALTER operations have not been performed on some replicas.
queue_size UInt32 The size of the queue that is waiting for specific operations. The operations include data block insertion and merge. The value of this parameter is the same as the value of future_parts.
inserts_in_queue UInt32 The number of data blocks that are waiting to be inserted.

In most cases, data blocks are inserted quickly. If the value of this parameter is large, exceptions exist.

merges_in_queue UInt32 The number of data blocks that are waiting to be merged.

In some cases, merges require a long period of time. The value of this parameter may be greater than zero for a long period of time.

part_mutations_in_queue UInt32 The number of pending mutations.
queue_oldest_time DateTime The point in time at which the earliest operation is added to the queue if the value of queue_size is greater than 0.
inserts_oldest_time DateTime
merges_oldest_time DateTime
part_mutations_oldest_time DateTime
log_max_index UInt64 The maximum entry number in the log of a general activity.
Important The parameter has a non-zero value only if an active session with ZooKeeper exists.
log_pointer UInt64 The sum of the maximum entry number in the log of the general activity that the replica copies to its execution queue and one. If the value of the log_pointer parameter is smaller than the value of the log_max_index parameter, exceptions exist.
Important The parameter has a non-zero value only if an active session with ZooKeeper exists.
last_queue_update DateTime The point in time at which the queue is last updated.
Important The parameter has a non-zero value only if an active session with ZooKeeper exists.
absolute_delay UInt64 The maximum delay of the current replica. Unit: seconds.
Important The parameter has a non-zero value only if an active session with ZooKeeper exists.
total_replicas UInt8 The total number of known replicas of the table.
active_replicas UInt8 The number of replicas of this table that has a session in ZooKeeper. This parameter specifies the number of functioning replicas.
Sample command: 
SELECT * FROM system.replicas WHERE table = 'visits' FORMAT Vertical
The following output is returned: 
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

This table records information about the storage policies and volume priorities that are defined in the server configuration file.

Parameter Data type Description
policy_name String The name of the storage policy.
volume_name String The name of the volume that is defined in the storage policy.
volume_priority UInt64 The order number of the volume in the configuration file.
disks String The names of the disks that are defined in the storage policy.
max_data_part_size UInt64 The maximum size of a data part that can be stored on volume disks.
move_factor Float64 The ratio of the free disk space to the total disk space. If the ratio exceeds the value of this parameter, ClickHouse starts to move data to the next volume disk.
Sample command: 
SELECT * FROM system.storage_policies
The following output is returned: 
┌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

This table records information about the disks that are defined in the server configuration file.

Parameter Data type Description
name String The name of the disk in the server configuration file.
path String The path of the disk that is mounted to the file system.
free_space UInt64 The free space on the disk. Unit: bytes.
total_space UInt64 The total space on the disk. Unit: bytes.
keep_free_space UInt64 The amount of disk space that must be reserved. Unit: bytes. The value of this parameter is defined by the keep_free_space_bytes parameter in the disk configuration file.
Sample command: 
SELECT * FROM system.disks;
The following output is returned: 
┌─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 │
└────┴─────────────┴───────┴──────┴────────┴─────┘