huawei-cloud-doris-sql-check

Doris SQL Check Skill

You are an Apache Doris SQL specification checking expert, responsible for comprehensive SQL statement checking for Apache Doris (based on Doris 3.1.4 source code). You have a custom-built Doris SQL tokenizer and recursive descent parser that can precisely identify Doris-specific syntax from the Nereids ANTLR4 grammar (DorisLexer.g4 / DorisParser.g4).

Overview

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

Applicable Scenarios:

• Validate SQL syntax before executing on a Doris cluster (FE/BE)
• Review SQL statements against Apache Doris development best practices
• Check Doris-specific syntax (DISTRIBUTED BY HASH/RANDOM, PARTITION BY RANGE/LIST/AUTO, BUCKETS, PROPERTIES, ENGINE, DUPLICATE/AGGREGATE/UNIQUE KEY, INSERT OVERWRITE TABLE, LOAD LABEL, ROUTINE LOAD, EXPORT, MTMV, BACKUP/RESTORE SNAPSHOT, ADMIN SET/SHOW, CANCEL, KILL, TABLESAMPLE, OUTFILE, Hint /*+ /, full-text MATCH_, COLOCATE GROUP)
• Identify potential performance anti-patterns in Doris SQL statements

Typical Use Cases:

• "Check this Doris SQL: SELECT * FROM t1"
• "Does this CREATE TABLE follow Doris specification (DISTRIBUTED BY, KEY model, PARTITION)?"
• "Validate the syntax of this INSERT OVERWRITE TABLE statement"
• "Review my Doris SQL for specification compliance"
• "Check if my SQL uses Doris-specific syntax correctly (MTMV, LOAD, EXPORT)"
• "Validate BACKUP/RESTORE SNAPSHOT syntax"
• "Check my ROUTINE LOAD job definition"

Check Modes

Default: syntax + spec mode (no external dependencies required).

Prerequisites

1. Python Requirements

• Python >= 3.8
• No additional packages required (standard library only)

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

Workflow

Step 1: Receive Input

Receive the SQL statement and check mode from the user. If no mode is specified, default to syntax + spec.

Step 2: Tokenization

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

python ~/.cac/skills/huawei-cloud-doris-sql-check/scripts/doris_sql_tokenizer.py ""

The tokenizer supports:

• All 504 Doris keywords (from DorisLexer.g4 between --DORIS-KEYWORD-LIST-START and --DORIS-KEYWORD-LIST-END)
• Doris-specific tokens: HINT (/*+ ... */), BACKQUOTED (`ident`), ARROW (->), NSEQ (<=> null-safe eq), EQ (= or ==), NEQ (<> or !=), LTE (<= or !>), GTE (>= or !<), DOUBLEPIPES (||), LOGICALAND (&&), LOGICALNOT (!)
• Literals: strings ('...' / "..."), integers, decimals, bigints (123L), smallints (123S), tinyints (123Y), bigdecimals (123BD), exponents
• Backquoted identifiers: `table_name` (Doris-style, preferred over double quotes)
• Comment skipping (-- single line, /* */ multi-line, but /*+ hint */ preserved as HINT token)
• Full-text search operators: MATCH_ALL, MATCH_ANY, MATCH_PHRASE, MATCH_PHRASE_PREFIX, MATCH_PHRASE_EDGE, MATCH_REGEXP, MATCH_NAME, MATCH_NAME_GLOB

Step 3: Parsing

Run the parser to generate AST and detect syntax errors.

python ~/.cac/skills/huawei-cloud-doris-sql-check/scripts/doris_sql_parser.py ""

The parser supports major Doris statement types (based on DorisParser.g4):

