huawei-cloud-mrs-hetu-sql-check

HetuEngine SQL Check Skill

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

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 HetuEngine cluster
• Review SQL statements against HetuEngine development best practices
• Check HetuEngine-specific syntax (PARTITIONED BY, CLUSTERED BY, STORED AS, TABLESAMPLE, etc.)
• Identify potential performance anti-patterns in SQL statements
• Check Hive-compatible syntax (ROW FORMAT, TBLPROPERTIES, INSERT OVERWRITE)

Typical Use Cases:

• "Check this HetuEngine SQL: SELECT * FROM t1"
• "Does this CREATE TABLE follow HetuEngine specification?"
• "Validate the syntax of this INSERT OVERWRITE statement"
• "Review my SQL for HetuEngine specification compliance"
• "Check if my SQL uses PARTITIONED BY correctly"

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-mrs-hetu-sql-check/scripts/hetu_sql_tokenizer.py ""

The tokenizer supports:

• All 400+ HetuEngine keywords (4 categories: RESERVED, COL_NAME, TYPE_FUNC_NAME, UNRESERVED)
• HetuEngine-specific tokens: TYPECAST (::), HINT (/*+ ... */ - limited), ARROW (->), DOUBLE_ARROW (->>)
• Backtick-quoted identifiers: identifier (Hive compatibility)
• Dollar-quoted strings: $$...$$ or $tag$...$tag$ (for Python UDF)
• Lambda expressions: x -> x + 1
• Literals: strings, integers, floats, bit strings, hex strings
• Parameter references: $1, $2...
• Unicode strings: U&'...'
• E-strings: E'...'
• National character strings: N'...'
• Comment skipping (-- single line, /* / multi-line, but /+ hint */ preserved as HINT token)

Step 3: Parsing

Run the parser to generate AST and detect syntax errors.

python ~/.cac/skills/huawei-cloud-mrs-hetu-sql-check/scripts/hetu_sql_parser.py ""

The parser supports major statement types:

• DML: SELECT, INSERT, UPDATE, DELETE, LOAD
• DDL: CREATE TABLE, CREATE TABLE AS, CREATE TABLE LIKE, ALTER TABLE, DROP, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE FUNCTION, CREATE SCHEMA, TRUNCATE
• TCL: START TRANSACTION, COMMIT, ROLLBACK
• UTILITY: EXPLAIN, SHOW, DESCRIBE, USE, SET, RESET, CALL, REFRESH MATERIALIZED VIEW

HetuEngine-specific syntax:

• PARTITIONED BY (col_name data_type, ...)
• CLUSTERED BY (col, ...) [SORTED BY (col, ...) INTO n BUCKETS]
• ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
• STORED AS {ORC|PARQUET|AVRO|RCBINARY|RCTEXT|SEQUENCEFILE|JSON|TEXTFILE|TEXTFILE_MULTIDELIM|CSV}
• TBLPROPERTIES (key=value, ...)
• INSERT OVERWRITE [TABLE] table_name (without INTO keyword)
• LOAD DATA INPATH filepath [OVERWRITE] INTO TABLE tablename [PARTITION(...)]
• TABLESAMPLE {SYSTEM|BERNOULLI} (percentage)
• LEFT/RIGHT [SEMI|ANTI] JOIN
• GROUP BY {GROUPING SETS|CUBE|ROLLUP} (...)
• FETCH {FIRST|NEXT} [count] {ROW|ROWS} {ONLY|WITH TIES}
• ORDER BY expression [ASC|DESC] [NULLS {FIRST|LAST}]
• WITH RECURSIVE cte_name AS (subquery)
• MATCH_RECOGNIZE pattern_recognition_specification
• CREATE VIRTUAL SCHEMA [IF NOT EXISTS] schema_name WITH (catalog=ctlg_name, schema=schm_name)
• CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name [WITH (need_auto_refresh=true, mv_validity=...)] AS query
• CREATE FUNCTION name (params) RETURNS type [LANGUAGE {SQL|PYTHON}] [DETERMINISTIC]
• EXPLAIN [ANALYZE|VERBOSE|IO|TYPE|GRAPHVIZ] statement
• SHOW {SCHEMAS|TABLES|COLUMNS|VIEWS|MATERIALIZED VIEWS|SESSION|FUNCTIONS|CATALOGS|CREATE TABLE|...}
• USE catalog_name.schema_name
• CALL procedure_name(arguments)
• REFRESH MATERIALIZED VIEW view_name
• Oracle (+) outer join (NOT supported, use standard JOIN)

