Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 48 ยท DataFrames and Spark SQL

Spark SQL and Temp Views

Query Spark DataFrames with plain SQL: register temp views, call spark.sql, use the catalog and CTEs, compare SQL with the DataFrame API and learn ANSI mode.

Many people who work with data already know SQL, and Spark respects that. Anything you can do with the DataFrame API you can also write as a SQL query, and you can mix the two in the same program. A DataFrame becomes a table-like name that SQL can see, you run a query with spark.sql(...), and you get a DataFrame back.

This lesson explains temp views, the spark.sql entry point, the catalog, common table expressions, how SQL and the DataFrame API relate (they compile to the same plans), and the ANSI mode setting that changes how errors behave. It continues from Reading and Writing Data.

The real Spark SQL engine cannot run in a browser, so the Spark code below is read-only. The runnable examples use Python's built-in SQLite as a stand-in: SQL dialects differ, but the ideas (views, catalogs, CTEs) carry over.

Temp views: giving a DataFrame a SQL name

A DataFrame is a Python object. SQL cannot see Python variables, so you register the DataFrame under a name:

Python
orders = spark.read.parquet("/data/warehouse/orders/")

orders.createOrReplaceTempView("orders")

top = spark.sql("""
    SELECT country, SUM(amount) AS revenue
    FROM orders
    WHERE amount > 0
    GROUP BY country
    ORDER BY revenue DESC
""")
top.show()

A temp view stores no data. It is just a name attached to the DataFrame's logical plan. When you query it, Spark substitutes the plan, so nothing is copied and no computation happens until an action runs (top is lazy like every other DataFrame; see Lazy Evaluation, Transformations and Actions).

There are several kinds of views:

Call Scope Lifetime
createTempView(name) One SparkSession Until the session ends; error if the name exists
createOrReplaceTempView(name) One SparkSession Same, but replaces an existing view
createGlobalTempView(name) All sessions of one application Until the application ends; use the global_temp. prefix
CREATE VIEW / saveAsTable A catalog (such as Hive metastore) Permanent, survives restarts

Most programs use createOrReplaceTempView, because it is safe to rerun. df.write.saveAsTable("sales.orders") writes the data and registers a permanent table in the catalog, so other applications can use it.

The catalog

The catalog is Spark's directory of databases, tables, views and functions. You can query it from SQL or from spark.catalog:

Python
spark.sql("SHOW TABLES").show()
spark.sql("DESCRIBE orders").show()
spark.sql("SHOW DATABASES").show()

spark.catalog.listTables()
spark.catalog.listColumns("orders")
spark.catalog.currentDatabase()
spark.catalog.dropTempView("orders")

By default, Spark uses an in-memory catalog that forgets everything when the session ends. With Hive support enabled (.enableHiveSupport()), or with table formats such as Delta Lake and Iceberg, the catalog is persistent. Names have up to three parts: catalog.database.table, with spark_catalog.default.orders being the full name of a table in the default catalog.

SQLite keeps something similar in its sqlite_master table. The stand-in below registers a "DataFrame" (a pandas table) as a view and inspects the catalog.

Python Views and a catalog with SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common table expressions and subqueries

Long queries become readable when you name the steps. A common table expression (CTE) starts with WITH:

Python
spark.sql("""
    WITH paid AS (
        SELECT * FROM orders WHERE status = 'paid'
    ),
    per_customer AS (
        SELECT customer_id, SUM(amount) AS total, COUNT(*) AS n
        FROM paid
        GROUP BY customer_id
    )
    SELECT customer_id, total
    FROM per_customer
    WHERE n >= 3
    ORDER BY total DESC
""")

Each CTE is like a temporary view that exists for one statement. Spark may inline a CTE where it is used, so a CTE referenced twice can be computed twice; it is not a cache. Other familiar features work as well: JOIN variants, UNION, CASE WHEN, subqueries in FROM and WHERE, IN/EXISTS, window functions (Window Functions) and most standard functions. Spark has many extras that are not in standard SQL, such as LATERAL VIEW explode(...), PIVOT, TABLESAMPLE, and higher-order functions on arrays.

SQL or the DataFrame API?

They are two front ends for one engine. Both are parsed into the same kind of logical plan, optimized by Catalyst, and executed identically. The same query, written both ways:

Python
# SQL
spark.sql("SELECT country, SUM(amount) AS revenue FROM orders GROUP BY country")

# DataFrame API
orders.groupBy("country").agg(F.sum("amount").alias("revenue"))

