Spark SQL Views

One of Spark's greatest strengths is its unified interface. By registering a DataFrame as a Temporary View, you can write native, industry-standard ANSI SQL queries to process data at massive scale. Spark translates your SQL queries into the exact same optimized physical plans generated by the DataFrame DSL.

Local Temporary Views vs. Global Temporary Views

Spark provides two levels of temporary views depending on their session visibility:

PySpark Code Example: Querying via Spark SQL

Here is a complete script registering a local view and running standard SQL aggregations:

from pyspark.sql import SparkSession

# 1. Setup Spark
spark = SparkSession.builder \
    .appName("Spark SQL Views") \
    .master("local[*]") \
    .getOrCreate()

# 2. Sample data
employees_data = [
    (1, "Alice", 90000, "Engineering"),
    (2, "Bob", 60000, "Marketing"),
    (3, "Charlie", 95000, "Engineering"),
    (4, "David", 50000, "Sales")
]
columns = ["id", "name", "salary", "department"]
df = spark.createDataFrame(employees_data, columns)

# 3. Register local temporary view
df.createOrReplaceTempView("employees")

# 4. Query data using native SQL syntax
# We will select high earners in the Engineering department
sql_query_a = """
    SELECT name, salary 
    FROM employees 
    WHERE department = 'Engineering' AND salary > 80000
"""
result_a = spark.sql(sql_query_a)
result_a.show()

# 5. Run SQL aggregation (Average salary per department)
sql_query_b = """
    SELECT department, ROUND(AVG(salary), 2) as avg_salary, COUNT(id) as head_count
    FROM employees
    GROUP BY department
    ORDER BY avg_salary DESC
"""
result_b = spark.sql(sql_query_b)
result_b.show()

Under the Hood: Unified Optimization

Whether you write:

• df.filter(col("salary") > 80000).select("name") (DataFrame DSL)
• SELECT name FROM employees WHERE salary > 80000 (Spark SQL)

The Catalyst Optimizer parses both statements into the exact same internal AST (Abstract Syntax Tree) logical plan, compiles them into the same physical plan, and executes them with the same speed! You can mix and match SQL and DSL statements inside the same pipeline seamlessly:

# Query with SQL and continue transforming with DataFrame DSL!
high_earners_df = spark.sql("SELECT * FROM employees WHERE salary > 70000")
final_df = high_earners_df.withColumn("bonus", high_earners_df["salary"] * 0.10)