system.query_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

Stores metadata and statistics about executed queries, such as start time, duration, error messages, resource usage, and other execution details. It does not store the results of queries.

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 do not recommend to turn off logging because information in this table is important for solving issues.

The flushing period of data is set in flush_interval_milliseconds parameter of the query_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.

The 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 initial_* columns.

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 QueryStart and QueryFinish types are created.
If an error occurred during query processing, two events with the QueryStart and ExceptionWhileProcessing types are created.
If an error occurred before launching the query, a single event with the ExceptionBeforeStart type is created.

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

You can use the log_formatted_queries setting to log formatted queries to the formatted_query column.

Columns

hostname (LowCardinality(String)) — Hostname of the server executing the query.
type (Enum8('QueryStart' = 1, 'QueryFinish' = 2, 'ExceptionBeforeStart' = 3, 'ExceptionWhileProcessing' = 4)) — Type of an event that occurred when executing the query. Values: QueryStart — successful start of query execution, QueryFinish — successful end of query execution, ExceptionBeforeStart — exception before the start of query execution, ExceptionWhileProcessing — exception during the query execution.
event_date (Date) — Query starting date.
event_time (DateTime) — Query starting time.
event_time_microseconds (DateTime64(6)) — Query starting time 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 in milliseconds.
read_rows (UInt64) — Total number of rows read from all tables and table functions participated in query. It includes usual subqueries, subqueries for IN and JOIN. For distributed queries read_rows includes the total number of rows read at all replicas. Each replica sends it's 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) — Total number of bytes read from all tables and table functions participated in query. It includes usual subqueries, subqueries for IN and JOIN. For distributed queries read_bytes includes the total number of rows read at all replicas. Each replica sends it's 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) — 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 (uncompressed). For other queries, the column value is 0.
result_rows (UInt64) — Number of rows in a result of the SELECT query, or a number of rows in the INSERT query.
result_bytes (UInt64) — RAM volume in bytes used to store a query result.
memory_usage (UInt64) — Memory consumption by the query.
current_database (LowCardinality(String)) — Name of the current database.
query (String) — Query string.
formatted_query (String) — Formatted query string.
normalized_query_hash (UInt64) — A numeric hash value, such as it is identical for queries differ only by values of literals.
query_kind (LowCardinality(String)) — Type of the query.
databases (Array(LowCardinality(String))) — Names of the databases present in the query.
tables (Array(LowCardinality(String))) — Names of the tables present in the query.
columns (Array(LowCardinality(String))) — Names of the columns present in the query.
partitions (Array(LowCardinality(String))) — Names of the partitions present in the query.
projections (Array(LowCardinality(String))) — Names of the projections used during the query execution.
views (Array(LowCardinality(String))) — Names of the (materialized or live) views present in the query.
exception_code (Int32) — Code of an exception.
exception (String) — Exception message.
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.
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) — Initial query starting time (for distributed query execution).
initial_query_start_time_microseconds (DateTime64(6)) — Initial query starting time with microseconds precision (for distributed 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 whether a query was executed over a secure interface
os_user (LowCardinality(String)) — Operating system 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) — The query number in a script with multiple queries for clickhouse-client.
script_line_number (UInt32) — The line number of the query start in a script with multiple queries for clickhouse-client.
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)) — HTTP header UserAgent passed in the HTTP query.
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 (see keyed).
distributed_depth (UInt64) — How many times a query was forwarded between servers.
revision (UInt32) — ClickHouse revision.
log_comment (String) — Log comment. It can be set to arbitrary string no longer than max_query_size. An empty string if it is not defined.
thread_ids (Array(UInt64)) — Thread ids that are participating in query execution. These threads may not have run simultaneously.
peak_threads_usage (UInt64) — Maximum count of simultaneous threads executing the query.
ProfileEvents (Map(LowCardinality(String), UInt64)) — ProfileEvents that measure different metrics. The description of them could be found in the table system.events
Settings (Map(LowCardinality(String), LowCardinality(String))) — Settings that were changed when the client ran the query. To enable logging changes to settings, set the log_query_settings parameter to 1.
used_aggregate_functions (Array(LowCardinality(String))) — Canonical names of aggregate functions, which were used during query execution.
used_aggregate_function_combinators (Array(LowCardinality(String))) — Canonical names of aggregate functions combinators, which were used during query execution.
used_database_engines (Array(LowCardinality(String))) — Canonical names of database engines, which were used during query execution.
used_data_type_families (Array(LowCardinality(String))) — Canonical names of data type families, which were used during query execution.
used_dictionaries (Array(LowCardinality(String))) — Canonical names of dictionaries, which were used during query execution.
used_formats (Array(LowCardinality(String))) — Canonical names of formats, which were used during query execution.
used_functions (Array(LowCardinality(String))) — Canonical names of functions, which were used during query execution.
used_storages (Array(LowCardinality(String))) — Canonical names of storages, which were used during query execution.
used_table_functions (Array(LowCardinality(String))) — Canonical names of table functions, which were used during query execution.
used_executable_user_defined_functions (Array(LowCardinality(String))) — Canonical names of executable user defined functions, which were used during query execution.
used_sql_user_defined_functions (Array(LowCardinality(String))) — Canonical names of sql user defined functions, which were used during query execution.
used_row_policies (Array(LowCardinality(String))) — The list of row policies names that were used during query execution.
used_privileges (Array(LowCardinality(String))) — Privileges which were successfully checked during query execution.
missing_privileges (Array(LowCardinality(String))) — Privileges that are missing during query execution.
transaction_id (Tuple(UInt64, UInt64, UUID)) — The identifier of the transaction in scope of which this query was executed.
query_cache_usage (Enum8('Unknown' = 0, 'None' = 1, 'Write' = 2, 'Read' = 3)) — Usage of the query cache during query execution. Values: 'Unknown' = Status unknown, 'None' = The query result was neither written into nor read from the query result cache, 'Write' = The query result was written into the query result cache, 'Read' = The query result was read from the query result cache.
asynchronous_read_counters (Map(LowCardinality(String), UInt64)) — Metrics for asynchronous reading.
is_internal (UInt8) — Indicates whether it is an auxiliary query executed internally.

