huawei-cloud-mrs-clickhouse-sql-check

SKILL.md

---
name: huawei-cloud-mrs-clickhouse-sql-check
description: |
  Comprehensive SQL statement checking for ClickHouse, supporting multiple kernel
  versions (24.8, 23.3, 22.3) and two check modes:
  1. Syntax Check - Keyword validation, statement structure verification, clause completeness,
     ClickHouse-specific syntax compatibility (SAMPLE BY, FINAL, ARRAY JOIN, PREWHERE,
     GLOBAL JOIN, ASOF JOIN, ENGINE, PARTITION BY, TTL, etc.) based on kernel source grammar
  2. Specification Check - Development specification rules (SPEC001-SPEC035) from
     MRS Development Specification v01, covering DDL table design, DDL operations, materialized views,
     DML data loading, query standards, and data modification standards
  Built-in custom ClickHouse SQL tokenizer (version-specific keywords from kernel source) and statement recognizer supporting 47 statement types (DML/DDL/DCL/TCL/Utility). Applicable when users need SQL quality review, syntax validation, or ClickHouse-specific syntax checking.
  Trigger: "Clickhouse SQL check"、"CK SQL check"、 "Clickhouse SQL 校验"、 "Clickhouse SQL 检查"、 "Clickhouse SQL specification", "Clickhouse SQL 规范检查"、"Clickhouse SQL optimization", "Clickhouse SQL 优化"、"检查 Clickhouse SQL"
tags: [clickhouse, sql, check, lint, syntax]
---

# ClickHouse SQL Check Skill

You are a ClickHouse SQL specification checking expert, responsible for comprehensive SQL
statement checking for ClickHouse. You have a custom-built ClickHouse SQL tokenizer
and statement recognizer that can precisely identify ClickHouse-specific syntax.

## 1. Overview

**Architecture**: This skill uses a three-stage pipeline:
Tokenizer (lexical analysis) → Parser (statement recognition + syntax analysis)
→ Rule Engine (syntax + spec checking) → Report Generation.

**Multi-Version Support**: Version-specific grammar rules (keywords, grammar, token types)
are stored in `rules/v{version}/` directories. Common development-spec rules are in
`rules/common/`. When a user invokes the skill, **ask which ClickHouse kernel version
to use once per session** (see Workflow below). Within the same session, reuse the
previously selected version and mode without asking again.

**Supported Versions**:

| Version | Keywords | Token Types | Statement Types | Source |
|---------|----------|-------------|-----------------|--------|
| 24.8 | 571 | 52 | 47 | `CommonParsers.h` APPLY_FOR_PARSER_KEYWORDS macro |
| 23.3 | 462 | 49 | 47 | `Parser*.cpp` ParserKeyword("...") calls (scattered) |
| 22.3 | 422 | 48 | 46 | `Parser*.cpp` ParserKeyword("...") calls (scattered) |

**Source**: All keywords and grammar rules are extracted directly from the
ClickHouse kernel source (`src/Parsers/CommonParsers.h`, `src/Parsers/Lexer.h`,
and `src/Parsers/Parser*.cpp`), ensuring accuracy and completeness.

**Check Modes**:

| Mode | Dependency | Description |
|------|------------|-------------|
| **syntax** | None | Syntax check: keyword validity, statement structure, clause completeness, ClickHouse syntax compatibility |
| **spec** | None | Specification check: 35 development-spec rules (SPEC001-SPEC035) from MRS dev standards |
| **all** | None | Execute both syntax and specification checks |

Default: syntax mode. Use `spec` or `all` to enable specification rules.

**Applicable Scenarios**:
- Validate SQL syntax before executing on ClickHouse cluster
- Check ClickHouse-specific syntax (ENGINE, ORDER BY, PARTITION BY, SAMPLE BY,
  TTL, ARRAY JOIN, PREWHERE, GLOBAL JOIN, ASOF JOIN, FINAL, etc.)
- Identify potential syntax errors in SQL statements
- Review SQL for ClickHouse version-specific compatibility

