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

  1. Build a DataFrame in Python
  2. createOrReplaceTempView gives it a name
  3. Query it with %sql or spark.sql()
  4. 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.
  5. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

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

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ 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? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic