Spark SQL & temp views
Databricks · Apache Spark on Databricks
Theory
Spark SQL lets you query DataFrames and tables with SQL. In a notebook you can use a %sql cell or call spark.sql("...") from Python, which returns a DataFrame. To query a DataFrame that is not a table yet, register it as a temporary view: df.createOrReplaceTempView("customers_v"). A temp view is just a name for the DataFrame's plan; it stores no data and lives only in the current Spark session (notebook).
Permanent views (CREATE VIEW main.sales.v ...) are saved in Unity Catalog and can be shared and granted. Global temp views are visible across notebooks on the same classic cluster but are not supported on serverless. For example, a customers DataFrame registered as customers_v can be grouped with SELECT city, COUNT(*) FROM customers_v GROUP BY city, and the result is again a DataFrame you can keep processing in Python.
Why we use it
Many people think in SQL, and some logic (window functions, CASE, complex joins) is clearer in SQL. Temp views let Python and SQL work on the same data inside one notebook without writing a table.
When to use it
Use a temp view to bridge Python and SQL in one notebook. Use CREATE VIEW in Unity Catalog when others must reuse or be granted the logic. Write a table when the result is large or needed later, because a view is recomputed every time.
How it works
- Build a DataFrame in Python
- createOrReplaceTempView gives it a name
- Query it with %sql or spark.sql()
- Must other users or notebooks reuse it? Yes: Create a permanent view or table in Unity Catalog. No: Keep the temp view for this session.
- The view disappears when the session ends
Syntax
df.createOrReplaceTempView("name")
spark.sql("SELECT ... FROM name") -> DataFrame
CREATE OR REPLACE TEMP VIEW name AS SELECT ...
CREATE VIEW main.sales.name AS SELECT ... (permanent, in Unity Catalog)Example
customers = spark.read.table("main.sales.customers")
customers.createOrReplaceTempView("customers_v")
by_city = spark.sql("""
SELECT city, COUNT(*) AS customers
FROM customers_v
GROUP BY city
ORDER BY customers DESC, city
""")
display(by_city)
-- or in a %sql cell
%sql
SELECT city, COUNT(*) AS customers FROM customers_v GROUP BY city;Key points
- A temp view stores no data; it is a named plan for the current session
- spark.sql() returns a DataFrame, so SQL and Python can be chained
- Global temp views are not supported on serverless compute
- Permanent views live in Unity Catalog and can be granted
- CREATE OR REPLACE TEMP VIEW works directly in SQL too