The selectExpr() function in Spark is used to select columns from a DataFrame using SQL expressions. It allows you to write SQL-like expressions directly within the DataFrame API, making it a powerful tool for performing complex transformations and calculations. This function is particularly useful when you want to leverage SQL syntax for column selection and manipulation.

​
1. Syntax

PySpark:

df.selectExpr(*exprs)

Spark SQL:

  • There is no direct equivalent in Spark SQL, but you can use SELECT with SQL expressions.

​
2. Parameters

  • exprs: A list of SQL expressions (as strings) to select or compute columns.

​
3. Key Features

  • SQL Expressions: Allows you to use SQL-like expressions for column selection and transformation.
  • Flexibility: Supports complex expressions, including arithmetic operations, string manipulations, and conditional logic.
  • Integration: Combines the power of SQL with the DataFrame API.

​
4. Examples

​
Example 1: Selecting Columns with SQL Expressions

PySpark:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("SelectExprExample").getOrCreate()

# Create DataFrame
data = [("Anand", 25, 3000), ("Bala", 30, 4000), ("Kavitha", 28, 3500), ("Raj", 35, 4500)]
columns = ["Name", "Age", "Salary"]

df = spark.createDataFrame(data, columns)

# Select columns using SQL expressions
df.selectExpr("Name", "Age", "Salary", "Salary * 0.1 as Bonus").show()

Output:

+-------+---+------+-----+
|   Name|Age|Salary|Bonus|
+-------+---+------+-----+
|  Anand| 25|  3000|300.0|
|   Bala| 30|  4000|400.0|
|Kavitha| 28|  3500|350.0|
|    Raj| 35|  4500|450.0|
+-------+---+------+-----+

​
Example 2: Using Conditional Logic in SQL Expressions

PySpark:

# Select columns with conditional logic
df.selectExpr("Name", "Age", "Salary", "CASE WHEN Age > 30 THEN 'Senior' ELSE 'Junior' END as Level").show()

Output:

+-------+---+------+------+
|   Name|Age|Salary| Level|
+-------+---+------+------+
|  Anand| 25|  3000|Junior|
|   Bala| 30|  4000|Junior|
|Kavitha| 28|  3500|Junior|
|    Raj| 35|  4500|Senior|
+-------+---+------+------+

​
Example 3: Using String Functions in SQL Expressions

PySpark:

# Select columns with string functions
df.selectExpr("Name", "Age", "Salary", "UPPER(Name) as NameUpper").show()

Output:

+-------+---+------+---------+
|   Name|Age|Salary|NameUpper|
+-------+---+------+---------+
|  Anand| 25|  3000|    ANAND|
|   Bala| 30|  4000|     BALA|
|Kavitha| 28|  3500|  KAVITHA|
|    Raj| 35|  4500|      RAJ|
+-------+---+------+---------+

​
Example 4: Using Aggregate Functions in SQL Expressions

PySpark:

# Select columns with aggregate functions
df.selectExpr("AVG(Salary) as AvgSalary", "MAX(Salary) as MaxSalary").show()

Output:

+---------+---------+
|AvgSalary|MaxSalary|
+---------+---------+
|   3750.0|     4500|
+---------+---------+

​
Example 5: Using Date Functions in SQL Expressions

PySpark:

from pyspark.sql.functions import current_date

# Add a current date column
df = df.withColumn("CurrentDate", current_date())

# Select columns with date functions
df.selectExpr("Name", "Age", "Salary", "YEAR(CurrentDate) as Year").show()

Output:

+-------+---+------+----+
|   Name|Age|Salary|Year|
+-------+---+------+----+
|  Anand| 25|  3000|2023|
|   Bala| 30|  4000|2023|
|Kavitha| 28|  3500|2023|
|    Raj| 35|  4500|2023|
+-------+---+------+----+

​
Example 6: Using Window Functions in SQL Expressions

PySpark:

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number

# Define a window specification
window_spec = Window.partitionBy("Age").orderBy("Salary")

# Select columns with window functions
df.selectExpr("Name", "Age", "Salary", "ROW_NUMBER() OVER (PARTITION BY Age ORDER BY Salary) as RowNumber").show()

Output:

+-------+---+------+---------+
|   Name|Age|Salary|RowNumber|
+-------+---+------+---------+
|  Anand| 25|  3000|        1|
|   Bala| 30|  4000|        1|
|Kavitha| 28|  3500|        1|
|    Raj| 35|  4500|        1|
+-------+---+------+---------+

​
Example 7: Using Nested SQL Expressions

PySpark:

# Select columns with nested SQL expressions
df.selectExpr("Name", "Age", "Salary", "(Salary * 0.1) + (Age * 10) as Total").show()

Output:

+-------+---+------+-----+
|   Name|Age|Salary|Total|
+-------+---+------+-----+
|  Anand| 25|  3000|550.0|
|   Bala| 30|  4000|700.0|
|Kavitha| 28|  3500|630.0|
|    Raj| 35|  4500|800.0|
+-------+---+------+-----+

​
5. Common Use Cases

  • Performing arithmetic operations on columns.
  • Applying conditional logic to create new columns.
  • Using string functions for text manipulation.
  • Computing aggregate statistics.

​
6. Performance Considerations

  • selectExpr() is efficient for large datasets as it processes SQL expressions in a distributed manner.
  • Use it judiciously for very wide DataFrames (many columns), as it processes all specified expressions.

​
7. Key Takeaways

  1. The selectExpr() function is used to select columns or compute new columns using SQL expressions.
  2. Allows you to use SQL-like syntax for column selection and transformation.