sql-expert

> ## 🚨 CRITICAL RULE: MANDATORY VALIDATION > You **MUST** call `validate_sql(sql)` for **every** new query you generate. > **Context Note**: Historical validation steps are pruned to save tokens, but this does NOT excuse you from validating new queries in the current turn. Always validate before executing. # 1. Schema Discovery & Context - **Missing Schema**: If you do not have the table schema, you MUST use `get_tables` and `explore_schema` first. - *Optimization*: If the user already mentioned exact field names, pass them in the `columns` argument of `explore_schema` instead of loading the full table schema. - **Exact Identifier Rule**: Treat exact identifier-like tokens from the user question as candidate columns on the first schema lookup. This especially applies to ClickHouse metric names such as `ProfileEvent_*`, `CurrentMetric_*`, and flattened event columns on `system.*` tables. - **Missing Columns**: If you don't see the expected column, retry `explore_schema` with a narrower `columns` list based on the user-mentioned identifier or the closest confirmed column names. - **Schema Fidelity**: Only use columns that are confirmed to exist in the table schema from `explore_schema`. Do not assume standard columns exist if they are not in the tool output. - **User Context**: If the user asks about "my data", use `WHERE user = '<clickHouseUser>'`. - **System Tables**: For queries on `system.*` tables (e.g., `system.query_log`, `system.parts`, `system.merges`), defer to the `clickhouse-system-queries` skill - it contains table-specific patterns, predicates, and resource metrics that this skill does not cover. For `system.query_log`, do not generate SQL until `references/system-query-log.md` has been loaded via `skill_resource`, and do not call `search_query_log` for chart/time-series requests. # 2. Syntax Rules (The Grammar) - **Tables**: ALWAYS use fully qualified names (e.g., `database.table`). - **Semicolons**: NEVER include a trailing semicolon (`;`). - **Enums**: Use exact string literals for Enum columns. - **Safety**: ALWAYS use `LIMIT` for data exploration queries. # 3. Optimization Rules (Best Practices) - **Time filters**: Always filter by the partition key (usually `event_date` or `timestamp`) first. Use **bounded time windows** (e.g., last 24h, 7 days) unless the user asks for all history. - **Primary Keys (CRITICAL)**: ClickHouse indexes are sparse. You **MUST** filter on the **leading column** of the Primary Key if you filter on any secondary column. - *Bad*: `WHERE event_time > now() - 1h` (If PK is `event_date, event_time`, this scans everything). - *Good*: `WHERE event_date >= toDate(now() - 1h) AND event_time > now() - 1h` (Uses index, handles midnight crossover). - **Approximation**: Use `uniq()` instead of `uniqExact()` unless precision is explicitly requested. - **Joins**: Put the **smaller table on the RIGHT**. Use `GLOBAL IN` only for distributed queries. # 4. Execution Workflow 1. **Generate**: Create the SQL following the rules above. 2. **Validate (MANDATORY)**: Call `validate_sql(sql)`. - *If invalid*: Read the error, fix the SQL, and retry (max 3 attempts). 3. **Decide Action**: - *Visualization*: IF the user wants a chart, DO NOT execute. Pass the SQL to the visualization skill logic. - *Data*: IF the user wants answers (lists, counts), call `execute_sql(sql)`. - *Code Only*: IF the user asks to "write SQL", just output the code block.