Step 4: Syntax Check

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

Syntax Check Rules (19 rules):

Step 5: Specification Check

Based on AST and Token stream, execute specification check rules.

Specification Check Rules (34 rules):

Step 6: Generate Report

Use the check engine to generate a Markdown format report:

python ~/.cac/skills/huawei-cloud-mrs-hetu-sql-check/scripts/hetu_sql_checker.py "" all

Report format:

# HetuEngine SQL 检查报告

**检查时间**: 2026-07-15T10:00:00
**语句类型**: SELECT
**检查模式**: all

## 检查概要

| 指标 | 值 |
|------|------|
| 检查规则数 | 53 |
| 通过 | 50 |
| 违规 | 3 |
| 错误 (ERROR) | 1 |
| 警告 (WARNING) | 1 |
| 提示 (INFO) | 1 |

## 语法检查

### [X] SYN003: 语法结构错误
- **级别**: ERROR
- **位置**: 行 1, 列 15
- **描述**: 缺少 FROM 子句
- **修复建议**: 添加 FROM table_name

## 规范检查

### [!] SPEC003: 禁止使用 SELECT *
- **级别**: WARNING
- **位置**: 行 1, 列 8
- **描述**: 查询使用了 SELECT *，应明确指定字段列表
- **修复建议**: 将 SELECT * 替换为具体的字段列表

Core Commands

hetu_sql_checker.py hetu_sql_parser.py hetu_sql_tokenizer.py

Parameters

Output Format

The check report is output in Markdown format, containing:

• 检查概要: Total rules, passed, violations by level
• 语法检查: Violations from syntax rules (SYN-ERR, SYN001-SYN019)
• 规范检查: Violations from specification rules (SPEC001-SPEC034)
• 原始 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-mrs-hetu-sql-check/scripts/hetu_sql_checker.py "" [syntax|spec|all]

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

from hetu_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 PARTITIONED BY and CLUSTERED BY for large tables to avoid SPEC001 and SPEC018
3. Use all mode for comprehensive checking
4. HetuEngine uses PARTITIONED BY (Hive-compatible) instead of PARTITION BY (PostgreSQL-style)
5. HetuEngine uses bucketed_by + bucket_count properties instead of DISTRIBUTE BY
6. Rules marked with requires_cluster: true (SPEC030) need cluster connection and are skipped in static mode
7. HetuEngine supports backtick-quoted identifiers (identifier) for Hive compatibility
8. HetuEngine INSERT OVERWRITE does not require INTO keyword (unlike DWS)
9. HetuEngine supports SEMI JOIN and ANTI JOIN (not supported in standard SQL)

References

Notes

1. Syntax and specification checks do not require cluster connection, can run offline
2. Rules marked "Requires cluster" (SPEC030) are skipped in static mode
3. HetuEngine is based on Presto/Trino with Hive compatibility, NOT PostgreSQL/openGauss like DWS
4. HetuEngine-specific syntax checking (PARTITIONED BY, CLUSTERED BY, STORED AS, TABLESAMPLE, etc.) is based on Presto/Trino grammar definitions
5. The check engine includes a custom tokenizer and recursive descent parser, no external SQL parsing libraries required
6. HetuEngine does NOT support: DISTRIBUTE BY, Oracle (+) outer join, TIMECAPSULE, Optimizer Hints (/*+ ... */ limited support)
7. HetuEngine DOES support: Virtual Schema, Materialized View with auto-refresh, CREATE FUNCTION (SQL/Python UDF), MATCH_RECOGNIZE, SEMI/ANTI JOIN, TABLESAMPLE, GROUPING SETS/CUBE/ROLLUP