clickhouse-system-queries
npx machina-cli add skill FrankChen021/datastoria/clickhouse-system-queries --openclawClickHouse System Queries Skill
Use this skill when the user asks for operational inspection on ClickHouse system.* tables.
Current table coverage:
system.query_logviareferences/system-query-log.md
Relationship to sql-expert:
sql-experthandles general SQL generation and user/business tables.- This skill handles system-table operational patterns and routing to table-specific references.
System Metrics and ProfileEvents
- For metric-style columns in system tables, first confirm the actual column shape from schema/reference before writing predicates.
- If the user already named an exact metric or event identifier, your first
explore_schemacall MUST pass that identifier in thecolumnslist instead of loading the full table schema. - Treat exact flattened metric names such as
ProfileEvent_DistributedConnectionFailTryas candidate columns unless the schema shows the table stores them in a map instead. - If
ProfileEventsis aMap, access entries asProfileEvents['Name']. - If the table exposes flattened columns, use
ProfileEvent_Name. - Apply the same rule to other metric maps or flattened event columns: use the representation that exists in the target table, not the one you assume.
Workflow
-
Resolve target system table and intent.
- Identify whether the request is about query history (
system.query_log) or other system tables. - If user does not provide a new time window, inherit the most recent explicit time window/range from conversation.
- If no prior explicit time context exists, default to the last 60 minutes.
- Identify whether the request is about query history (
-
Load the matching reference and follow it strictly.
system.query_log->references/system-query-log.md- For unsupported system tables, use
sql-expertfor safe fallback SQL generation.
-
Execute with
execute_sql.- Default to
LIMIT 50unless the user specifies otherwise. - Keep predicates aligned with intent and table semantics.
- Default to
-
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 omit
LIMITin exploratory queries.
Source
git clone https://github.com/FrankChen021/datastoria/blob/master/src/lib/ai/skills/clickhouse-system-queries/SKILL.mdView on GitHub Overview
This skill handles operational inspections of ClickHouse system.* tables, focusing on system.query_log. It routes requests to table-specific references (like references/system-query-log.md) and uses sql-expert as a fallback for unsupported system tables. Queries are executed with safe, time-bounded predicates and a default LIMIT, returning concise findings and actionable next steps.
How This Skill Works
Resolve the target system table and intent, defaulting to the last 60 minutes if no time window is provided. Load and follow the table’s reference (e.g., system.query_log -> references/system-query-log.md); for unsupported tables, delegate to sql-expert. Execute with execute_sql using a default LIMIT of 50 and predicates aligned to the table semantics; validate metric column names via explore_schema and use the appropriate flattening or Map access when needed.
When to Use It
- Ask for recent system.query_log activity within a time window; if none is provided, default to the last 60 minutes.
- Audit operational patterns on system tables other than system.query_log; if unsupported, fall back to sql-expert.
- Query a specific metric from ProfileEvents, ensuring the correct column representation (flattened name like ProfileEvent_Name or Map access like ProfileEvents['Name']) based on the schema.
- Perform exploratory queries that require a LIMIT, which defaults to 50 unless the user specifies otherwise.
- After execution, receive a concise findings summary and a suggested next actions plan.
Quick Start
- Step 1: Resolve target system table and intent; apply default 60-minute window if none provided.
- Step 2: Load the appropriate reference (e.g., references/system-query-log.md) and prepare the query.
- Step 3: Execute with execute_sql (LIMIT 50 by default) and summarize the results.
Best Practices
- Always apply time bounds for log-like system tables (e.g., system.query_log).
- Always load and follow the table-specific reference when available (e.g., references/system-query-log.md).
- Never omit LIMIT in exploratory queries; default to 50 unless overridden.
- Verify metric column names with explore_schema and use the correct representation (flattened names or Map syntax) per the target table.
- If a system table is unsupported, route to sql-expert for safe SQL generation.
Example Use Cases
- Retrieve the latest 50 entries from system.query_log within the last 60 minutes, selecting Query, EventTime, and TotalElapsedMs.
- Identify the slowest 5 queries in the last hour from system.query_log (ORDER BY TotalElapsedMs DESC LIMIT 5).
- Access a ProfileEvent metric using Map syntax, e.g., ProfileEvents['DistributedConnectionFailTry'], after confirming the exact schema.
- Attempt to inspect an unsupported system table; the skill delegates to sql-expert for safe SQL generation.
- Run the query, then return a concise summary of findings with recommended next steps.