explain() on both prints the same physical plan (see Catalyst Optimizer and Query Plans). So the choice is about people and code structure, not speed:

  • SQL is great for analysts, long queries with CTEs, and reuse of existing queries. Its weakness: it is a string, so typos show up only at run time, and it is awkward to build dynamically.
  • The DataFrame API is great for programmatic pipelines, reusable functions, loops over column names, and IDE help. Its weakness: it can be harder to read for big multi-step queries.

Many teams mix them: SQL for the shape of the query, DataFrame code for generated parts. The functions expr("...") and selectExpr("...") let you drop SQL snippets into the DataFrame API, which you saw in Columns and Expressions. Do not build SQL strings by pasting user input into them; that is a SQL injection risk. Newer Spark versions accept named parameters (spark.sql("... WHERE amount > :min", args={"min": 10})) for exactly this reason.

flowchart LR
    S["spark.sql(...)"] --> P["Parser"]
    D["DataFrame API calls"] --> L["Logical plan"]
    P --> L
    L --> O["Catalyst optimizer"]
    O --> X["Physical plan and execution"]

SQL text and DataFrame code meet in the same logical plan.

The next stand-in runs one question through three layers: a plain SQL query, a CTE version and the equivalent step-by-step Python (like the DataFrame API). All three must agree.

Python SQL, a CTE and the DataFrame way agree

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

ANSI mode: what happens on bad data

Spark has a setting, spark.sql.ansi.enabled, that decides how strictly SQL rules are applied. It changes what happens in cases such as:

Situation ANSI off (older default) ANSI on
1 / 0 NULL Error: divide by zero
CAST('abc' AS INT) NULL Error: invalid input
Integer overflow Silently wraps around Error: overflow
Invalid array index NULL Error

Spark 4.0 turned ANSI mode on by default; Spark 3.x had it off. Check the version you run. Off mode is forgiving: bad values become nulls and the job finishes, which can hide data problems. On mode fails loudly, which is usually what you want for correctness. Even with ANSI on, you can ask for forgiveness explicitly with try_cast, try_divide, try_add and similar try_ functions, which return null instead of failing. Set the mode per session:

Python
spark.conf.set("spark.sql.ansi.enabled", "true")
spark.sql("SELECT try_divide(10, 0)").show()      # null, no error

The stand-in shows the two behaviors side by side. SQLite itself behaves like "ANSI off" for division.

Python Forgiving versus strict (ANSI) division

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Real versus stand-in. Real Spark SQL parses your query into a logical plan, optimizes it with Catalyst and runs it on a cluster, where views are plan names and the catalog can be a Hive metastore. SQLite is a single-process engine with a slightly different dialect (no LATERAL VIEW, different functions), and its sqlite_master table only imitates the catalog. The principle is the same: a view stores no data, CTEs name steps, and SQL and code give the same result.

Common mistakes

  • Querying a DataFrame name in spark.sql without registering a view first.
  • Thinking a temp view caches data. It does not; use cache() (see Caching and Persistence).
  • Using createTempView in a notebook cell and getting "already exists" on the second run; use createOrReplaceTempView.
  • Pasting user input into SQL strings instead of using parameters.
  • Expecting the same results with ANSI on and off for casts and division, and being surprised after a Spark upgrade.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does df.createOrReplaceTempView('t') do?

    A temp view is only a name for the logical plan. It stores no data and lives as long as the SparkSession.
  2. 2. How do spark.sql('SELECT ... GROUP BY ...') and the equivalent DataFrame groupBy compare in performance?

    Both front ends produce a logical plan that Catalyst optimizes; the physical plans are the same for the same query.
  3. 3. A CTE is referenced twice in a query. What can you assume?

    A CTE is a named subquery. Use cache() or a temp table if you need to materialize a result.
  4. 4. With ANSI mode on, what does SELECT CAST('abc' AS INT) do?

    ANSI mode raises an error for invalid casts. Use try_cast to get null instead.
  5. 5. Which call makes a view visible to other sessions of the same application?

    Global temp views live in the special global_temp database and are shared across sessions until the application ends.

Summary

  • createOrReplaceTempView gives a DataFrame a SQL name; a view holds no data and is lazy like any DataFrame.
  • spark.sql returns a DataFrame; the catalog lists databases, tables, views and functions, and is only persistent with a metastore or table format.
  • CTEs name query steps for readability; they are not caches.
  • SQL and the DataFrame API share one optimizer, so pick by readability and tooling, and never paste raw input into SQL strings.
  • ANSI mode makes invalid casts, overflow and division by zero fail instead of returning null; try_ functions opt back into null.

Next: combine tables in Joins.