Spark SQL Tutorial: Run SQL Queries in PySpark
Spark tutorial · PySpark.in
Spark SQL lets you query PySpark DataFrames with plain SQL: register a DataFrame as a view with createOrReplaceTempView(), run spark.sql("SELECT ..."), and you get a DataFrame back. This Spark SQL tutorial explains how it works and shows each step with runnable examples.
What is Spark SQL?
Spark SQL is Apache Spark's module for working with structured data using SQL syntax. It allows data engineers and analysts to leverage SQL queries on large datasets, all while benefiting from Spark’s distributed computing power and optimization engine. Spark SQL integrates closely with the PySpark DataFrame API – in fact, it runs on the same underlying engine. This means whether you write code in DataFrame operations or in SQL, the performance is identical because both are translated to the same optimized execution plans by Spark’s Catalyst optimizer. In other words, you can choose the syntax (SQL vs. DataFrame) based on what is more readable or convenient for you, without worrying about speed differences.
Why use Spark SQL?
Spark SQL provides a uniform interface for working with structured data. You can think of it as giving Spark a relational database capability on top of its distributed data processing engine. Key features and reasons to use Spark SQL include:
- Familiar SQL Syntax: If you or your team are comfortable with SQL, you can query data in Spark using standard SELECT/WHERE/GROUP BY queries. This lowers the learning curve and makes collaboration between SQL users and Python/Scala developers easier.
- Integrated with DataFrames: The Spark SQL engine powers the DataFrame API. Every DataFrame operation (e.g. filters, joins, aggregations) is ultimately handled by Spark SQL under the hood. Conversely, you can register a DataFrame as a table and run SQL on it. Both approaches share the same optimizer and execution engine.
- Catalyst Optimizer: Spark SQL introduces the Catalyst optimizer, an advanced query optimizer that analyzes and optimizes the logical plan of your queries. Catalyst applies techniques like predicate pushdown, column pruning, and join reordering automatically to improve performance. It generates an efficient physical execution plan for your query, often performing as well as hand-tuned code. Catalyst is at the core of Spark SQL’s performance, using Scala’s functional programming features to build an extensible optimization framework.
Spark's Catalyst Optimizer transforms SQL queries and DataFrame operations through multiple phases before execution. When you run a query, Spark first parses it into an unresolved logical plan and then performs analysis (resolving columns and data types, using the catalog to check table definitions). Next, Catalyst applies logical optimizations – e.g., simplifying expressions, pushing down filters to data sources, and rearranging joins for efficiency. After optimization, Spark creates one or more physical plans (actual strategies to execute the query, such as selecting a join algorithm or using an index), and may compare their estimated costs. Finally, Spark uses code generation to compile parts of the plan into bytecode for efficiency. Catalyst uses a mix of rule-based and cost-based optimization during these stages, all transparently – you usually don’t need to tweak anything to benefit from these optimizations. You can print the plan Catalyst chose for any query with explain(), shown later in this tutorial.
Spark SQL in the PySpark Ecosystem: In PySpark, Spark SQL is accessed via the SparkSession object. The SparkSession is the entry point that combines the Spark Context, SQL Context, and Hive Context . To get started, you need to initialize a SparkSession in your Python environment:
```
from pyspark.sql import SparkSession
# Initialize a SparkSession
spark = SparkSession.builder \
.appName("SparkSQLBasics") \
.getOrCreate()
# Example: creating a DataFrame (this could also be loaded from a file or other source)
data = [("Michael", 29), ("Andy", 30), ("Justin", 19)]
columns = ["name", "age"]
people_df = spark.createDataFrame(data, schema=columns)
people_df.printSchema()
people_df.show()
```
This confirms Spark has recognized the data types. DataFrames in Spark are conceptually like tables: they have named columns and schema. You can perform operations using the DataFrame API (as shown by printSchema, show, filters, etc.), which are optimized by Catalyst under the hood just like SQL queries. In fact, you can seamlessly intermix DataFrame code and SQL: for instance, you could filter with the DataFrame API then use SQL for aggregation, and both will be combined into one optimized plan.
Query Execution: Spark SQL (and DataFrame operations) uses lazy evaluation. When you write transformations (SQL queries or DataFrame methods), Spark builds a logical plan but defers actual computation until an action is called (like show(), collect(), or writing data out). Upon an action, Spark’s Catalyst optimizer steps in (as described above) to optimize the query plan before execution. The work is then parallelized across the cluster’s worker nodes. This approach means that as a user you focus on what data you want, and Spark decides how to efficiently get it. You can inspect the query plan by calling df.explain() or spark.sql("YOUR QUERY").explain(), which will show the physical plan and confirm things like filter pushdowns or join strategies chosen by Catalyst.
In summary, Spark SQL is a powerful way to work with big data using familiar SQL syntax, tightly integrated with PySpark’s DataFrame operations. It plays a pivotal role in the PySpark ecosystem by enabling declarative data analysis (you state what you want via SQL, and Spark figures out the rest) while still achieving high performance through advanced query optimizations. Whether you prefer SQL or Python code, Spark SQL ensures you get the best of both worlds: ease of expression and efficient execution.
Creating Temporary Tables and Views
Overview: In Spark SQL, a temporary view (or temp table) is a way to assign a name to a DataFrame so that you can query it with SQL commands. This is analogous to creating a virtual table in your Spark session. Once you create a temporary view, you can run SQL queries as if the DataFrame were a table in a database. Spark actually supports two kinds of temp views: session-scoped temporary views and global temporary views. It’s important to understand the difference and when to use each.
What is a Temporary View?
A temporary view is essentially a pointer or reference to the DataFrame’s data, accessible by a name. It does not persist data to disk – it’s just a metadata object within Spark. Creating a temp view is straightforward: you call DataFrame.createOrReplaceTempView("view_name"). For example:
```
hide
people_df.createOrReplaceTempView("people")
```
This gives the DataFrame people_df the name "people" for SQL queries. You haven’t duplicated the data; you’ve just created a label for it. Now, you can use spark.sql() to run SQL commands against this view. For instance:
```
from pyspark.sql import SparkSession
# Initialize a SparkSession
spark = SparkSession.builder \
.appName("SparkSQLBasics") \
.getOrCreate()
# Example: creating a DataFrame (this could also be loaded from a file or other source)
data = [("Michael", 29), ("Andy", 30), ("Justin", 19)]
columns = ["name", "age"]
people_df = spark.createDataFrame(data, schema=columns)
# Register the DataFrame as a temporary view (session-scoped)
people_df.createOrReplaceTempView("people")
# Query the temporary view using Spark SQL
result_df = spark.sql("SELECT name, age FROM people WHERE age < 25")
result_df.show()
```
This would select the name and age of all people under 25 from our people view. Under the hood, Spark translates that SQL into the equivalent DataFrame operations on people_df. The ability to register DataFrames as views means you can seamlessly mix SQL and PySpark in your workflow. A view “gives your DataFrame a name that SQL queries can reference, just like a table in a traditional database”. It’s a convenient bridge between the DataFrame API and Spark SQL.
Temporary View vs. Global Temporary View: By default, a temp view like the one above is tied to your SparkSession. This is a session-scoped temporary view. It exists only for the duration of your Spark session (or until you explicitly drop it), and it’s visible only to that SparkSession. If you create a new Spark session or if the current one restarts, the view disappears. Spark also provides global temporary views, which are a bit different:
- A temporary view (session-scoped) is visible only in the SparkSession that created it. It will disappear when that session terminates. Use createOrReplaceTempView("name") for session-scoped views.
- A global temporary view is visible to all SparkSessions in the same Spark application and will remain until the application stops. You create one with DataFrame.createGlobalTempView("name"). Spark places global temp views in a special database called global_temp. To query a global view from any session, you must prefix it with the database name global_temp. For example, SELECT * FROM global_temp.view_name. The view name is shared across sessions.
In code, creating a global view looks like:
```
hide
# Create a global temporary view
people_df.createGlobalTempView("people_global")
#Now the DataFrame’s contents can be queried as global_temp.people_global from any session.
spark.newSession().sql("SELECT COUNT(*) FROM global_temp.people_global").show()
```
Now the DataFrame’s contents can be queried as global_temp.people_global from any session. For instance, if you start a new SparkSession (using spark.newSession()), you could still do it.
This would retrieve data from the global view in a new session (where a normal temp view would not be accessible).
When to use which?
In most cases, you’ll use session-scoped temp views for convenience within a single job or interactive session. They are simple and automatically cleaned up when your session ends. Use createOrReplaceTempView when you just need to run some SQL queries in your current session or notebook. On the other hand, global temp views are useful if you have multiple SparkSessions (for example, in a multi-user environment or if you deliberately create separate sessions) that need to share the same data view. A global view can be accessed from different sessions as long as the Spark application is running. This can be useful in certain long-running applications or if you create a view in one notebook and want to use it in another (within the same Spark application).
However, caution is warranted with global views:
- Because they persist until the Spark application terminates (or until you drop them manually), they can accumulate if not managed. Always drop global views when you no longer need them (spark.sql("DROP VIEW global_temp.view_name")) to free resources.
- In shared clusters, global views are visible to all users on that cluster (since they share the Spark application), which might not be desired. Use them sparingly in multi-tenant environments, and consider naming conventions to avoid collisions. As the Spark documentation notes, if you want a temporary view to be shared among all sessions and kept alive until the application terminates, a global temp view is the mechanism to do that.
To summarize:
- createOrReplaceTempView: Creates a session-scoped temporary table view. Simple to use, auto-dropped with session end. Ideal for most use cases.
- createGlobalTempView: Creates a global temporary view accessible across sessions (in the global_temp database). Use when you explicitly need cross-session sharing. Remember to reference it with the global_temp. prefix in SQL and drop it when done.
Example – Using Temp and Global Views: Suppose we have our people_df DataFrame. Let’s create both a temp view and a global view and see how to use them:
```
from pyspark.sql import SparkSession
# Initialize a SparkSession
spark = SparkSession.builder \
.appName("SparkSQLBasics") \
.getOrCreate()
# Example: creating a DataFrame (this could also be loaded from a file or other source)
data = [("Michael", 29), ("Andy", 30), ("Justin", 19)]
columns = ["name", "age"]
people_df = spark.createDataFrame(data, schema=columns)
people_df.createOrReplaceTempView("people_view") # session-scoped temp view
people_df.createGlobalTempView("people_global_view") # global temp view
# Query the session-scoped view
spark.sql("SELECT COUNT(*) AS count_people FROM people_view").show()
# Query the global view (note the global_temp database)
spark.sql("SELECT COUNT(*) AS count_people FROM global_temp.people_global_view").show()
```
You would see that both queries return the same count of people (3 in our small example). If we were to open a new Spark session and try to query people_view, it would fail (not found), but querying global_temp.people_global_view would still work (as long as the original Spark application is still running). This demonstrates the difference in scope.
spark.sql() vs the DataFrame API
Spark SQL and PySpark are not competitors: spark.sql() and DataFrame methods compile to the same Catalyst plan, so pick whichever reads better. SQL suits analysts and long queries with many joins and aggregations; the DataFrame API suits Python logic, reusable functions and building queries step by step. Because spark.sql() returns a DataFrame, you can mix both in one pipeline. Run this block to see the same result twice and the plan Spark executes:
```python
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum as sum_
spark = SparkSession.builder.getOrCreate()
sales = spark.createDataFrame(
[("North", "Laptop", 1200.0), ("North", "Phone", 650.0),
("South", "Laptop", 1100.0), ("South", "Tablet", 300.0), ("South", "Phone", 700.0)],
["region", "product", "amount"],
)
sales.createOrReplaceTempView("sales")
# 1. Spark SQL: spark.sql() returns a DataFrame
by_region_sql = spark.sql("""
SELECT region, SUM(amount) AS total
FROM sales
WHERE amount >= 500
GROUP BY region
ORDER BY total DESC
""")
by_region_sql.show()
# 2. The same query with the DataFrame API
by_region_df = (sales.filter(col("amount") >= 500)
.groupBy("region")
.agg(sum_("amount").alias("total"))
.orderBy(col("total").desc()))
by_region_df.show()
# Both are planned by Catalyst; explain() prints the physical plan
by_region_sql.explain()
```
Spark SQL FAQ
What does spark.sql() return? A DataFrame. Nothing runs until you call an action such as show(), count() or write, so you can keep transforming the result with DataFrame methods.
Do I need a temporary view to use Spark SQL? Yes for DataFrames: a view gives the DataFrame a name that SQL can reference. Tables saved in a catalog (Hive metastore or Unity Catalog) can be queried directly.
Is Spark SQL faster than the DataFrame API? No. Both are optimised by Catalyst into the same physical plan, so performance is the same for the same logic.
How long does a temporary view last? A view made with createOrReplaceTempView() lasts until the SparkSession ends; a global temporary view lasts until the Spark application stops and is read as global_temp.name.
Next in the PySpark SQL series
- Running SQL queries on DataFrames: filters, joins, aggregations and subqueries with spark.sql().
- Registering UDFs in SQL: call your own Python functions from SQL.
- SQL window functions: ROW_NUMBER, RANK and running totals.
- SQL practice questions: sharpen the SQL you run in Spark.
- Free online PySpark compiler: run every example in your browser.
More Spark tutorials
- Apache Spark Runtime Architecture
- RDD vs DataFrame vs Dataset in Spark: Differences
- Actions vs Transformations
- Lazy Evaluation in PySpark
- DataFrame Operations in PySpark
- Spark SQL Queries in PySpark: spark.sql() Examples
PySpark tutorial: all lessons · All tutorials · Try the free PySpark compiler · Practice challenges