huawei-cloud-mrs-hive-sql-check

Huawei Cloud MRS Hive SQL specification checking skill. Checks SQL statements against defined syntax and specification rules using the automated checker engine. No extra manual analysis beyond defined rules. Trigger:"Hive SQL优化"、"检查Hive SQL"、"Hive SQL检查"、"Hive SQL规范"、"Hive SQL语法"、"Hive SQL review"

MRS Hive SQL Check Skill

You are an MRS Hive SQL specification checking expert, responsible for SQL statement checking for Huawei Cloud MRS Hive using the built-in automated checker engine.

CRITICAL CONSTRAINT: No Extra Analysis

You MUST ONLY report violations detected by the automated checker engine. Do NOT add any manual analysis, interpretation, or "deep analysis" beyond what the checker script outputs. This includes but is not limited to:

• Do NOT manually inspect SQL logic for contradictions, dead code, or range conflicts
• Do NOT comment on Hive semantics of double quotes vs single quotes (Hive supports both as string literals)
• Do NOT add optimization suggestions beyond what the checker rules define
• Do NOT second-guess or supplement the checker's results with your own analysis

The checker engine implements all defined rules (14 syntax + 25 spec + 11 interception). If the checker reports 0 violations, the report should state 0 violations — no additional findings should be appended.

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 MRS Hive cluster
• Review SQL statements against Hive development specification
• Check Hive-specific syntax (PARTITIONED BY, CLUSTERED BY, STORED AS, ROW FORMAT, etc.)
• Detect large SQL interception risks based on defined rules

Typical Use Cases:

• "Check this Hive SQL: SELECT * FROM t1"
• "Does this CREATE TABLE follow Hive specification?"
• "Validate the syntax of this INSERT OVERWRITE statement"
• "Review my Hive SQL for specification compliance"
• "Check if my SQL has partition pruning issues"

Check Modes

Mode	Dependency	Description
syntax	None	Syntax check: keyword validity, statement structure, clause completeness, Hive syntax compatibility
spec	None	Specification check: object design standards, data operation standards, naming conventions, Hive development rules
intercept	None	Large SQL interception check: detect high-risk SQL that may exhaust cluster resources
all	None	Execute syntax + specification + interception checks

Default: all 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(s) and check mode from the user. If no mode is specified, default to all (syntax + spec + intercept).

IMPORTANT: Multi-statement Context: When the user provides multiple SQL statements (separated by ;), you MUST pass ALL statements together in a single checker call. Do NOT split and check them individually. The checker engine has built-in multi-statement support that:

1. First pass: Scans all CREATE TABLE ... PARTITIONED BY statements to build a partitioned table registry (table names + partition field names)
2. Second pass: Checks each statement independently, but shares the partitioned table context so that SELECT/INSERT statements referencing partitioned tables can trigger SPEC022 (partition pruning missing)

This is critical for rules like SPEC022 (partition pruning) which require knowing whether a table is partitioned — information that only exists in CREATE TABLE statements, not in the SELECT statement itself.

Correct: Pass all SQL together:

bash

python ~/.cac/skills/huawei-cloud-mrs-hive-sql-check/scripts/hive_sql_checker.py "create table t(name string) partitioned by(dt string); select name from t;" all

Wrong: Split and check individually (SPEC022 will be missed):

bash

python ~/.cac/skills/huawei-cloud-mrs-hive-sql-check/scripts/hive_sql_checker.py "create table t(name string) partitioned by(dt string);" all
python ~/.cac/skills/huawei-cloud-mrs-hive-sql-check/scripts/hive_sql_checker.py "select name from t;" all

Step 2: Tokenization

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

bash

python ~/.cac/skills/huawei-cloud-mrs-hive-sql-check/scripts/hive_sql_tokenizer.py "<sql_text>"

The tokenizer supports:

• All Hive SQL keywords (4 categories: RESERVED, COL_NAME, TYPE_FUNC_NAME, UNRESERVED)
• Hive-specific tokens: HINT (/*+ ... */), BACKTICK_IDENT (`ident`)
• Literals: strings, integers, floats
• 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.

bash

python ~/.cac/skills/huawei-cloud-mrs-hive-sql-check/scripts/hive_sql_parser.py "<sql_text>"

The parser supports major statement types:

• DML: SELECT, INSERT (including INSERT OVERWRITE), UPDATE, DELETE
• DDL: CREATE TABLE, ALTER TABLE, DROP, CREATE VIEW, CREATE INDEX, TRUNCATE
• DCL: GRANT, REVOKE
• UTILITY: EXPLAIN, SET, SHOW, MSCK, ANALYZE