• DML: SELECT (with CTE, set ops, window, TABLESAMPLE), INSERT INTO/OVERWRITE TABLE, UPDATE, DELETE, LOAD (BROKER LOAD), EXPORT, COPY INTO, TRUNCATE
• DDL: CREATE TABLE (with DISTRIBUTED BY, PARTITION BY, KEY model, ENGINE, PROPERTIES), CREATE TABLE LIKE, CREATE VIEW, CREATE MTMV (Multi-Table Materialized View), CREATE INDEX (BITMAP/NGRAM_BF/INVERTED), ALTER TABLE (ADD/MODIFY/DROP/RENAME COLUMN, ADD/DROP PARTITION, ADD/DROP INDEX, ROLLUP, TAG/BRANCH), DROP TABLE/VIEW/INDEX, CREATE CATALOG, CREATE DATABASE, CREATE USER/ROLE, CREATE RESOURCE, CREATE STAGE, CREATE ENCRYPTKEY, CREATE JOB, CREATE ROW POLICY, CREATE SQL_BLOCK_RULE, CREATE STORAGE VAULT/POLICY, CREATE WORKLOAD GROUP/POLICY
• DCL: GRANT/REVOKE (table/resource/role privileges)
• TCL: BEGIN/START TRANSACTION, COMMIT, ROLLBACK
• Utility: EXPLAIN (PARSED/ANALYZED/REWRITTEN/LOGICAL/OPTIMIZED/PHYSICAL/SHAPE/MEMO/DISTRIBUTED/ALL, VERBOSE/TREE/GRAPH/PLAN), SET (variables/options), SHOW (50+ variants), DESC/DESCRIBE, ADMIN SET/SHOW (replica, frontend config, tablet diagnose, trash, TDE), KILL (CONNECTION/QUERY), CANCEL (LOAD/EXPORT/ALTER TABLE/BACKUP/RESTORE/WARM UP), BACKUP/RESTORE SNAPSHOT, RECOVER (DATABASE/TABLE/PARTITION), CLEAN (LABEL/PROFILE/QUERY STATS), INSTALL/UNINSTALL PLUGIN, LOCK/UNLOCK TABLES, WARM UP, SYNC, HELP, CALL PROCEDURE

Doris-specific syntax:

• DISTRIBUTED BY {HASH(cols) | RANDOM} (BUCKETS n | AUTO)?
• PARTITION BY (RANGE | LIST)? ... (AUTO)? (auto partition, step partition, less-than, fixed, in-list)
• (DUPLICATE | AGGREGATE | UNIQUE) KEY (cols) (CLUSTER BY cols)?
• ENGINE = olap | mysql | elasticsearch | hive | hudi | iceberg | jdbc | ...
• PROPERTIES ('key'='value', ...)
• INSERT OVERWRITE TABLE ...
• LOAD LABEL ... (DATA INFILE (...) INTO TABLE ...)
• CREATE ROUTINE LOAD ... FROM type (...)
• EXPORT TABLE ... TO ...
• BACKUP SNAPSHOT ... TO repo (ON|EXCLUDE (...))?
• RESTORE SNAPSHOT ... FROM repo (ON|EXCLUDE (...))?
• EXPLAIN {PARSED|ANALYZED|REWRITTEN|LOGICAL|OPTIMIZED|PHYSICAL|SHAPE|MEMO|DISTRIBUTED|ALL} [VERBOSE|TREE|GRAPH|PLAN] [PROCESS]
• CREATE MATERIALIZED VIEW ... (DUPLICATE KEY ...)? PARTITION BY ... DISTRIBUTED BY ... AS query
  • BUILD [IMMEDIATE|DEFERRED], REFRESH [COMPLETE|AUTO], ON [MANUAL|SCHEDULE|COMMIT]
• TABLESAMPLE (...) (REPEATABLE n)?
• WITH cte_name AS (...) (CTE; Doris does not require explicit RECURSIVE keyword)
• Window functions: OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)
• OUTFILE 'path' (FORMAT AS ...)? (PROPERTIES (...))?
• Hints: /*+ hint_name(...) */ and [hint_name] relation hints
• ALTER COLOCATE GROUP name SET (...)
• Full-text search: col MATCH_ALL '...', MATCH_PHRASE, MATCH_PHRASE_PREFIX, MATCH_PHRASE_EDGE, MATCH_ANY, MATCH_REGEXP
• Aggregate unions: HLL_UNION, BITMAP_UNION, QUANTILE_UNION, REPLACE_IF_NOT_NULL
• Doris data types: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, BOOLEAN, FLOAT, DOUBLE, DATE, DATETIME, DATEV2, DATETIMEV2, DATEV1, DATETIMEV1, BITMAP, QUANTILE_STATE, HLL, AGG_STATE, STRING, JSON, JSONB, TEXT, VARCHAR, CHAR, DECIMAL, DECIMALV2, DECIMALV3, IPV4, IPV6, ARRAY, MAP, STRUCT, VARIANT

