Spark SQL Temporary Views: createOrReplaceTempView and Global Temp Views

1.From DataFrame to SQL: registering a local temporary view

A DataFrame built in Python has no name that SQL can see. Calling spark.sql("SELECT * FROM people") only works once something called people exists in the catalog. The usual way to give a DataFrame that name is createOrReplaceTempView(name), which "creates or replaces a local temporary view with this DataFrame." It takes one argument, name, a string naming the view.

After that, spark.sql runs the query and returns a DataFrame with the result. You can move freely between the two APIs: build a DataFrame, register it, query it with SQL, and keep working on the result as a DataFrame.

df = spark.createDataFrame([(2, "Alice"), (5, "Bob")], schema=["age", "name"])
df.createOrReplaceTempView("people")

df2 = df.filter(df.age > 3)
df2.createOrReplaceTempView("people")
df3 = spark.sql("SELECT * FROM people")
assert sorted(df3.collect()) == sorted(df2.collect())

spark.catalog.dropTempView("people")
# True

Notice what the assertion proves. The second createOrReplaceTempView("people") call points the name at df2, the filtered DataFrame, so the SQL query returns only Bob's row. The view is a name that refers to a DataFrame, not a copy of the data taken at registration time, and re-registering the name moves it to a different DataFrame.

The word *local* is about scope. The view's lifetime "is tied to the SparkSession that was used to create this DataFrame." The SQL reference describes the same behaviour from the SQL side: temporary views "are visible only to the session that created them and are dropped when the session ends." Nothing is written to storage, and another session can't see the name.

Sources12

2.createTempView vs createOrReplaceTempView: what happens on a name clash

Spark has two methods for registering a local view, and the only difference between them is what happens on a name clash. createTempView(name) "creates a local temporary view with this DataFrame" and has the same session-tied lifetime, but it refuses to overwrite: it "throws TempTableAlreadyExistsException, if the view name already exists in the catalog." createOrReplaceTempView(name) replaces the existing view instead.

This is why createOrReplaceTempView is the common choice in notebooks. If you re-run a cell that calls createTempView, it fails the second time. If you re-run one that calls createOrReplaceTempView, it just re-points the name. To reuse a name with createTempView, drop the view first, which is exactly what the documentation example does.

df.createTempView("people")  # doctest: +IGNORE_EXCEPTION_DETAIL
# Traceback (most recent call last):
# ...
# AnalysisException: "Temporary table 'people' already exists;"

spark.catalog.dropTempView("people")
# True
df.createTempView("people")

Sources3

3.Global temporary views and the global_temp schema

A local view belongs to one SparkSession. To share a view across sessions in the same application, you register a *global* temporary view with createGlobalTempView(name) or createOrReplaceGlobalTempView(name). Its lifetime "is tied to this Spark application" rather than to a single session.

The naming difference matters on the exam. Global temporary views "are tied to a system preserved temporary schema global_temp", so you query them with that prefix: global_temp.people, not people. The create/replace split works exactly as it does for local views. createGlobalTempView throws TempTableAlreadyExistsException if the name already exists, and createOrReplaceGlobalTempView replaces it. Global views also have their own drop call, spark.catalog.dropGlobalTempView.

df = spark.createDataFrame([(2, "Alice"), (5, "Bob")], schema=["age", "name"])
df.createOrReplaceGlobalTempView("people")

df2 = df.filter(df.age > 3)
df2.createOrReplaceGlobalTempView("people")
df3 = spark.table("global_temp.people")
sorted(df3.collect()) == sorted(df2.collect())
# True

df = spark.createDataFrame([(2, "Alice"), (5, "Bob")], schema=["age", "name"])
df.createGlobalTempView("people")
df2 = spark.sql("SELECT * FROM  ? .people")

Sources425

4.Querying views with spark.sql and spark.table, and dropping them

Once a view is registered, there are two ways to read it. spark.sql(sqlQuery) "returns a DataFrame representing the result of the given query", so the view can appear anywhere a table name can, including joins, filters and aggregations. spark.table(name) returns the view directly as a DataFrame, without any SQL. The documentation uses both: spark.sql("SELECT * FROM global_temp.people") for one global view and spark.table("global_temp.people") for the other. The example below combines two local views with the DataFrame API.

