Temporary Views in Spark

1. What Are Views and Why Do We Use Them?

Before diving into temporary views, let's establish what a view is in general. At its core, a view is a stored query that you can interact with as if it were a table. It's a "virtual table" that doesn't physically store data. Instead, it runs its underlying query against the source tables every time you access it, ensuring the data is always up-to-date.

Views are powerful because they allow you to encapsulate complex logic. Imagine a query that joins several tables, applies filters, and calculates new columns. Instead of re-writing that logic every time you need it, you can save it as a view and simply query the view with a SELECT statement. This modularizes your code and promotes reusability.

To see a practical example of how a view can simplify a complex query, let's watch a video.

As you saw, the v_sales_info view hides a significant amount of logic, allowing the analyst to simply query v_sales_info without needing to worry about the underlying joins or calculations. This is the primary benefit of using views.

2. Temporary Views: The Data Engineer's Scratchpad

The views in the video were standard, persistent views. They are saved in the database schema (metastore) and are accessible to anyone with the right permissions until they are explicitly dropped.

However, in data engineering pipelines, we often need to create intermediate transformations that are only relevant for the duration of our current script or notebook session. We don't want to clutter the metastore with these temporary objects. This is where temporary views come in.

A temporary view in Databricks has two key characteristics:

1. Session-Scoped: It only exists for your current Spark session. If you detach the notebook from the cluster or the cluster restarts, the temporary view is gone.
2. Not Persistent: It is not registered in the metastore (e.g., Unity Catalog or Hive Metastore), so it won't be visible to other users or in other notebooks.

This makes them the perfect tool for breaking down a complex transformation pipeline into logical, sequential steps within a single notebook or job.

This article provides an excellent summary and comparison.

3. Creating and Using Temporary Views

You can create temporary views using both SQL and the PySpark DataFrame API.

Using SQL

The syntax is very similar to creating a standard view, but you add the TEMPORARY keyword.

CREATE OR REPLACE TEMPORARY VIEW view_name AS
SELECT ...
FROM ...
WHERE ...;

Using PySpark

If you have a DataFrame, you can register it as a temporary view directly. This is a very common pattern when you are mixing Python and SQL code in a notebook.

my_dataframe.createOrReplaceTempView("view_name")

Once the view is created using either method, you can immediately query it using Spark SQL, even in a different cell of the same notebook.

The following video demonstrates the creation of standard, temporary, and a third type, global temporary views.

4. Hands-On Practice: Modularizing a Transformation

Let's apply these concepts. In this exercise, we'll simulate a common data engineering task: cleaning and preparing raw data for analysis. Your physics background might make this scenario familiar—we'll work with simulated data from a particle detector.

Open a new Databricks SQL notebook and follow these steps.

Step 1: Set Up Raw Data
First, let's create a table with some raw event data. We'll use a VALUES clause for simplicity.

CREATE OR REPLACE TABLE raw_detector_events (
  event_id INT,
  particle_id INT,
  energy_gev DOUBLE,
  detector_segment STRING,
  is_noise BOOLEAN
);

INSERT INTO raw_detector_events VALUES
  (1001, 11, 5.4, 'seg_A1', false),
  (1002, 22, 102.5, 'seg_B3', false),
  (1003, 99, 0.1, 'seg_A1', true),
  (1004, 13, 17.2, 'seg_C2', false),
  (1005, 22, 99.8, 'seg_B4', false),
  (1006, 99, 0.2, 'seg_C2', true);

Step 2: Create a "Cleaned" Temporary View
Our raw data has noise and uses numerical IDs for particles. Our first transformation step will be to filter out noise and map the particle IDs to meaningful names.

Create a temporary view that performs these cleaning steps.

CREATE OR REPLACE TEMPORARY VIEW cleaned_events_view AS
SELECT
  event_id,
  energy_gev,
  detector_segment,
  CASE particle_id
    WHEN 11 THEN 'electron'
    WHEN 13 THEN 'muon'
    WHEN 22 THEN 'photon'
    ELSE 'unknown'
  END AS particle_name
FROM raw_detector_events
WHERE is_noise = false;

Step 3: Query the Temporary View
Verify that your view works by querying it. It should return only the clean events with the particle_name column.

SELECT * FROM cleaned_events_view;

You should see 4 rows of clean data.

Step 4: Chain Views for Further Aggregation (Challenge)
Temporary views are powerful because you can build on them. Create a second temporary view that uses the cleaned_events_view to calculate the average energy for each particle type.

CREATE OR REPLACE TEMPORARY VIEW avg_energy_by_particle_view AS
SELECT
  particle_name,
  COUNT(*) AS event_count,
  AVG(energy_gev) AS average_energy_gev
FROM cleaned_events_view
GROUP BY particle_name;

Now, query your final aggregated view:

SELECT * FROM avg_energy_by_particle_view
ORDER BY average_energy_gev DESC;

This demonstrates a simple, two-step pipeline using temporary views. Each view has a clear, singular purpose, making the logic easy to understand and test.

5. A Note on Global Temporary Views

Databricks also supports global temporary views.

• Temporary View: Visible only within the notebook/session where it was created.
• Global Temporary View: Visible to all notebooks attached to the same cluster.

They are created using CREATE GLOBAL TEMPORARY VIEW. To query them, you must use the special global_temp schema prefix: SELECT * FROM global_temp.my_global_view;.

While less common than session-scoped temporary views, they are useful for sharing temporary data between different jobs or notebooks running concurrently on the same cluster, without making the data permanent.

Conclusion

In this lesson, you've learned how to leverage temporary views to bring structure and clarity to your data transformation logic. This is a foundational skill for building robust and maintainable ELT pipelines in Databricks.

Key Takeaways

• Views are named, virtual tables based on a SELECT query. They don't store data but provide a way to reuse complex logic.
• Temporary views are session-scoped and are not saved to the metastore. They are automatically dropped when the Spark session ends.
• They are ideal for breaking down complex transformations into a series of logical, named steps within a single data pipeline or notebook.
• You can create them using CREATE TEMPORARY VIEW in SQL or the .createOrReplaceTempView() method on a DataFrame in PySpark.

Next Steps

Temporary views are excellent for modularizing logic across an entire session. But what if you only need a temporary result set for a single, complex query? In our next lesson, we will cover Common Table Expressions (CTEs). You'll learn how to use the WITH clause to define temporary, named result sets that exist only for the duration of one query, further enhancing the readability of your SQL code.