How to Boost Spark SQL DAG Efficiency with Regex‑Driven Temporary Views

Background

In modern enterprise data platforms, Spark SQL is the core tool for data processing and analysis, while a scheduling system ensures orderly execution. Mainstream big‑data schedulers (Airflow, Azkaban, DolphinScheduler, etc.) organize tasks as DAGs, but static DAGs become a bottleneck as business complexity grows.

Current Situation

In the company’s risk‑control domain, thousands of offline tasks generate many intermediate tables and redundant data copies. Typical problems include:

Proliferation of intermediate tables, wasting storage.

Overly long task chains, increasing scheduling overhead.

Complex dependencies due to many intermediate tables.

Low execution efficiency caused by frequent I/O and context switches.

These wide‑table join tasks are ideal candidates for regex‑based SQL processing.

Technical Approach to SQL Task Optimization

1. Use session‑level temporary views

Spark SQL can register query results as logical temporary views without materializing them, reducing I/O, simplifying dependencies, and improving execution speed.

Reduce I/O overhead by avoiding persistent intermediate results.

Simplify dependencies by replacing physical tables with logical views.

Increase efficiency by executing multiple joins within the same Spark session.

2. Apply regex matching in SQL parsing

Regex can be used to:

Identify table dependencies.

Rewrite SQL to replace physical tables with temporary views.

Detect sequences of tasks that can be merged.

3. Technical feasibility

Regex works well because most SQL dialects have relatively fixed syntax patterns. It can handle:

Pattern matching such as ^INSERT for insert statements.

Fuzzy matching like FROM .*\s.

Extraction of WHERE clauses for later view creation.

Regex is portable across Spark SQL, Hive SQL, Presto SQL, and can be used in Python, Java, or Scala on any OS.

4. Intermediate table handling

Intermediate tables used by only one downstream task can be turned into temporary views, eliminating unnecessary materialization.

Target Analysis

The diagram below illustrates a typical DAG in an enterprise big‑data platform, with root_node, workflow, start_node, end_node, and task nodes (Spark SQL or Hive SQL).

When multiple tasks (task_1‑task_4) produce tables used only by a final task (task_4), they can be merged into a single Spark job, reducing I/O and simplifying the DAG.

Implementation Steps

Obtain workflow metadata (task list and adjacency table) via platform APIs.

Perform topological sorting on the adjacency table to get execution order.

Fetch task metadata, including SQL scripts, parameters, and input/output tables.

Identify intermediate and final output tables.

Normalize SQL: format scripts with sqlparse, split by semicolons, and standardize parameter names.

Use regex to replace intermediate table INSERT statements with session‑level temporary view creation.

Replace FROM clauses that reference intermediate tables with the new temporary view names.

Parameterize output table names (e.g., replace schema with a placeholder).

Remove SET statements that are not needed after merging.

Combine the reordered SQL statements into a single script for execution.

import sqlparse
stmt_list = sqlparse.parse(sql_script)
format_stmt_list = []
for stmt in stmt_list:
    stmt = sqlparse.format(stmt,
        keyword_case='upper',
        reindent_aligned=True,
        use_space_around_operators=True,
        strip_comments=True)
    format_stmt_list.append(stmt)

import re
pattern = re.compile(rf"INSERT (INTO |OVERWRITE )?(TABLE )?({'|'.join(mid_table_set)})(PARTITION)?\s?((.*=?.*\))?", re.IGNORECASE)
sql_stmt = re.sub(pattern, replace, sql_stmt)

Practical Results

In the Smart Decision Platform 4.0 – FeatureStore project, 921 task chains (average depth 13) were optimized. After applying the tool, task‑restructuring speed increased roughly fourfold compared to manual effort.

Conclusion

The article demonstrates how to replace intermediate tables with session‑level temporary views, merge multiple Spark SQL scripts, and produce a single, normalized script. This approach enables large‑scale, systematic governance of task chains, improves maintainability, and facilitates automated testing and further optimization.