ApsaraDB for ClickHouse:View query logs

Prerequisites

The query logging feature is enabled.

Usage notes

• Query logs may contain sensitive information. You must properly manage query logs.

• You need to regularly clean up and archive query logs to avoid excessive log files.

  Note

  • By default, the query_log table in ApsaraDB for ClickHouse is configured with a time-to-live (TTL) period of 15 days. This indicates that query logs are automatically deleted 15 days after the logs are generated.

  • If you want to reduce the disk space occupied by the query_log table, you can modify the TTL period of the query_log table on the Parameter Configuration page in the ApsaraDB for ClickHouse console. To facilitate the identification of cluster issues, we recommend that you set the TTL value to at least seven days. For more information about how to modify parameters, see Configure parameters in the config.xml file.

Sample environment

In this topic, the s-2-r-0 node is used as the basic environment. In actual use, modify the parameters based on your business requirements. If you do not know your node name, you can use one of the following ways to obtain the node name:

• You can obtain the node name on the Cluster Monitoring tab in the ApsaraDB for ClickHouse console. For more information about how to go to the Cluster Monitoring tab page, see View cluster monitoring information.

• You can execute the following SQL statement to obtain the names of all nodes in your cluster:

  SELECT * FROM system.clusters;

View the recent queries for which errors are reported

Viewing error logs helps you improve system stability and security by offering the following benefits:

• Quick issue identification: Error messages help you directly identify the causes of issues and troubleshoot the issues in a timely manner.

• Trend analysis: You can know the time and patterns of errors by analyzing error logs. This allows you to implement proactive code optimization and configuration modification.

• Security issue prevention: You can identify potential security vulnerabilities, such as SQL injection and unauthorized access, by analyzing error logs.

Query template

SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time,
    exception
FROM clusterAllReplicas('default',system.query_log) ql
WHERE ((event_time >= '<startTime>') 
       AND 
       (event_time <= '<endTime>')) AND (lowerUTF8(query) LIKE '%insert into sdk_event_record_local%') 
       AND (type != 'QueryStart')
       AND (exception_code != 0)
       [AND substring(hostname(),38,8) = '<nodeName>']
ORDER BY event_time DESC
[LIMIT <x>]

Parameters

Example

View the error logs of queries executed on the s-2-r-0 node during the period from 22:00:00 on November 22, 2021 to 23:00:00 on November 22, 2021, and specify that the first 30 rows in the query result are returned.

SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time,
    exception
FROM clusterAllReplicas('default',system.query_log) ql
WHERE (lowerUTF8(query) LIKE '%insert into sdk_event_record_local%') AND 
 ((event_time >= '2021-11-22 22:00:00') 
       AND 
       (event_time <= '2021-11-22 23:00:00')) 
       AND (type != 'QueryStart')
       AND (exception_code != 0)
AND substring(hostname(),38,8) = 's-2-r-0'
ORDER BY event_time DESC
LIMIT 30

View recent SQL queries

--View the recent write-type SQL queries, including the number of rows and the size of bytes for each batch.
SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time
FROM clusterAllReplicas('default',system.query_log) ql
WHERE ((event_time >= '<startTime>') 
       AND (event_time <= '<endTime>'))
       AND (lowerUTF8(query) ILIKE '%insert into%') AND (type != 'QueryStart')
       [AND substring(hostname(),38,8) = '<nodeName>']
ORDER BY event_time DESC
[LIMIT x]

--View the recent write-type SQL queries, including the number of rows and the size of bytes for each batch. 
SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time
FROM clusterAllReplicas('default',system.query_log) ql
WHERE ((event_time >= '2021-11-22 22:00:00') 
       AND (event_time <= '2021-11-22 23:00:00'))
       AND (lowerUTF8(query) LIKE '%insert into sdk_event_record_local%') 
       AND (type != 'QueryStart')
       AND substring(hostname(),38,8) = 's-2-r-0'