**Typical Use Cases**:
- "Check this SQL: SELECT * FROM t1"
- "Does this CREATE TABLE have valid ClickHouse syntax?"
- "Validate the syntax of this MERGE statement"
- "Check if my SQL uses ClickHouse-specific syntax correctly"
- "Check if this ClickHouse SQL syntax is correct"

## 2. Prerequisites

### 2.1 Python Requirements
- Python >= 3.8
- No additional packages required (standard library only)

### 2.2 Security Rules
- This skill performs static SQL analysis only, no cluster connection required
- SQL text is processed locally, no data is sent externally
- No credentials or authentication required

### 2.3 Environment
- No ClickHouse cluster connection needed
- No KooCLI or clickhouse-client binary required
- Works fully offline

## 3. Workflow

### Step 1: Receive Input & Select Version

Receive the SQL statement from the user.

**Version selection (MANDATORY, once per session)**: On the first invocation of a new
session, ask the user to select the ClickHouse kernel version AND check mode together
using AskUserQuestion (two questions: version + mode). **Do NOT ask again within the
same session** — reuse the previously selected version and mode for all subsequent SQL
checks in that session. A new session requires re-selection.

Rules for skipping the prompt:
- If the user already specified a version and/or mode in their message, use it directly
  without asking (and remember it for the rest of the session).
- If a previous invocation in the same session already established version/mode, reuse
  those values silently.
- Only the first invocation of a brand-new session with no prior version/mode context
  triggers the AskUserQuestion prompt.

The version determines which keyword list and grammar rules are used for syntax checking.

**Check mode**: Asked together with version in the same AskUserQuestion call. If no mode
is specified by the user, default to syntax.

### Step 2: Tokenization

Run the tokenizer to convert SQL text into a Token stream.

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_tokenizer.py "<sql_text>" [version]
```

The tokenizer supports:
- Version-specific ClickHouse keywords (24.8: 571, 23.3: 462, case-insensitive)
- ClickHouse-specific tokens: `::` (typecast), `->` (arrow/lambda), `||` (concatenation),
  `<=>` (NULL-safe equality, 24.8 only), `<>` / `!=` (not equals)
- Literals: strings (single-quoted), numbers (int/float/hex), identifiers (bare/backtick/quoted)
- Comment skipping (-- single line, /* */ multi-line with nesting support)
- Compound keyword recognition (ORDER BY, GROUP BY, CREATE TABLE, etc.)
- Error detection (unclosed strings, invalid characters, etc.)

### Step 3: Statement Recognition & Parsing

Run the parser to identify statement type and detect syntax errors.

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_parser.py "<sql_text>" [version]
```

The parser supports major statement types:
- **DML**: SELECT (with all ClickHouse extensions), INSERT, INSERT SELECT, DELETE, UPDATE, OPTIMIZE
- **DDL**: CREATE TABLE/DATABASE/VIEW/MATERIALIZED VIEW/DICTIONARY/FUNCTION/INDEX,
  ALTER TABLE (all actions), DROP, RENAME, ATTACH, DETACH, UNDROP, CHECK, DESCRIBE
- **DCL**: GRANT, REVOKE
- **TCL**: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION SNAPSHOT
- **Utility**: EXPLAIN, SHOW, SET, KILL, SYSTEM, BACKUP, RESTORE, WATCH, USE

ClickHouse-specific syntax:
- `SELECT`: SAMPLE, FINAL, ARRAY JOIN, PREWHERE, GLOBAL JOIN, ASOF JOIN,
  WITH FILL, INTERPOLATE, WITH TIES, TOP N, LIMIT BY, WITH TOTALS/ROLLUP/CUBE,
  WINDOW, QUALIFY (24.8 only), GROUP BY ALL, ORDER BY ALL, DISTINCT ON, FETCH FIRST/NEXT
- `CREATE TABLE`: ENGINE, ORDER BY, PARTITION BY, PRIMARY KEY, SAMPLE BY,
  TTL, SETTINGS, CODEC, STATISTICS, PROJECTION, CONSTRAINT
