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

  1. Have a DataFrame
  2. createOrReplaceTempView('orders')
  3. spark.sql('SELECT ... FROM orders ...')
  4. Spark parses and analyzes the SQL against the view's plan
  5. Catalyst optimizes it like DataFrame code
  6. 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

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic