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?
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 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
ClickHouse provides several system tables that are used for different purposes. Here are some of the most important system tables:
- system.tables: This table contains information about all the tables in the database, including their names, engine types, and creation time.
- system.columns: This table contains information about the columns in each table, including their names, data types, and default values.
- system.databases: This table contains information about all the databases in the system, including their names, engine types, and creation time.
- system.functions: This table contains information about all the functions available in the system, including their names, parameters, and return types.
- system.parts: This table contains information about the individual shards of each table, including their location, size, and partitioning key.
- system.settings: This table contains information about all the settings available in the system, including their names, data types, and default values.
Here are some examples of how system tables can be used in ClickHouse:
- Get a list of all the tables in the database:
SELECT name, engine FROM system.tables
- Get a list of all the columns in a specific table:
SELECT name, type FROM system.columns WHERE table_name = 'my_table'
- 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
- Get the value of a specific setting:
SELECT value FROM system.settings WHERE name = 'max_threads'
List Of All System Tables
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
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.