- `ALTER TABLE`: ADD/MODIFY/DROP/RENAME/CLEAR/MATERIALIZE COLUMN,
  ADD/DROP/CLEAR/MATERIALIZE INDEX/PROJECTION/STATISTICS,
  partition operations (DROP/DETACH/ATTACH/MOVE/REPLACE/FETCH/FREEZE PARTITION)
- `SYSTEM`: RELOAD/FLUSH/START/STOP/DROP/SYNC/ENABLE/DISABLE command families
- `BACKUP/RESTORE`: TABLE/DATABASE/DICTIONARY/ALL TO/FROM Disk/File/S3/Azure

### Step 4: Syntax Check

Based on tokenization and parsing results, execute syntax check rules.

**Syntax Check Rules**:

| Rule ID | Name | Level | Description |
|---------|------|-------|-------------|
| SYN-ERR | Lexical Error | ERROR | Unrecognized characters, unclosed strings/comments |
| SYN001 | Invalid Keyword | ERROR | Keyword not supported by the selected ClickHouse version |
| SYN002 | Reserved Keyword as Identifier | WARNING | Reserved keyword used as identifier without quoting |
| SYN003 | Missing Required Clause | ERROR | Missing required clause (e.g., SELECT without column list) |
| SYN004 | Clause Ordering Error | ERROR | SQL clause order does not conform to grammar |
| SYN005 | Unclosed String | ERROR | String literal not properly closed |
| SYN006 | Unclosed Comment | ERROR | Multi-line comment not properly closed |
| SYN007 | Unclosed Identifier | ERROR | Quoted identifier not properly closed |
| SYN008 | Invalid Number Format | ERROR | Malformed number literal |
| SYN009 | Unexpected Character | ERROR | Unrecognized character in SQL text |
| SYN010 | CREATE TABLE Missing ENGINE | WARNING | CREATE TABLE without ENGINE clause |
| SYN011 | MergeTree Missing ORDER BY | WARNING | MergeTree family table without ORDER BY |
| SYN012 | DELETE Missing WHERE | WARNING | DELETE without WHERE clause |
| SYN013 | UPDATE Missing WHERE | WARNING | UPDATE without WHERE clause |
| SYN014 | Invalid JOIN Syntax | ERROR | Invalid JOIN combination (e.g., CROSS with ANY) |
| SYN015 | Unclosed Parenthesis | ERROR | Unbalanced parentheses |

### Step 4b: Specification Check

Based on the MRS ClickHouse development specification (v01, 2026-07-03), execute 35
specification rules. Use `spec` or `all` mode to enable. Spec rules are version-independent.

**Specification Check Rules (SPEC001-SPEC035)**:

| Rule ID | Name | Level | Category | Description |
|---------|------|-------|----------|-------------|
| SPEC001 | Buffer engine prohibited | ERROR | DDL | Buffer engine risks data loss on restart/fault |
| SPEC002 | Recommend Replicated engine | WARNING | DDL | Recommend Replicated*MergeTree for reliability |
| SPEC003 | Non-standard table name | WARNING | DDL | Table name should start with letter, alphanumeric+underscore |
| SPEC004 | String type for date/time prohibited | WARNING | DDL | Use Date/DateTime types instead of String for dates |
| SPEC005 | String type for numbers prohibited | WARNING | DDL | Use numeric types instead of String for numbers |
| SPEC006 | Too many Nullable columns | INFO | DDL | Too many Nullable columns waste memory |
| SPEC007 | Numeric type not minimized | INFO | DDL | Use smallest sufficient numeric type |
| SPEC008 | LowCardinality not used for low-cardinality | INFO | DDL | Use LowCardinality for columns with cardinality < 100k |
| SPEC009 | Table exceeds 5000 columns | WARNING | DDL | Single table should not exceed 5000 columns |
| SPEC010 | Missing TTL lifecycle | INFO | DDL | Tables should have TTL or periodic partition cleanup |
| SPEC011 | Too many ORDER BY fields | WARNING | DDL | ORDER BY should have <= 4 fields, not null |
| SPEC012 | PRIMARY KEY not prefix of sort key | WARNING | DDL | PRIMARY KEY should be prefix of ORDER BY |
| SPEC014 | DROP/ALTER without NO DELAY | INFO | DDL | Add NO DELAY for immediate execution |
| SPEC015 | Skip indexes exceed 5 per table | WARNING | DDL | Keep skip indexes <= 5 per table |
| SPEC016 | Excessive partition count risk | INFO | DDL | Keep partitions <= 10000, use integer partition column |
| SPEC017 | Non-standard MV naming | INFO | DDL | Aggregation tables: _{type}_agg, MVs: _{type}_mv |
| SPEC018 | MV without explicit target table | WARNING | DDL | Use TO keyword to specify target table for MV |
| SPEC019 | POPULATE for MV creation prohibited | ERROR | DDL | POPULATE risks data loss during creation |
| SPEC020 | MV missing TTL | INFO | DDL | MV target table should have TTL matching source |
| SPEC021 | INSERT into distributed table | WARNING | DML | Write to local tables, not distributed tables |
| SPEC022 | INSERT not limited to single partition | INFO | DML | Batch inserts should target single partition |
| SPEC023 | Kafka engine prohibited | ERROR | DML | Avoid ClickHouse Kafka engine, consume in app instead |
| SPEC024 | SELECT * query prohibited | WARNING | Query | Specify columns explicitly, avoid SELECT * |
| SPEC025 | Recommend uniqCombined over distinct | INFO | Query | uniqCombined is faster than countDistinct/distinct |
| SPEC026 | Distributed JOIN without GLOBAL | WARNING | Query | Use GLOBAL JOIN/IN/NOT IN for distributed queries |
| SPEC027 | Complex multi-table JOIN not split | INFO | Query | Split complex multi-table JOINs into two-table joins |
| SPEC028 | JOIN order: large table join small table | INFO | Query | Large table JOIN small table, small table on right |
| SPEC029 | Use FINAL with caution | INFO | Query | FINAL triggers full merge, use sparingly |
| SPEC030 | Use DELETE/UPDATE mutation with caution | WARNING | DML | Mutations are slow and block merge, use sparingly |
| SPEC031 | Modify index columns prohibited | ERROR | DML | Never UPDATE ORDER BY / PRIMARY KEY columns |
| SPEC032 | Use OPTIMIZE with caution | INFO | DML | OPTIMIZE forces merge, runs in off-peak hours |
| SPEC033 | Batch cleanup not via partition | INFO | DML | Batch cleanup via DROP PARTITION |
| SPEC034 | Decimal type mismatch in type-sensitive functions | WARNING | Query | Decimal scale/precision mismatch in coalesce/ifNull/nullIf causes implicit conversion |
| SPEC035 | IN/NOT IN column count mismatch | ERROR | Query | Column count mismatch between left and right sides of IN/NOT IN causes runtime errors or logic errors |

### Step 5: Generate Report

Use the check engine to generate a Markdown format report:

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "<sql_text>" [syntax|spec|all] [version]
```

## 4. Core Commands

### 4.1 Full Check (Recommended)

Run complete syntax + specification check:

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "<sql_text>" all 24.8
```

### 4.2 Syntax Check Only

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "<sql_text>" syntax 24.8
```

### 4.3 Specification Check Only

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "<sql_text>" spec 24.8
```

### 4.4 Tokenization Debug

Inspect token stream for debugging:

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_tokenizer.py "<sql_text>" 24.8
```

### 4.5 Parser Debug

Inspect parsed statement structure:

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_parser.py "<sql_text>" 24.8
```

### 4.6 Python API Usage

```python
from ck_sql_checker import check_sql_markdown
report = check_sql_markdown("SELECT * FROM t1", "all", "24.8")
print(report)
```

## 5. Parameters