df1 = spark.createDataFrame([(1, "John"), (2, "Jane")], schema=["id", "name"])
df2 = spark.createDataFrame([(3, "Jake"), (4, "Jill")], schema=["id", "name"])
df1.createTempView("table1")
df2.createTempView("table2")
result_df = spark.table("table1").union(spark.table("table2"))

*When* the view name gets resolved depends on the deployment mode. The spark.sql reference states that "in Spark Classic, a temporary view referenced in spark.sql is resolved immediately." Under Spark Connect, the same reference is analysed lazily, so if a view is dropped, modified, or replaced after spark.sql, the execution may fail or generate different results.

To clean up, use the catalog. spark.catalog.dropTempView("people") removes a local view and spark.catalog.dropGlobalTempView("people") removes a global one. In the documentation examples, both return True when they drop the view.

Sources6

5.The SQL equivalent: CREATE TEMPORARY VIEW

The DataFrame methods have a SQL counterpart. The CREATE VIEW statement accepts TEMPORARY and GLOBAL TEMPORARY modifiers, so you can register views from SQL with the same local and global scopes.

-- Query-backed or metric view
CREATE [ OR REPLACE ] [ [ GLOBAL ] TEMPORARY ] VIEW [ IF NOT EXISTS ] view_name

The clauses line up with the DataFrame methods. OR REPLACE plays the role of createOrReplace...: "CREATE OR REPLACE VIEW view_name is equivalent to DROP VIEW IF EXISTS view_name followed by CREATE VIEW view_name." SQL also has an option the DataFrame API doesn't offer. With IF NOT EXISTS, if a view by this name already exists the CREATE VIEW statement is ignored, so nothing is raised and nothing is replaced. You may specify at most one of IF NOT EXISTS or OR REPLACE.

One naming rule applies to every temporary view: "A temporary view's name must not be qualified." You write CREATE TEMPORARY VIEW people, not CREATE TEMPORARY VIEW db.people. Global views are still read back through global_temp.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.createTempView quietly replaces a view that already has the same name.Why is that wrong?

   Only the createOrReplace... methods replace an existing view. createTempView and createGlobalTempView raise TempTableAlreadyExistsException, which appears in PySpark as an AnalysisException, when the name is already taken.

   Covered in createTempView vs createOrReplaceTempView: what happens on a name clash

2. 2.After createGlobalTempView("people"), you can query it with SELECT * FROM people.Why is that wrong?

   Global temporary views are registered in the system-preserved global_temp schema, so they have to be referenced as global_temp.people.

   Covered in Global temporary views and the global_temp schema

3. 3.A view created with createOrReplaceTempView stays available to later sessions, like a table.Why is that wrong?

   A local temporary view is visible only to the session that created it and is dropped when that session ends. Nothing is persisted.

   Covered in From DataFrame to SQL: registering a local temporary view

4. 4.Global temporary views are the portable choice and work on every Databricks compute type.Why is that wrong?

   Global temp views are not supported on Databricks serverless compute. Session-scoped views created with createOrReplaceTempView are the recommended alternative there.

   Covered in Global temporary views and the global_temp schema

Practise it for real

Register a DataFrame as a local temporary view, replace it, query it with SQL, and drop it

1. 1.Run df = spark.createDataFrame([(2, "Alice"), (5, "Bob")], schema=["age", "name"]) and then df.createOrReplaceTempView("people").

   Why: This registers the DataFrame under a name that SQL can resolve.

   You should see: No output. spark.sql("SELECT * FROM people") now returns both rows.

2. 2.Run df2 = df.filter(df.age > 3) followed by df2.createOrReplaceTempView("people").

   Why: This shows that createOrReplaceTempView re-points an existing name instead of failing.

   You should see: No error, even though the name people already existed.

3. 3.Run df3 = spark.sql("SELECT * FROM people") and compare sorted(df3.collect()) with sorted(df2.collect()).

   Why: This confirms the SQL query now reads the filtered DataFrame.

   You should see: The two lists are equal and contain only Bob's row.

4. 4.Run df.createTempView("people").

   Why: This shows that createTempView refuses to overwrite an existing view.

   You should see: An AnalysisException saying the temporary table 'people' already exists.

5. 5.Run spark.catalog.dropTempView("people").

   Why: This removes the local view from the session's catalog.

   You should see: Returns True.