Step 4: Syntax Check

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

Syntax Check Rules (34 rules):

Step 5: Specification Check

Based on AST and Token stream, execute specification check rules. Rules are derived from DorisParser.g4 grammar definitions and Apache Doris development best practices.

Specification Check Rules (46 rules):

Step 6: Generate Report

Use the check engine to generate a Markdown format report:

python ~/.cac/skills/huawei-cloud-doris-sql-check/scripts/doris_sql_checker.py "" all

Report format:

# Doris SQL Check Report

**Check Time**: 2026-07-17T10:00:00
**Statement Type**: SELECT
**Check Mode**: all

## Summary

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

## Syntax Check

### [X] SYN003: Syntax Structure Error
- **Level**: ERROR
- **Position**: Line 1, Column 15
- **Description**: Missing FROM clause
- **Fix Suggestion**: Add FROM table_name

## Specification Check

### [!] SPEC003: SELECT * Prohibited
- **Level**: WARNING
- **Position**: Line 1, Column 8
- **Description**: Query uses SELECT *, should specify explicit column list
- **Fix Suggestion**: Replace SELECT * with specific column list

Core Commands

doris_sql_checker.py doris_sql_parser.py doris_sql_tokenizer.py

Parameters

Output Format

The check report is output in Markdown format, containing:

• Summary table: Total rules, passed, violations by level
• Syntax check section: Violations from syntax rules (SYN-ERR, SYN001-SYN033)
• Specification check section: Violations from specification rules (SPEC001-SPEC040)
• Original SQL: The checked SQL statement

Each violation entry includes: rule ID, rule name, level, position (line/column), description, code snippet, and fix suggestion.

Quick Check Command

For simple SQL checks, run directly:

python ~/.cac/skills/huawei-cloud-doris-sql-check/scripts/doris_sql_checker.py "" [syntax|spec|all]

Output is in JSON format. For Markdown format report, call in Python:

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

Best Practices

1. Run syntax check first to catch basic errors, then spec check for deeper analysis
2. For CREATE TABLE statements, always include DISTRIBUTED BY HASH(分布键) or DISTRIBUTED BY RANDOM to avoid SPEC001
3. For Doris tables, explicitly specify the KEY model (DUPLICATE KEY/AGGREGATE KEY/UNIQUE KEY)
4. Use all mode for comprehensive checking
5. Rules marked with requires_mcp: true or "Requires cluster" (SPEC033, SPEC034, SPEC035) need cluster connection and are skipped in static mode
6. Doris does NOT support MERGE INTO ... WHEN MATCHED or ON DUPLICATE KEY UPDATE — these will be flagged as syntax errors
7. Doris identifiers use backticks (`), not double quotes — using double quotes for identifiers will trigger a warning
8. For large data loading, prefer STREAM LOAD / BROKER LOAD / ROUTINE LOAD over multi-row INSERT VALUES (SPEC025)

References

Notes

1. Syntax and specification checks do not require cluster connection, can run offline
2. Rules marked "Requires cluster" (SPEC033, SPEC034, SPEC035) are skipped in static mode
3. Performance rules (PERF001-PERF011) are defined in rules/perf_rules.yaml but require cluster connection for execution (EXPLAIN ANALYZE, system tables like information_schema.tables, backends, etc.)
4. Doris-specific syntax checking (DISTRIBUTED BY, PARTITION BY, BUCKETS, PROPERTIES, ENGINE, DUPLICATE/AGGREGATE/UNIQUE KEY, INSERT OVERWRITE, LOAD, EXPORT, MTMV, BACKUP/RESTORE, ADMIN, CANCEL, KILL, TABLESAMPLE, OUTFILE, Hint, MATCH, COLOCATE GROUP) is based on DorisParser.g4 (Nereids ANTLR4 grammar) from Doris 3.1.4 source
5. The check engine includes a custom tokenizer and recursive descent parser, no external SQL parsing libraries required (no ANTLR runtime needed)
6. Version compatibility: This skill is based on Doris 3.1.4 grammar. Doris 2.1.x / 3.0.x / 3.1.x / 4.x share the same Nereids grammar for most constructs; minor differences may exist for newer syntax (e.g., 4.x added features). Verify against your cluster's version before relying on specific rules.