SPARK Reference Guide

Revision Time: 6 mins

Spark SQL Reference

Run declarative SQL queries, catalog operations, temporary views, and execution plan analysis directly in PySpark.

createOrReplaceTempView

Return: None

Creates or replaces a session-scoped temporary view bound to the current SparkSession.

Used In: Ad-hoc SQL Querying, Hybrid DataFrame/SQL Workflows

Syntax signature:df.createOrReplaceTempView(name)

Code snippet:

python

df.createOrReplaceTempView('employees')
spark.sql('SELECT department, AVG(salary) FROM employees GROUP BY department').show()

Expected Output:

department	avg(salary)
Engineering	95000.0
Marketing	68000.0

Remember: Temp views are session-scoped and do not register in external metastores (Hive Metastore). They vanish when SparkSession closes.

createGlobalTempView

Return: None

Creates a cross-session global temporary view tied to the system database `global_temp`.

Used In: Multi-session Pipelines, Shared In-memory Datasets

Syntax signature:df.createGlobalTempView(name)

Code snippet:

python

df.createGlobalTempView('global_sales')
# Query from any session using global_temp qualification:
spark.sql('SELECT SUM(amount) FROM global_temp.global_sales').show()

Expected Output:

sum(amount
1500000.0

Remember: Global temp views survive across multiple SparkSession instances within the same Spark application lifetime.

spark.sql

Return: DataFrame

Executes an inline SQL query string and returns the output as a PySpark DataFrame.

Used In: Complex Analytical Queries, CTEs, Window Functions, Subqueries

Syntax signature:spark.sql(sqlQuery)

Code snippet:

python

res = spark.sql('''
  WITH RankedEmps AS (
    SELECT emp_id, name, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
    FROM employees
  )
  SELECT * FROM RankedEmps WHERE rnk <= 3
''')

Expected Output:

emp_id	name	department	salary	rnk
101	Alice	Engineering	120000	1
105	Bob	Engineering	110000	2

Remember: Spark SQL queries undergo identical Catalyst optimization (logical plan, physical plan, code generation) as DataFrame API calls.

spark.catalog.listTables

Return: list[Table]

Inspects and lists all registered tables/views in the current database catalog.

Used In: Catalog Audits, Dynamic Pipeline Automation

Syntax signature:spark.catalog.listTables(dbName=None)

Code snippet:

python

tables = spark.catalog.listTables()
for t in tables:
    print(t.name, t.isTemporary, t.tableType)

Expected Output:

employees True TEMPORARY
global_sales True TEMPORARY

Remember: Useful for programmatic metadata audits and verifying if a table is cached or temporary.

explain

Return: None

Prints the Catalyst execution plans (Parsed, Analyzed, Optimized, Physical Plan) to console.

Used In: Performance Tuning, Bottleneck Diagnosis, Join Debugging

Syntax signature:df.explain(extended=True) # or mode='formatted' / 'cost'

Code snippet:

python

spark.sql('SELECT * FROM employees WHERE salary > 80000').explain(mode='formatted')

Expected Output:

== Physical Plan ==
*(1) Filter (salary#5 > 80000)
+- *(1) Scan ExistingRDD[emp_id#4,name#5,salary#6]

Remember: Crucial for identifying WholeStageCodegen `*(1)`, BroadcastHashJoin vs SortMergeJoin, and file scan filter pushdowns.

spark.catalog.uncacheTable

Return: None

Removes a cached table or view from Spark executor memory and disk storage.

Used In: Memory Management, Resource Reclamation

Syntax signature:spark.catalog.uncacheTable(tableName)

Code snippet:

python

spark.catalog.cacheTable('employees')
# After query completes:
spark.catalog.uncacheTable('employees')

Expected Output:Table uncached from memory

Remember: Prevents memory leaks in long-running Spark applications (e.g. streaming or Databricks notebooks).

Previous TopicRDD API Reference Next TopicRead & Write APIs Reference