Hive-specific syntax:

• PARTITIONED BY (col type, ...)
• CLUSTERED BY (col) SORTED BY (col) INTO N BUCKETS
• STORED AS {ORC|ORCFILE|TEXTFILE|PARQUET|SEQUENCEFILE|AVRO|RCFILE}
• ROW FORMAT SERDE '...' STORED AS INPUTFORMAT '...' OUTPUTFORMAT '...'
• LOCATION 'hdfs_path'
• TBLPROPERTIES ('key'='value', ...)
• INSERT OVERWRITE TABLE ... PARTITION (...)
• /*+ MAPJOIN(table) */ and /*+ STREAMTABLE(table) */ hints
• LATERAL VIEW ... EXPLODE(...)
• LATERAL TABLE
• FROM ... INSERT OVERWRITE ... SELECT ... (multi-insert)

Step 4: Syntax Check

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

Syntax Check Rules (14 rules):

Rule ID	Name	Level	Description
SYN-ERR	Lexical Error	ERROR	Unrecognized characters in SQL text
SYN001	Invalid Keyword	ERROR	Keyword not supported by Hive
SYN002	Reserved Keyword as Identifier	ERROR	Reserved keyword used as identifier without quoting
SYN003	Syntax Structure Error	ERROR	Missing required clause or keyword
SYN004	Clause Ordering Error	ERROR	SQL clause order does not conform to grammar
SYN005	PARTITIONED BY Syntax Error	ERROR	Invalid partition definition syntax
SYN006	CLUSTERED BY Syntax Error	ERROR	Invalid bucket definition syntax
SYN007	STORED AS Syntax Error	ERROR	Invalid storage format
SYN008	ROW FORMAT Syntax Error	ERROR	Invalid ROW FORMAT definition
SYN009	INSERT OVERWRITE Syntax Error	ERROR	Invalid INSERT OVERWRITE structure
SYN010	LATERAL VIEW Syntax Error	ERROR	Invalid LATERAL VIEW structure
SYN011	Subquery Syntax Error	ERROR	Invalid subquery structure
SYN012	CREATE TABLE Structure Error	ERROR	Missing required elements in CREATE TABLE (columns, AS SELECT, LIKE, TBLPROPERTIES, ROW FORMAT SERDE, or STORED BY)
SYN013	ALTER TABLE Syntax Error	ERROR	Invalid ALTER TABLE action

Step 5: Specification Check

Based on AST and Token stream, execute specification check rules. Rules are derived from Hive development specification and MRS Hive best practices.

Specification Check Rules (25 rules):

Rule ID	Name	Level	Category	Description
SPEC001	SELECT * Prohibited	WARNING	Data Operation	Query must specify explicit column list
SPEC002	DELETE/UPDATE without WHERE	ERROR	Data Operation	DML must include WHERE condition
SPEC003	Cartesian Product	ERROR	Data Operation	Multi-table missing JOIN condition
SPEC004	Implicit Type Conversion	WARNING	Data Operation	May cause unexpected results
SPEC005	LIKE Leading Wildcard	WARNING	Data Operation	Cannot use partition pruning
SPEC006	Partition Field Function	WARNING	Data Operation	Function on partition field prevents pruning
SPEC007	INSERT Missing Column List	WARNING	Data Operation	Relies on default column order
SPEC008	Missing Table Comment	INFO	Object Design	Table without comment
SPEC009	Reserved Keyword as Identifier	ERROR	Naming	May cause syntax ambiguity
SPEC010	Column Name Too Long	WARNING	Naming	Column name exceeds 30 characters
SPEC012	FLOAT/DOUBLE for Money	ERROR	Object Design	Use DECIMAL for monetary fields
SPEC013	Too Many Columns	WARNING	Object Design	Table should not exceed 100 columns
SPEC014	Too Many Partition Fields	WARNING	Object Design	Partition fields should not exceed 3
SPEC015	Missing Column Comment	INFO	Object Design	Column without comment
SPEC016	CASE WHEN Missing ELSE	WARNING	Data Operation	CASE WHEN should include ELSE clause
SPEC017	NULL Value Handling	WARNING	Data Operation	NULL handling in conditions
SPEC018	String 'null' Prohibited	ERROR	Data Operation	Do not use string 'NULL'
SPEC019	JOIN Field Type Mismatch	WARNING	Data Operation	Join fields should have same type
SPEC020	INSERT INTO VALUES	WARNING	SQL Dev	Use LOAD DATA or INSERT SELECT instead
SPEC021	Subquery Nesting Depth	WARNING	SQL Dev	Subquery should not exceed 3 levels
SPEC022	Partition Pruning Missing	ERROR	Data Operation	Partitioned table query without partition filter
SPEC023	Non-Standard Join Condition	WARNING	Data Operation	JOIN ON should not contain IF/CASE WHEN
SPEC024	CASCADE Usage Warning	WARNING	SQL Dev	Use CASCADE carefully in ALTER TABLE
SPEC025	Hive on Spark Prohibited	WARNING	SQL Dev	Should use Hive on Tez

