Contains information about executed queries, for example, start time, duration of processing, error messages.
This table doesn’t contain the ingested data for
You can change settings of queries logging in the query_log section of the server configuration.
You can disable queries logging by setting log_queries = 0. We don’t recommend to turn off logging because information in this table is important for solving issues.
ClickHouse doesn’t delete data from the table automatically. See Introduction for more details.
system.query_log table registers two kinds of queries:
- Initial queries that were run directly by the client.
- Child queries that were initiated by other queries (for distributed query execution). For these types of queries, information about the parent queries is shown in the
Each query creates one or two rows in the
query_log table, depending on the status (see the
type column) of the query:
- If the query execution was successful, two rows with the
QueryFinishtypes are created .
- If an error occurred during query processing, two events with the
ExceptionWhileProcessingtypes are created .
- If an error occurred before launching the query, a single event with the
ExceptionBeforeStarttype is created.
type(Enum8) — Type of an event that occurred when executing the query. Values:
'QueryStart' = 1— Successful start of query execution.
'QueryFinish' = 2— Successful end of query execution.
'ExceptionBeforeStart' = 3— Exception before the start of query execution.
'ExceptionWhileProcessing' = 4— Exception during the query execution.
event_date(Date) — Query starting date.
event_time(DateTime) — Query starting time.
query_start_time(DateTime) — Start time of query execution.
query_start_time_microseconds(DateTime64) — Start time of query execution with microsecond precision.
query_duration_ms(UInt64) — Duration of query execution in milliseconds.
read_rows(UInt64) — Total number or rows read from all tables and table functions participated in query. It includes usual subqueries, subqueries for
JOIN. For distributed queries
read_rowsincludes the total number of rows read at all replicas. Each replica sends it’s
read_rowsvalue, and the server-initiator of the query summarize all received and local values. The cache volumes doesn’t affect this value.
read_bytes(UInt64) — Total number or bytes read from all tables and table functions participated in query. It includes usual subqueries, subqueries for
JOIN. For distributed queries
read_bytesincludes the total number of rows read at all replicas. Each replica sends it’s
read_bytesvalue, and the server-initiator of the query summarize all received and local values. The cache volumes doesn’t affect this value.
written_rows(UInt64) — For
INSERTqueries, the number of written rows. For other queries, the column value is 0.
written_bytes(UInt64) — For
INSERTqueries, the number of written bytes. For other queries, the column value is 0.
result_rows(UInt64) — Number of rows in a result of the
SELECTquery, or a number of rows in the
result_bytes(UInt64) — RAM volume in bytes used to store a query result.
memory_usage(UInt64) — Memory consumption by the query.
query(String) — Query string.
exception(String) — Exception message.
exception_code(Int32) — Code of an exception.
stack_trace(String) — Stack trace. An empty string, if the query was completed successfully.
is_initial_query(UInt8) — Query type. Possible values:
- 1 — Query was initiated by the client.
- 0 — Query was initiated by another query as part of distributed query execution.
user(String) — Name of the user who initiated the current query.
query_id(String) — ID of the query.
address(IPv6) — IP address that was used to make the query.
port(UInt16) — The client port that was used to make the query.
initial_user(String) — Name of the user who ran the initial query (for distributed query execution).
initial_query_id(String) — ID of the initial query (for distributed query execution).
initial_address(IPv6) — IP address that the parent query was launched from.
initial_port(UInt16) — The client port that was used to make the parent query.
interface(UInt8) — Interface that the query was initiated from. Possible values:
- 1 — TCP.
- 2 — HTTP.
os_user(String) — Operating system username who runs clickhouse-client.
client_hostname(String) — Hostname of the client machine where the clickhouse-client or another TCP client is run.
client_name(String) — The clickhouse-client or another TCP client name.
client_revision(UInt32) — Revision of the clickhouse-client or another TCP client.
client_version_major(UInt32) — Major version of the clickhouse-client or another TCP client.
client_version_minor(UInt32) — Minor version of the clickhouse-client or another TCP client.
client_version_patch(UInt32) — Patch component of the clickhouse-client or another TCP client version.
http_method(UInt8) — HTTP method that initiated the query. Possible values:
- 0 — The query was launched from the TCP interface.
- 1 —
GETmethod was used.
- 2 —
POSTmethod was used.
http_user_agent(String) — The
UserAgentheader passed in the HTTP request.
quota_key(String) — The “quota key” specified in the quotas setting (see
revision(UInt32) — ClickHouse revision.
thread_numbers(Array(UInt32)) — Number of threads that are participating in query execution.
ProfileEvents.Names(Array(String)) — Counters that measure different metrics. The description of them could be found in the table system.events
ProfileEvents.Values(Array(UInt64)) — Values of metrics that are listed in the
Settings.Names(Array(String)) — Names of settings that were changed when the client ran the query. To enable logging changes to settings, set the
log_query_settingsparameter to 1.
Settings.Values(Array(String)) — Values of settings that are listed in the
SELECT * FROM system.query_log LIMIT 1 FORMAT Vertical;
Row 1: ────── type: QueryStart event_date: 2020-05-13 event_time: 2020-05-13 14:02:28 query_start_time: 2020-05-13 14:02:28 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 query: SELECT 1 exception_code: 0 exception: stack_trace: is_initial_query: 1 user: default query_id: 5e834082-6f6d-4e34-b47b-cd1934f4002a address: ::ffff:127.0.0.1 port: 57720 initial_user: default initial_query_id: 5e834082-6f6d-4e34-b47b-cd1934f4002a initial_address: ::ffff:127.0.0.1 initial_port: 57720 interface: 1 os_user: bayonet client_hostname: clickhouse.ru-central1.internal client_name: ClickHouse client client_revision: 54434 client_version_major: 20 client_version_minor: 4 client_version_patch: 1 http_method: 0 http_user_agent: quota_key: revision: 54434 thread_ids:  ProfileEvents.Names:  ProfileEvents.Values:  Settings.Names: ['use_uncompressed_cache','load_balancing','log_queries','max_memory_usage'] Settings.Values: ['0','random','1','10000000000']
- system.query_thread_log — This table contains information about each query execution thread.