Temp views & spark.sql
Apache Spark · Spark SQL
Theory
spark.sql(query) runs a SQL query and returns the result as a DataFrame, so you can mix SQL and Python freely. To query a DataFrame by name, register it as a view: df.createOrReplaceTempView('orders') creates a session-scoped temporary view that disappears when the SparkSession ends and is visible only in that session. df.createOrReplaceGlobalTempView('orders') registers it in the special global_temp database, visible to all sessions of the same application until it stops. Views store no data; each query re-runs the DataFrame's plan.
spark.sql also accepts parameters: named markers like :min_amount with args={'min_amount': 20000} let you pass values safely instead of building SQL strings with f-strings. Permanent tables live in a catalog (Hive metastore, Unity Catalog or another) and are queried the same way. For example, after registering orders and customers as views, spark.sql('SELECT c.city, SUM(o.amount) ... GROUP BY c.city') returns sales per city as a normal DataFrame you can .show() or write.
Why we use it
Many data engineers and analysts think in SQL. Temp views let you use SQL on any DataFrame without creating permanent tables, and the result is still a DataFrame, so SQL and Python steps fit in the same pipeline.
When to use it
Use temp views for intermediate steps in notebooks and jobs, global temp views to share between sessions of one application, and catalog tables for data other jobs or users need. Use args parameters instead of string formatting for values coming from users or configs.
How it works
- Have a DataFrame
- createOrReplaceTempView('orders')
- spark.sql('SELECT ... FROM orders ...')
- Spark parses and analyzes the SQL against the view's plan
- Catalyst optimizes it like DataFrame code
- Result returned as a DataFrame
Syntax
df.createOrReplaceTempView("orders")
spark.sql("SELECT * FROM orders WHERE amount > :min", args={"min": 20000})
df.createOrReplaceGlobalTempView("orders") # SELECT * FROM global_temp.orders
spark.catalog.listTables(); spark.catalog.dropTempView("orders")Example
from pyspark.sql import SparkSession, functions as F
spark = (SparkSession.builder.master("local[1]")
.config("spark.ui.enabled", "false")
.getOrCreate())
orders = spark.createDataFrame([
(101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
(102, 2, "West", "Phone", 2, 30000.0, "2026-01-06"),
(103, 1, "North", "Mouse", 3, 1500.0, "2026-01-06"),
(104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
(105, 4, "West", "Tablet", 1, 25000.0, "2026-01-08"),
(106, 2, "East", "Phone", 1, 15000.0, "2026-01-08"),
(107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
(108, 3, "South", "Mouse", 4, 2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
"qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))
customers = spark.createDataFrame([
(1, "Asha", "Jaipur", "Retail"),
(2, "Ravi", "Pune", "Corporate"),
(3, "Meera", "Delhi", "Retail"),
(4, "Karan", "Jaipur", "SMB"),
(5, "Priya", "Mumbai", "Corporate"),
(6, "Vikram", "Bengaluru", "SMB"),
], "customer_id INT, name STRING, city STRING, segment STRING")
orders.createOrReplaceTempView("orders")
customers.createOrReplaceTempView("customers")
city_sales = spark.sql("""
SELECT c.city, COUNT(*) AS orders, SUM(o.amount) AS sales
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city
ORDER BY sales DESC
""")
city_sales.show() # a normal DataFrame
print(type(city_sales).__name__)
# Named parameters instead of f-strings
spark.sql("SELECT order_id, amount FROM orders WHERE amount >= :min ORDER BY order_id",
args={"min": 50000}).show()
customers.createOrReplaceGlobalTempView("customers_g")
print(spark.newSession().sql("SELECT COUNT(*) AS n FROM global_temp.customers_g").first().n)
print(sorted(t.name for t in spark.catalog.listTables()))
# Output:
# +------+------+-------+
# | city|orders| sales|
# +------+------+-------+
# |Jaipur| 3|81500.0|
# | Delhi| 2|54000.0|
# |Mumbai| 1|48000.0|
# | Pune| 2|45000.0|
# +------+------+-------+
#
# DataFrame
# +--------+-------+
# |order_id| amount|
# +--------+-------+
# | 101|55000.0|
# | 104|52000.0|
# +--------+-------+
#
# 6
# ['customers', 'orders']
Key points
- spark.sql() returns a DataFrame; mix SQL and DataFrame code freely
- Temp view: session-scoped, no data stored, re-runs the plan on every query
- Global temp view: shared across sessions of one app, query as global_temp.name
- Use args={...} with :name markers instead of f-strings to avoid SQL injection
- createOrReplaceTempView replaces silently; createTempView fails if the name exists