分析ClickHouse的查询日志_云数据库 ClickHouse(ClickHouse)-阿里云帮助中心

通过查询日志，您可以查看查询的执行情况、识别慢查询，有助于您提升系统性能和解决潜在问题。本文介绍如何通过SQL查看云数据库ClickHouse的查询日志。

前提条件

确保查询日志已开启。

说明

ClickHouse默认查询日志已开启。
您可以通过执行SHOW settings like 'log_queries'; 来检查参数配置log_queries，以确认查询日志是否已开启。如果该参数配置为1，则表示查询日志已开启；若该参数为0，则表示查询日志未开启。您可以通过以下SQL语句开启查询日志。
```
SET GLOBAL ON CLUSTER default log_queries = 1;
```

注意事项

查询日志可能会包含敏感信息，请妥善管理查询日志信息。
定期清理归档查询日志，避免日志文件太大。
说明
- ClickHouse中为query_log表默认配置了15天的TTL策略，查询日志产生后会在15天后自动删除。
- 如果您希望减少查询日志相关的系统表占用的磁盘空间，可以通过控制台参数管理功能修改query_log表的TTL策略。为了方便定位实例问题，建议您query_log表的TTL策略至少设置为7天。如何修改参数，请参见配置config.xml参数。

示例环境

以下示例以s-2-r-0节点为基础环境，在实际使用过程中，请根据您的场景修改对应参数。如果您不知道如何获取节点名称，可以通过以下方式获取。

通过控制台：您可以在集群监控页面，获取节点名称。如何进入集群监控页面，请参见查看集群监控信息。
通过SQL语句：您可以执行以下语句，获取集群所有节点的名称。
```
SELECT * FROM system.clusters;
```

查看最近报错的Query

查看报错日志对于提升系统的稳定性和安全性具有重要意义。主要体现在以下几个方面：

快速定位问题：通过报错信息可以直接定位到导致问题的具体原因，从而快速修复问题。
趋势分析：通过分析报错日志，识别错误发生的时间与模式，可以进行防御性的代码优化或者配置调整。
安全：通过分析错误日志，发现系统存在的潜在安全问题，比如SQL注入、非法访问等。

查询模板

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>]

参数说明

参数	说明	值示例
startTime	要查询数据的开始时间。格式：yyyy-mm-dd hh:mi:ss	2021-11-22 22:00:00
endTime	要查询数据的结束时间。格式：yyyy-mm-dd hh:mi:ss	2021-11-22 23:00:00
nodeName	集群节点名称。	s-2-r-0
x	返回查询结果集中的前x行。	30

示例

查询2021-11-22 22:00:00 至 2021-11-22 23:00:00时间段内，s-2-r-0节点上的执行查询的错误日志信息，返回结果中的前30行数据。

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

查看最近SQL

写入SQL

查询模板

--查看最新写入的SQL，每个batch的行数和bytes大小：
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]

参数说明

参数	说明	值示例
startTime	要查询数据的开始时间。格式：yyyy-mm-dd hh:mi:ss	2021-11-22 22:00:00
endTime	要查询数据的结束时间。格式：yyyy-mm-dd hh:mi:ss	2021-11-22 23:00:00
nodeName	集群节点名称。	s-2-r-0
x	返回查询结果集中的前x行。	30

示例

查询2021-11-22 22:00:00 至 2021-11-22 23:00:00时间段内，s-2-r-0节点上写入SQL的日志信息，返回结果中的前30行数据。

--查看最新写入的SQL，每个batch的行数和bytes大小： 
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

非写入SQL

查询模板

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]

参数说明

参数	说明	示例
time	查询当前时间往前推的时间。单位：分。	60
nodeName	集群节点名称	s-2-r-0
x	返回查询结果集中的前x行。	100

示例

查询s-2-r-0节点上近60分钟的非写入SQL，返回查询结果中的前100条数据。

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

查看时间段内执行次数超过n次的非写入语句

查询模板

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>]

参数说明

参数	说明	值示例
startTime	要查询数据的开始时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 12:00:00
endTime	要查询数据的结束时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 17:00:00
queryNum	要查询超出的次数。	1000
nodeName	集群节点名称。	s-2-r-0
x	返回查询结果集中的前x行。	50

示例

查询2022-09-23 12:00:00 至 2022-09-23 17:00:00时间段内，s-2-r-0节点上查询次数超过1000次的非写入的SQL语句。

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

查询一段时间内每小时或每分钟执行查询的统计

每小时聚合

一段时间内，每小时查询数量的统计和查询的平均耗时。

查询模板

--按照每小时聚合
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 '%异常容器%'
  AND read_rows != 0
  AND substring(hostname(), 38, 8) = '<nodeName>'
GROUP BY t
[LIMIT x]

参数说明

参数	说明	值示例
startTime	要查询数据的开始时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 12:00:00
endTime	要查询数据的结束时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 17:00:00
nodeName	集群节点名称。	s-2-r-0
x	返回查询结果集中的前x行。	50

查询示例

查询2022-09-23 08:00:00 至 2022-09-23 17:00:00时间段内，s-2-r-0节点上每小时查询数量的统计和查询的平均耗时。

--按照每小时聚合
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 '%异常容器%'
  AND read_rows != 0
  AND substring(hostname(), 38, 8) = 's-2-r-0'
GROUP BY t
LIMIT 50

每分钟聚合

一段时间内，每分钟的查询数量统计和查询的平均耗时。

查询模板

--按照每分钟聚合
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 '%异常容器%'
  AND substring(hostname(), 38, 8) = '<nodeName>'
  AND read_rows != 0
GROUP BY t
[LIMIT x]

参数说明

参数	说明	值示例
startTime	要查询数据的开始时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 12:00:00
endTime	要查询数据的结束时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 17:00:00
nodeName	集群节点名称。	s-2-r-0
x	返回查询结果集中的前x行。	50

查询示例

查询 2022-09-23 08:00:00 至 2022-09-23 17:00:00 时间段内，s-2-r-0节点上每分钟查询数量的统计和查询的平均耗时。

--按照每分钟聚合
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 '%异常容器%'
  AND substring(hostname(), 38, 8) = 's-2-r-0'
  AND read_rows != 0
GROUP BY t
LIMIT 50

查看LEFT JOIN的查询个数

查询一个时间段内LEFT JOIN的查询个数。

查询模板

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)

参数说明

参数	说明	值示例
startTime	要查询数据的开始时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 12:00:00
endTime	要查询数据的结束时间。格式：yyyy-mm-dd hh:mi:ss	2022-09-23 21:00:00
nodeName	集群节点名称。	s-2-r-0

查询示例

查询2024-06-25 12:00:00 至 2024-06-25 15:00:00时间段内，s-2-r-0节点上执行LEFT JOIN语句的个数。

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)

查询用户执行非写入SQL数量排行

查询模板

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]

参数说明

参数	说明	值示例
nodeName	集群节点名称	s-2-r-0
x	要查询的排名次数。	10

示例

查询在s-2-r-0节点上执行非写入查询语句的前十名用户。

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