| Parameter | Required/Optional | Description | Default |
|-----------|-------------------|-------------|---------|
| `sql_text` | Required | SQL statement to check | N/A |
| `version` | Required | ClickHouse kernel version: 24.8, 23.3, 22.3 | No default; user must select on first invocation per session, reused within session |
| `check_mode` | Optional | Check mode: syntax/spec/all | syntax |

## 6. Output Format

Report format:

```markdown
# ClickHouse SQL Check Report

**Check Time**: 2026-07-27T10:00:00
**ClickHouse Version**: 24.8
**Statement Type**: SELECT
**Check Mode**: all

## Summary

| Metric | Value |
|--------|-------|
| Total Rules | 51 |
| Passed | 48 |
| Violations | 3 |
| Errors (ERROR) | 1 |
| Warnings (WARNING) | 1 |
| Infos (INFO) | 1 |

## Syntax Check

### [X] SYN011: MergeTree Missing ORDER BY
- **Level**: WARNING
- **Position**: Line 1, Column 1
- **Description**: MergeTree family table without ORDER BY clause
- **Fix Suggestion**: Add ORDER BY (column1, column2, ...)

## Specification Check

### [!] SPEC010: Missing TTL lifecycle
- **Level**: INFO
- **Position**: Line 1, Column 1
- **Description**: Tables should have TTL or periodic partition cleanup
- **Fix Suggestion**: Add TTL column + toIntervalMonth(N) clause

## Original SQL

```sql
{original_sql}
```
```

**IMPORTANT: Always present check results in table format**, not as bullet lists or
paragraphs. When checking one or multiple SQL statements, always use three tables:
1. A **violation detail table** (columns: Rule ID, Rule Name, Level, SQL, Position, Description, Fix Suggestion)
2. A **summary table** (columns: metrics like total rules, passed, violations, ERROR/WARNING/INFO counts, per SQL)
3. A **fix suggestion table** (columns: SQL, Issue, Suggestion)

This table format is mandatory for all SQL check outputs.

## 7. Verification Methods

### 7.1 Verify Tokenization

Run the tokenizer independently to verify keyword recognition:

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_tokenizer.py "SELECT * FROM t1 FINAL" 24.8
```

Expected: Token stream should include `FINAL` as a recognized keyword for version 24.8.

### 7.2 Verify Parser Recognition

Run the parser to verify statement type identification:

```bash
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_parser.py "CREATE TABLE t (a UInt32) ENGINE = MergeTree ORDER BY a" 24.8
```

Expected: Output should show `statement_type: CREATE_TABLE` with parsed column and ENGINE info.

### 7.3 Verify Version-Specific Keywords

Test that version-specific keywords are correctly recognized:

| Keyword | 24.8 | 23.3 | 22.3 |
|---------|------|------|------|
| QUALIFY | ✅ | ❌ | ❌ |
| INTERPOLATE | ✅ | ✅ | ❌ |
| UNDROP | ✅ | ✅ | ❌ |
| GROUP BY ALL | ✅ | ✅ | ❌ |

Run tokenizer with different versions to confirm keyword availability.

### 7.4 Verify Spec Rules

Run spec check on known violations to confirm rule detection:

```bash
# Should trigger SPEC024 (SELECT * prohibited)
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "SELECT * FROM t1" spec 24.8

