huawei-cloud-mrs-clickhouse-sql-check

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"

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}

text

**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	571 ClickHouse 24.8 keyword definitions
Keywords v23.3	462 ClickHouse 23.3 keyword definitions
Keywords v22.3	422 ClickHouse 22.3 keyword definitions
Token Types v24.8	24.8 lexer token type definitions
Token Types v23.3	23.3 lexer token type definitions
Token Types v22.3	22.3 lexer token type definitions
Grammar v24.8	24.8 statement type grammar definitions
Grammar v23.3	23.3 statement type grammar definitions
Grammar v22.3	22.3 statement type grammar definitions
Spec Rules	35 development specification rules (SPEC001-SPEC035)
Token Types Ref	Token type reference documentation
SELECT Grammar	SELECT statement grammar reference
DDL Grammar	DDL statement grammar reference
DML Grammar	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

text

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