Overview of System Tables In ClickHouse

Introduction

ClickHouse is an open-source columnar database management system designed for handling large volumes of data. It is known for its high performance, scalability, and flexibility. One of the key features of ClickHouse is its system tables, which are tables that contain metadata about the database schema, configuration, and usage. In this article, we will dive deeper into the ClickHouse system tables and discuss their importance and usage.

What are System Tables in ClickHouse?

System tables are special tables that exist in every ClickHouse database and contain metadata information about the database schema, configuration, and usage. They are created automatically by ClickHouse and are used by administrators and developers to manage the database and to optimize queries.

---

Note: System tables are hidden by default.

System tables are stored in the ‘system’ database, which is hidden by default and cannot be accessed directly by users.

To enable that please follow official ClickHouse doc from here.

---

Why are ClickHouse System Tables Important?

System tables play a crucial role in the administration and development of ClickHouse databases. They provide information about the database schema, configuration, and usage that can be used for the following purposes:

• Database management: System tables can be used to manage database objects such as tables, columns, indexes, and functions. They provide information about the database schema and can be used to create, modify, and drop database objects.
• Query optimization: System tables contain statistics about the distribution of data in tables, which can be used to optimize queries. For example, the system.parts table provides information about the distribution of data in individual shards of a table, which can be used to optimize queries that access specific shards.
• System monitoring: System tables can be used to monitor the performance and usage of the database. They provide information about the memory usage, CPU usage, disk usage, and query throughput of the database.

Types of System Tables in ClickHouse

ClickHouse provides several system tables that are used for different purposes. Here are some of the most important system tables:

1. system.tables: This table contains information about all the tables in the database, including their names, engine types, and creation time.
2. system.columns: This table contains information about the columns in each table, including their names, data types, and default values.
3. system.databases: This table contains information about all the databases in the system, including their names, engine types, and creation time.
4. system.functions: This table contains information about all the functions available in the system, including their names, parameters, and return types.
5. system.parts: This table contains information about the individual shards of each table, including their location, size, and partitioning key.
6. system.settings: This table contains information about all the settings available in the system, including their names, data types, and default values.

ClickHouse System Table Usage Examples

Here are some examples of how system tables can be used in ClickHouse:

1. Get a list of all the tables in the database:

SELECT name, engine
FROM system.tables

2. Get a list of all the columns in a specific table:

SELECT name, type
FROM system.columns
WHERE table_name = 'my_table'

3. Get the location and size of a specific shard:

SELECT partition_id, path, size
FROM system.parts
WHERE table_name = 'my_table' AND partition_id = 1

4. Get the value of a specific setting:

SELECT value
FROM system.settings
WHERE name = 'max_threads'

List Of All System Tables in ClickHouse

system.asynchronous_metric_log
system.asynchronous_metrics
system.clusters
system.columns
system.contributors
system.crash_log
system.current_roles
system.data_skipping_indices
system.data_type_families
system.databases
system.detached_parts
system.dictionaries
system.disks
system.distributed_ddl_queue
system.distribution_queue
system.enabled_roles
system.errors
system.events
system.functions
system.grants
system.graphite_retentions
system.licenses
system.merge_tree_settings
system.merges
system.metric_log
system.metrics
system.mutations
system.numbers
system.numbers_mt
system.one
system.opentelemetry_span_log
system.part_log
system.parts
system.parts_columns
system.processes
system.query_log
system.query_thread_log
system.query_views_log
system.quota_limits
system.quota_usage
system.quotas
system.quotas_usage
system.replicas
system.replicated_fetches
system.replication_queue
system.role_grants
system.roles
system.row_policies
system.settings
system.settings_profile_elements
system.settings_profiles
system.stack_trace
system.storage_policies
system.table_engines
system.tables
system.text_log
system.time_zones
system.trace_log
system.users
system.zookeeper
system.zookeeper_log

Conclusion

System tables are a crucial feature of ClickHouse that provide metadata information about the database schema, configuration, and usage. They are used for database management, query optimization, and system monitoring. By using system tables, administrators and developers can optimize the performance of their ClickHouse databases and improve their overall efficiency.

To read more ClickHouse internals, do consider reading the below articles

• ClickHouse Data Types: LowCardinality for Query Performance Acceleration
• ClickHouse Performance: Implementing Bloom Filters in ClickHouse
• ClickHouse Proxy Server for Read-Write Splitting with Connection Pooling & Query Cache
• Introduction to Working Datasets in ClickHouse