# Should trigger SYN011 (MergeTree missing ORDER BY)
python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "CREATE TABLE t (a UInt32) ENGINE = MergeTree" spec 24.8
```

## 8. Best Practices

1. **Run syntax check first** to catch basic errors, then spec check for deeper analysis
2. **Always specify the ClickHouse version** matching your target cluster to ensure accurate keyword and grammar validation
3. **Use `all` mode for comprehensive checking** before production deployment
4. **Pay attention to version-specific keywords** (e.g., QUALIFY is 24.8+ only, INTERPOLATE is 23.3+ only)
5. **For distributed tables**, check SPEC026 (GLOBAL JOIN) and SPEC021 (INSERT into distributed)
6. **For MergeTree tables**, always verify ORDER BY (SYN011) and consider TTL (SPEC010)
7. **For Materialized Views**, follow SPEC017-SPEC020: naming convention, explicit target table, no POPULATE, matching TTL

## 9. References

| Document | Description |
|----------|-------------|
| [Keywords v24.8](rules/v24.8/keywords.py) | 571 ClickHouse 24.8 keyword definitions |
| [Keywords v23.3](rules/v23.3/keywords.py) | 462 ClickHouse 23.3 keyword definitions |
| [Keywords v22.3](rules/v22.3/keywords.py) | 422 ClickHouse 22.3 keyword definitions |
| [Token Types v24.8](rules/v24.8/token_types.py) | 24.8 lexer token type definitions |
| [Token Types v23.3](rules/v23.3/token_types.py) | 23.3 lexer token type definitions |
| [Token Types v22.3](rules/v22.3/token_types.py) | 22.3 lexer token type definitions |
| [Grammar v24.8](rules/v24.8/grammar_rules.py) | 24.8 statement type grammar definitions |
| [Grammar v23.3](rules/v23.3/grammar_rules.py) | 23.3 statement type grammar definitions |
| [Grammar v22.3](rules/v22.3/grammar_rules.py) | 22.3 statement type grammar definitions |
| [Spec Rules](rules/common/spec_rules.py) | 35 development specification rules (SPEC001-SPEC035) |
| [Token Types Ref](references/token_types.md) | Token type reference documentation |
| [SELECT Grammar](references/select_grammar.md) | SELECT statement grammar reference |
| [DDL Grammar](references/ddl_grammar.md) | DDL statement grammar reference |
| [DML Grammar](references/dml_misc_grammar.md) | DML and utility statement grammar reference |

## 10. Notes

1. **Syntax check** does not require cluster connection, can run offline
2. **Multi-version**: Each version's rules are independently stored; adding a new version
   does not affect existing versions
3. **Keywords** are extracted directly from the corresponding ClickHouse kernel source
4. **Grammar rules** cover 47 statement types from the kernel's Parser classes
5. **Specification rules** (35 rules, SPEC001-SPEC035) are derived from
   MRS Development Specification v01 (2026-07-03) Chapter 1: ClickHouse Application Development
   Standards, covering DDL table design, DDL operations, materialized views, DML
   data loading, query standards, and data modification standards
6. Some spec rules (e.g., partition count, data volume, JOIN order) require
   runtime information and are approximated with static heuristics
7. The check engine includes a custom tokenizer and statement recognizer,
   no external SQL parsing libraries required

### Directory Structure

```
rules/
  common/
    spec_rules.py          # Version-independent dev-spec rules (SPEC001-SPEC035)
  v24.8/
    keywords.py            # 571 keywords (from CommonParsers.h APPLY_FOR_PARSER_KEYWORDS)
    grammar_rules.py       # 47 statement types
    token_types.py         # 52 token types (includes Spaceship <=>)
  v23.3/
    keywords.py            # 462 keywords (from Parser*.cpp ParserKeyword("...") calls)
    grammar_rules.py       # 47 statement types (no QUALIFY/PASTE JOIN/MODIFY REFRESH)
    token_types.py         # 49 token types (no Spaceship/Caret/Ellipsis)
  v22.3/
    keywords.py            # 422 keywords (from Parser*.cpp ParserKeyword("...") calls)
    grammar_rules.py       # 46 statement types (no UNDROP/INTERPOLATE/GROUP BY ALL/Named Collections)
    token_types.py         # 48 token types (no Spaceship/Caret/Ellipsis/PipeMark)
scripts/
  version_loader.py        # Dynamic version module loader
  ck_sql_tokenizer.py      # Tokenizer (version-aware via init_version())
  ck_sql_parser.py         # Parser (version-aware via init_version())
  ck_sql_checker.py        # Check engine (version-aware)
```

To add a new ClickHouse version:
1. Create `rules/v{version}/` directory
2. Extract keywords, grammar_rules, token_types from that version's kernel source
3. The skill automatically detects and supports the new version