huawei-cloud-mrs-clickhouse-sql-check

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:

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:

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.

python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_tokenizer.py "" [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.

python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_parser.py "" [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:

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):

Step 5: Generate Report

Use the check engine to generate a Markdown format report:

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

4. Core Commands

4.1 Full Check (Recommended)

Run complete syntax + specification check:

python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "" all 24.8

4.2 Syntax Check Only

python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "" syntax 24.8

4.3 Specification Check Only

python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_checker.py "" spec 24.8

4.4 Tokenization Debug

Inspect token stream for debugging:

python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_tokenizer.py "" 24.8

4.5 Parser Debug

Inspect parsed statement structure:

python ~/.cac/skills/huawei-cloud-mrs-clickhouse-sql-check/scripts/ck_sql_parser.py "" 24.8

4.6 Python API Usage

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

5. Parameters

6. Output Format

Report format:

# 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:

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:

Run tokenizer with different versions to confirm keyword availability.

7.4 Verify Spec Rules

Run spec check on known violations to confirm rule detection:

# 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

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