system.query_thread_log

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the clusterAllReplicas function. See here for further details.

Description

Contains information about threads that execute queries, for example, thread name, thread start time, duration of query processing.

To start logging:

1. Configure parameters in the query_thread_log section.
2. Set log_query_threads to 1.

The flushing period of data is set in flush_interval_milliseconds parameter of the query_thread_log server settings section. To force flushing, use the SYSTEM FLUSH LOGS query.

ClickHouse does not delete data from the table automatically. See Introduction for more details.

You can use the log_queries_probability) setting to reduce the number of queries, registered in the query_thread_log table.

Columns

• hostname (LowCardinality(String)) — Hostname of the server executing the query.
• event_date (Date) — The date when the thread has finished execution of the query.
• event_time (DateTime) — The date and time when the thread has finished execution of the query.
• event_time_microseconds (DateTime64(6)) — The date and time when the thread has finished execution of the query with microseconds precision.
• query_start_time (DateTime) — Start time of query execution.
• query_start_time_microseconds (DateTime64(6)) — Start time of query execution with microsecond precision.
• query_duration_ms (UInt64) — Duration of query execution.
• read_rows (UInt64) — Number of read rows.
• read_bytes (UInt64) — Number of read bytes.
• written_rows (UInt64) — For INSERT queries, the number of written rows. For other queries, the column value is 0.
• written_bytes (UInt64) — For INSERT queries, the number of written bytes. For other queries, the column value is 0.
• memory_usage (Int64) — The difference between the amount of allocated and freed memory in context of this thread.
• peak_memory_usage (Int64) — The maximum difference between the amount of allocated and freed memory in context of this thread.
• thread_name (LowCardinality(String)) — Name of the thread.
• thread_id (UInt64) — Internal thread ID.
• master_thread_id (UInt64) — OS initial ID of initial thread.
• current_database (LowCardinality(String)) — Name of the current database.
• query (String) — Query string.
• normalized_query_hash (UInt64) — The hash of normalized query - with wiped constants, etc.
• is_initial_query (UInt8) — Query type. Possible values: 1 — Query was initiated by the client, 0 — Query was initiated by another query for distributed query execution.
• connection_address (IPv6) — The client IP address from which the connection was made. When connected through a proxy, this will be the address of the proxy.
• connection_port (UInt16) — The client port from which the connection was made. When connected through a proxy, this will be the port of the proxy.
• user (LowCardinality(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. When connected through a proxy and auth_use_forwarded_address is set, this will be the address of the client instead of the proxy.
• port (UInt16) — The client port that was used to make the query. When connected through a proxy and auth_use_forwarded_address is set, this will be the port of the client instead of the proxy.
• initial_user (LowCardinality(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.
• initial_query_start_time (DateTime) — Start time of the initial query execution.
• initial_query_start_time_microseconds (DateTime64(6)) — Start time of the initial query execution
• authenticated_user (LowCardinality(String)) — Name of the user who was authenticated in the session.
• interface (UInt8) — Interface that the query was initiated from. Possible values: 1 — TCP, 2 — HTTP.
• is_secure (UInt8) — The flag which shows whether the connection was secure.
• os_user (LowCardinality(String)) — OSs username who runs clickhouse-client.
• client_hostname (LowCardinality(String)) — Hostname of the client machine where the clickhouse-client or another TCP client is run.
• client_name (LowCardinality(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.
• script_query_number (UInt32) — A sequential query number in a multi-query script.
• script_line_number (UInt32) — A line number in a multi-query script where the current query starts.
• http_method (UInt8) — HTTP method that initiated the query. Possible values: 0 — The query was launched from the TCP interface, 1 — GET method was used., 2 — POST method was used.
• http_user_agent (LowCardinality(String)) — The UserAgent header passed in the HTTP request.
• http_referer (String) — HTTP header Referer passed in the HTTP query (contains an absolute or partial address of the page making the query).
• forwarded_for (String) — HTTP header X-Forwarded-For passed in the HTTP query.
• quota_key (String) — The 'quota key' specified in the quotas setting.
• distributed_depth (UInt64) — How many times a query was forwarded between servers.
• revision (UInt32) — ClickHouse revision.
• ProfileEvents (Map(LowCardinality(String), UInt64)) — ProfileEvents that measure different metrics for this thread. The description of them could be found in the table system.events.

Aliases:

• ProfileEvents.Names — Alias for mapKeys(ProfileEvents).
• ProfileEvents.Values — Alias for mapValues(ProfileEvents).

Example

 SELECT * FROM system.query_thread_log LIMIT 1 \G

Row 1:
──────
hostname:                      clickhouse.eu-central1.internal
event_date:                    2020-09-11
event_time:                    2020-09-11 10:08:17
event_time_microseconds:       2020-09-11 10:08:17.134042
query_start_time:              2020-09-11 10:08:17
query_start_time_microseconds: 2020-09-11 10:08:17.063150
query_duration_ms:             70
read_rows:                     0
read_bytes:                    0
written_rows:                  1
written_bytes:                 12
memory_usage:                  4300844
peak_memory_usage:             4300844
thread_name:                   TCPHandler
thread_id:                     638133
master_thread_id:              638133
query:                         INSERT INTO test1 VALUES
is_initial_query:              1
user:                          default
query_id:                      50a320fd-85a8-49b8-8761-98a86bcbacef
address:                       ::ffff:127.0.0.1
port:                          33452
initial_user:                  default
initial_query_id:              50a320fd-85a8-49b8-8761-98a86bcbacef
initial_address:               ::ffff:127.0.0.1
initial_port:                  33452
interface:                     1
os_user:                       bharatnc
client_hostname:               tower
client_name:                   ClickHouse
client_revision:               54437
client_version_major:          20
client_version_minor:          7
client_version_patch:          2
http_method:                   0
http_user_agent:
quota_key:
revision:                      54440
ProfileEvents:        {'Query':1,'SelectQuery':1,'ReadCompressedBytes':36,'CompressedReadBufferBlocks':1,'CompressedReadBufferBytes':10,'IOBufferAllocs':1,'IOBufferAllocBytes':89,'ContextLock':15,'RWLockAcquiredReadLocks':1}

See Also

• system.query_log — Description of the query_log system table which contains common information about queries execution.
• system.query_views_log — This table contains information about each view executed during a query.