Finding The Queries That Resulted In An Error

In ClickHouse, there is a table called query_log which is located in system database. query_log table gives you information about all the queries running on the database. This is an useful option for troubleshooting your queries.

There are 4 types of queries in ClickHouse. There are ;

• '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.

To find error given queries we have to run select statement with type 3 and type 4 condition. Example;

SELECT type, query, query_id, event_time,query_duration_ms,read_rows,read_bytes,written_rows,written_bytes,memory_usage
FROM system.query_log where type = 3 or type = 4
ORDER BY event_time DESC

Result;

type:              ExceptionBeforeStart
query:             select ApplicationName as NAME, toInt32(0) as MAX_LEN, ClickHouse Java Client as DEFAULT_VALUE, Application name as DESCRIPTION union all select CustomHttpHeaders as NAME, toInt32(0) as MAX_LEN,  as DEFAULT_VALUE, Custom HTTP headers as DESCRIPTION union all select CustomHttpParameters as NAME, toInt32(0) as MAX_LEN,  as DEFAULT_VALUE, Customer HTTP query parameters as DESCRIPTION
query_id:          f142c503-8f34-4b6f-a942-bc53bd2cfb85
event_time:        2022-07-25 16:16:33
query_duration_ms: 0
read_rows:         0
read_bytes:        0
written_rows:      0
written_bytes:     0
memory_usage:      0

To find last 1 day’s queries, please run the command below;

SELECT type, query, query_id, event_time,query_duration_ms,read_rows,read_bytes,written_rows,written_bytes,memory_usage
FROM system.query_log where type = 3 or type = 4 and event_time > now() - 3600 * 24
ORDER BY event_time DESC

If you want to find your error given queries by count please run the command given below;

SELECT
    count() 
FROM (
   SELECT type, query, query_id, event_time,query_duration_ms,read_rows,read_bytes,written_rows,written_bytes,memory_usage 
FROM system.query_log where type = 3 or type = 4 
ORDER BY event_time DESC
)

Top 10 Slow Queries

SELECT query, query_id, elapsed, read_rows,read_bytes, memory_usage, peak_memory_usage, written_rows,written_bytes 
FROM system.processes
ORDER BY elapsed DESC 
LIMIT 10

To find more information about query_log table, please visit official ClickHouse docs in here.