ClickHouse System Queries Skill

Use this skill when the user asks for operational inspection on ClickHouse

system.*

tables.

Current table coverage:

• system.query_log

  via

  references/system-query-log.md

Relationship to

sql-expert

:

• sql-expert

  handles general SQL generation and user/business tables.
• This skill handles system-table operational patterns and routing to table-specific references.

System Metrics and ProfileEvents

• Confirm column shape from schema/reference before writing predicates.
• If the user named an exact metric, pass it in the

  columns

  list via

  explore_schema

  instead of loading the full table schema.
• If

  ProfileEvents

  is a

  Map

  , access entries as

  ProfileEvents['Name']

  . If flattened, use

  ProfileEvent_Name

  .

Example — map vs flattened access:

-- Map access
SELECT ProfileEvents['DistributedConnectionFailTry'] AS fails
FROM system.query_log WHERE event_date = today();

-- Flattened column access
SELECT ProfileEvent_DistributedConnectionFailTry AS fails
FROM system.query_log WHERE event_date = today();

Workflow

1. Resolve target — identify system table and intent. Inherit the most recent time window from conversation, or default to last 60 minutes.

2. Load reference — for

   system.query_log

   , call

   skill_resource

   to load

   references/system-query-log.md

   before writing any SQL. For unsupported tables, fall back to

   sql-expert

   .

3. Execute — choose the right tool:

   • search_query_log

     for standard ranked searches and filtered lookups

   • execute_sql

     for visualization, time-bucketed aggregation, trends, or histograms

   -- search_query_log: standard lookup
   -- finds top 10 slowest queries in the last hour
   
   -- execute_sql: time-bucketed visualization
   SELECT toStartOfFiveMinutes(event_time) AS bucket,
          count() AS queries,
          avg(query_duration_ms) AS avg_ms
   FROM system.query_log
   WHERE event_date = today() AND event_time > now() - INTERVAL 1 HOUR
   GROUP BY bucket ORDER BY bucket
   

   Default to

   LIMIT 50

   unless the user specifies otherwise.

4. Summarize with concise findings and next actions.

Guardrails

• Always apply time bounds for log-like system tables
• Always use the table-specific reference when available
• Never generate

  system.query_log

  SQL until

  references/system-query-log.md

  is loaded in the current turn
• Never use

  search_query_log

  for chart-oriented requests
• Never omit

  LIMIT

  in exploratory queries