How to Filter and Project Spark DataFrames with PySpark SQL
Simulate a SQL SELECT with WHERE using PySpark DataFrame select and filter to project columns and apply conditions.
Requires third-party packages — install first
pip install pyspark
Python code
23 linesfrom pyspark.sql import SparkSession
from pyspark.sql.functions import col
spark = SparkSession.builder.appName("QueryFilterMock").master("local[2]").getOrCreate()
data = [
("Alice", 28, "Engineering"),
("Bob", 35, "Sales"),
("Carol", 32, "Engineering"),
("David", 25, "Marketing"),
("Eve", 29, "Engineering")
]
df = spark.createDataFrame(data, ["name", "age", "department"])
# Simulate a SQL query: SELECT name, age FROM employees WHERE department = 'Engineering' AND age > 26
filtered_df = df.select("name", "age").filter(
(col("department") == "Engineering") & (col("age") > 26)
)
filtered_df.show()
spark.stop()Output
+-----+---+
| name|age|
+-----+---+
|Alice| 28|
|Carol| 32|
| Eve| 29|
+-----+---+How it works
The select method projects only the specified columns, mimicking a SQL SELECT clause. The filter method applies a boolean condition, akin to WHERE in SQL. Using col("department") references the column by name, and the & operator combines conditions with logical AND. The SparkSession creates an in-memory DataFrame from a list of tuples, providing a quick way to test queries without a cluster.
Common mistakes
- Using `&&` instead of `&` for logical AND in PySpark conditions.
- Forgetting to wrap conditions in parentheses when combining with `&`.
- Calling `show()` before stopping the SparkSession, leaving resources open.
Variations
- Use `df.where()` instead of `filter()` — they are aliases.
- Use SQL string with `spark.sql("SELECT name, age FROM ... WHERE ...")` after creating a temporary view.
Real-world use cases
- Pre-filtering large datasets in an ETL pipeline before writing to a data warehouse.
- Generating user-facing reports that only need specific fields for a subset of records.
- Sampling a slice of data for exploratory analysis or ad-hoc data science work.
Sponsored
More from Big data & Spark
Keep learning
Related tutorials and quizzes for this topic.