Aliases:

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

Examples

Basic example

SELECT * FROM system.query_log WHERE type = 'QueryFinish' ORDER BY query_start_time DESC LIMIT 1 FORMAT Vertical;

Row 1:
──────
hostname:                              clickhouse.eu-central1.internal
type:                                  QueryFinish
event_date:                            2021-11-03
event_time:                            2021-11-03 16:13:54
event_time_microseconds:               2021-11-03 16:13:54.953024
query_start_time:                      2021-11-03 16:13:54
query_start_time_microseconds:         2021-11-03 16:13:54.952325
query_duration_ms:                     0
read_rows:                             69
read_bytes:                            6187
written_rows:                          0
written_bytes:                         0
result_rows:                           69
result_bytes:                          48256
memory_usage:                          0
current_database:                      default
query:                                 DESCRIBE TABLE system.query_log
formatted_query:
normalized_query_hash:                 8274064835331539124
query_kind:
databases:                             []
tables:                                []
columns:                               []
projections:                           []
views:                                 []
exception_code:                        0
exception:
stack_trace:
is_initial_query:                      1
user:                                  default
query_id:                              7c28bbbb-753b-4eba-98b1-efcbe2b9bdf6
address:                               ::ffff:127.0.0.1
port:                                  40452
initial_user:                          default
initial_query_id:                      7c28bbbb-753b-4eba-98b1-efcbe2b9bdf6
initial_address:                       ::ffff:127.0.0.1
initial_port:                          40452
initial_query_start_time:              2021-11-03 16:13:54
initial_query_start_time_microseconds: 2021-11-03 16:13:54.952325
interface:                             1
os_user:                               sevirov
client_hostname:                       clickhouse.eu-central1.internal
client_name:                           ClickHouse
client_revision:                       54449
client_version_major:                  21
client_version_minor:                  10
client_version_patch:                  1
http_method:                           0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
revision:                              54456
log_comment:
thread_ids:                            [30776,31174]
ProfileEvents:                         {'Query':1,'NetworkSendElapsedMicroseconds':59,'NetworkSendBytes':2643,'SelectedRows':69,'SelectedBytes':6187,'ContextLock':9,'RWLockAcquiredReadLocks':1,'RealTimeMicroseconds':817,'UserTimeMicroseconds':427,'SystemTimeMicroseconds':212,'OSCPUVirtualTimeMicroseconds':639,'OSReadChars':894,'OSWriteChars':319}
Settings:                              {'load_balancing':'random','max_memory_usage':'10000000000'}
used_aggregate_functions:              []
used_aggregate_function_combinators:   []
used_database_engines:                 []
used_data_type_families:               []
used_dictionaries:                     []
used_formats:                          []
used_functions:                        []
used_storages:                         []
used_table_functions:                  []
used_executable_user_defined_functions:[]
used_sql_user_defined_functions:       []
used_privileges:                       []
missing_privileges:                    []
query_cache_usage:                     None

Cloud example

In ClickHouse Cloud, system.query_log is local to each node; to see all entries you must query via clusterAllReplicas.

For example, to aggregate query_log rows from every replica in the “default” cluster you can write:

SELECT * 
FROM clusterAllReplicas('default', system.query_log)
WHERE event_time >= now() - toIntervalHour(1)
LIMIT 10
SETTINGS skip_unavailable_shards = 1;

Description​

Columns​

Examples​

See Also​

Description

Columns

Examples

See Also