ORDER BY event_time DESC
LIMIT 30

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('default', system.query_log)  
WHERE (event_date = today()) AND (event_time >= (now() - <time>)) AND (is_initial_query = 1) AND (query NOT ILIKE 'INSERT INTO%'  [AND substring(hostname(),38,8) = '<nodeName>'])  
ORDER BY event_time DESC  
[LIMIT x]

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('default', system, query_log)  
WHERE (event_date = today()) AND (event_time >= (now() - 60)) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
AND substring(hostname(),38,8) = 's-2-r-0'
ORDER BY event_time DESC  
LIMIT 100

View non-write statements that are executed more than N times within a specific period of time

Query template

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = '<nodeName>'
   GROUP BY SQL
   ORDER BY avgTime DESC)
WHERE queryNum > <queryNum> 
[LIMIT <x>]

Parameters

Example

View the non-write SQL statements that are executed more than 1,000 times on the s-2-r-0 node during the period from 12:00:00 on September 23, 2022 to 17:00:00 on September 23, 2022.

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE event_time > toDateTime('2022-09-23 12:00:00')
     AND event_time < toDateTime('2022-09-23 17:00:00')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = 's-2-r-0'
   GROUP BY SQL
   ORDER BY avgTime DESC)
WHERE queryNum > 1000
LIMIT 50

View the execution statistics of queries by hour or minute within a specific period of time

--View execution statistics by hour
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  AND query not like '%INSERT INTO%'
  AND query like '%Faulty container%'
  AND read_rows != 0
  AND substring(hostname(), 38, 8) = '<nodeName>'
GROUP BY t
[LIMIT x]

--View execution statistics by hour
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('2022-09-23 08:00:00')
  AND event_time < toDateTime('2022-09-23 17:00:00')
  AND query not like '%INSERT INTO%'
  AND query like '%Faulty container%'
  AND read_rows != 0
  AND substring(hostname(), 38, 8) = 's-2-r-0'
GROUP BY t
LIMIT 50

--View execution statistics by minute
SELECT toMinute(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  AND query not like '%INSERT INTO%'
  AND query like '%Faulty container%'
  AND substring(hostname(), 38, 8) = '<nodeName>'
  AND read_rows != 0
GROUP BY t
[LIMIT x]

--View execution statistics by minute
SELECT toMinute(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('2022-09-23 12:00:00')
  AND event_time < toDateTime('2022-09-23 13:00:00')
  AND query not like '%INSERT INTO%'
  AND query like '%Faulty container%'
  AND substring(hostname(), 38, 8) = 's-2-r-0'
  AND read_rows != 0
GROUP BY t
LIMIT 50

View the number of LEFT JOIN queries

View the number of LEFT JOIN queries within a specific period of time.

Query template

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE query like '%JOIN%'
     AND read_rows != 0
     AND event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = '<nodeName>'
   GROUP BY SQL
   ORDER BY queryNum DESC)

Parameters

Example

View the number of LEFT JOIN queries executed on the s-2-r-0 node during the period from 12:00:00 on June 25, 2024 to 15:00:00 on June 25, 2024.

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE query like '%JOIN%'
     AND read_rows != 0
     AND event_time > toDateTime('2024-06-25 12:00:00')
     AND event_time < toDateTime('2024-06-25 15:00:00')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = 's-2-r-0'
   GROUP BY SQL
   ORDER BY queryNum DESC)

View the ranking of users based on the number of non-write SQL queries they executed

Query template

SELECT   
    user,   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows  
FROM clusterAllReplicas('default', system, query_log)  
WHERE (event_date = yesterday()) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%' AND substring(hostname(),38,8) = '<nodeName>')  
GROUP BY user  
ORDER BY query_times DESC  
[LIMIT x]

Parameters

Example

View the top 10 users who execute the most non-write queries on the s-2-r-0 node.

SELECT   
    user,   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows  
FROM clusterAllReplicas('default', system, query_log)  
WHERE (event_date = yesterday()) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%' AND substring(hostname(),38,8) = 's-2-r-0')  
GROUP BY user  
ORDER BY query_times DESC  
LIMIT 10