Querying DataFrames with Spark SQL and Temporary Views

1. Why Use SQL on DataFrames?

Before we dive into the "how," let's consider the "why." While the DataFrame API provides a comprehensive set of methods for data manipulation, there are several reasons to use SQL as well:

• Leverage Existing Skills: You already have a foundation in SQL. Spark allows you to apply that knowledge directly, which can speed up development and make your code more intuitive.
• Readability and Conciseness: For certain complex operations, like multi-table joins with intricate logic or nested queries, a single SQL statement can often be more readable than a chain of PySpark method calls.
• Flexibility: You are not forced to choose one or the other. You can create a DataFrame using PySpark, query it with SQL to produce a new DataFrame, and then continue transforming it with the PySpark API.

The key to enabling this is a concept called a temporary view.

2. Creating a Temporary View

A temporary view in Spark is a lightweight pointer or alias to a DataFrame. It is not a separate copy of the data. Once you create a temporary view from a DataFrame, you can refer to it by name in SQL queries just as you would a regular table.

Key characteristics of a temporary view:

• It's temporary: The view exists only for the duration of your current Spark session. If you restart your cluster or start a new notebook, the view will be gone.
• It's a view, not a table: It doesn't store any data itself. It simply provides a name that the Spark SQL engine can use to query the underlying DataFrame.

To create a temporary view, you use the createOrReplaceTempView() method on your DataFrame.

The following video provides an excellent walkthrough of this entire process. You'll see how to create a DataFrame, register it as a temporary view, and then query it using two different methods.

3. The Two Ways to Query: spark.sql() and %sql

As you saw in the video, there are two primary ways to execute SQL queries against a temporary view in a Databricks notebook.

Method 1: The spark.sql() Function

This is the programmatic way. You pass a SQL query as a string to the spark.sql() function. It executes the query and returns the result as a new PySpark DataFrame.

# Assume 'df' is a DataFrame with 'id' and 'name' columns
df.createOrReplaceTempView("employees_view")




# Use spark.sql() to query the view
# The result is a new DataFrame
name_only_df = spark.sql("SELECT name FROM employees_view WHERE id > 1")




# You can now work with this new DataFrame using the PySpark API
name_only_df.show()

Method 2: The %sql Magic Command

This is an interactive feature specific to Databricks notebooks. When you start a cell with %sql, you are telling the notebook to interpret the entire cell as SQL code, not Python. This is very convenient for exploration and analysis.

%sql
SELECT name FROM employees_view WHERE id > 1

The image below visually summarizes this workflow. A DataFrame s is created and then registered as a temporary view named ages. A subsequent cell uses %sql to directly query this view.

4. Advanced SQL and Alternative Functions

The power of this approach is that you can use the full spectrum of SQL, including the subqueries and window functions you're already familiar with. This allows for sophisticated analysis using a syntax you know well.

While createOrReplaceTempView combined with spark.sql is the most common pattern, Spark also provides functions like expr() and selectExpr() to embed SQL expressions directly inside DataFrame API calls. You can learn more about these in the official Databricks documentation.

5. Hands-On Exercise

Now it's time to put this into practice. This exercise is adapted from a problem on the Data Engineer Academy blog.

Setup: Open a new Databricks notebook and run the following code to create a sample DataFrame.

# Sample sales data
sales_data = [
    ("Alice", "2023-01-15", 550.00),
    ("Bob", "2023-01-16", 450.00),
    ("Alice", "2023-01-17", 600.00),
    ("Charlie", "2023-01-18", 300.00),
    ("Bob", "2023-01-19", 800.00)
]
columns = ["employee_name", "sale_date", "sale_amount"]
sales_df = spark.createDataFrame(sales_data, columns)




# Display the DataFrame to verify
display(sales_df)

Step 1: Create a Temporary View
Register the sales_df DataFrame as a temporary view named sales_data_view.

# Your code here
sales_df.createOrReplaceTempView("sales_data_view")
print("Temporary view 'sales_data_view' created successfully.")

Step 2: Query the View with spark.sql()
Use the spark.sql() function to write a query that selects all sales records where the sale_amount is greater than $500. Store the result in a new DataFrame called high_sales_df and display it.

# Your code here
high_sales_df = spark.sql("SELECT * FROM sales_data_view WHERE sale_amount > 500")

display(high_sales_df)

Step 3: Query the View with %sql
In a new cell, use the %sql magic command to find the total sales amount for each employee. Order the results by the total sales in descending order.

-- Your code here in a new cell
%sql
SELECT 
  employee_name,
  SUM(sale_amount) AS total_sales
FROM sales_data_view
GROUP BY employee_name
ORDER BY total_sales DESC

This exercise demonstrates how seamlessly you can switch between the PySpark API and SQL to analyze your data.

Conclusion

Congratulations! You have successfully completed the final lesson in our module on PySpark DataFrame transformations. You are now equipped with two powerful paradigms for data manipulation: the programmatic PySpark API and the declarative Spark SQL engine. Knowing how and when to use each is a key skill for an effective data engineer.

Key Takeaways

• You can leverage existing SQL skills in PySpark by creating temporary views from DataFrames.
• The createOrReplaceTempView("view_name") method registers a DataFrame as a queryable, session-scoped view.
• The spark.sql("...") function executes a SQL query and returns the result as a new DataFrame, allowing you to integrate SQL into your Python code.
• The %sql magic command provides a convenient way to run SQL queries interactively in Databricks notebooks.
• This approach enables you to use the full power of SQL, including aggregations, joins, subqueries, and window functions, directly on your DataFrames.

Next Steps

This lesson concludes our deep dive into data transformation. You now know how to read, transform, and save data. In the next module, we will shift our focus to Incremental Data Ingestion. We'll start by exploring Auto Loader, a powerful Databricks feature for efficiently and incrementally ingesting large numbers of files from cloud storage into your Delta tables, setting the foundation for robust, scalable data pipelines.