MRS Spark SQL Check Skill

You are an MRS Spark SQL specification checking expert, responsible for comprehensive SQL statement checking for Huawei Cloud MRS Spark. You have a custom-built Spark SQL tokenizer and recursive descent parser that can precisely identify Spark-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 MRS Spark cluster
• Review SQL statements against Spark SQL development specification
• Check Spark-specific syntax (USING, OPTIONS, CACHE TABLE, CREATE TEMP VIEW, etc.)
• Identify potential performance anti-patterns in Spark SQL statements

Typical Use Cases:

• "Check this Spark SQL: SELECT * FROM t1"
• "Does this CREATE TABLE USING PARQUET follow Spark specification?"
• "Validate the syntax of this INSERT OVERWRITE statement"
• "Review my Spark SQL for specification compliance"

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-spark-sql-check/scripts/spark_sql_tokenizer.py "<sql_text>"

The tokenizer supports:

• All Spark SQL keywords (4 categories: RESERVED, COL_NAME, TYPE_FUNC_NAME, UNRESERVED)
• Spark-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.

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

The parser supports major statement types:

• DML: SELECT, INSERT (including INSERT OVERWRITE), UPDATE, DELETE, MERGE
• DDL: CREATE TABLE (with USING/OPTIONS), ALTER TABLE, DROP, CREATE VIEW/TEMP VIEW/GLOBAL TEMP VIEW, TRUNCATE
• DCL: GRANT, REVOKE
• UTILITY: EXPLAIN, SET, SHOW, DESCRIBE, ANALYZE TABLE
• Spark-specific: CACHE TABLE, UNCACHE TABLE, CLEAR CACHE, REFRESH TABLE/FUNCTION, ADD JAR, LIST JAR, RESET

Spark-specific syntax:

• CREATE TABLE ... USING {parquet|orc|json|csv|...} [OPTIONS (...)]
• CACHE [LAZY] TABLE table_name [AS SELECT ...]
• CREATE [OR REPLACE] [GLOBAL] TEMP [MATERIALIZED] VIEW
• REFRESH TABLE table_name / REFRESH FUNCTION func_name
• ADD JAR /path/to/file.jar
• /*+ BROADCAST(table) */ and /*+ COALESCE(N) */ hints
• LATERAL VIEW ... EXPLODE(...)
• PARTITIONED BY (col_name) (Spark-style, column names only)

Step 4: Syntax Check

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

Syntax Check Rules (20 rules):

Step 5: Specification Check

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

Specification Check Rules (29 rules):

Step 6: Generate Report

Use the check engine to generate a Markdown format report:

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

Report format:

# MRS Spark SQL Check Report

**Check Time**: yyyy-mm-ddThh:mm:ss
**Statement Type**: SELECT
**Check Mode**: all

## Summary

| Metric | Value |
|--------|-------|
| Total Rules | 56 |
| Passed | 51 |
| 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

### [!] SPEC001: 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

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-SYN020)
• Specification check section: Violations from specification rules (SPEC001-SPEC029)
• Large SQL interception section: Violations from interception rules (INTERCEPT001-INTERCEPT007)
• Original SQL: The checked SQL statement

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

Core Commands

spark_sql_checker.py spark_sql_parser.py spark_sql_tokenizer.py

Best Practices

1. Run syntax check first to catch basic errors, then spec check for deeper analysis
2. For CREATE TABLE statements, prefer USING parquet over STORED AS PARQUET
3. Use ORC or Parquet 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
6. Use /*+ BROADCAST(small_table) */ hint for small-large table joins

References

Notes

1. Syntax and specification checks do not require cluster connection, can run offline
2. Spark-specific syntax checking (USING, OPTIONS, CACHE TABLE, etc.) is based on Spark SQL grammar definitions
3. The check engine includes a custom tokenizer and recursive descent parser, no external SQL parsing libraries required
4. Spark SQL is derived from HiveQL; Hive-compatible syntax (STORED AS, CLUSTERED BY, ROW FORMAT) is also supported