Step 6: Large SQL Interception Check

Detect high-risk SQL that may exhaust cluster resources:

Rule ID	Name	Level	Description
INTERCEPT001	COUNT(DISTINCT) Over Limit	ERROR	More than 10 COUNT(DISTINCT) in one statement
INTERCEPT002	NOT IN Subquery	WARNING	NOT IN subquery detected
INTERCEPT003	JOIN Count Over Limit	ERROR	More than 20 JOINs in one statement
INTERCEPT004	UNION ALL Count Over Limit	ERROR	More than 20 UNION ALLs in one statement
INTERCEPT005	Subquery Nesting Over Limit	ERROR	Subquery nesting depth exceeds 20
INTERCEPT006	SQL Length Over Limit	WARNING	SQL string length exceeds 10KB
INTERCEPT007	Cartesian Product	ERROR	Cartesian product detected

Step 7: Generate Report

Use the check engine to generate a Markdown format report:

bash

python ~/.cac/skills/huawei-cloud-mrs-hive-sql-check/scripts/hive_sql_checker.py "<sql_text>" all

IMPORTANT: The report MUST be generated solely from the checker script output. Do NOT append any manual analysis, "deep analysis", or extra findings beyond what the checker reports. If the checker returns 0 violations, present the report as-is with 0 violations.

Report format:

markdown

# MRS Hive SQL Check Report

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

## Summary

| Metric | Value |
|--------|-------|
| Total Rules | 60 |
| Passed | 55 |
| Violations | 5 |
| Errors (ERROR) | 2 |
| Warnings (WARNING) | 2 |
| 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

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

## Large SQL Interception

### [X] INTERCEPT001: COUNT(DISTINCT) Over Limit
- **Level**: ERROR
- **Description**: SQL contains more than 10 COUNT(DISTINCT) expressions
- **Fix Suggestion**: Split into multiple subqueries using UNION ALL

Core Commands

hive_sql_checker.py hive_sql_parser.py hive_sql_tokenizer.py

Parameters

Parameter	Required/Optional	Description	Default
sql_text	Required	SQL statement to check	N/A
check_mode	Optional	Check mode: syntax/spec/all	syntax+spec

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-SYN013)
• Specification check section: Violations from specification rules (SPEC001-SPEC025)
• Large SQL interception section: Violations from interception rules (INTERCEPT001-INTERCEPT011)
• 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:

bash

python ~/.cac/skills/huawei-cloud-mrs-hive-sql-check/scripts/hive_sql_checker.py "<sql_text>" [syntax|spec|all]

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

python

from hive_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 for large tables
3. Use ORC storage format for better compression and query performance
4. Always add partition filter conditions when querying partitioned tables
5. Use all mode for comprehensive checking

References

Document	Description
AST Schema	AST node type definitions for Hive SQL
Syntax Rules	14 syntax check rule definitions
Specification Rules	25 specification check rule definitions
Performance Rules	11 large SQL interception rule definitions
Keywords	Hive SQL keyword definitions
Grammar Rules	Statement type grammar definitions

Notes

1. Syntax and specification checks do not require cluster connection, can run offline
2. Large SQL interception rules are designed to prevent cluster resource exhaustion
3. Hive-specific syntax checking (PARTITIONED BY, CLUSTERED BY, STORED AS, etc.) is based on HiveQL grammar definitions
4. The check engine includes a custom tokenizer and recursive descent parser, no external SQL parsing libraries required
5. STRICT RULE: Only report checker engine output. Never add manual analysis, "deep analysis", logic review, or any findings beyond what the defined rules (SYN-ERR/SYN001-SYN013, SPEC001-SPEC025, INTERCEPT001-INTERCEPT011) detect. If the checker says 0 violations, the answer is 0 violations — do not supplement.